SQL CRUD Operations

This tutorial covers the fundamental Create, Read, Update, and Delete (CRUD) operations in SQL. These operations are the building blocks for interacting with data in any relational database.

Language:

1. CREATE (INSERT)

The INSERT statement is used to add new rows (records) to a table. You can insert a single row or multiple rows at once.

Inserting a Single Row

Specify the table name and the values for each column.

INSERT INTO Customers (CustomerID, CompanyName, ContactName, City)
VALUES (101, 'Northwind Traders', 'Maria Anders', 'Berlin');

Inserting Multiple Rows

Provide values for multiple rows in a comma-separated list.

INSERT INTO Products (ProductID, ProductName, UnitPrice)
VALUES
    (78, 'Gumbo Mix', 25.00),
    (79, 'Northwoods Cranberry Sauce', 40.00),
    (80, 'Northwoods Apple Cider', 32.00);

Inserting Rows with Specific Columns

If you are only providing values for some columns, explicitly list the columns you are inserting into.

INSERT INTO Employees (EmployeeID, LastName, FirstName)
VALUES (10, 'Doe', 'John');

2. READ (SELECT)

The SELECT statement is used to retrieve data from one or more tables. It's the most frequently used SQL command.

Selecting All Columns

Use the asterisk (*) to select all columns from a table.

SELECT *
FROM Customers;

Selecting Specific Columns

List the column names you want to retrieve.

SELECT CompanyName, ContactName, City
FROM Customers;

Filtering Rows (WHERE Clause)

Use the WHERE clause to specify conditions for selecting rows.

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 50;

Sorting Results (ORDER BY Clause)

Use ORDER BY to sort the results in ascending (ASC) or descending (DESC) order.

SELECT CompanyName, City
FROM Customers
ORDER BY City ASC;

3. UPDATE

The UPDATE statement modifies existing records in a table. Always use a WHERE clause to avoid updating all rows.

Updating Specific Rows

Specify the table, the columns to update, and the new values, along with the condition to identify the rows.

UPDATE Products
SET UnitPrice = UnitPrice * 1.10
WHERE ProductID = 15;

Updating Multiple Columns

You can update multiple columns in a single UPDATE statement.

UPDATE Customers
SET City = 'London', PostalCode = 'SW1A 0AA'
WHERE CustomerID = 5;

Important: Always test UPDATE statements with a WHERE clause on a small subset of data or in a test environment before running them on production data.

4. DELETE

The DELETE statement removes existing records from a table. Similar to UPDATE, the WHERE clause is crucial.

Deleting Specific Rows

Use the WHERE clause to specify which rows to delete.

DELETE FROM Orders
WHERE OrderDate < '2020-01-01';

Deleting All Rows

If you omit the WHERE clause, all rows in the table will be deleted. This is often achieved more efficiently using TRUNCATE TABLE for large tables, but DELETE is transactional.

DELETE FROM TemporaryData;

Tip: For deleting all rows from a table, TRUNCATE TABLE is generally faster and uses fewer system resources than DELETE without a WHERE clause, as it logs less information. However, TRUNCATE cannot be rolled back easily.

Complete CRUD Example (Customers Table)

Let's assume a Customers table with columns: CustomerID, CompanyName, City.

Create a new customer:

INSERT INTO Customers (CustomerID, CompanyName, City)
VALUES (102, 'Globex Corporation', 'Springfield');

Read all customers from Springfield:

SELECT CompanyName, CustomerID
FROM Customers
WHERE City = 'Springfield';

Update the city for Globex Corporation:

UPDATE Customers
SET City = 'Shelbyville'
WHERE CompanyName = 'Globex Corporation';

Delete the customer from Shelbyville:

DELETE FROM Customers
WHERE CompanyName = 'Globex Corporation';