SQL & Query Processing: Syntax, Joins, Subqueries, Views, Indexes

SQL Language Categories and Commands

SQL (Structured Query Language) is divided into three main categories based on their functions:

1. Data Definition Language (DDL)

Commands that define the database structure (tables, schemas, indexes).

classDiagram
    class DDL {
        +CREATE (table, index, view, schema)
        +ALTER (modify structure)
        +DROP (delete objects)
        +TRUNCATE (remove all rows)
    }
    DDL --> "Defines" Database

Example:

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

2. Data Manipulation Language (DML)

Commands that modify data (insert, update, delete, select).

classDiagram
    class DML {
        +SELECT (query data)
        +INSERT (add rows)
        +UPDATE (modify rows)
        +DELETE (remove rows)
        +MERGE (upsert)
    }
    DML --> "Modifies" Data

Example:

INSERT INTO Customer (customer_id, name, email)
VALUES (1, 'Ramesh Adhikari', 'ramesh@example.com');

3. Data Control Language (DCL)

Commands that control access (GRANT, REVOKE).

classDiagram
    class DCL {
        +GRANT (permissions)
        +REVOKE (remove permissions)
    }
    DCL --> "Manages" Security

Example:

GRANT SELECT ON Customer TO 'Analyst';

Query Processing: How SQL Queries Work

When you run a query, the database follows these steps:

1. Parsing: Checks syntax.
2. Optimization: Chooses the best execution plan.
3. Execution: Fetches and returns data.

sequenceDiagram
    User->>DBMS: Executes SQL Query
    DBMS->>Parser: Checks Syntax
    Parser-->>DBMS: Validated Query
    DBMS->>Optimizer: Generates Execution Plan
    Optimizer-->>DBMS: Best Plan
    DBMS->>Executor: Runs Plan
    Executor-->>User: Returns Results

Example:

-- Find all customers from Kathmandu
SELECT * FROM Customer
WHERE city = 'Kathmandu';

Execution Plan (Simplified):

1. Scan Customer table.
2. Filter rows where city = 'Kathmandu'.
3. Return matching records.

Types of Joins (With Real-World Example)

Joins combine rows from two or more tables based on a related column.

1. Inner Join

Returns only matching rows.

erDiagram
    Customer ||--o{ Order : places
    Order ||--|{ Product : contains

Example (eSewa Transactions):

-- Find all orders placed by customers from Kathmandu
SELECT Order.order_id, Customer.name, Product.product_name
FROM Order
INNER JOIN Customer ON Order.customer_id = Customer.customer_id
INNER JOIN Product ON Order.product_id = Product.product_id
WHERE Customer.city = 'Kathmandu';

2. Left (Outer) Join

Returns all rows from the left table + matching right rows.

Example (Daraz Order Tracking):

-- Show all customers, even those without orders
SELECT Customer.name, Order.order_id
FROM Customer
LEFT JOIN Order ON Customer.customer_id = Order.customer_id;

3. Right Join

Returns all rows from the right table + matching left rows.

4. Full Outer Join

Returns all rows from both tables.

5. Cross Join

Returns Cartesian product (all possible combinations).

Comparison Table:

Subqueries and Nested Queries

A subquery is a query inside another query.

Types of Subqueries:

1. Scalar Subquery (returns a single value).
2. Row Subquery (returns a row).
3. Table Subquery (returns a table).

Example (Ncell Billing):

-- Find customers who spent more than the average
SELECT name
FROM Customer
WHERE total_spent > (
    SELECT AVG(total_spent)
    FROM Customer
);

Views in SQL

A view is a virtual table based on a SQL query.

Example (NEPSE Stock Data):

CREATE VIEW HighValueStocks AS
SELECT symbol, price
FROM Stock
WHERE price > 1000;

Advantages:

• Simplifies complex queries.
• Enhances security (hide sensitive columns).

Disadvantages:

• Performance overhead (views are not stored physically).
• Limited DML operations (some databases restrict UPDATE/DELETE on views).

Indexes and Query Optimization

An index is a data structure (like a book’s index) that speeds up searches.

Types of Indexes:

1. Clustered Index (determines physical order of data).
2. Non-Clustered Index (separate structure, points to data).
3. Composite Index (index on multiple columns).

Example (Bank Loan Processing):

-- Index on 'customer_id' speeds up loan approvals
CREATE INDEX idx_customer_id ON Loan(customer_id);

When to Use Indexes?

• Columns frequently used in WHERE, JOIN, or ORDER BY.
• Columns with high selectivity (many unique values).

Disadvantages:

• Slows down INSERT/UPDATE (indexes must be updated).
• Uses extra storage.

In the Real World

1. eSewa Transactions

   • Uses joins to link customer accounts with payment records.
   • Example: When you pay an electricity bill, eSewa runs:

     SELECT customer_name, amount
     FROM Payment
     INNER JOIN Customer ON Payment.customer_id = Customer.id
     WHERE transaction_id = 'TXN12345';
     

2. Daraz Order Tracking

   • Uses subqueries to check order status:

     SELECT order_status
     FROM Order
     WHERE order_id = 'DARAZ123'
     AND shipped_date > CURRENT_DATE - INTERVAL '3 days';
     

3. Ncell Billing System

   • Uses views to simplify monthly reports:

     CREATE VIEW MonthlyRevenue AS
     SELECT customer_id, SUM(amount) AS total_spent
     FROM CallLog
     GROUP BY customer_id;
     

Exam Tip

1. Practice Writing Queries

   • The exam tests SQL syntax (e.g., INNER JOIN vs LEFT JOIN).
   • Always indent queries for readability.

2. Understand Execution Plans

   • Explain how a query runs (e.g., "The optimizer picks an index scan first").

3. Real-World Scenarios

   • Expect questions like: "How would you design a query to track delayed Daraz deliveries?" → Use LEFT JOIN + WHERE shipped_date IS NULL.

4. Common Mistakes to Avoid

   • Forgetting GROUP BY in aggregate queries.
   • Using NOT IN instead of NOT EXISTS (performance difference).