CRUD Operations in SQL

Understanding CRUD Operations

CRUD represents the four basic operations that can be performed on data in a persistent storage system. In SQL, these operations map to specific statements:

CREATE: Inserting Data

The INSERT statement adds new rows to a table. There are several ways to use INSERT, depending on your needs.

Basic INSERT Syntax

-- Insert a new customer with specified columns
INSERT INTO customers (customer_id, customer_name, contact_name, country)
VALUES (91, 'Acme Inc.', 'John Smith', 'USA');
          

-- Insert values for all columns in order
-- (Ensure you match the table's column order exactly)
INSERT INTO customers
VALUES (92, 'TechCorp', 'Jane Doe', 'jane@techcorp.com', '123 Tech St', 'San Francisco', '94107', 'USA');
          

Inserting Multiple Rows

-- Insert multiple products at once
INSERT INTO products (product_name, unit_price, category_id)
VALUES 
    ('Organic Green Tea', 18.50, 1),
    ('Dark Chocolate Bar', 3.95, 2),
    ('Himalayan Pink Salt', 6.75, 2);
          

Inserting Data from Another Table

-- Copy customers from the USA to a backup table
INSERT INTO customers_usa (customer_id, customer_name, contact_name)
SELECT customer_id, customer_name, contact_name
FROM customers
WHERE country = 'USA';
          

Important Considerations for INSERT

• Primary Keys: For auto-increment columns, you typically omit the column from your INSERT
• Data Types: Values must match expected data types for each column
• Constraints: Foreign keys, unique constraints, and other rules must be satisfied
• Default Values: Columns with default values can be omitted from your INSERT statement
• NULL Values: Use NULL explicitly for missing values if the column allows it

-- Using NULL for missing values and relying on defaults
INSERT INTO orders (customer_id, order_date, shipping_address)
VALUES 
    (42, '2023-05-10', '123 Main St'),  -- shipped_date will use DEFAULT
    (57, '2023-05-11', NULL);           -- NULL for shipping_address
          

READ: Selecting Data

We covered SELECT extensively in our previous lecture, but here's a quick reminder of the basic syntax:

-- Retrieve specific columns with a condition
SELECT column1, column2, ...
FROM table_name
WHERE condition;
          

-- Find all products in the Beverages category
SELECT product_id, product_name, unit_price, units_in_stock
FROM products
WHERE category_id = 1
ORDER BY unit_price DESC;
          

UPDATE: Modifying Data

The UPDATE statement allows you to modify existing data in your tables.

Basic UPDATE Syntax

-- Update the price of a specific product
UPDATE products
SET unit_price = 24.99
WHERE product_id = 15;
          

-- Update multiple columns for a customer
UPDATE customers
SET 
    contact_name = 'Maria Anders',
    phone = '030-0074321',
    last_updated = CURRENT_TIMESTAMP
WHERE customer_id = 4;
          

Updating Multiple Rows

-- Apply a 10% price increase to all products in the Beverages category
UPDATE products
SET unit_price = unit_price * 1.10
WHERE category_id = 1;
          

Using Calculated Values

-- Update inventory after an order
UPDATE products
SET 
    units_in_stock = units_in_stock - 5,
    last_updated = CURRENT_DATE
WHERE product_id = 10;
          

Updating Based on Other Tables

-- Update customer discount based on their order volume
UPDATE customers c
JOIN (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id
SET c.discount_rate = 
    CASE 
        WHEN o.order_count > 10 THEN 0.15
        WHEN o.order_count > 5 THEN 0.10
        ELSE 0.05
    END;
          

UPDATE with Subquery (PostgreSQL/Oracle)

-- Update customer discount based on their order volume
UPDATE customers
SET discount_rate = 
    CASE 
        WHEN order_count > 10 THEN 0.15
        WHEN order_count > 5 THEN 0.10
        ELSE 0.05
    END
FROM (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    GROUP BY customer_id
) o
WHERE customers.customer_id = o.customer_id;
          

-- DANGEROUS: Updates ALL products!
UPDATE products
SET unit_price = 9.99;
          

DELETE: Removing Data

The DELETE statement removes rows from a table.

Basic DELETE Syntax

-- Delete a specific customer
DELETE FROM customers
WHERE customer_id = 45;
          

Deleting Multiple Rows

-- Delete all discontinued products with no inventory
DELETE FROM products
WHERE discontinued = 1 AND units_in_stock = 0;
          

Deleting with Subqueries

-- Delete orders from customers who haven't placed an order in 2 years
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE last_order_date < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR)
);
          

-- DANGEROUS: Deletes ALL customers!
DELETE FROM customers;
          

Truncating Tables

When you need to delete ALL rows in a table efficiently, TRUNCATE is usually a better option than DELETE.

-- Delete all rows from the temp_logs table
TRUNCATE TABLE temp_logs;
          

Practical CRUD Examples

Example: Online Store Product Management

-- CREATE: Add a new product
INSERT INTO products (product_name, supplier_id, category_id, unit_price, units_in_stock)
VALUES ('Organic Quinoa', 5, 7, 12.99, 25);

-- READ: Retrieve product information
SELECT product_id, product_name, unit_price, units_in_stock
FROM products
WHERE category_id = 7
ORDER BY unit_price;

-- UPDATE: Change price and restock
UPDATE products
SET 
    unit_price = 14.99,
    units_in_stock = units_in_stock + 50
WHERE product_id = 78;

-- DELETE: Remove a discontinued product
DELETE FROM products
WHERE product_id = 42;
          

Example: User Account Management

-- CREATE: Register a new user
INSERT INTO users (username, email, password_hash, created_at)
VALUES ('johndoe', 'john@example.com', 'hashed_password_value', CURRENT_TIMESTAMP);

-- READ: Retrieve user profile
SELECT user_id, username, email, profile_image, created_at
FROM users
WHERE username = 'johndoe';

-- UPDATE: Change email and update last login time
UPDATE users
SET 
    email = 'john.doe@example.com',
    last_login = CURRENT_TIMESTAMP
WHERE user_id = 123;

-- DELETE: Remove an inactive account
DELETE FROM users
WHERE last_login < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
AND status = 'inactive';
          

Practical Activities

Activity 1: Basic CRUD Operations

Using a sample database, practice the following operations:

1. Insert a new supplier with details of your choice
2. Retrieve all products supplied by your new supplier
3. Update the contact information for your supplier
4. Delete a specific order (be careful to choose one that won't violate constraints)

Activity 2: Car Dealership Database

Imagine you're building a database for a car dealership. Write SQL statements to:

1. Add a new car to the inventory table
2. Update the price of all 2022 model cars by reducing them 5%
3. Find all available cars of a specific make and model
4. Remove a car from inventory after it's been sold
5. Update the status of multiple cars from "in transit" to "available"

Activity 3: Handling Common Scenarios

Write SQL statements to handle these real-world scenarios:

1. Duplicate a product with a slightly different name but the same properties
2. Move all customers from one region to another when reorganizing territories
3. Apply a bulk discount to all products in a specific category
4. Archive old orders by copying them to an archive table, then deleting them from the active orders table
5. Update inventory quantities after receiving a shipment of multiple products

Key Takeaways

• SQL provides standardized statements for all CRUD operations: INSERT, SELECT, UPDATE, and DELETE
• INSERT allows adding one or more rows to a table, with flexibility in specifying columns
• UPDATE modifies existing data, and can target single or multiple rows based on conditions
• DELETE removes rows based on conditions, while TRUNCATE efficiently removes all rows
• Always use WHERE clauses with UPDATE and DELETE to avoid unintended changes
• Consider testing your conditions with SELECT before executing destructive operations
• CRUD operations form the foundation of most database interactions in applications

Next Steps

In our next lecture, we'll explore SQL joins and relationships, learning how to retrieve data from multiple related tables.