Unit 3: Relational Operations

Why This Chapter Pays Your Salary

JOINs and subqueries are the #1 topic in SQL interviews. Every data analyst, backend developer, and DBA writes multi-table queries daily. If Unit 2 taught you to build one table, this unit teaches you to connect the entire database into meaningful reports. A Flipkart analyst writing a "Top 10 products by revenue per category per month" report uses JOINs, GROUP BY, HAVING, window functions, and CTEs — all in one query.

📦 Schema Setup — E-Commerce System (Flipkart-style)

All examples in this chapter use an e-commerce domain — familiar and interview-relevant.

SQL — Schema Setup
CREATE TABLE categories (
    category_id   NUMBER(5)     PRIMARY KEY,
    category_name VARCHAR2(50)  NOT NULL UNIQUE
);
CREATE TABLE customers (
    customer_id   NUMBER(10)    PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    city          VARCHAR2(50),
    reg_date      DATE           DEFAULT SYSDATE
);
CREATE TABLE products (
    product_id    NUMBER(10)    PRIMARY KEY,
    product_name  VARCHAR2(100) NOT NULL,
    category_id   NUMBER(5)     REFERENCES categories(category_id),
    price         NUMBER(10,2)  CHECK (price > 0)
);
CREATE TABLE orders (
    order_id      NUMBER(10)    PRIMARY KEY,
    customer_id   NUMBER(10)    REFERENCES customers(customer_id),
    order_date    DATE           DEFAULT SYSDATE,
    status        VARCHAR2(15)  CHECK (status IN ('PENDING','SHIPPED','DELIVERED','CANCELLED'))
);
CREATE TABLE order_items (
    item_id       NUMBER(10)    PRIMARY KEY,
    order_id      NUMBER(10)    REFERENCES orders(order_id),
    product_id    NUMBER(10)    REFERENCES products(product_id),
    quantity      NUMBER(5)     CHECK (quantity > 0),
    unit_price    NUMBER(10,2)  NOT NULL
);

-- Sample Data
INSERT INTO categories VALUES (1,'Electronics');
INSERT INTO categories VALUES (2,'Clothing');
INSERT INTO categories VALUES (3,'Books');
INSERT INTO categories VALUES (4,'Home & Kitchen');

INSERT INTO customers VALUES (1,'Rahul Sharma','Mumbai',DATE '2023-01-15');
INSERT INTO customers VALUES (2,'Priya Patel','Pune',DATE '2023-03-20');
INSERT INTO customers VALUES (3,'Amit Joshi','Delhi',DATE '2023-06-10');
INSERT INTO customers VALUES (4,'Sneha Kulkarni','Bangalore',DATE '2024-01-05');
INSERT INTO customers VALUES (5,'Rajesh Gupta','Mumbai',DATE '2024-02-28');

INSERT INTO products VALUES (101,'iPhone 15',1,79999);
INSERT INTO products VALUES (102,'Samsung Galaxy S24',1,69999);
INSERT INTO products VALUES (103,'Levi''s Jeans',2,2499);
INSERT INTO products VALUES (104,'DBMS by Navathe',3,650);
INSERT INTO products VALUES (105,'Prestige Cooker',4,1850);
INSERT INTO products VALUES (106,'Allen Solly Shirt',2,1299);

INSERT INTO orders VALUES (1001,1,DATE '2024-11-15','DELIVERED');
INSERT INTO orders VALUES (1002,2,DATE '2024-11-20','DELIVERED');
INSERT INTO orders VALUES (1003,1,DATE '2024-12-01','SHIPPED');
INSERT INTO orders VALUES (1004,3,DATE '2024-12-10','CANCELLED');
INSERT INTO orders VALUES (1005,2,DATE '2025-01-05','PENDING');

INSERT INTO order_items VALUES (1,1001,101,1,79999);
INSERT INTO order_items VALUES (2,1001,104,2,650);
INSERT INTO order_items VALUES (3,1002,103,3,2499);
INSERT INTO order_items VALUES (4,1002,106,1,1299);
INSERT INTO order_items VALUES (5,1003,102,1,69999);
INSERT INTO order_items VALUES (6,1004,105,2,1850);
INSERT INTO order_items VALUES (7,1005,101,1,79999);
COMMIT;

Learning Outcomes — Bloom's Taxonomy