New to SQL? Learn How to Utilize Create, Read, Update, Delete (CRUD)

If you’re new to the world of databases and SQL (Structured Query Language), you might have heard the term CRUD before. CRUD stands for Create, Read, Update, and Delete, and these are the fundamental operations that you’ll use to interact with your data in a database.

In this blog, we’ll walk you through each of these CRUD operations, providing simple and real-world examples to help you grasp the concepts quickly. To make it even more relatable, we’ll use an inventory management scenario throughout our discussion.

What is CRUD?

CRUD represents four basic actions you can perform on data in a database:

1. Create: This operation is used to add new records to a database.
2. Read: It involves retrieving data from the database.
3. Update: You can modify existing records using this operation.
4. Delete: As the name suggests, it’s used to remove data from the database.

Now, let’s dive into each of these CRUD operations with SQL examples and relate them to inventory management.

1. Create (C)

In an inventory management system, creating new records would involve adding new products or items to your database. Here’s an SQL statement to insert a new product:

-- SQL Query to Create a New Product
INSERT INTO products (product_name, price, quantity)
VALUES ('Laptop', 899.99, 10);

In this example:

• products is the name of the table where we want to insert data.
• product_name, price, and quantity are the columns we want to populate with data.
• We’re creating a new product with the name ‘Laptop,’ a price of $899.99, and a quantity of 10.

Note: You can try out all the SQL’s in W3 school spaces.

2. Read (R)

Reading data is the process of retrieving information from your database. In inventory management, you might want to retrieve the list of products. Here’s an SQL query to do that:

-- SQL Query to Read Products
SELECT * FROM products;

In this query:

• SELECT is used to specify which columns we want to retrieve. In this case, * means all columns.
• FROM specifies the table we want to retrieve data from, which is products.

3. Update (U)

Updating records allows you to modify information about existing products. Let’s say you want to change the price of a laptop:

-- SQL Query to Update Product Price
UPDATE products
SET price = 799.99
WHERE product_name = 'Laptop';

Here:

• UPDATE specifies the table we want to update, which is products.
• SET indicates the column we want to modify, which is price, and the new value is set to $799.99.
• WHERE specifies which record(s) to update. In this case, we’re updating the product with the name ‘Laptop.’

4. Delete (D)

Deleting records removes unwanted data from your database. If you decide to discontinue a product, you can use the following SQL statement to delete it:

-- SQL Query to Delete a Product
DELETE FROM products
WHERE product_name = 'Discontinued Product';

In this query:

• DELETE FROM specifies the table from which we want to delete data, which is products.
• WHERE is used to specify the condition that must be met for the record to be deleted. In this case, we’re deleting the product with the name ‘Discontinued Product.’

Real-Life Scenario: Inventory Management

Imagine you run an online electronics store. Your inventory management database keeps track of all the products you sell. Using CRUD operations, you can manage your inventory efficiently:

• Create: Add new products as you expand your inventory.
• Read: Retrieve information about products to display on your website, such as product details, prices, and availability.
• Update: Adjust product quantities as they are sold or restocked. Update prices and other product details as needed.
• Delete: Remove discontinued or no longer available products from your database.

By understanding and using CRUD operations, you can keep your inventory up to date, provide accurate product information to your customers, and run a successful online business.

Commonly Used SQL Clauses

Certainly! In addition to the CRUD operations, SQL queries often include various clauses to refine and manipulate data. Here are some commonly used SQL clauses:

1. GROUP BY: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions like SUM, AVG, COUNT, etc. In the context of inventory management, you might use it to group products by category and calculate the total quantity in each category:

   -- SQL Query to Group Products by Category and Calculate Total Quantity
   SELECT category, SUM(quantity) as total_quantity
   FROM products
   GROUP BY category;

2. ORDER BY: The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order based on one or more columns. For example, you can retrieve products sorted by price in descending order:

   -- SQL Query to Retrieve Products Sorted by Price (Highest to Lowest)
   SELECT * FROM products
   ORDER BY price DESC;

3. HAVING: The HAVING clause is used in conjunction with GROUP BY to filter grouped rows based on a specified condition. For instance, if you want to find product categories with a total quantity greater than 50:

   -- SQL Query to Find Categories with Total Quantity > 50
   SELECT category, SUM(quantity) as total_quantity
   FROM products
   GROUP BY category
   HAVING total_quantity > 50;

4. DISTINCT: The DISTINCT keyword is used to eliminate duplicate rows from the result set. If you want to retrieve a list of unique product categories:

   -- SQL Query to Retrieve Unique Product Categories
   SELECT DISTINCT category
   FROM products;

5. LIMIT: The LIMIT clause is used to restrict the number of rows returned by a query. This can be useful for pagination or limiting the size of result sets. For example, if you want to retrieve the top 10 products by price:

   -- SQL Query to Retrieve Top 10 Products by Price
   SELECT * FROM products
   ORDER BY price DESC
   LIMIT 10;

These are some of the commonly used SQL clauses that can help you perform more advanced and precise operations on your data. As you become more familiar with SQL, you’ll discover many other clauses and functions that allow you to manipulate and query your database effectively.

Want Coding Challenges? Check out ReviewNPrep to improve your coding skills.

Conclusion

SQL and CRUD operations are essential skills for anyone working with databases. With practice and hands-on experience, you’ll become more proficient in managing and manipulating data in your database.

Remember, the key to mastering SQL is to start simple and gradually work your way up to more complex queries and operations.

Happy data management!

Further Reading:

Check out this blog for practical applications of SQL and tips to become better at it.

Discover the steps you need to take to progress in your software development career with this comprehensive guide.