SQL and Query Processing: Syntax, Joins, Subqueries, and Optimization

TAKEAWAYS:

• SQL is a declarative language for querying relational databases, not procedural (no line-by-line execution).
• Joins combine tables via keys (e.g., INNER JOIN merges matching rows; LEFT JOIN keeps all left-table rows).
• Subqueries nest queries inside others (e.g., WHERE salary > (SELECT AVG(salary) FROM employees)).
• Query optimization relies on indexes, execution plans, and cost-based estimators (e.g., MySQL’s EXPLAIN).
• Embedded SQL integrates SQL into application code (e.g., Java’s JDBC, Python’s sqlite3).
• Real-world use: eSewa’s transaction logs use SQL joins to link users, payments, and merchants; Daraz’s order queues rely on GROUP BY to track inventory by product.

1. Introduction to SQL

SQL (Structured Query Language) is the standard language for interacting with relational databases. It supports:

• Data Definition Language (DDL): CREATE, ALTER, DROP (e.g., CREATE TABLE employees (EmpID INT PRIMARY KEY, Name VARCHAR(50))).
• Data Manipulation Language (DML): INSERT, UPDATE, DELETE, SELECT.
• Data Control Language (DCL): GRANT, REVOKE (permissions).

Why SQL? Before databases, file systems stored data redundantly (e.g., employee records duplicated in HR and Payroll files). SQL eliminates this by centralizing data in tables with keys (primary/foreign) and enforcing integrity (e.g., no duplicate EmpID).

1.1 SQL vs. File Processing

Example: A company’s HR file might store employee salaries in both the employees and payroll files. If the salary changes in one file but not the other, inconsistency arises. SQL avoids this by linking tables via foreign keys (e.g., DeptID in employees references DeptID in departments).

2. Core SQL Queries

SQL queries follow the SELECT-FROM-WHERE structure:

SELECT column1, column2
FROM table1
WHERE condition
GROUP BY column3
HAVING group_condition
ORDER BY column4;

2.1 SELECT and Projections

Projection: Select specific columns.

-- List all employees in the 'IT' department
SELECT FirstName, LastName, Salary
FROM employees
WHERE DeptID = (SELECT DeptID FROM departments WHERE DeptName = 'IT');

Visualization:

graph TD
  A["employees"] -->|"DeptID = (SELECT DeptID FROM departments WHERE DeptName = 'IT')"| B["Subquery Result: DeptID=10"]
  B -->|"DeptID=10"| C["employees (filtered)"]
  C -->|"Result"| D["Final Result: IT employees"]

2.2 WHERE and Filtering

Filters rows based on conditions:

-- Employees earning > $50,000
SELECT *
FROM employees
WHERE Salary > 50000;

Real-world tie: NTC’s call records use WHERE to filter active users by location:

SELECT user_id, call_duration
FROM call_logs
WHERE location = 'Kathmandu' AND call_duration > 300;

2.3 GROUP BY and Aggregation

Groups rows and computes aggregates (COUNT, SUM, AVG):

-- Average salary by department
SELECT DeptName, AVG(Salary)
FROM employees e
JOIN departments d ON e.DeptID = d.DeptID
GROUP BY DeptName;

Visualization:

2.4 HAVING vs. WHERE

• WHERE filters before grouping.
• HAVING filters after grouping.

-- Departments with >3 employees
SELECT DeptName, COUNT(*)
FROM employees e
JOIN departments d ON e.DeptID = d.DeptID
GROUP BY DeptName
HAVING COUNT(*) > 3;

3. Joins: Combining Tables

Joins link tables via keys. Types:

Worked Example: Link employees and departments to find IT salaries.

-- INNER JOIN: Only IT employees
SELECT e.FirstName, d.DeptName, e.Salary
FROM employees e
INNER JOIN departments d ON e.DeptID = d.DeptID
WHERE d.DeptName = 'IT';

-- LEFT JOIN: All employees (even non-IT)
SELECT e.FirstName, d.DeptName, e.Salary
FROM employees e
LEFT JOIN departments d ON e.DeptID = d.DeptID;

Visualization:

graph TD
  A["employees"] -->|"DeptID"| B["departments"]
  B -->|"DeptName"| C["INNER JOIN Result"]
  A -->|"All rows"| D["LEFT JOIN Result (includes NULLs)"]
  C -->|"e.DeptID=d.DeptID"| C
  D -->|"e.DeptID=d.DeptID or NULL"| D

4. Subqueries and Nested Queries

Subqueries run inside other queries. Types:

1. Scalar Subquery: Returns a single value.

   -- Employees earning > avg salary
   SELECT FirstName, Salary
   FROM employees
   WHERE Salary > (SELECT AVG(Salary) FROM employees);
   

2. Correlated Subquery: Depends on outer query.

   -- Employees earning more than their manager
   SELECT e.FirstName, e.Salary
   FROM employees e
   WHERE e.Salary > (SELECT m.Salary
                     FROM employees m
                     WHERE m.EmpID = e.ManagerID);
   

3. IN Subquery: Checks membership.

   -- Employees in IT or HR
   SELECT *
   FROM employees
   WHERE DeptID IN (SELECT DeptID FROM departments WHERE DeptName IN ('IT', 'HR'));
   

Real-world tie: Pathao’s driver availability uses subqueries to check:

-- Drivers available in Kathmandu with >4.5 rating
SELECT driver_id, rating
FROM drivers
WHERE location = 'Kathmandu'
AND rating > (SELECT AVG(rating) FROM drivers WHERE location = 'Kathmandu');

5. Advanced Querying: UNION, INTERSECT, EXCEPT

Example: Find employees in both IT and HR (using INTERSECT).

-- Employees in IT AND HR (unlikely, but possible)
SELECT EmpID
FROM employees
WHERE DeptID IN (SELECT DeptID FROM departments WHERE DeptName = 'IT')
INTERSECT
SELECT EmpID
FROM employees
WHERE DeptID IN (SELECT DeptID FROM departments WHERE DeptName = 'HR');

6. Query Optimization

Optimization improves performance by:

1. Indexing: Speeds up WHERE clauses (e.g., index on EmpID).

   CREATE INDEX idx_empid ON employees(EmpID);
   

2. Execution Plans: Databases generate plans (e.g., MySQL’s EXPLAIN).

   EXPLAIN SELECT * FROM employees WHERE DeptID = 10;
   

   Output:

   +----+-------------+----------+------------+-------+---------------+---------+----------+------+------+----------+----------------+
   | id | select_type | table    | type       | key   | key_len       | ref     | rows     | Extra | ...  |          |
   +----+-------------+----------+------------+-------+---------------+---------+----------+------+------+----------+----------------+
   | 1  | SIMPLE      | employees| const      | PRIMARY| 4             | const   | 1        | NULL | ...  |          |
   +----+-------------+----------+------------+-------+---------------+---------+----------+------+------+----------+----------------+
   

   • type: const means the query uses the index directly (fast).
   • rows: 1 suggests only one row is scanned.

3. Cost-Based Optimizer: Estimates the "cost" (I/O, CPU) of each plan and picks the cheapest.

Real-world tie: NEPSE’s stock queries use indexes on symbol and timestamp to avoid full-table scans:

-- Optimized: Index on (symbol, timestamp)
SELECT symbol, price
FROM stocks
WHERE symbol = 'NEPSE' AND timestamp > '2023-01-01';

7. Embedded SQL

Embedded SQL integrates SQL into application code (e.g., Java, Python). Example in Python with SQLite:

import sqlite3

conn = sqlite3.connect('company.db')
cursor = conn.cursor()

# Embedded SQL (DML)
cursor.execute("SELECT * FROM employees WHERE Salary > ?", (50000,))
results = cursor.fetchall()

for row in results:
    print(row)

conn.close()

Advantages:

• Reuses database logic in apps (e.g., eSewa’s transaction validation).
• Reduces redundant code (e.g., Daraz’s inventory checks).

Disadvantages:

• Security risk if inputs aren’t sanitized (SQL injection).
• Tight coupling between app and database.

8. Transaction Management in Queries

Transactions ensure ACID properties (Atomicity, Consistency, Isolation, Durability). Example:

-- Transfer $1000 from Emp1 to Emp2
BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 1000 WHERE emp_id = 1;
    UPDATE accounts SET balance = balance + 1000 WHERE emp_id = 2;
COMMIT;

If the second update fails, the transaction rolls back to avoid inconsistency.

Real-world tie: Khalti’s payment processing uses transactions:

-- Atomic transfer: User → Merchant
BEGIN;
    UPDATE user_accounts SET balance = balance - 100 WHERE user_id = 123;
    UPDATE merchant_accounts SET balance = balance + 100 WHERE merchant_id = 456;
COMMIT;

In the real world

1. eSewa’s Transaction Logs

   • Idea: Uses INNER JOINs to link user transactions, merchants, and timestamps.
   • Query: SELECT user_id, merchant_id, amount FROM transactions JOIN users ON transactions.user_id = users.id WHERE status = 'completed';
   • Why it matters: Ensures no double-charging and tracks fraud.

2. Daraz’s Order Queues

   • Idea: GROUP BY and HAVING aggregate orders by product to manage inventory.
   • Query: SELECT product_id, COUNT(*) FROM orders GROUP BY product_id HAVING COUNT(*) > 100;
   • Why it matters: Triggers restock alerts for high-demand items.

3. NTC’s Call Routing

   • Idea: Subqueries filter active users by location and signal strength.
   • Query: SELECT user_id FROM call_logs WHERE location = 'Pokhara' AND signal > 80;
   • Why it matters: Routes calls to the nearest tower for faster connectivity.

Exam Tip

• Focus on joins: 30% of exam questions test INNER, LEFT, RIGHT joins. Practice linking 2–3 tables.
• Subqueries are high-yield: Expect 1–2 questions on scalar/correlated subqueries. Memorize the IN syntax.
• Optimization is practical: Know EXPLAIN, indexes, and GROUP BY vs. HAVING. Draw execution plans for full marks.
• Embedded SQL: Show how to embed a SELECT in Python/Java. Avoid raw SQL injection examples.
• Transactions: Always pair BEGIN/COMMIT with ROLLBACK in failure cases. Use a real-world example (e.g., bank transfer).

Common Pitfalls:

• Forgetting ON in joins (e.g., FROM A JOIN B is invalid; must specify ON A.key = B.key).
• Confusing WHERE (row-level) and HAVING (group-level).
• Overlooking DISTINCT in UNION (it removes duplicates by default).

Mermaid Diagram: SQL Query Execution Flow

sequenceDiagram
    participant Client as User/App
    participant DB as Database Engine
    participant Storage as Disk/Index

    Client->>DB: EXECUTE SELECT * FROM employees WHERE Salary > 50000
    DB->>Storage: Check index on Salary (B-tree)
    Storage-->>DB: Returns matching EmpIDs (100, 200, 300)
    DB->>Storage: Fetch full rows for EmpIDs 100, 200, 300
    Storage-->>DB: Returns employee records
    DB-->>Client: Returns 3 rows