SQL Advanced Topics: Subqueries, Joins, DDL Commands & Real-World Database Logic

Core Concepts: Subqueries, Joins, and DDL

1. Subqueries: Nested Queries for Complex Logic

Subqueries are queries inside queries that return a single value, a set of values, or a table. They help break down complex problems into smaller, manageable parts.

Types of Subqueries

mindmap
  root((Subqueries))
    Single-Row Subquery
      "Returns one value (e.g., MAX, MIN, COUNT)"
      Example: `WHERE salary > (SELECT AVG(salary) FROM employees)`
    Multi-Row Subquery
      "Returns multiple rows (e.g., IN, ANY, ALL)"
      Example: `WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Kathmandu')`
    Correlated Subquery
      "Depends on the outer query (runs for each row)"
      Example: `SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id)`

Key Uses of Subqueries

• Filtering: WHERE salary > (SELECT AVG(salary) FROM employees)
• Comparison: WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 1000000)
• Existence Check: WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = e.customer_id)

Example: Finding Employees Earning More Than Their Department’s Average

SELECT name, salary, dept_id
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

Output:

2. Joins: Combining Tables

Joins combine rows from two or more tables based on related columns. They are essential for retrieving data spread across multiple tables.

Types of Joins

Output:

Example: LEFT JOIN (All Employees, Even Without Departments)

SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;

Output (includes employees with no department):

3. DDL Commands: Defining and Modifying Database Structure

DDL (Data Definition Language) commands create, modify, or delete database objects (tables, indexes, constraints).

Key DDL Commands

Example: Altering a Table to Add a Column

ALTER TABLE employees
ADD COLUMN email VARCHAR(100) UNIQUE;

In the Real World

1. eSewa (Nepal Government)

   • Subqueries: When checking if a user has unpaid bills, eSewa runs:

     SELECT user_id FROM users WHERE EXISTS (SELECT 1 FROM bills WHERE user_id = users.user_id AND status = 'Pending');
     

   • Joins: Combining users, transactions, and bills tables to show a user’s payment history.

2. Daraz (E-Commerce)

   • LEFT JOIN: When displaying orders, Daraz uses:

     SELECT o.order_id, u.username, p.product_name, o.status
     FROM orders o
     LEFT JOIN users u ON o.user_id = u.user_id
     LEFT JOIN products p ON o.product_id = p.product_id;
     

     → Shows all orders, even if the user or product is deleted.

3. NTC (Nepal Telecom)

   • DDL: NTC’s database schema is built using CREATE TABLE for customers, plans, and bills. When adding a new plan, they use:

     ALTER TABLE plans ADD COLUMN discount_percentage DECIMAL(5,2);
     

Visual: How Joins Work

Exam Tip

1. Subqueries:

   • Always check if the subquery returns one value (use =, <, >) or multiple values (use IN, ANY, ALL).
   • Correlated subqueries are common in exams—practice rewriting them with EXISTS.

2. Joins:

   • INNER JOIN = "Only matching rows."
   • OUTER JOIN = "All rows from one table + matches."
   • CROSS JOIN = "Every possible combination" (rarely used in real systems).
   • Draw a Venn diagram in your exam to visualize joins.

3. DDL:

   • DROP is permanent—unlike DELETE, it cannot be rolled back.
   • TRUNCATE is faster than DELETE but resets auto-increment counters.
   • Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE) are often tested—know their syntax.

4. Common Mistakes to Avoid:

   • Forgetting ON in JOIN conditions.
   • Using NATURAL JOIN without knowing column names.
   • Mixing up LEFT and RIGHT joins.

Practice Question: Write a query to find all customers who have placed orders but have no pending payments (use NOT EXISTS). Answer:

SELECT customer_id, name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
    AND NOT EXISTS (
        SELECT 1 FROM payments p
        WHERE p.order_id = o.order_id AND p.status = 'Pending'
    )
);