SQL Interview Ultimate Guide Concepts, Questions & Advanced

🎯 Practice: Filtering by a Calculated Column (Alias Issue)

You need to find all employees whose estimated annual bonus, calculated as 10% of their salary, is greater than $5,000. Try to use an alias for the bonus calculation in your SELECT statement and then filter by it.

SELECT
    first_name,
    last_name,
    salary,
    (salary * 0.10) AS annual_bonus
FROM
    Employees
WHERE
    annual_bonus > 5000; -- ERROR: 'annual_bonus' is unknown here!

-- Option 1: Repeat the calculation (simplest for single use)
SELECT
    first_name,
    last_name,
    salary,
    (salary * 0.10) AS annual_bonus
FROM
    Employees
WHERE
    (salary * 0.10) > 5000;

-- Option 2: Using a Subquery (more maintainable for complex calculations)
SELECT
    first_name,
    last_name,
    salary,
    annual_bonus
FROM
    (SELECT
        first_name,
        last_name,
        salary,
        (salary * 0.10) AS annual_bonus
    FROM
        Employees
    ) AS subquery_with_bonus
WHERE
    annual_bonus > 5000;

🎯 Practice: Filtering Rows vs. Filtering Groups

You need to find the average salary for each department, but only for employees hired after January 1, 2020. Additionally, you only want to see departments where this calculated average salary is greater than $65,000.

SELECT
    department,
    AVG(salary) AS avg_dept_salary
FROM
    Employees
WHERE
    hire_date > '2020-01-01' -- Filters individual rows BEFORE grouping
GROUP BY
    department
HAVING
    AVG(salary) > 65000; -- Filters groups AFTER aggregation

🎯 Practice: Ordering by a Derived Column Alias

List all employees, showing their full name (first_name concatenated with last_name) and their salary. Order the results alphabetically by the full name.

SELECT
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name,
    salary
FROM
    Employees
ORDER BY
    full_name ASC; -- 'full_name' alias is recognized here!

🎯 Practice: Retrieving Top N Ordered Results

Find the 3 most recently hired employees. Include their full name, department, salary, and hire date.

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    department,
    salary,
    hire_date
FROM
    Employees
ORDER BY
    hire_date DESC -- Sort by hire date in descending order (most recent first)
LIMIT 3; -- Get only the top 3 rows from the sorted result set

🎯 Practice: Creating Tables with Mixed Constraints

You need to create two tables for an e-commerce platform: Categories and Products. The Categories table should have category_id (Primary Key, integer) and category_name (unique, not null, varchar(100)). The Products table should have:

• product_id (Primary Key, integer, auto-incrementing if possible)
• product_name (not null, unique, varchar(255))
• price (decimal, must be greater than 0)
• stock_quantity (integer, default 0)
• category_id (Foreign Key referencing Categories.category_id)

-- First, create the Categories table as Products will reference it
CREATE TABLE Categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE
);

-- Now, create the Products table
CREATE TABLE Products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- PostgreSQL syntax for auto-increment PK
    -- (For MySQL, use INT AUTO_INCREMENT PRIMARY KEY)
    -- (For SQL Server, use INT IDENTITY(1,1) PRIMARY KEY)
    product_name VARCHAR(255) NOT NULL UNIQUE,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0), -- Inline CHECK constraint
    stock_quantity INT DEFAULT 0, -- Inline DEFAULT constraint
    category_id INT,
    -- External Foreign Key constraint for clarity
    CONSTRAINT fk_category
        FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

🎯 Practice: Modifying Table Structures with ALTER TABLE

You have the Products table created previously. Now, a new requirement comes in:

1. Add a new column called description of type TEXT (which can be NULL) to the Products table.
2. It's realized that product names might be longer than 255 characters, so you need to increase the length of the product_name column to VARCHAR(500).

-- 1. Add the 'description' column
ALTER TABLE Products
ADD COLUMN description TEXT;

-- 2. Increase the length of 'product_name'
-- Syntax varies slightly by RDBMS:

-- PostgreSQL:
ALTER TABLE Products
ALTER COLUMN product_name TYPE VARCHAR(500);

-- SQL Server:
ALTER TABLE Products
ALTER COLUMN product_name VARCHAR(500);

-- MySQL:
ALTER TABLE Products
MODIFY COLUMN product_name VARCHAR(500);

-- 1. Drop the 'stock_quantity' column
ALTER TABLE Products
DROP COLUMN stock_quantity;

-- 2. Add a composite UNIQUE constraint
-- Note: If 'product_name' was already UNIQUE on its own, this would be redundant
-- but demonstrates composite constraint syntax.
ALTER TABLE Products
ADD CONSTRAINT UQ_ProductCategoryName UNIQUE (product_name, category_id);

-- Scenario 1: Remove specific old records, with rollback capability.
-- Assuming 'log_date' is the timestamp column.
BEGIN TRANSACTION; -- Start a transaction (syntax might vary: START TRANSACTION;)

DELETE FROM AuditLogs
WHERE log_date < CURRENT_DATE - INTERVAL '6 MONTH'; -- PostgreSQL example (adjust for your RDBMS)
-- For SQL Server: WHERE log_date < DATEADD(month, -6, GETDATE());
-- For MySQL: WHERE log_date < DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

-- If you realize you made a mistake:
-- ROLLBACK;

-- If satisfied:
-- COMMIT;

-- Scenario 2: Clear all data, keep structure, fastest way.
TRUNCATE TABLE AuditLogs;

-- Scenario 3: Decommission the entire table.
DROP TABLE AuditLogs;

5. Data Manipulation Language (DML) & Querying

Data Manipulation Language (DML) commands are the core of interacting with your data. These commands allow you to add, modify, delete, and retrieve data stored within your database tables.

Modifying Data

INSERT (Adding new rows)

The INSERT statement adds one or more new rows of data into a table. You can insert a single row or multiple rows in a single command.

-- Single Row Insert (specifying columns)
INSERT INTO Employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (101, 'Alice', 'Smith', 'alice@example.com', '2023-01-15', 75000.00, 1);

-- Single Row Insert (all columns, order must match table definition)
INSERT INTO Employees
VALUES (102, 'Bob', 'Johnson', 'bob@example.com', '2023-02-20', 82000.00, 2, NULL); -- Assuming a 'phone_number' column, which is NULL

-- Bulk Insert (multiple rows)
INSERT INTO Employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES
    (103, 'Charlie', 'Brown', 'charlie@example.com', '2023-03-01', 68000.00, 1),
    (104, 'Diana', 'Prince', 'diana@example.com', '2023-03-10', 95000.00, 3);

-- Inserting from another SELECT query (e.g., from a staging table)
INSERT INTO ProductionTable (col1, col2)
SELECT staging_col1, staging_col2
FROM StagingTable
WHERE status = 'processed';

UPDATE (Modifying existing rows)

The UPDATE statement modifies existing data in a table. It is absolutely critical to use a WHERE clause with UPDATE to specify which rows should be affected.

-- Update a single employee's salary
UPDATE Employees
SET salary = 80000.00
WHERE employee_id = 101;

-- Update multiple employees in a specific department
UPDATE Employees
SET salary = salary * 1.05 -- Give a 5% raise
WHERE department_id = 1 AND hire_date < '2023-01-01';

-- Update a column to NULL
UPDATE Employees
SET email = NULL
WHERE employee_id = 103;

DELETE (Removing existing rows)

The DELETE statement removes one or more rows from a table. Like UPDATE, the WHERE clause is paramount.

-- Delete a single employee record
DELETE FROM Employees
WHERE employee_id = 102;

-- Delete all employees from a specific department
DELETE FROM Employees
WHERE department_id = 3;

-- Delete all employees hired before a certain date
DELETE FROM Employees
WHERE hire_date < '2022-01-01';

The SELECT Statement Deep Dive

The SELECT statement is the most frequently used DML command, used for retrieving data from a database. Mastering its various clauses is essential.

Selection Logic (CASE Statements)

CASE statements allow you to apply conditional logic directly within your query, creating new derived columns based on conditions. This is incredibly powerful for data transformation and reporting.

SELECT
    employee_id,
    first_name,
    salary,
    CASE
        WHEN salary < 60000 THEN 'Junior'
        WHEN salary BETWEEN 60000 AND 90000 THEN 'Mid-Level'
        WHEN salary > 90000 THEN 'Senior'
        ELSE 'Unknown' -- Optional, but good practice for completeness
    END AS career_level,
    CASE department_id
        WHEN 1 THEN 'Marketing'
        WHEN 2 THEN 'Sales'
        WHEN 3 THEN 'Engineering'
        ELSE 'Other'
    END AS department_name
FROM
    Employees;

Key points:

• CASE statements evaluate conditions sequentially. The first WHEN condition that evaluates to TRUE will have its corresponding THEN value returned.
• If no WHEN conditions are met, the value in the ELSE clause is returned. If ELSE is omitted and no conditions are met, NULL is returned.
• There are two main forms:
  • Searched CASE: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... END (More flexible, conditions can be anything).
  • Simple CASE: CASE column_name WHEN value1 THEN result1 WHEN value2 THEN result2 ... END (Compares a single expression to various values).

Filtering (WHERE)

The WHERE clause filters rows based on specified conditions before any grouping or aggregation (remember the order of execution!).

• Operator Precedence (`AND` vs. `OR`):
  • AND has higher precedence than OR. This means conditions joined by AND are evaluated first.
  • Use parentheses to explicitly control the order of evaluation and avoid unexpected results.

-- Example: (salary > 70000 AND department_id = 1) OR hire_date > '2023-06-01'
-- Employees earning >70k in Dept 1, OR anyone hired after June 1, 2023.
SELECT *
FROM Employees
WHERE (salary > 70000 AND department_id = 1) OR hire_date > '2023-06-01';

• Pattern Matching (`LIKE` with `%` and `_`):
  • %: Matches any sequence of zero or more characters.
  • _: Matches any single character.

-- Find employees whose first name starts with 'A'
SELECT * FROM Employees WHERE first_name LIKE 'A%';

-- Find employees whose last name has 'son' anywhere in it
SELECT * FROM Employees WHERE last_name LIKE '%son%';

-- Find employees whose email is exactly 5 characters long before '@example.com'
SELECT * FROM Employees WHERE email LIKE '_____@example.com';

• List Filtering (`IN` vs. `NOT IN`):
  • IN: Matches any value in a specified list.
  • NOT IN: Matches any value *not* in a specified list.

-- Find employees in departments 1 or 3
SELECT * FROM Employees WHERE department_id IN (1, 3);

-- Find employees NOT in departments 1, 2, or 3
SELECT * FROM Employees WHERE department_id NOT IN (1, 2, 3);

• Range Filtering (`BETWEEN` inclusive boundaries):
  • BETWEEN value1 AND value2: Matches values within the specified range, *inclusive* of both value1 and value2.

-- Find employees with salary between 60,000 and 80,000 (inclusive)
SELECT * FROM Employees WHERE salary BETWEEN 60000.00 AND 80000.00;

-- Find employees hired in Q1 2023
SELECT * FROM Employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-03-31';

• Edge Case: Filtering NULLs (`IS NULL` vs. `= NULL`):
  • As discussed in Section 2, direct comparison operators like =, !=, <, > with NULL always result in UNKNOWN.
  • To find rows where a column is NULL, use IS NULL.
  • To find rows where a column is not NULL, use IS NOT NULL.

-- Correctly find employees with no assigned phone number
SELECT * FROM Employees WHERE phone_number IS NULL;

-- Correctly find employees who DO have an assigned phone number
SELECT * FROM Employees WHERE phone_number IS NOT NULL;

-- This will NOT work as expected (returns no rows, because NULL = NULL is UNKNOWN)
-- SELECT * FROM Employees WHERE phone_number = NULL;

Sorting & Limits

Once rows are filtered, you often want to present them in a specific order and possibly retrieve only a subset.

• `ORDER BY` (Multi-level sorting):
  • Sorts the result set by one or more columns in ascending (`ASC`, default) or descending (`DESC`) order.
  • You can sort by multiple columns, with later columns acting as tie-breakers.

-- Order by last name ascending, then first name ascending
SELECT *
FROM Employees
ORDER BY last_name ASC, first_name ASC;

-- Order by salary descending, then hire date descending (most recent first)
SELECT *
FROM Employees
ORDER BY salary DESC, hire_date DESC;

• Handling NULLs in sort order (`NULLS FIRST`/`LAST`):
  • Many RDBMS (like PostgreSQL, Oracle) allow you to specify how NULL values should be treated in sorting (e.g., appear first or last). SQL Server and MySQL handle NULLs differently by default (often treating them as the lowest values).

-- PostgreSQL example: Employees with NULL phone numbers appear first when sorting by phone number
SELECT employee_id, first_name, phone_number
FROM Employees
ORDER BY phone_number ASC NULLS FIRST;

-- PostgreSQL example: Employees with NULL phone numbers appear last
SELECT employee_id, first_name, phone_number
FROM Employees
ORDER BY phone_number ASC NULLS LAST;

• `LIMIT`/`OFFSET` (Pagination basics):
  • LIMIT n: Restricts the number of rows returned by the query to `n`.
  • OFFSET m: Skips `m` rows before beginning to return the rows. Used for pagination.
  • Syntax varies:
    • LIMIT x OFFSET y (PostgreSQL, MySQL, SQLite)
    • TOP x (SQL Server)
    • FETCH FIRST x ROWS ONLY OFFSET y ROWS (SQL Standard, Oracle 12c+)

-- Get the 5 highest-paid employees
SELECT *
FROM Employees
ORDER BY salary DESC
LIMIT 5; -- MySQL/PostgreSQL

-- For SQL Server:
-- SELECT TOP 5 * FROM Employees ORDER BY salary DESC;

-- Get the next 5 highest-paid employees (e.g., for page 2 of results)
SELECT *
FROM Employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5; -- MySQL/PostgreSQL (skips the first 5, gets the next 5)

-- For SQL Server (using OFFSET FETCH):
-- SELECT * FROM Employees ORDER BY salary DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Practice & Application

-- Assume Employees table structure:
-- (employee_id INT, first_name VARCHAR, last_name VARCHAR, email VARCHAR,
--  hire_date DATE, salary DECIMAL, department_id INT, phone_number VARCHAR)

-- 1. Insert a new employee
INSERT INTO Employees (employee_id, first_name, last_name, email, hire_date, salary, department_id, phone_number)
VALUES (105, 'John', 'Doe', 'john.doe@example.com', CURRENT_DATE, 70000.00, 1, NULL);

-- 2. Update employee 101 (Alice Smith) with a 10% raise
UPDATE Employees
SET salary = salary * 1.10
WHERE employee_id = 101;

-- 3. Delete employees with a NULL email
DELETE FROM Employees
WHERE email IS NULL; -- Correctly using IS NULL for filtering NULLs

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary IS NULL THEN 'Undetermined' -- Handle NULL first if desired
        WHEN salary < 60000 THEN 'Low'
        WHEN salary BETWEEN 60000 AND 90000 THEN 'Medium'
        WHEN salary > 90000 THEN 'High'
        ELSE 'Undetermined' -- Catch-all, though previous WHENs should cover most
    END AS SalaryCategory
FROM
    Employees
ORDER BY
    salary DESC;

SELECT
    employee_id,
    first_name,
    last_name,
    hire_date,
    department_id
FROM
    Employees
WHERE
    (first_name LIKE 'A%' OR last_name LIKE '%o%') -- Parentheses are CRUCIAL here due to AND/OR precedence
    AND hire_date BETWEEN '2023-01-01' AND '2023-12-31'
    AND department_id IN (1, 3)
ORDER BY
    hire_date ASC;

SELECT
    employee_id,
    first_name,
    department_id,
    phone_number
FROM
    Employees
ORDER BY
    department_id ASC,
    phone_number DESC NULLS LAST -- Sort phone numbers DESC, with NULLs at the end
LIMIT 5 OFFSET 5; -- For Page 2, LIMIT 5 (rows per page), OFFSET 5 (skip first page)

6. Aggregation, Grouping & Granularity

While SELECT retrieves individual rows, often you need to summarize data. This is where aggregation and grouping come into play, allowing you to derive insights from collections of rows rather than just individual ones.

Aggregate Functions

Aggregate functions (also known as "group functions") perform a calculation on a set of rows and return a single value.

• COUNT(*) vs. COUNT(column_name) (Handling NULLs):
  • COUNT(*): Counts all rows in the result set, including those with NULL values in any column. It literally counts "how many rows are there?".
  • COUNT(column_name): Counts only the non-NULL values in the specified column_name. It counts "how many values are present in this column?".
  • COUNT(DISTINCT column_name): Counts the number of unique non-NULL values in the specified column.

-- Assume Employees table has some NULL emails and phone numbers.

-- Total number of employees (rows)
SELECT COUNT(*) AS total_employees
FROM Employees; -- e.g., 100

-- Number of employees with a recorded email address
SELECT COUNT(email) AS employees_with_email
FROM Employees; -- e.g., 95 (if 5 emails are NULL)

-- Number of unique department IDs
SELECT COUNT(DISTINCT department_id) AS unique_departments
FROM Employees;

• SUM(), AVG():
  • SUM(column_name): Calculates the total sum of non-NULL values in a numeric column.
  • AVG(column_name): Calculates the average of non-NULL values in a numeric column.

-- Total salary paid across all employees
SELECT SUM(salary) AS total_payroll
FROM Employees;

-- Average salary of all employees
SELECT AVG(salary) AS average_salary
FROM Employees;

• MIN(), MAX() (Works on Strings/Dates too):
  • MIN(column_name): Returns the smallest value in the column (numerically, alphabetically, or chronologically).
  • MAX(column_name): Returns the largest value in the column (numerically, alphabetically, or chronologically).

-- Highest and lowest salaries
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary
FROM Employees;

-- Earliest and latest hire dates
SELECT MIN(hire_date) AS earliest_hire, MAX(hire_date) AS latest_hire
FROM Employees;

-- Alphabetically first and last last names
SELECT MIN(last_name) AS first_last_name, MAX(last_name) AS last_last_name
FROM Employees;

Grouping Mechanics (`GROUP BY`)

The GROUP BY clause divides the rows returned by the FROM and WHERE clauses into groups, and then an aggregate function is applied to each group. This allows you to get summaries *per category*.

• The rule of "Non-aggregated columns must be grouped":

  Any column that appears in the SELECT list that is *not* an aggregate function (like COUNT(), SUM(), etc.) must also appear in the GROUP BY clause. This ensures that the database knows how to categorize and summarize the data.

  -- Valid: Calculate average salary PER department
  SELECT
      department_id,          -- This is not aggregated
      AVG(salary) AS avg_salary_by_dept
  FROM
      Employees
  GROUP BY
      department_id;          -- So it MUST be in GROUP BY
  
  -- Invalid (will cause an error): Trying to select first_name without grouping it
  -- SELECT department_id, first_name, AVG(salary) FROM Employees GROUP BY department_id;
  -- Error: column "Employees.first_name" must appear in the GROUP BY clause or be used in an aggregate function

• Grouping by expressions or derived values:

  You can group by any column, or even an expression or derived value (like a CASE statement or a part of a date).

  -- Group by hire year (PostgreSQL/MySQL example)
  SELECT
      EXTRACT(YEAR FROM hire_date) AS hire_year, -- Extract year
      COUNT(*) AS total_hires
  FROM
      Employees
  GROUP BY
      EXTRACT(YEAR FROM hire_date); -- Group by the extracted year
  
  -- Group by a CASE statement's output (e.g., career_level from previous section)
  SELECT
      CASE
          WHEN salary < 60000 THEN 'Low'
          WHEN salary BETWEEN 60000 AND 90000 THEN 'Medium'
          WHEN salary > 90000 THEN 'High'
          ELSE 'Undetermined'
      END AS SalaryCategory,
      COUNT(*) AS num_employees
  FROM
      Employees
  GROUP BY
      SalaryCategory; -- Grouping by the alias is often supported as well, or the full CASE expression

Filtering Groups (`HAVING`)

The HAVING clause is used to filter groups based on conditions applied to aggregate functions. It operates after GROUP BY.

• WHERE vs. HAVING (Pre-aggregation vs. Post-aggregation):
  • WHERE: Filters individual rows before GROUP BY is applied. It cannot use aggregate functions.
  • HAVING: Filters entire groups after GROUP BY is applied and aggregate functions have been calculated. It *must* use aggregate functions (or columns from the GROUP BY clause).

-- Find departments where the average salary is above 70,000,
-- considering only employees hired after 2022.
SELECT
    department_id,
    AVG(salary) AS avg_dept_salary,
    COUNT(*) AS num_employees_in_group
FROM
    Employees
WHERE
    hire_date > '2022-12-31' -- Filters individual employees BEFORE grouping
GROUP BY
    department_id
HAVING
    AVG(salary) > 70000; -- Filters groups AFTER aggregation

Funnel Chart: Data Reduction from FROM → WHERE → GROUP BY → HAVING

This visual demonstrates how each clause reduces the data set, narrowing down the focus from all raw data to the specific insights you need.

7. Joins & Relational Mechanics

Databases are powerful because they allow you to store related pieces of information in separate, organized tables. Joins are the cornerstone of the relational model, enabling you to combine data from two or more tables based on related columns.

Join Theory

A great way to conceptualize different join types is through Venn Diagrams, which illustrate how rows from two sets (tables) are combined.

Venn Diagrams: Interpreting Join Types

Join Types & Behaviors

Let's look at the practical syntax and typical use cases.

-- Example Tables:
-- Customers (customer_id, customer_name)
-- Orders (order_id, customer_id, order_date, total_amount)

• INNER JOIN (Intersection):

  Combines rows from two tables where the join condition is true. Rows that do not have a match in both tables are excluded.

  SELECT C.customer_name, O.order_id, O.total_amount
  FROM Customers C
  INNER JOIN Orders O ON C.customer_id = O.customer_id;
  -- Shows only customers who have placed orders, and their orders.

• LEFT JOIN (Preserving left table, NULLs on right):

  Returns all rows from the left table (Customers), and the matching rows from the right table (Orders). If there is no matching order for a customer, the order-related columns will be NULL.

  SELECT C.customer_name, O.order_id, O.total_amount
  FROM Customers C
  LEFT JOIN Orders O ON C.customer_id = O.customer_id;
  -- Shows ALL customers, including those with no orders (order_id, total_amount will be NULL for them).

• RIGHT JOIN (Preserving right table):

  Returns all rows from the right table (Orders), and the matching rows from the left table (Customers). If an order has no matching customer (e.g., bad data), the customer-related columns will be NULL.

  SELECT C.customer_name, O.order_id, O.total_amount
  FROM Customers C
  RIGHT JOIN Orders O ON C.customer_id = O.customer_id;
  -- Shows ALL orders, even if they don't have a matching customer (customer_name will be NULL).
  -- Less common as LEFT JOIN can often achieve the same by swapping tables.

• FULL OUTER JOIN (Union of both):

  Returns all rows when there is a match in either the left or right table. If a row in one table doesn't have a match in the other, the non-matching side will have NULLs.

  SELECT C.customer_name, O.order_id, O.total_amount
  FROM Customers C
  FULL OUTER JOIN Orders O ON C.customer_id = O.customer_id;
  -- Shows all customers (with or without orders) AND all orders (with or without customers).
  -- Where there's no match, the respective columns will be NULL.

• CROSS JOIN (Cartesian Product - Warning signs):

  Combines every row from the first table with every row from the second table. If table A has $N$ rows and table B has $M$ rows, the result will have $N \times M$ rows. It does not require an `ON` clause.

  SELECT C.customer_name, O.order_id
  FROM Customers C
  CROSS JOIN Orders O;
  -- Produces every possible combination of a customer and an order.
  -- Use with extreme caution, often signals an error if not intended (e.g., generating all possible pairings).

• SELF JOIN (Hierarchical data):

  Joining a table to itself. This is useful for comparing rows within the same table, often for hierarchical data (e.g., finding employees and their managers in an Employees table).

  -- Assume Employees table has: employee_id, employee_name, manager_id
  SELECT
      E.employee_name AS Employee,
      M.employee_name AS Manager
  FROM
      Employees E
  LEFT JOIN
      Employees M ON E.manager_id = M.employee_id;
  -- Links each employee to their manager (or NULL if no manager).

Join Syntax & Best Practices

• ON (General conditions) vs. USING (Named columns):
  • ON: The most flexible way to specify join conditions. It allows any valid condition (equality, inequality, comparison operators, functions, etc.).

    FROM Customers C JOIN Orders O ON C.customer_id = O.customer_id AND O.order_date > '2023-01-01'

  • USING: A shorthand for INNER JOIN or LEFT JOIN when you are joining on columns that have the same name in both tables.

    FROM Customers JOIN Orders USING (customer_id)

    USING is syntactically cleaner when applicable, but less flexible than ON. The column specified in USING appears only once in the result set, unlike ON where both join columns from each table are retained.
• Aliasing tables for readability:

  Assigning short aliases (e.g., C for Customers, O for Orders) to tables makes queries shorter and much more readable, especially with multiple joins or self-joins.

  SELECT C.customer_name, O.order_date -- Use aliases to specify which table a column comes from
  FROM Customers AS C -- 'AS' is optional but good practice
  INNER JOIN Orders AS O
  ON C.customer_id = O.customer_id;

Common Pitfalls

• The "Fan-out" problem (Duplicate rows after join):

  This occurs when a row in one table matches multiple rows in another table, leading to an explosion of duplicate (or near-duplicate) rows. This is especially problematic before aggregation, as it can inflate counts or sums.

  Example: Customer with Multiple Phone Numbers

  Imagine a Customers table and a CustomerPhones table (one-to-many relationship). If a customer has two phone numbers and you join these tables, that customer's details will appear twice for each phone number. If you then try to COUNT customers, you'll get an inflated number.

  Customers (C)	CustomerPhones (CP)	Result of SELECT * FROM C JOIN CP ON C.cust_id = CP.cust_id
  cust_id: 1, name: Alice	phone_id: 101, cust_id: 1, number: 555-1234	cust_id: 1, name: Alice, phone_id: 101, number: 555-1234
  	phone_id: 102, cust_id: 1, number: 555-5678	cust_id: 1, name: Alice, phone_id: 102, number: 555-5678
  cust_id: 2, name: Bob	phone_id: 201, cust_id: 2, number: 555-9876	cust_id: 2, name: Bob, phone_id: 201, number: 555-9876

  If you then do COUNT(DISTINCT C.cust_id), it will still be correct (2 customers). But if you did COUNT(*) on the joined result, you'd get 3, which is wrong if you're trying to count *customers*.

  Solution: Be mindful of relationship cardinality. Use DISTINCT with aggregate functions where appropriate, or aggregate in subqueries/CTEs *before* joining.

• Joining on NULL values:

  Remember from Section 2 that NULL = NULL evaluates to UNKNOWN. This means that rows where the join column is NULL in both tables will not match using a standard equality join condition (ON T1.col = T2.col).

  -- If a customer's customer_id is NULL in Customers, and an order's customer_id is NULL in Orders,
  -- they will NOT join using: ON C.customer_id = O.customer_id
  -- To explicitly join on NULLs (rare but possible), you'd need:
  -- ON C.customer_id = O.customer_id OR (C.customer_id IS NULL AND O.customer_id IS NULL)

Practice & Application

-- Assume Customer and Orders tables exist with sample data:
-- Customers: { (1, 'Alice'), (2, 'Bob'), (3, 'Charlie') }
-- Orders:    { (101, 1, 50.00), (102, 2, 75.00), (103, 1, 120.00) }

SELECT
    C.customer_name,
    O.order_id,
    O.total_amount
FROM
    Customers C
INNER JOIN
    Orders O ON C.customer_id = O.customer_id;

-- Assume Customers: { (1, 'Alice'), (2, 'Bob'), (3, 'Charlie') }
-- Orders:    { (101, 1, 50.00), (102, 2, 75.00), (103, 1, 120.00) }

SELECT
    C.customer_name,
    O.order_id,
    O.total_amount
FROM
    Customers C
LEFT JOIN
    Orders O ON C.customer_id = O.customer_id;

-- Assume Customers: { (1, 'Alice'), (2, 'Bob'), (3, 'Charlie') }
-- Orders:    { (101, 1, 50.00), (102, 2, 75.00), (103, 1, 120.00), (104, 999, 200.00) } -- Order 104 has no matching customer

SELECT
    C.customer_name,
    O.order_id,
    O.total_amount
FROM
    Customers C
RIGHT JOIN
    Orders O ON C.customer_id = O.customer_id;

-- Assume Customers: { (1, 'Alice'), (2, 'Bob'), (3, 'Charlie') }
-- Orders:    { (101, 1, 50.00), (102, 2, 75.00), (103, 1, 120.00), (104, 999, 200.00) }

SELECT
    C.customer_name,
    O.order_id,
    O.total_amount
FROM
    Customers C
FULL OUTER JOIN -- Or FULL JOIN in many systems
    Orders O ON C.customer_id = O.customer_id;

-- Assume Employees table:
-- { (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2) }
-- Alice (ID 1) has no manager. Bob and Charlie report to Alice. David reports to Bob.

SELECT
    E.employee_name AS EmployeeName,
    M.employee_name AS ManagerName
FROM
    Employees E -- Alias the table as 'E' for Employee
LEFT JOIN
    Employees M ON E.manager_id = M.employee_id; -- Alias as 'M' for Manager

-- Assume tables:
-- Customers: { (1, 'Alice'), (2, 'Bob') }
-- Orders:    { (101, 1), (102, 1), (103, 2) }
-- Addresses: { (A1, 1, '123 Main St'), (A2, 1, '456 Oak Ave'), (A3, 2, '789 Pine Rd') }

-- Incorrect approach (Fan-out problem leading to inflated order counts):
-- If you first join Customers and Addresses, Alice would appear twice.
-- Then joining Orders would make Alice's orders count twice for each address.
/*
SELECT
    C.customer_name,
    A.street,
    COUNT(O.order_id) AS total_orders -- This would be inflated for Alice
FROM
    Customers C
LEFT JOIN Addresses A ON C.customer_id = A.customer_id
LEFT JOIN Orders O ON C.customer_id = O.customer_id
GROUP BY
    C.customer_id, C.customer_name, A.street;
*/

-- Correct Solution: Aggregate orders first, then join.
WITH CustomerOrderSummary AS (
    SELECT
        customer_id,
        COUNT(order_id) AS num_orders
    FROM
        Orders
    GROUP BY
        customer_id
)
SELECT
    C.customer_name,
    A.street,
    COS.num_orders -- Correct count per customer
FROM
    Customers C
LEFT JOIN
    Addresses A ON C.customer_id = A.customer_id
LEFT JOIN
    CustomerOrderSummary COS ON C.customer_id = COS.customer_id
ORDER BY
    C.customer_name, A.street;

8. Subqueries, CTEs & Query Architecture

As SQL queries become more complex, simply chaining `JOIN` and `WHERE` clauses can become unwieldy. Subqueries and Common Table Expressions (CTEs) offer powerful ways to break down complex problems into smaller, more manageable parts, improving both readability and maintainability.

Subqueries (Nested Queries)

A subquery (or inner query) is a query nested inside another SQL query (the outer query). They can be used in various parts of a SQL statement.

• Scalar Subquery: Returns one value

  A subquery that returns a single row and a single column. It can be used anywhere a single value is expected (e.g., in a SELECT list, WHERE clause comparison).

  -- Find employees whose salary is greater than the average salary
  SELECT
      employee_name,
      salary
  FROM
      Employees
  WHERE
      salary > (SELECT AVG(salary) FROM Employees); -- Scalar subquery returning one value

• Column Subquery: Returns a list (`IN` clause)

  A subquery that returns a single column but multiple rows. It's commonly used with operators like IN, NOT IN, ANY, ALL.

  -- Find employees who work in departments located in 'New York'
  SELECT
      E.employee_name,
      D.department_name
  FROM
      Employees E
  JOIN
      Departments D ON E.department_id = D.department_id
  WHERE
      D.department_id IN (SELECT department_id FROM Departments WHERE location = 'New York'); -- Column subquery returning a list

• Row/Table Subquery: Returns a virtual table (`FROM` clause)

  A subquery that returns multiple columns and multiple rows, essentially acting as a temporary table. These are often called derived tables when used in the FROM clause.

  -- Find employees whose salary is above their department's average
  SELECT
      E.employee_name,
      E.salary,
      DA.avg_dept_salary
  FROM
      Employees E
  JOIN
      (SELECT department_id, AVG(salary) AS avg_dept_salary
       FROM Employees
       GROUP BY department_id) AS DA -- Table subquery (derived table)
  ON E.department_id = DA.department_id
  WHERE
      E.salary > DA.avg_dept_salary;

• Correlated Subquery: Dependent on outer query (Performance hit)

  A subquery that depends on the outer query for its values. It executes once for each row processed by the outer query, which can lead to poor performance on large datasets.

  -- Find employees who earn more than the average salary in their *own* department
  SELECT
      E.employee_name,
      E.salary,
      E.department_id
  FROM
      Employees E
  WHERE
      E.salary > (SELECT AVG(salary) FROM Employees WHERE department_id = E.department_id);
      -- The inner subquery depends on E.department_id from the outer query.

  Correlated subqueries are often less performant than joins or CTEs because they are re-evaluated for every row of the outer query. While sometimes necessary, consider alternatives like joins with derived tables or window functions.

Common Table Expressions (CTEs)

CTEs provide a way to define a temporary, named result set that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, DELETE). They significantly improve query readability and can sometimes optimize execution.

• Syntax: WITH cte_name AS ...

  WITH DepartmentAvgSalary AS (
      SELECT
          department_id,
          AVG(salary) AS avg_dept_salary
      FROM
          Employees
      GROUP BY
          department_id
  )
  SELECT
      E.employee_name,
      E.salary,
      DAS.avg_dept_salary
  FROM
      Employees E
  JOIN
      DepartmentAvgSalary DAS ON E.department_id = DAS.department_id
  WHERE
      E.salary > DAS.avg_dept_salary;

• Readability benefits over subqueries:

  CTEs make complex queries much easier to read and understand by breaking them into logical, named steps. Instead of deeply nested subqueries, you define each step clearly at the beginning of the query.

• Reusability within the same query:

  A single CTE can be referenced multiple times within the same subsequent query. You can also chain CTEs, where one CTE references a previously defined CTE.

  WITH HighEarners AS (
      SELECT employee_id, employee_name, salary
      FROM Employees
      WHERE salary > 90000
  ),
  EngineeringHighEarners AS (
      SELECT HE.employee_id, HE.employee_name, HE.salary, D.department_name
      FROM HighEarners HE
      JOIN Departments D ON HE.employee_id = D.employee_id -- (Assuming a joinable department_id in HighEarners)
      WHERE D.department_name = 'Engineering'
  )
  SELECT * FROM EngineeringHighEarners;

  Note: The example above is conceptual. For EngineeringHighEarners to join Departments, HighEarners would need to also include `department_id`.

Best Practices

Decision Tree for Choosing CTEs vs. Subqueries vs. Temp Tables

When should you use which technique?

Practice & Application

SELECT
    E.first_name,
    E.last_name,
    E.salary
FROM
    Employees E
WHERE
    E.salary > (SELECT AVG(salary) FROM Employees); -- Scalar subquery returns single average salary

-- Assume Departments (department_id, department_name)
-- Assume Employees (employee_id, first_name, last_name, hire_date, department_id)

SELECT
    D.department_name
FROM
    Departments D
WHERE
    D.department_id IN (SELECT DISTINCT department_id
                        FROM Employees
                        WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'); -- Column subquery returns list of IDs

SELECT
    E.first_name,
    E.last_name,
    E.salary,
    DA.avg_dept_salary
FROM
    Employees E
JOIN
    (SELECT department_id, AVG(salary) AS avg_dept_salary
     FROM Employees
     GROUP BY department_id) AS DA -- Derived Table (Table Subquery)
ON E.department_id = DA.department_id
WHERE
    E.salary > DA.avg_dept_salary;

WITH DepartmentAverage AS (
    SELECT
        department_id,
        AVG(salary) AS avg_dept_salary
    FROM
        Employees
    GROUP BY
        department_id
)
SELECT
    E.first_name,
    E.last_name,
    E.salary,
    DA.avg_dept_salary
FROM
    Employees E
JOIN
    DepartmentAverage DA ON E.department_id = DA.department_id
WHERE
    E.salary > DA.avg_dept_salary;

-- Assume Employees (employee_id, first_name, last_name, salary, department_id)
-- Assume Departments (department_id, department_name)

WITH EmployeeRanked AS (
    SELECT
        E.employee_id,
        E.first_name,
        E.last_name,
        E.salary,
        E.department_id,
        ROW_NUMBER() OVER (PARTITION BY E.department_id ORDER BY E.salary DESC) AS rank_in_dept
    FROM
        Employees E
),
Top2PerDepartment AS (
    SELECT
        ER.employee_id,
        ER.first_name,
        ER.last_name,
        ER.salary,
        ER.department_id,
        ER.rank_in_dept
    FROM
        EmployeeRanked ER
    WHERE
        ER.rank_in_dept <= 2 -- Filter for the top 2 ranked employees
)
SELECT
    T2.first_name,
    T2.last_name,
    T2.salary,
    D.department_name,
    T2.rank_in_dept
FROM
    Top2PerDepartment T2
JOIN
    Departments D ON T2.department_id = D.department_id
ORDER BY
    D.department_name, T2.rank_in_dept;

9. Database Constraints & Indexing Strategy

Ensuring data quality and optimizing database performance are two critical aspects of working with SQL. Constraints guarantee the integrity of your data, while indexes dramatically speed up data retrieval.

Data Integrity Constraints

Constraints are rules enforced on data columns in a table. They are used to limit the type of data that can go into a table, ensuring the accuracy and reliability of the data.

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL -- username cannot be NULL
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) UNIQUE -- product_name must be unique
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY, -- order_id is unique and cannot be NULL
    order_date DATE
);

CREATE TABLE OrderItems (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
    -- This ensures order_id must exist in the Orders table
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10, 2) CHECK (salary > 0) -- Salary must be positive
);

CREATE TABLE Tasks (
    task_id INT PRIMARY KEY,
    task_description TEXT,
    status VARCHAR(20) DEFAULT 'Pending' -- Default status is 'Pending'
);

Indexing Fundamentals (Under the Hood)

An index is a special lookup table that the database search engine can use to speed up data retrieval. Without indexes, the database engine would have to scan every row in a table to find matching data, which is slow for large tables.

What is an Index? (The Book Index Analogy)

Think of an index in a database like the index at the back of a textbook:

• Without an index: If you want to find all mentions of "SQL Joins" in a textbook without an index, you'd have to read every single page from cover to cover. This is a "full table scan" in database terms.
• With an index: You go to the index, find "SQL Joins", see it's on pages 57, 123, and 201. You immediately jump to those pages. This is how a database uses an index to quickly locate relevant rows.

-- Creating a simple index on a column
CREATE INDEX idx_employee_lastname
ON Employees (last_name);

-- Creating a composite index on multiple columns
CREATE INDEX idx_order_customer_date
ON Orders (customer_id, order_date DESC);

Clustered (Physical order) vs. Non-Clustered (Logical pointer)

These are the two main types of indexes, differing in how they store and manage data.

Trade-offs: Read speed increase vs. Write/Update speed decrease

Indexes are not a free lunch. While they significantly boost read performance (SELECT statements), they come with costs:

• ✅ Pros (Reads):
  • Faster data retrieval for queries using WHERE clauses, JOIN conditions, ORDER BY, GROUP BY on indexed columns.
  • Reduces I/O operations by allowing direct access to relevant data instead of full table scans.
• ❌ Cons (Writes/Updates):
  • Increased Storage: Indexes consume disk space (as they are separate data structures).
  • Slower DML Operations: Every time you INSERT, UPDATE, or DELETE a row in the main table, the corresponding indexes also need to be updated. This adds overhead and slows down write operations.
  • Maintenance Overhead: The database engine needs to maintain indexes, which can impact performance, especially for tables with high write activity.

B-Tree Structure Diagram (Simplified)

Most SQL databases use B-tree (Balanced Tree) structures for their indexes. This structure allows for efficient searching, insertion, and deletion.

Practice & Application

-- First, assume Students and Courses tables already exist with appropriate PKs:
-- CREATE TABLE Students (student_id INT PRIMARY KEY, student_name VARCHAR(100));
-- CREATE TABLE Courses (course_id INT PRIMARY KEY, course_name VARCHAR(100));

CREATE TABLE CourseEnrollments (
    enrollment_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- PostgreSQL syntax for auto-increment PK
    -- (For MySQL, use INT AUTO_INCREMENT PRIMARY KEY)
    -- (For SQL Server, use INT IDENTITY(1,1) PRIMARY KEY)
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade VARCHAR(2) CHECK (grade IN ('A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'F')),
    
    -- Composite UNIQUE key for student_id and course_id
    CONSTRAINT UQ_StudentCourse UNIQUE (student_id, course_id),
    
    -- Foreign Key referencing Students table
    CONSTRAINT FK_StudentEnrollment FOREIGN KEY (student_id) REFERENCES Students(student_id),
    
    -- Foreign Key referencing Courses table
    CONSTRAINT FK_CourseEnrollment FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    registration_date DATE
);

-- 1. Make the 'email' column NOT NULL
ALTER TABLE Users
ALTER COLUMN email SET NOT NULL; -- PostgreSQL syntax
-- For SQL Server: ALTER TABLE Users ALTER COLUMN email VARCHAR(100) NOT NULL;
-- For MySQL: ALTER TABLE Users MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- 2. Add 'last_login_timestamp' with a default value
ALTER TABLE Users
ADD COLUMN last_login_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP; -- Or NOW() in MySQL/SQL Server

SELECT transaction_id, transaction_date, amount
FROM Transactions
WHERE customer_id = 12345
  AND transaction_date BETWEEN '2023-01-01' AND '2023-01-31'
  AND status = 'completed'
ORDER BY transaction_date DESC;

CREATE INDEX idx_transactions_customer_date_status_amount
ON Transactions (customer_id, transaction_date DESC, status, amount);

10. Set Operations

Set operations in SQL allow you to combine the results of two or more SELECT statements into a single result set. Unlike joins, which combine data horizontally based on related columns, set operations combine data vertically, stacking one result set on top of another.

Vertical Combination of Data

Let's explore the primary set operators:

-- Assume two tables with similar structures:
-- Employees (employee_id, name, city)
-- Contractors (contractor_id, name, city)
-- Both have a 'name' and 'city' column for demonstration.

• UNION (Removes duplicates, slower):

  Combines the result sets of two or more SELECT statements and returns all distinct rows. Any duplicate rows across the combined result sets are removed.

  SELECT name, city FROM Employees
  UNION
  SELECT name, city FROM Contractors;
  -- Result: All unique names and cities from both tables. If 'John, New York' appears in both, it will only be listed once.

• UNION ALL (Keeps duplicates, faster):

  Combines the result sets of two or more SELECT statements, but unlike UNION, it retains all duplicate rows. This makes it generally faster as the database doesn't need to perform a distinct sort operation.

  SELECT name, city FROM Employees
  UNION ALL
  SELECT name, city FROM Contractors;
  -- Result: All names and cities from both tables, including duplicates. If 'John, New York' appears in both, it will be listed twice.

• INTERSECT (Rows in both sets):

  Returns only the rows that are present in both result sets. Only rows that are identical in all selected columns will be returned.

  SELECT name, city FROM Employees
  INTERSECT
  SELECT name, city FROM Contractors;
  -- Result: Only names and cities that exist in BOTH Employees AND Contractors.

• EXCEPT / MINUS (Rows in A but not B):

  Returns rows from the first SELECT statement that are not found in the second SELECT statement. (MINUS is used in Oracle, EXCEPT in SQL Server, PostgreSQL, MySQL 8.0+).

  SELECT name, city FROM Employees
  EXCEPT -- Or MINUS in Oracle
  SELECT name, city FROM Contractors;
  -- Result: Names and cities that are in Employees but NOT in Contractors.

Requirements

For set operations to work correctly, the participating SELECT statements must adhere to two crucial rules:

• ✅ Column Count Match:

  The number of columns in all SELECT statements being combined must be identical.

  -- This would fail because the second SELECT has fewer columns
  -- SELECT name, city FROM Employees
  -- UNION
  -- SELECT name FROM Contractors; -- ERROR: column mismatch

• ✅ Data Type Compatibility:

  The data types of corresponding columns in each SELECT statement must be compatible (e.g., you can't `UNION` an `INT` with a `VARCHAR` in the same column position). Implicit conversion often occurs if types are related (e.g., `INT` to `DECIMAL`), but it's best to be explicit or ensure types are similar.

  -- This might lead to unexpected results or errors if type mismatch is severe
  -- SELECT employee_id, name FROM Employees
  -- UNION
  -- SELECT date_of_birth, full_name FROM Contacts; -- ERROR or weird conversion

Edge Case: Sorting results in Set Operations

When you use UNION, UNION ALL, INTERSECT, or EXCEPT, the ORDER BY clause can only be specified once, at the very end of the entire combined query. It applies to the final, consolidated result set.

-- Incorrect: ORDER BY inside each SELECT
-- (SELECT name FROM Employees ORDER BY name) UNION (SELECT name FROM Contractors ORDER BY name); -- Syntax Error

-- Correct: ORDER BY at the end, referring to columns by their final selected name/position
SELECT name, city FROM Employees
UNION ALL
SELECT name, city FROM Contractors
ORDER BY name ASC, city DESC; -- Applies to the combined result set

Practice & Application

-- Sample Data (conceptual):
-- Employees: { ('E1', 'Alice', 'HR', 'alice@example.com'), ('E2', 'Bob', 'IT', 'bob@example.com'), ('E3', 'Charlie', 'Sales', 'charlie@example.com') }
-- Contractors: { ('C1', 'Bob', 'IT', 'bob@example.com'), ('C2', 'David', 'Marketing', 'david@example.com') }

SELECT name, department, email FROM Employees
UNION ALL
SELECT name, department, email FROM Contractors
ORDER BY name;

-- Sample Data (conceptual):
-- Employees: { ('E1', 'Alice', 'HR', 'alice@example.com'), ('E2', 'Bob', 'IT', 'bob@example.com'), ('E3', 'Charlie', 'Sales', 'charlie@example.com') }
-- Contractors: { ('C1', 'Bob', 'IT', 'bob@example.com'), ('C2', 'David', 'Marketing', 'david@example.com') }

SELECT email FROM Employees
UNION
SELECT email FROM Contractors
ORDER BY email;

-- Sample Data (conceptual):
-- Employees: { ('E1', 'Alice', 'HR', 'alice@example.com'), ('E2', 'Bob', 'IT', 'bob@example.com'), ('E3', 'Charlie', 'Sales', 'charlie@example.com') }
-- Contractors: { ('C1', 'Bob', 'IT', 'bob@example.com'), ('C2', 'David', 'Marketing', 'david@example.com'), ('C3', 'Alice', 'Sales', 'alice@example.com') }
-- (Note: Alice in Contractors has a different department, but same name/email)

SELECT name, email FROM Employees
INTERSECT
SELECT name, email FROM Contractors
ORDER BY name;

-- Sample Data (conceptual):
-- Employees: { ('E1', 'Alice', 'HR', 'alice@example.com'), ('E2', 'Bob', 'IT', 'bob@example.com'), ('E3', 'Charlie', 'Sales', 'charlie@example.com') }
-- Contractors: { ('C1', 'Bob', 'IT', 'bob@example.com'), ('C2', 'David', 'Marketing', 'david@example.com'), ('C3', 'Alice', 'Sales', 'alice@example.com') }

SELECT name, email FROM Employees
EXCEPT -- Use MINUS in Oracle
SELECT name, email FROM Contractors
ORDER BY name;

11. Views and Stored Procedures

Beyond raw tables and ad-hoc queries, databases offer mechanisms to encapsulate logic and improve performance, security, and reusability. Views and Stored Procedures are two such powerful features.

Views (Virtual Tables)

A view is a virtual table based on the result-set of a SQL query. It contains rows and columns, just like a real table, but it does not store data itself. Instead, it retrieves data from the underlying tables whenever it is queried.

CREATE VIEW ActiveEmployees AS
SELECT
    employee_id,
    first_name,
    last_name,
    email,
    department_id
FROM
    Employees
WHERE
    status = 'Active';

-- You can now query the view just like a table:
SELECT * FROM ActiveEmployees WHERE department_id = 1;

• Hiding complexity / Business logic encapsulation:

  Views allow you to pre-package complex queries (e.g., involving multiple joins, aggregations, or complex CASE statements) and present them as a simple table. This simplifies interactions for users or applications who don't need to understand the underlying complexity.

  -- Instead of users writing this complex query every time:
  SELECT
      C.customer_name,
      SUM(O.total_amount) AS total_spent,
      COUNT(O.order_id) AS num_orders
  FROM
      Customers C
  LEFT JOIN
      Orders O ON C.customer_id = O.customer_id
  GROUP BY
      C.customer_name
  HAVING
      COUNT(O.order_id) > 0;
  
  -- You can define a view:
  CREATE VIEW CustomerSpendingSummary AS
  SELECT
      C.customer_name,
      SUM(O.total_amount) AS total_spent,
      COUNT(O.order_id) AS num_orders
  FROM
      Customers C
  LEFT JOIN
      Orders O ON C.customer_id = O.customer_id
  GROUP BY
      C.customer_name
  HAVING
      COUNT(O.order_id) > 0;
  
  -- And then users just query the view:
  SELECT * FROM CustomerSpendingSummary WHERE total_spent > 1000;

• Security (Restricting access to specific columns/rows):

  Views can act as a security mechanism. Instead of granting users direct access to sensitive base tables, you can grant them access only to a view that exposes a subset of columns or rows. For example, a view might exclude salary or personal identifying information.

  -- Granting a user access to the Employees table exposes ALL columns.
  -- Instead, create a view that hides sensitive data:
  CREATE VIEW PublicEmployeeInfo AS
  SELECT
      employee_id,
      first_name,
      last_name,
      department_id
  FROM
      Employees;
  
  -- Then grant access ONLY to the view:
  -- GRANT SELECT ON PublicEmployeeInfo TO 'reporting_user';

• Materialized Views (Cached data for performance - Conceptual):

  While standard views are virtual and re-execute their query every time they are accessed, materialized views (supported in some RDBMS like PostgreSQL, Oracle, SQL Server) are different. They physically store the result of the query at the time of their creation or last refresh.

  • ✅ Pros: Much faster read performance, especially for complex aggregations, as the data is pre-calculated.
  • ❌ Cons: The data in a materialized view can become stale. It needs to be explicitly refreshed (manually or on a schedule) to reflect changes in the underlying base tables.

  -- PostgreSQL syntax example:
  CREATE MATERIALIZED VIEW DailySalesSummary AS
  SELECT
      order_date,
      SUM(total_amount) AS daily_revenue,
      COUNT(order_id) AS total_orders
  FROM
      Orders
  GROUP BY
      order_date;
  
  -- To update its data:
  -- REFRESH MATERIALIZED VIEW DailySalesSummary;

Stored Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. It's a collection of SQL statements and control-of-flow statements (like `IF`, `LOOP`).

-- PostgreSQL syntax example (syntax varies significantly by RDBMS)
CREATE PROCEDURE GetEmployeeCountByDepartment(
    IN dept_id INT,
    OUT employee_count INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO employee_count
    FROM Employees
    WHERE department_id = dept_id;
END;
$$;

-- To execute (PostgreSQL):
-- CALL GetEmployeeCountByDepartment(1, NULL); -- Result will be in a message or output parameter
-- For SQL Server: EXEC GetEmployeeCountByDepartment @dept_id = 1, @employee_count = @count OUTPUT;

• Pre-compiled SQL code:

  When a stored procedure is created, the database compiles it once and stores the execution plan. Subsequent executions use this pre-compiled plan, which can result in faster performance compared to executing raw SQL statements repeatedly.

• Accepting parameters:

  Stored procedures can accept input parameters, allowing for dynamic and flexible execution. They can also return output parameters or result sets.

  -- Example with input and output parameters
  CREATE PROCEDURE UpdateEmployeeSalary (
      IN employee_id_param INT,
      IN percentage_raise DECIMAL(5, 2),
      OUT new_salary_out DECIMAL(10, 2)
  )
  LANGUAGE plpgsql
  AS $$
  BEGIN
      UPDATE Employees
      SET salary = salary * (1 + percentage_raise)
      WHERE employee_id = employee_id_param;
  
      SELECT salary INTO new_salary_out
      FROM Employees
      WHERE employee_id = employee_id_param;
  END;
  $$;
  
  -- CALL UpdateEmployeeSalary(101, 0.05, NULL);

• Reducing network traffic:

  Instead of sending multiple complex SQL statements over the network, an application can simply send a single command to execute a stored procedure. The procedure then executes on the database server, reducing network latency and traffic.

• Security: Similar to views, stored procedures can provide an extra layer of security. Users can be granted permission to execute a procedure without having direct permissions on the underlying tables, protecting sensitive data and enforcing business rules.
• Encapsulation of Business Logic: Complex business rules and workflows can be implemented directly within the database, ensuring consistency across all applications that interact with the data.

Practice & Application

-- Assume an existing 'Employees' table with:
-- employee_id, first_name, last_name, salary, department_id, hire_date, email, is_active

CREATE VIEW HR_Employee_Overview AS
SELECT
    employee_id,
    first_name,
    last_name,
    department_id,
    hire_date
FROM
    Employees
WHERE
    is_active = TRUE; -- Assuming a boolean 'is_active' column

-- To use the view:
-- SELECT * FROM HR_Employee_Overview WHERE department_id = 5;

-- Assume Customers (customer_id, customer_name, email)
-- Assume Orders (order_id, customer_id, order_date, total_amount, status)

CREATE VIEW Customer_Revenue_Summary AS
SELECT
    C.customer_name,
    COUNT(O.order_id) AS total_orders,
    SUM(O.total_amount) AS total_spending
FROM
    Customers C
INNER JOIN -- Use INNER JOIN to only include customers who have placed orders
    Orders O ON C.customer_id = O.customer_id
GROUP BY
    C.customer_name
ORDER BY
    total_spending DESC; -- ORDER BY is allowed within the view definition, but usually applied to query against view. This is for presentation.

-- To use the view:
-- SELECT customer_name, total_spending FROM Customer_Revenue_Summary WHERE total_orders > 5;

-- Assume Employees (employee_id, first_name, department_id)
-- Assume Departments (department_id, department_name)

-- PostgreSQL syntax (syntax varies significantly by RDBMS, e.g., SQL Server uses CREATE PROCEDURE)
CREATE OR REPLACE PROCEDURE TransferEmployee(
    IN p_employee_id INT,
    IN p_new_department_id INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    dept_exists BOOLEAN;
BEGIN
    -- Check if the new department ID exists
    SELECT EXISTS (SELECT 1 FROM Departments WHERE department_id = p_new_department_id)
    INTO dept_exists;

    IF dept_exists THEN
        UPDATE Employees
        SET department_id = p_new_department_id
        WHERE employee_id = p_employee_id;
        RAISE NOTICE 'Employee % transferred to Department % successfully.', p_employee_id, p_new_department_id;
    ELSE
        RAISE EXCEPTION 'Department ID % does not exist.', p_new_department_id;
    END IF;
END;
$$;

-- To execute (PostgreSQL):
-- CALL TransferEmployee(101, 2);   -- Should succeed
-- CALL TransferEmployee(102, 999); -- Should raise an exception/error

-- Assume Products (product_id, product_name, price, category_id)

-- PostgreSQL syntax (syntax varies significantly by RDBMS)
CREATE OR REPLACE PROCEDURE GetProductsByPriceRange(
    IN p_min_price DECIMAL(10, 2),
    IN p_max_price DECIMAL(10, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT
        product_id,
        product_name,
        price
    FROM
        Products
    WHERE
        price BETWEEN p_min_price AND p_max_price
    ORDER BY
        price ASC;
END;
$$;

-- To execute (PostgreSQL):
-- CALL GetProductsByPriceRange(10.00, 50.00);

12. Window Functions (The "Cheat Code")

Window functions are a powerful and often "advanced" SQL feature that allow you to perform calculations across a set of table rows that are related to the current row. What makes them unique is that, unlike aggregate functions (like SUM() with GROUP BY), window functions do not collapse the rows into a single output row; they retain the individual row details while still providing aggregated or ranked insights.

Conceptual Understanding

• Aggregation without collapsing rows:

  With GROUP BY and aggregate functions, you get one row per group. With window functions, you get one output row for each input row, but each output row includes a calculated value based on a "window" of related rows.

  -- Example: Get each employee's salary AND the average salary of their department
  SELECT
      employee_id,
      first_name,
      salary,
      department_id,
      AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary -- Window function!
  FROM
      Employees;
  -- This returns every employee's row, with an additional column showing their department's average.

• The OVER() clause anatomy:

  The magic of window functions lies within the OVER() clause. It defines the "window" or set of rows on which the function operates. It has two main components:

  • PARTITION BY: Divides the result set into partitions (groups) to which the window function is independently applied. Think of it as the "grouping" mechanism for the window.
  • ORDER BY: Orders the rows within each partition. This is crucial for functions that depend on order, like ranking or lead/lag.

Ranking Functions

These functions assign a rank to each row within its partition based on the specified order.

• ROW_NUMBER() (Unique increment):

  Assigns a unique, sequential integer to each row within its partition, starting from 1. If there are ties in the ordering, ROW_NUMBER() assigns arbitrary but unique ranks.

  SELECT
      employee_id,
      first_name,
      salary,
      department_id,
      ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank_in_dept
  FROM
      Employees;

• RANK() (Skips numbers on ties):

  Assigns the same rank to rows that have identical values in the ORDER BY clause. If there are ties, the next rank value will "skip" numbers (e.g., if two rows are rank 1, the next unique rank will be 3).

• DENSE_RANK() (No skipping on ties):

  Similar to RANK(), it assigns the same rank to tied rows. However, DENSE_RANK() does not skip rank numbers after a tie (e.g., if two rows are rank 1, the next unique rank will be 2).

Table Comparison of Ranking Functions

Let's illustrate ROW_NUMBER(), RANK(), and DENSE_RANK() within a single partition (e.g., for one department, ordered by salary DESC).

Positional Functions

These functions allow you to access data from preceding or succeeding rows within the current row's window.

• LAG() (Previous row access):

  Retrieves a value from a row that comes before the current row within the partition, based on the `ORDER BY` clause. Useful for calculating differences or seeing previous states.

  -- Get each employee's salary and the salary of the employee hired immediately before them in their department
  SELECT
      employee_id,
      first_name,
      hire_date,
      salary,
      department_id,
      LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date) AS previous_hire_salary
  FROM
      Employees;

• LEAD() (Next row access):

  Retrieves a value from a row that comes after the current row within the partition, based on the `ORDER BY` clause. Useful for forecasting or comparing with future states.

  -- Get each employee's salary and the salary of the employee hired immediately after them in their department
  SELECT
      employee_id,
      first_name,
      hire_date,
      salary,
      department_id,
      LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS next_hire_salary -- 0 is default if no next row
  FROM
      Employees;

Partitioning & Ordering

The `OVER()` clause components are critical for defining the scope and sequence of your window function calculations.

• PARTITION BY (Resetting the window):

  Defines the groups of rows over which the window function operates. When the `PARTITION BY` column's value changes, the window (and thus the function's calculation) "resets".

  Example: AVG(salary) OVER (PARTITION BY department_id) calculates a separate average for each unique department_id.

  If `PARTITION BY` is omitted, the entire result set is treated as a single partition (one big window).

• ORDER BY (Sorting within the window):

  Determines the logical order of rows within each partition. This is especially important for ranking functions (like ROW_NUMBER()) and positional functions (like LAG()/LEAD()) as their results are order-dependent.

  Example: ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) ranks employees within each department from highest to lowest salary.

Practice & Application

-- Assume Employees table: (employee_id, first_name, last_name, salary, department_id, hire_date)

SELECT
    first_name,
    last_name,
    salary,
    department_id,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary,
    MAX(salary) OVER (PARTITION BY department_id) AS max_dept_salary
FROM
    Employees
ORDER BY
    department_id, salary DESC;

-- Assume Employees table with sample data for department 101:
-- | employee_id | first_name | salary | department_id |
-- |-------------|------------|--------|---------------|
-- | 1           | Alice      | 100000 | 101           |
-- | 2           | Bob        | 90000  | 101           |
-- | 3           | Charlie    | 80000  | 101           |
-- | 4           | David      | 80000  | 101           |
-- | 5           | Eve        | 70000  | 101           |
-- | 6           | Frank      | 70000  | 101           |

SELECT
    first_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val
FROM
    Employees
WHERE
    department_id = 101
ORDER BY
    salary DESC, first_name;

-- Assume Employees table: (employee_id, first_name, last_name, salary, department_id, hire_date)

SELECT
    first_name,
    last_name,
    hire_date,
    salary,
    department_id,
    LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date ASC) AS previous_hire_salary
FROM
    Employees
ORDER BY
    department_id, hire_date ASC;

-- Assume Orders table: (order_id, customer_id, order_date, amount)

SELECT
    customer_id,
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_spending
    -- The default frame for ORDER BY is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
FROM
    Orders
ORDER BY
    customer_id, order_date ASC;

13. Common SQL Interview Scenarios & Patterns

SQL interviews often revolve around a set of recurring problems and data manipulation challenges. Mastering these patterns will significantly boost your confidence and performance.

Duplicate Handling

Dealing with duplicate records is a very common task in data cleaning and analysis.

• Finding duplicates (`GROUP BY` + `HAVING count > 1`):

  To identify rows that have duplicate values for one or more columns, group by those columns and use HAVING to filter for groups with a count greater than 1.

  -- Find duplicate emails in the Employees table
  SELECT
      email,
      COUNT(email) AS num_duplicates
  FROM
      Employees
  GROUP BY
      email
  HAVING
      COUNT(email) > 1;

  This identifies the *values* that are duplicated. To see the *actual duplicate rows*, you'd typically join this result back to the original table.
• Deleting duplicates (Using CTEs or Row_Number):

  This is a trickier task as you usually want to keep one instance of the duplicate. Window functions, especially ROW_NUMBER(), are ideal for this.

  -- Delete duplicate rows, keeping one instance (e.g., the one with the lowest employee_id)
  WITH EmployeeDuplicates AS (
      SELECT
          *, -- Select all columns to uniquely identify the row
          ROW_NUMBER() OVER (PARTITION BY email ORDER BY employee_id ASC) AS rn
      FROM
          Employees
  )
  DELETE FROM Employees
  WHERE employee_id IN (
      SELECT employee_id FROM EmployeeDuplicates WHERE rn > 1
  );
  -- Or if your RDBMS supports DELETE FROM CTE directly (e.g., SQL Server):
  -- DELETE FROM EmployeeDuplicates WHERE rn > 1;

Top N Analysis

Finding the highest, lowest, or "N-th" value/record is a frequent interview question.

• Finding N-th highest/lowest value (Subquery vs. DENSE_RANK):

  Window functions provide the most elegant solution for this. DENSE_RANK() is often preferred over RANK() because it handles ties without skipping subsequent ranks, which typically aligns with how "N-th" is interpreted.

  -- Find the 2nd highest salary
  WITH RankedSalaries AS (
      SELECT
          employee_id,
          first_name,
          last_name,
          salary,
          DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
      FROM
          Employees
  )
  SELECT
      first_name,
      last_name,
      salary
  FROM
      RankedSalaries
  WHERE
      salary_rank = 2;

Step-by-step logic for N-th highest value

Let's illustrate how DENSE_RANK() helps find the N-th highest value, using the 2nd highest salary as an example.

Cumulative Metrics

Calculating running totals or moving averages helps analyze trends over time.

• Running Totals (`SUM() OVER(ORDER BY...)`):

  As seen in the Window Functions section, this is a direct application of the SUM() aggregate as a window function with an implicit (or explicit) frame clause.

  -- Calculate a running total of order amounts by customer over time
  SELECT
      customer_id,
      order_date,
      amount,
      SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total_amount
  FROM
      Orders
  ORDER BY
      customer_id, order_date;

• Moving Averages:

  Similar to running totals, but you define a "moving window" (frame) to calculate the average over a specified number of preceding/following rows.

  -- Calculate a 3-day moving average of daily sales
  SELECT
      sale_date,
      daily_sales,
      AVG(daily_sales) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_moving_avg
  FROM
      DailySales;

Data Transformation

SQL provides many functions to clean, reformat, and derive new values from existing data.

• String manipulation (`LEFT`, `RIGHT`, `SUBSTRING`, `CONCAT`):

  Essential for cleaning, parsing, and formatting text data.

  SELECT
      full_name,
      LEFT(full_name, 5) AS first_5_chars,
      RIGHT(full_name, 3) AS last_3_chars,
      SUBSTRING(full_name, 7, 4) AS middle_4_chars, -- Start at 7th char, take 4
      CONCAT(first_name, ' ', last_name) AS full_name_concat
  FROM
      Customers;

• Date manipulation (`DATEDIFF`, `DATEADD`, Extracting Year/Month):

  Crucial for time-series analysis, age calculations, and filtering by date parts.

  SELECT
      order_date,
      EXTRACT(YEAR FROM order_date) AS order_year, -- PostgreSQL/MySQL
      EXTRACT(MONTH FROM order_date) AS order_month,
      DATEDIFF('day', hire_date, CURRENT_DATE) AS days_since_hire, -- PostgreSQL/SQL Server syntax differs
      DATEADD('month', 3, order_date) AS three_months_later_date -- PostgreSQL/SQL Server syntax differs
  FROM
      Orders;

• Coalescing (`COALESCE` to handle NULLs):

  COALESCE() returns the first non-NULL expression in a list. It's excellent for providing default values when a column might be NULL.

  -- Display phone_number, but if NULL, display 'N/A'
  SELECT
      employee_id,
      first_name,
      COALESCE(phone_number, 'N/A') AS contact_phone
  FROM
      Employees;

Practice & Application

-- Assume Customers table:
-- (customer_id INT PRIMARY KEY, first_name VARCHAR, last_name VARCHAR, email VARCHAR)
-- Sample data could have:
-- (1, 'Alice', 'Smith', 'alice@example.com')
-- (2, 'Bob', 'Jones', 'bob@example.com')
-- (3, 'Charlie', 'Brown', 'alice@example.com') -- Duplicate email for Alice
-- (4, 'Diana', 'Prince', 'diana@example.com')
-- (5, 'Eve', 'Green', 'bob@example.com')     -- Duplicate email for Bob

-- 1. Find all duplicate email addresses
SELECT
    email,
    COUNT(email) AS duplicate_count
FROM
    Customers
GROUP BY
    email
HAVING
    COUNT(email) > 1;

-- 2. Delete duplicate customers, keeping the one with the lowest customer_id
WITH RankedCustomers AS (
    SELECT
        customer_id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id ASC) AS rn
    FROM
        Customers
)
DELETE FROM Customers
WHERE customer_id IN (
    SELECT customer_id FROM RankedCustomers WHERE rn > 1
);
-- For SQL Server, a more direct DELETE from CTE might be used:
-- DELETE RC FROM RankedCustomers RC WHERE RC.rn > 1;

-- Assume Products and OrderItems tables exist.
-- Sample data:
-- Products: (1, 'Laptop', 101), (2, 'Mouse', 101), (3, 'Keyboard', 101), (4, 'Monitor', 101), (5, 'Desk', 102), (6, 'Chair', 102)
-- OrderItems: (OI1, 1, 5, 1000), (OI2, 1, 2, 1000), (OI3, 2, 10, 20), (OI4, 3, 8, 50), (OI5, 4, 3, 200), (OI6, 5, 2, 150), (OI7, 5, 1, 150), (OI8, 6, 4, 80)

WITH ProductSales AS (
    SELECT
        P.product_id,
        P.product_name,
        P.category_id,
        SUM(OI.quantity * OI.price_per_unit) AS total_revenue
    FROM
        Products P
    JOIN
        OrderItems OI ON P.product_id = OI.product_id
    GROUP BY
        P.product_id, P.product_name, P.category_id
),
RankedProductSales AS (
    SELECT
        product_id,
        product_name,
        category_id,
        total_revenue,
        DENSE_RANK() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) AS rank_in_category
    FROM
        ProductSales
)
SELECT
    product_name,
    category_id,
    total_revenue
FROM
    RankedProductSales
WHERE
    rank_in_category = 3
ORDER BY
    category_id, total_revenue DESC;

-- Assume DailySales table: (sale_date DATE, total_sales_amount DECIMAL)

WITH MonthlySales AS (
    SELECT
        DATE_TRUNC('month', sale_date) AS sales_month, -- PostgreSQL/MySQL; for SQL Server, use DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1)
        SUM(total_sales_amount) AS monthly_total_sales
    FROM
        DailySales
    GROUP BY
        DATE_TRUNC('month', sale_date)
),
LaggedMonthlySales AS (
    SELECT
        sales_month,
        monthly_total_sales,
        LAG(monthly_total_sales, 1) OVER (ORDER BY sales_month ASC) AS previous_month_sales
    FROM
        MonthlySales
)
SELECT
    sales_month,
    monthly_total_sales,
    previous_month_sales,
    CASE
        WHEN previous_month_sales IS NULL THEN NULL -- No previous month to compare
        WHEN previous_month_sales = 0 THEN NULL -- Avoid division by zero if previous month had 0 sales
        ELSE (monthly_total_sales - previous_month_sales) * 100.0 / previous_month_sales
    END AS growth_percentage
FROM
    LaggedMonthlySales
ORDER BY
    sales_month ASC;

-- Assume Users table: (user_id, first_name, last_name, email, phone_number)

SELECT
    user_id,
    CONCAT(last_name, ', ', first_name) AS full_name, -- 1. Concatenate for full name
    SUBSTRING(email FROM POSITION('@' IN email) + 1) AS email_domain, -- 2. Extract domain (PostgreSQL/MySQL)
    -- For SQL Server: SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS email_domain,
    COALESCE(phone_number, 'Contact Email') AS preferred_contact -- 3. Handle NULL phone number
FROM
    Users
ORDER BY
    full_name;

14. Advanced Topics & ACID

Moving beyond basic querying and data manipulation, understanding transactions, concurrency, and database design principles (normalization) is crucial for building robust, reliable, and performant database systems. These topics are often explored in more advanced SQL interview questions.

Transactions & Concurrency

A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database. Transactions are critical for maintaining data integrity, especially in multi-user environments.

ACID Properties: The Pillars of Reliability

The reliability of database transactions is described by the ACID properties:

Diagram of a Transaction Lifecycle (Begin → Commit/Rollback)

-- Example of a transaction
BEGIN TRANSACTION; -- Start the transaction

UPDATE Accounts SET balance = balance - 100 WHERE account_id = 'A123';
INSERT INTO Transactions (from_account, to_account, amount) VALUES ('A123', 'B456', 100);
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 'B456';

-- Check if everything went well (e.g., no negative balances, etc.)
-- IF conditions_met THEN
    COMMIT; -- Save changes to the database
-- ELSE
    ROLLBACK; -- Undo all changes made since BEGIN TRANSACTION
-- END IF;

Normalization (Database Design)

Normalization is a systematic approach to designing relational database schemas to minimize data redundancy and improve data integrity. It involves breaking down a large table into smaller, related tables and defining relationships between them.

• 1NF (First Normal Form): Atomic values
  • Each column must contain atomic (indivisible) values. No multi-valued attributes or repeating groups.
  • Each row must be unique (have a primary key).
  Violation Example: A 'phone_numbers' column containing "555-1234, 555-5678" for a single person.
• 2NF (Second Normal Form): No partial dependencies
  • Must be in 1NF.
  • All non-key attributes must be fully functionally dependent on the entire primary key. (This applies only to tables with composite primary keys).
  Violation Example: If `(order_id, product_id)` is a composite PK, and `product_name` depends only on `product_id` (a *part* of the PK), not on `order_id`. This should be in a separate `Products` table.
• 3NF (Third Normal Form): No transitive dependencies
  • Must be in 2NF.
  • All non-key attributes must be non-transitively dependent on the primary key. This means no non-key attribute should depend on another non-key attribute.
  Violation Example: In an `Employees` table, if `department_name` depends on `department_id`, and `department_id` depends on `employee_id`. Here, `department_name` transitively depends on `employee_id` via `department_id`. `department_name` should be in a separate `Departments` table.
• Denormalization (When to break rules for performance):

  Sometimes, for specific performance reasons (e.g., complex reporting, reducing joins on read-heavy systems), you might intentionally introduce redundancy into your database design. This is called denormalization.

  • ✅ Pros: Faster read queries (fewer joins), simplified queries.
  • ❌ Cons: Increased data redundancy, higher risk of data inconsistency, slower write operations (due to updating redundant data).
  Denormalization is a conscious trade-off made after careful performance analysis, not a default design choice.

Practice & Application

-- Assume Accounts table: (account_id INT PRIMARY KEY, balance DECIMAL(10, 2))
-- Initial state: Account 1 has $500, Account 2 has $100.
-- Let's test with Account 1 having $50 for the ROLLBACK case after running the success case.

BEGIN TRANSACTION;

-- Check balance of Account 1
DECLARE @account_A_balance DECIMAL(10, 2);
SELECT @account_A_balance = balance FROM Accounts WHERE account_id = 1;

IF @account_A_balance >= 100 THEN
    -- Debit Account 1
    UPDATE Accounts
    SET balance = balance - 100
    WHERE account_id = 1;

    -- Credit Account 2
    UPDATE Accounts
    SET balance = balance + 100
    WHERE account_id = 2;

    -- All operations successful, commit the transaction
    COMMIT;
    RAISE NOTICE 'Transfer successful!';
ELSE
    -- Insufficient funds, roll back the transaction
    ROLLBACK;
    RAISE NOTICE 'Transfer failed: Insufficient funds in Account 1.';
END IF;

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_address VARCHAR(255),
    phone_numbers VARCHAR(255) -- Stores multiple phone numbers like "555-1234, 555-5678"
);

INSERT INTO Customers VALUES (1, 'Alice', '123 Main St', '555-1234, 555-5678');
INSERT INTO Customers VALUES (2, 'Bob', '456 Oak Ave', '555-9999');

-- Original Customers table remains (without phone_numbers column or just with a single main_phone column)
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_address VARCHAR(255)
);

-- New table for phone numbers
CREATE TABLE CustomerPhones (
    phone_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- Surrogate PK for phone number entry
    customer_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    CONSTRAINT FK_CustomerPhone FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    CONSTRAINT UQ_CustomerPhoneNumber UNIQUE (customer_id, phone_number) -- A customer can't have the same phone number twice
);

-- Sample Data after normalization:
-- Customers:
-- (1, 'Alice', '123 Main St')
-- (2, 'Bob', '456 Oak Ave')

-- CustomerPhones:
-- (101, 1, '555-1234')
-- (102, 1, '555-5678')
-- (103, 2, '555-9999')

CREATE TABLE OrderDetails (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100), -- Product name depends only on product_id
    quantity INT,
    price_per_unit DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO OrderDetails VALUES (101, 1, 'Laptop', 1, 1200.00);
INSERT INTO OrderDetails VALUES (101, 2, 'Mouse', 2, 25.00);
INSERT INTO OrderDetails VALUES (102, 1, 'Laptop', 1, 1200.00);

-- Original OrderDetails table modified to store only order-specific item details
CREATE TABLE OrderItems (
    order_id INT,
    product_id INT,
    quantity INT,
    price_per_unit DECIMAL(10, 2), -- This price might vary per order, so keep it here
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT FK_OrderItemOrder FOREIGN KEY (order_id) REFERENCES Orders(order_id), -- Assuming an Orders table exists
    CONSTRAINT FK_OrderItemProduct FOREIGN KEY (product_id) REFERENCES Products(product_id) -- References the new Products table
);

-- New Products table for product-specific information
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL UNIQUE
);

-- Sample Data after normalization:
-- OrderItems:
-- (101, 1, 1, 1200.00)
-- (101, 2, 2, 25.00)
-- (102, 1, 1, 1200.00)

-- Products:
-- (1, 'Laptop')
-- (2, 'Mouse')

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100) -- Department name depends on department_id
);

INSERT INTO Employees VALUES (1, 'Alice', 10, 'Sales');
INSERT INTO Employees VALUES (2, 'Bob', 20, 'Marketing');
INSERT INTO Employees VALUES (3, 'Charlie', 10, 'Sales');

-- Employees table
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    CONSTRAINT FK_EmployeeDepartment FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

-- New Departments table
CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
);

-- Sample Data after normalization:
-- Employees:
-- (1, 'Alice', 10)
-- (2, 'Bob', 20)
-- (3, 'Charlie', 10)

-- Departments:
-- (10, 'Sales')
-- (20, 'Marketing')

-- Denormalized table for reporting (created/refreshed daily)
CREATE TABLE SalesFact_Denormalized (
    sales_fact_id INT PRIMARY KEY,
    order_id INT,
    order_date DATE,
    customer_id INT,
    customer_name VARCHAR(100), -- Redundant: Copied from Customers
    product_id INT,
    product_name VARCHAR(100), -- Redundant: Copied from Products
    quantity INT,
    total_item_price DECIMAL(10, 2)
);

-- Data for this table would typically be populated via ETL processes
-- (Extract, Transform, Load) from the normalized OLTP system.

15. Exam & Interview Strategy Guide

You've covered a comprehensive range of SQL topics, from fundamentals to advanced concepts. Now, let's equip you with a robust strategy for approaching SQL interview questions and technical exams confidently.

Problem Solving Framework

Adopt a structured approach to tackle any SQL problem. This framework helps break down complex tasks into manageable steps.

SELECT
  -- ... columns ...
FROM
  Table1
JOIN
  Table2 ON ...
WHERE
  -- ... basic filters ...
GROUP BY
  -- ... grouping columns ...
HAVING
  -- ... group filters ...
ORDER BY
  -- ... sorting ...

Debugging Your Own Code

Even the best developers write bugs. Knowing how to find and fix them efficiently is a valuable skill.

• 🛠️ Check Intermediate Results with CTEs: If your query is long or complex, use CTEs to isolate different parts of the logic. Select from each CTE individually to verify that it's producing the expected intermediate data. This is far easier than trying to debug a single, monolithic query.
• 🛠️ Verify Row Counts Before/After Joins: The "fan-out" problem (Section 7) is a common source of errors. Before and after each `JOIN` or `UNION`, check the `COUNT(*)` to ensure your row count changes as expected. Unexpected increases often point to incorrect join conditions or implicit Cartesian products.
• 🛠️ Simplify, then Rebuild: If a query is not working, remove all non-essential clauses (ORDER BY, complex `WHERE` conditions, extra columns). Get the simplest core query working, then gradually add back complexity, testing at each step.

Review Checklist

Before submitting your solution or declaring it "done," run through these critical checks:

• ✅ Did I handle NULLs? This is a common pitfall. Are `NULL`s correctly filtered (IS NULL/IS NOT NULL), sorted (NULLS FIRST/LAST), or replaced (COALESCE)?
• ✅ Did I handle duplicates? Does the problem require unique records (`DISTINCT`, `GROUP BY`, `UNION`) or are duplicates expected (`UNION ALL`)? Are aggregations (like `COUNT`) impacted by unintended duplicates?
• ✅ Is the syntax dialect-specific? Be aware of differences between T-SQL (SQL Server), PostgreSQL, MySQL, Oracle SQL. Functions like `DATE_TRUNC`, `DATEDIFF`, `CONCAT`, and auto-incrementing primary keys vary. State your assumed dialect if not specified.
• ✅ Is the query efficient? For interviews, mentioning potential optimizations (indexes, avoiding correlated subqueries, using `EXISTS` instead of `IN` for subqueries if `NULL`s are an issue) can impress.
• ✅ Is the code readable? Use aliases, consistent capitalization, and proper indentation. Comments can explain complex logic.

Resources

Practice is paramount! Here are some recommended platforms for honing your SQL skills:

• 📚 LeetCode: Excellent for algorithmic SQL problems, covers a wide range of difficulty.
• 📚 HackerRank: Good for beginner to intermediate SQL challenges.
• 📚 StrataScratch: Focuses on real-world data science interview questions, often from top tech companies.
• 📚 SQLZoo: Interactive tutorials and exercises, great for learning basics.
• 📚 Kaggle Notebooks: Explore public datasets and see how others solve problems with SQL.

Homework / Challenges

WITH CustomerLifetimeMetrics AS (
    SELECT
        C.customer_id,
        C.customer_name,
        SUM(O.total_amount) AS total_spending,
        COUNT(DISTINCT O.order_id) AS total_orders,
        COUNT(DISTINCT OI.product_id) AS distinct_products_purchased
    FROM
        Customers C
    JOIN
        Orders O ON C.customer_id = O.customer_id
    JOIN
        OrderItems OI ON O.order_id = OI.order_id
    GROUP BY
        C.customer_id, C.customer_name
)
SELECT * FROM CustomerLifetimeMetrics; -- Intermediate result check

-- This could be another CTE or derived table
WITH CustomerLifetimeMetrics AS (
    -- ... (Same as Step 1)
),
OverallAverages AS (
    SELECT
        AVG(total_spending) AS avg_customer_spending,
        AVG(total_orders) AS avg_customer_orders
    FROM
        CustomerLifetimeMetrics
)
SELECT * FROM OverallAverages; -- Intermediate result check

WITH CustomerLifetimeMetrics AS (
    SELECT
        C.customer_id,
        C.customer_name,
        SUM(O.total_amount) AS total_spending,
        COUNT(DISTINCT O.order_id) AS total_orders,
        COUNT(DISTINCT OI.product_id) AS distinct_products_purchased
    FROM
        Customers C
    JOIN
        Orders O ON C.customer_id = O.customer_id
    JOIN
        OrderItems OI ON O.order_id = OI.order_id
    GROUP BY
        C.customer_id, C.customer_name
),
OverallAverages AS (
    SELECT
        AVG(total_spending) AS avg_customer_spending,
        AVG(total_orders) AS avg_customer_orders
    FROM
        CustomerLifetimeMetrics
)
SELECT
    CLM.customer_name,
    CLM.total_spending,
    CLM.total_orders,
    CLM.distinct_products_purchased
FROM
    CustomerLifetimeMetrics CLM, OverallAverages OA -- Using implicit CROSS JOIN or just reference scalar values
WHERE
    CLM.total_spending > OA.avg_customer_spending
    AND CLM.total_orders > OA.avg_customer_orders
ORDER BY
    CLM.total_spending DESC;

WITH DistinctDailyLogins AS (
    SELECT
        user_id,
        CAST(login_timestamp AS DATE) AS login_day -- Extract just the date part (syntax varies by RDBMS)
        -- For MySQL: DATE(login_timestamp)
        -- For SQL Server: CAST(login_timestamp AS DATE)
    FROM
        UserLogins
    GROUP BY
        user_id, CAST(login_timestamp AS DATE) -- Group to get distinct login days
),
SELECT * FROM DistinctDailyLogins; -- Intermediate result check

WITH DistinctDailyLogins AS (
    -- ... (Same as Step 1)
),
StreakGroups AS (
    SELECT
        user_id,
        login_day,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) AS rn,
        CAST(login_day AS DATE) - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) * INTERVAL '1 DAY') AS streak_group
        -- The above line calculates the difference from a 'virtual' continuous sequence.
        -- If login_day is 2023-01-05 and rn is 5, streak_group is 2023-01-01.
        -- If next login_day is 2023-01-06 and rn is 6, streak_group is 2023-01-01. (Same group)
        -- If next login_day is 2023-01-08 and rn is 7, streak_group is 2023-01-01. (Still same group due to direct date arithmetic)
        -- This isn't quite right for 'more than 24 hours'. Let's adjust for DATEDIFF for cleaner streak grouping.

        -- Corrected for DATEDIFF logic:
        -- DATEDIFF (in days) from an arbitrary fixed date.
        -- Then subtract the ROW_NUMBER to get the streak_group.
        -- Example (PostgreSQL DATEDIFF-equivalent):
        -- CAST(login_day AS DATE) - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) * INTERVAL '1 DAY') AS streak_group_identifier
        -- Or simply, as an integer representing the group:
        -- (EXTRACT(EPOCH FROM login_day) / 86400) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) AS streak_group_id
        -- Let's use `login_day - INTERVAL '1 day' * (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day))` which works in PostgreSQL.
        -- For SQL Server/MySQL, convert date to integer (e.g., YYYYMMDD) or use DATEDIFF from a base date.
        -- Simpler `login_day - (row_number() over (partition by user_id order by login_day)) * INTERVAL '1 day'` should work on most `DATE` types
    FROM
        DistinctDailyLogins
)
SELECT * FROM StreakGroups; -- Intermediate result check

WITH DistinctDailyLogins AS (
    -- ... (Same as Step 1)
),
StreakGroups AS (
    SELECT
        user_id,
        login_day,
        -- Using an expression that results in the same value for consecutive days, but changes when a gap occurs
        -- Example (PostgreSQL-like syntax for date arithmetic):
        login_day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) * INTERVAL '1 DAY') AS streak_identifier
        -- For SQL Server: DATEADD(day, -1 * (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day)), login_day) AS streak_identifier
        -- For MySQL: DATE_SUB(login_day, INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day)) DAY) AS streak_identifier
    FROM
        DistinctDailyLogins
),
StreakLengths AS (
    SELECT
        user_id,
        streak_identifier,
        COUNT(*) AS current_streak_length
    FROM
        StreakGroups
    GROUP BY
        user_id, streak_identifier
)
SELECT * FROM StreakLengths; -- Intermediate result check

WITH DistinctDailyLogins AS (
    SELECT
        user_id,
        CAST(login_timestamp AS DATE) AS login_day -- Using CAST(date AS DATE) for cross-RDBMS clarity
    FROM
        UserLogins
    GROUP BY
        user_id, CAST(login_timestamp AS DATE)
),
StreakGroups AS (
    SELECT
        user_id,
        login_day,
        -- This calculation creates a common "group" for consecutive days.
        -- If a day is consecutive, the difference between its `login_day` and its `row_number` will be constant.
        CAST(login_day AS DATE) - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) * INTERVAL '1 DAY') AS streak_identifier
        -- Adjust INTERVAL '1 DAY' type based on RDBMS. Some might need `NUMERIC_DATE - ROW_NUMBER()`
    FROM
        DistinctDailyLogins
),
StreakLengths AS (
    SELECT
        user_id,
        streak_identifier,
        COUNT(*) AS current_streak_length
    FROM
        StreakGroups
    GROUP BY
        user_id, streak_identifier
)
SELECT
    user_id,
    MAX(current_streak_length) AS longest_streak_days
FROM
    StreakLengths
GROUP BY
    user_id
ORDER BY
    user_id;