SQL Interview Questions and Answers (2026 Edition)

Answer: SQL (Structured Query Language) is a standard language used to create, read, update, and delete data stored in a relational database. It lets you define table structures, insert and modify records, and run complex queries to retrieve exactly the data you need.

Example Scenario: A company stores employee records in a relational database and uses SQL to fetch employees hired after 2023.

SELECT emp_name, hire_date FROM Employees WHERE hire_date > '2023-01-01';

Answer: SQL is declarative (you describe what you want, not how to get it), platform-independent, supports multiple data types, allows complex joins and aggregations, enforces data integrity through constraints, and integrates transaction control for safe multi-step operations.

Example Scenario: You can write the same SELECT query on MySQL, PostgreSQL, or SQL Server with only minor syntax differences.

SELECT COUNT(*) FROM Employees;

Answer: SQL commands are grouped into five categories: DDL (Data Definition Language) for schema, DML (Data Manipulation Language) for data, DCL (Data Control Language) for permissions, TCL (Transaction Control Language) for transactions, and DQL (Data Query Language) for SELECT-based retrieval.

Example Scenario: CREATE TABLE (DDL), INSERT INTO (DML), GRANT (DCL), COMMIT (TCL), and SELECT (DQL) are all SQL commands from different categories.

CREATE TABLE Departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(50));

Answer: DDL commands (CREATE, ALTER, DROP, TRUNCATE) define or modify the structure of database objects and auto-commit. DML commands (INSERT, UPDATE, DELETE) manipulate the actual data inside tables and can be rolled back within a transaction.

Example Scenario: ALTER TABLE changes a table's structure (DDL); UPDATE changes the values inside it (DML).

ALTER TABLE Employees ADD COLUMN email VARCHAR(100);
UPDATE Employees SET email = 'a@b.com' WHERE emp_id = 1;

Answer: CHAR is a fixed-length string type that pads unused space with blanks, making it faster for consistently sized data. VARCHAR is variable-length and stores only the actual characters used plus a small length indicator, saving space for variable data.

Example Scenario: A 2-letter state code fits CHAR(2); a customer name of variable length fits VARCHAR(50) better.

CREATE TABLE Customers (state CHAR(2), full_name VARCHAR(50));

Answer: Constraints are rules enforced on table columns to maintain data accuracy and integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.

Example Scenario: A CHECK constraint can ensure an employee's salary is always positive.

CREATE TABLE Employees (emp_id INT PRIMARY KEY, salary DECIMAL(10,2) CHECK (salary > 0));

Answer: A Primary Key uniquely identifies each row in a table. It cannot contain NULL values, must be unique, and a table can have only one primary key (which may span multiple columns as a composite key).

Example Scenario: emp_id uniquely identifies each row in the Employees table.

CREATE TABLE Employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(50));

Answer: A Foreign Key is a column (or set of columns) in one table that references the Primary Key of another table, enforcing referential integrity between the two tables.

Example Scenario: The dept_id column in Employees references dept_id in Departments.

CREATE TABLE Employees (emp_id INT PRIMARY KEY, dept_id INT, FOREIGN KEY (dept_id) REFERENCES Departments(dept_id));

Answer: A Primary Key does not allow NULLs and only one exists per table. A Unique Key allows one NULL value (in most databases) and a table can have multiple unique keys.

Example Scenario: email can be a Unique Key allowing NULL for one row, while emp_id remains the single Primary Key.

CREATE TABLE Employees (emp_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE);

Answer: SQL operators include Arithmetic (+, -, *, /, %), Comparison (=, !=, >, <, >=, <=), Logical (AND, OR, NOT, BETWEEN, IN, LIKE), and Set operators (UNION, INTERSECT, EXCEPT/MINUS).

Example Scenario: The BETWEEN operator filters salaries within a given range.

SELECT emp_name FROM Employees WHERE salary BETWEEN 40000 AND 80000;

Answer: WHERE filters individual rows before any grouping happens and cannot use aggregate functions. HAVING filters groups after GROUP BY has been applied and can use aggregate functions like SUM() or COUNT().

Example Scenario: You want departments with more than 5 employees.

SELECT dept_id, COUNT(*) AS emp_count FROM Employees GROUP BY dept_id HAVING COUNT(*) > 5;

Answer: ORDER BY sorts the result set by one or more columns, in ascending (ASC, default) or descending (DESC) order.

Example Scenario: Sort employees by salary from highest to lowest.

SELECT emp_name, salary FROM Employees ORDER BY salary DESC;

Answer: GROUP BY groups rows sharing the same values in specified columns so aggregate functions (SUM, AVG, COUNT, etc.) can be applied to each group separately.

Example Scenario: Find the total salary paid per department.

SELECT dept_id, SUM(salary) AS total_salary FROM Employees GROUP BY dept_id;

Answer: DELETE removes rows one at a time (can use WHERE, is logged, rollback-able), TRUNCATE removes all rows at once (faster, minimal logging, resets identity, cannot use WHERE), and DROP removes the entire table structure along with its data permanently.

Example Scenario: You want to clear all data but keep the table structure for reuse.

TRUNCATE TABLE Employees;

Answer: NULL represents missing, unknown, or inapplicable data. It is not the same as zero or an empty string, and any arithmetic or comparison with NULL returns NULL (unknown) — you must use IS NULL / IS NOT NULL to test for it.

Example Scenario: An employee record without a recorded phone number stores NULL, not '0' or ''.

SELECT emp_name FROM Employees WHERE phone IS NULL;

Answer: IN checks whether a value matches any value in a specified list. BETWEEN checks whether a value falls within an inclusive range of two values.

Example Scenario: Filter employees who work in dept_id 1, 3, or 5, versus employees with salary between 30000 and 60000.

SELECT * FROM Employees WHERE dept_id IN (1,3,5);
SELECT * FROM Employees WHERE salary BETWEEN 30000 AND 60000;

Answer: LIKE performs pattern matching in string columns using wildcards: % for zero or more characters and _ for exactly one character.

Example Scenario: Find all employees whose name starts with 'A'.

SELECT emp_name FROM Employees WHERE emp_name LIKE 'A%';

Answer: UNION combines results from two SELECT queries and removes duplicate rows, which involves extra processing. UNION ALL combines results without removing duplicates, making it faster.

Example Scenario: Combine current employees and former employees lists.

SELECT emp_name FROM Employees
UNION
SELECT emp_name FROM FormerEmployees;

Answer: Aggregate functions perform a calculation on a set of rows and return a single summarized value. Common ones are COUNT(), SUM(), AVG(), MIN(), and MAX().

Example Scenario: Find the average salary across the company.

SELECT AVG(salary) AS avg_salary FROM Employees;

Answer: COUNT(*) counts all rows regardless of NULLs. COUNT(1) behaves the same as COUNT(*) in modern optimizers (counts all rows). COUNT(column_name) counts only rows where that specific column is NOT NULL.

Example Scenario: Count all employees vs count employees who have a manager assigned.

SELECT COUNT(*) AS total_emp, COUNT(manager_id) AS with_manager FROM Employees;

Answer: A Composite Key is a primary key made up of two or more columns that together uniquely identify a row, used when no single column is unique enough on its own.

Example Scenario: In an Enrollments table, (student_id, course_id) together form a composite key.

CREATE TABLE Enrollments (student_id INT, course_id INT, PRIMARY KEY (student_id, course_id));

Answer: A Candidate Key is any column or combination of columns that could qualify as a unique identifier for a table. One candidate key is chosen as the Primary Key; the rest become Alternate Keys.

Example Scenario: In Employees, both emp_id and email could independently identify a row uniquely, making both candidate keys.

-- emp_id and email are both candidate keys;
-- emp_id is chosen as Primary Key

Answer: A Super Key is any set of one or more columns that can uniquely identify a row; it may include extra, unnecessary attributes. Every Candidate Key is a Super Key, but not every Super Key is minimal enough to be a Candidate Key.

Example Scenario: (emp_id, emp_name) is a super key because it's unique, even though emp_name alone isn't needed.

-- Super Key example: (emp_id, emp_name, dept_id)

Answer: A Clustered Index determines the physical storage order of table data — a table can have only one. A Non-Clustered Index creates a separate structure with pointers back to the actual data rows — a table can have many.

Example Scenario: Primary key columns typically get a clustered index automatically in SQL Server.

CREATE CLUSTERED INDEX idx_emp_id ON Employees(emp_id);
CREATE NONCLUSTERED INDEX idx_emp_name ON Employees(emp_name);

Answer: Normalization is the process of organizing data to reduce redundancy and improve data integrity by dividing large tables into smaller related ones. Key normal forms: 1NF (atomic values, no repeating groups), 2NF (no partial dependency on composite keys), 3NF (no transitive dependency), and BCNF (every determinant is a candidate key).

Example Scenario: Splitting a single 'Orders' table containing customer address into separate Orders and Customers tables removes repeated address data.

-- Before: Orders(order_id, cust_name, cust_address, product)
-- After 3NF:
-- Customers(cust_id, cust_name, cust_address)
-- Orders(order_id, cust_id, product)

Answer: INNER JOIN returns only the rows that have matching values in both joined tables based on the join condition.

Example Scenario: Get employee names along with their department names, only for employees who have an assigned department.

SELECT e.emp_name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.dept_id;

Answer: LEFT JOIN returns all rows from the left table and the matched rows from the right table; unmatched right-side columns are filled with NULL.

Example Scenario: List all employees, including those not yet assigned to any department.

SELECT e.emp_name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.dept_id;

Answer: RIGHT JOIN returns all rows from the right table and the matched rows from the left table; unmatched left-side columns are filled with NULL.

Example Scenario: List all departments, including ones with no employees assigned yet.

SELECT e.emp_name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.dept_id = d.dept_id;

Answer: FULL OUTER JOIN returns all rows from both tables, matching where possible and filling NULLs where no match exists on either side.

Example Scenario: List every employee and every department, matched where possible, unmatched on either side otherwise.

SELECT e.emp_name, d.dept_name FROM Employees e FULL OUTER JOIN Departments d ON e.dept_id = d.dept_id;

Answer: A SELF JOIN joins a table to itself, typically to compare rows within the same table, such as finding employees and their managers stored in the same table.

Example Scenario: Find each employee along with their manager's name, where manager_id references emp_id in the same table.

SELECT e.emp_name AS employee, m.emp_name AS manager FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.emp_id;

Answer: CROSS JOIN returns the Cartesian product of two tables — every row from the first table paired with every row from the second, with no join condition.

Example Scenario: Generate every possible combination of Products and Sizes for a catalog.

SELECT p.product_name, s.size_name FROM Products p CROSS JOIN Sizes s;

Answer: Use a LEFT JOIN from Employees to Departments and filter for rows where the department key is NULL, meaning no match was found.

Example Scenario: Departments without a matching entry in Departments for some employees.

SELECT e.emp_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.dept_id WHERE d.dept_id IS NULL;

Answer: A Subquery (or inner query) is a query nested inside another SQL query, used to return data that the outer query then uses for filtering, comparison, or as a derived table.

Example Scenario: Find employees who earn more than the company's average salary.

SELECT emp_name, salary FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);

Answer: A Correlated Subquery references a column from the outer query, so it re-executes once for every row processed by the outer query, unlike a regular subquery that runs independently.

Example Scenario: Find employees earning more than the average salary of their own department.

SELECT e1.emp_name, e1.salary FROM Employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM Employees e2 WHERE e2.dept_id = e1.dept_id);

Answer: A JOIN combines columns from multiple tables into one result set in a single pass and is usually more efficient for retrieving combined data. A Subquery is often used for filtering or existence checks and can be less efficient, especially when correlated, though it can be more readable for certain logic.

Example Scenario: Get employees along with department names — better solved with a JOIN than a subquery.

-- JOIN approach
SELECT e.emp_name, d.dept_name FROM Employees e JOIN Departments d ON e.dept_id = d.dept_id;

Answer: A CTE, defined with WITH, is a temporary named result set that exists only for the duration of a single query, improving readability and enabling recursive queries.

Example Scenario: Find the top 3 highest-paid employees per department using a CTE with ROW_NUMBER().

WITH RankedEmp AS (
  SELECT emp_name, dept_id, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM Employees
)
SELECT * FROM RankedEmp WHERE rnk <= 3;

Answer: A Recursive CTE references itself to process hierarchical or recursive data, such as organizational charts or category trees, by combining an anchor query with a recursive query joined back to the CTE.

Example Scenario: Find all employees under a given manager, including indirect reports, in an org hierarchy.

WITH RECURSIVE OrgChart AS (
  SELECT emp_id, emp_name, manager_id FROM Employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, e.manager_id FROM Employees e
  JOIN OrgChart o ON e.manager_id = o.emp_id
)
SELECT * FROM OrgChart;

Answer: Window functions perform calculations across a set of rows related to the current row (a 'window') without collapsing the result into a single row, using the OVER() clause. Examples include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and SUM() OVER().

Example Scenario: Show each employee's salary alongside the running total of salaries within their department.

SELECT emp_name, dept_id, salary,
SUM(salary) OVER (PARTITION BY dept_id ORDER BY emp_id) AS running_total
FROM Employees;

Answer: ROW_NUMBER() assigns a unique sequential number to every row with no gaps or ties. RANK() assigns the same rank to tied rows but skips subsequent rank numbers. DENSE_RANK() also assigns the same rank to ties but does not skip the next rank number.

Example Scenario: Rank employees by salary within a department where two employees are tied for 2nd place.

SELECT emp_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM Employees;

Answer: LAG() accesses data from a previous row and LEAD() accesses data from a following row within the same result set, without needing a self-join — useful for period-over-period comparisons.

Example Scenario: Compare each month's sales to the previous month's sales.

SELECT month, sales,
LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales
FROM MonthlySales;

Answer: A View is a virtual table based on the result of a stored SELECT query. It doesn't store data itself (in most cases) but simplifies complex queries, and can restrict access to specific columns for security.

Example Scenario: Create a simplified view exposing only non-sensitive employee details.

CREATE VIEW EmployeePublic AS
SELECT emp_id, emp_name, dept_id FROM Employees;

Answer: An Index is a database object that improves the speed of data retrieval by creating a sorted structure (often a B-Tree) on one or more columns, at the cost of extra storage and slightly slower writes.

Example Scenario: Speed up searches on the email column, which is frequently filtered.

CREATE INDEX idx_employee_email ON Employees(email);

Answer: A Stored Procedure is a precompiled, reusable block of SQL code stored in the database that can accept parameters, contain logic (loops, conditionals), and be called repeatedly by name.

Example Scenario: A procedure that gives a raise to all employees in a given department.

CREATE PROCEDURE GiveRaise (IN p_dept_id INT, IN p_amount DECIMAL(10,2))
BEGIN
  UPDATE Employees SET salary = salary + p_amount WHERE dept_id = p_dept_id;
END;

Answer: A Trigger is a special stored procedure that automatically executes in response to specific events (INSERT, UPDATE, DELETE) on a table, often used for auditing or enforcing complex business rules.

Example Scenario: Automatically log every salary update into an audit table.

CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
  INSERT INTO SalaryAudit(emp_id, old_salary, new_salary, changed_at)
  VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END;

Answer: A Transaction is a sequence of one or more SQL operations executed as a single logical unit of work — either all operations succeed (COMMIT) or none do (ROLLBACK), preserving data consistency.

Example Scenario: Transfer money between two bank accounts safely.

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acct_id = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acct_id = 2;
COMMIT;

Answer: ACID stands for Atomicity (all-or-nothing execution), Consistency (data moves from one valid state to another), Isolation (concurrent transactions don't interfere with each other), and Durability (once committed, changes survive system failures).

Example Scenario: A failed bank transfer transaction rolls back completely, leaving no partial update — demonstrating Atomicity.

-- Atomicity example
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acct_id = 1;
-- error occurs here
ROLLBACK;

Answer: The four standard isolation levels are READ UNCOMMITTED (allows dirty reads), READ COMMITTED (prevents dirty reads), REPEATABLE READ (prevents dirty and non-repeatable reads), and SERIALIZABLE (strictest, prevents phantom reads too, at the cost of concurrency).

Example Scenario: An e-commerce checkout system might use REPEATABLE READ to avoid inconsistent stock counts mid-transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Answer: COMMIT permanently saves all changes made during the current transaction. ROLLBACK undoes changes made since the last COMMIT (or SAVEPOINT). SAVEPOINT sets a named point within a transaction that you can roll back to without undoing the entire transaction.

Example Scenario: Roll back only part of a multi-step transaction using a savepoint.

BEGIN TRANSACTION;
UPDATE Employees SET salary = salary + 1000 WHERE dept_id = 1;
SAVEPOINT sp1;
UPDATE Employees SET salary = salary + 2000 WHERE dept_id = 2;
ROLLBACK TO sp1;
COMMIT;

Answer: Denormalization intentionally introduces controlled redundancy into a normalized schema to reduce the number of joins needed, improving read performance for reporting or analytics-heavy workloads at the cost of some data duplication.

Example Scenario: A reporting dashboard table stores a pre-joined, flattened combination of Orders, Customers, and Products for faster queries.

CREATE TABLE SalesReportFlat AS
SELECT o.order_id, c.cust_name, p.product_name, o.quantity, o.order_date
FROM Orders o JOIN Customers c ON o.cust_id = c.cust_id JOIN Products p ON o.product_id = p.product_id;

Answer: OLTP (Online Transaction Processing) systems handle many short, frequent read/write transactions, like order processing, and favor normalized schemas. OLAP (Online Analytical Processing) systems handle complex analytical queries over large historical datasets and favor denormalized, star/snowflake schemas.

Example Scenario: An e-commerce checkout database is OLTP; a sales analytics warehouse summarizing years of orders is OLAP.

-- OLTP: fast single-row insert
INSERT INTO Orders (cust_id, product_id, quantity) VALUES (101, 5, 2);
-- OLAP: heavy aggregate query
SELECT product_id, SUM(quantity) FROM Orders GROUP BY product_id;

Answer: DELETE is fully logged and can be rolled back within a transaction. TRUNCATE is minimally logged and, in most databases, cannot be rolled back once committed (though some databases allow it inside an explicit transaction). DROP removes the object entirely and generally cannot be rolled back after commit.

Example Scenario: Accidentally truncating a production table without a transaction wrapper.

-- Safer approach
BEGIN TRANSACTION;
TRUNCATE TABLE Orders;
-- verify before committing
ROLLBACK;

Answer: A Deadlock occurs when two or more transactions hold locks that the other transactions need, creating a cycle where none can proceed. It can be avoided by accessing tables in a consistent order, keeping transactions short, and using appropriate isolation levels.

Example Scenario: Transaction A locks Table1 then waits for Table2, while Transaction B locks Table2 then waits for Table1.

-- Best practice: always update tables in the same order
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE acct_id = 2;
COMMIT;

Answer: A Function must return a single value (or table) and can be used inside a SELECT statement, but cannot modify data or use transaction control statements in most databases. A Stored Procedure can perform DML/DDL operations, doesn't have to return a value, and is called independently, not embedded in a query.

Example Scenario: A function to calculate bonus vs a procedure to apply that bonus to all employees.

CREATE FUNCTION CalcBonus(salary DECIMAL) RETURNS DECIMAL
RETURN salary * 0.10;

Answer: EXPLAIN shows the database's query execution plan — how it intends to scan tables, use indexes, join data, and estimate cost — helping developers identify performance bottlenecks before optimizing a query.

Example Scenario: Diagnose why a query filtering on email is slow.

EXPLAIN SELECT * FROM Customers WHERE email = 'test@example.com';

Answer: Query optimization is the process of rewriting or restructuring SQL to reduce execution time and resource usage. Techniques include adding proper indexes, avoiding SELECT *, using JOINs instead of correlated subqueries where possible, filtering early, and analyzing execution plans.

Example Scenario: Replacing SELECT * with only the needed columns to reduce I/O.

-- Before
SELECT * FROM Orders WHERE cust_id = 101;
-- After
SELECT order_id, order_date, amount FROM Orders WHERE cust_id = 101;

Answer: SELECT * retrieves all columns even if unnecessary, increasing I/O, network transfer, and memory usage. It also breaks if the table schema changes and makes queries harder to read, plus it can prevent the optimizer from using covering indexes efficiently.

Example Scenario: A report only needs emp_name and salary but uses SELECT *, unnecessarily pulling large text/blob columns too.

SELECT emp_name, salary FROM Employees;

Answer: A Covering Index includes all the columns a query needs, so the database can satisfy the query entirely from the index without accessing the actual table data, significantly improving performance.

Example Scenario: A query that filters by dept_id and selects only emp_name can be fully served by an index on (dept_id, emp_name).

CREATE INDEX idx_dept_name ON Employees(dept_id, emp_name);

Answer: Excessive indexing slows down INSERT, UPDATE, and DELETE operations because every index must be updated alongside the data, and it increases storage overhead. Indexes should be created based on actual query patterns, not preemptively on every column.

Example Scenario: A heavily-written logging table with 10 indexes suffers slow inserts during peak traffic.

-- Review and drop unused indexes
DROP INDEX idx_unused_column ON LogTable;

Answer: Index Fragmentation happens when data pages become disorganized due to frequent inserts, updates, and deletes, causing the index to no longer be stored in optimal contiguous order, which slows down reads. It's fixed by rebuilding or reorganizing the index periodically.

Example Scenario: A frequently updated Orders table gradually develops fragmented indexes over months.

ALTER INDEX idx_orders_date ON Orders REBUILD;

Answer: Check the execution plan for full table scans, ensure join columns are indexed (especially foreign keys), filter data as early as possible with WHERE before joining large tables, avoid unnecessary columns, and consider breaking the query into smaller steps or using temp tables for very complex logic.

Example Scenario: A report joining Orders, Customers, and Products runs slowly.

-- Ensure indexes exist on join keys
CREATE INDEX idx_orders_cust ON Orders(cust_id);
CREATE INDEX idx_orders_prod ON Orders(product_id);

Answer: Partitioning splits a large table into smaller, more manageable physical segments (by range, list, or hash) while still being queried as one logical table, improving performance and maintenance for very large datasets.

Example Scenario: Partition a Sales table by year so queries filtering on recent years scan far less data.

CREATE TABLE Sales (sale_id INT, sale_date DATE, amount DECIMAL)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

Answer: A Temporary Table physically stores data for the session/transaction and can be indexed and reused across multiple statements. A CTE exists only for the duration of a single query, is not indexed, and is generally re-evaluated each time it's referenced.

Example Scenario: Reusing intermediate results across several queries in a session vs. simplifying one complex query.

CREATE TEMPORARY TABLE TempHighEarners AS
SELECT * FROM Employees WHERE salary > 80000;

Answer: JOIN combines columns from two tables side by side based on a related column. UNION stacks rows from two queries vertically, requiring the same number of columns and compatible data types in both queries.

Example Scenario: Combine two lists of names (UNION) vs. combining employee and department details (JOIN).

SELECT emp_name FROM Employees
UNION
SELECT cust_name FROM Customers;

Answer: Besides UNION and UNION ALL, SQL supports INTERSECT (returns only rows common to both queries) and EXCEPT/MINUS (returns rows in the first query that are not in the second).

Example Scenario: Find customers who placed orders in both 2024 and 2025.

SELECT cust_id FROM Orders WHERE YEAR(order_date) = 2024
INTERSECT
SELECT cust_id FROM Orders WHERE YEAR(order_date) = 2025;

Answer: Use a subquery with LIMIT/OFFSET, or DISTINCT with ORDER BY and OFFSET, or a window function like DENSE_RANK() to skip the top value and get the next distinct one.

Example Scenario: Find the second highest salary in Employees.

SELECT DISTINCT salary FROM Employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

Answer: Group by the columns that should be unique, then use HAVING COUNT(*) > 1 to find groups that appear more than once.

Example Scenario: Find duplicate email addresses in the Customers table.

SELECT email, COUNT(*) AS cnt FROM Customers GROUP BY email HAVING COUNT(*) > 1;

Answer: Use a CTE with ROW_NUMBER() partitioned by the duplicate-defining columns, then delete rows where the row number is greater than 1.

Example Scenario: Remove duplicate customer emails, keeping the earliest inserted row.

WITH Ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY cust_id) AS rn
  FROM Customers
)
DELETE FROM Ranked WHERE rn > 1;

Answer: Use DENSE_RANK() in a CTE or subquery, then filter WHERE rank = N — this correctly handles ties, unlike LIMIT/OFFSET approaches.

Example Scenario: Find the 3rd highest distinct salary.

WITH RankedSalary AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employees
)
SELECT DISTINCT salary FROM RankedSalary WHERE rnk = 3;

Answer: Use SUM() as a window function with ORDER BY inside OVER() to accumulate values row by row within a defined order (and optional partition).

Example Scenario: Calculate cumulative sales for each day in a Sales table.

SELECT sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM Sales;

Answer: Use AVG() as a window function with a ROWS BETWEEN frame to define how many preceding/following rows to include in each average calculation.

Example Scenario: Calculate a 3-day moving average of sales.

SELECT sale_date, amount,
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day
FROM Sales;

Answer: Use a LEFT JOIN from Customers to Orders and filter where the Orders key is NULL, or use a NOT IN / NOT EXISTS subquery.

Example Scenario: Identify customers with zero orders for a re-engagement email campaign.

SELECT c.cust_id, c.cust_name FROM Customers c
LEFT JOIN Orders o ON c.cust_id = o.cust_id
WHERE o.order_id IS NULL;

Answer: NOT IN can behave unexpectedly and return zero rows if the subquery's result set contains even one NULL value. NOT EXISTS uses a correlated existence check and handles NULLs safely, making it generally the more reliable choice.

Example Scenario: Find employees whose dept_id is not present in a Departments list that might contain a NULL.

SELECT emp_name FROM Employees e
WHERE NOT EXISTS (SELECT 1 FROM Departments d WHERE d.dept_id = e.dept_id);

Answer: Use conditional aggregation with CASE WHEN inside SUM()/COUNT(), or a database-specific PIVOT operator, to transform row values into separate columns.

Example Scenario: Show total sales per product as separate columns for each quarter.

SELECT product_id,
SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS Q1_sales,
SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS Q2_sales
FROM QuarterlySales GROUP BY product_id;

Answer: Use a correlated subquery or window function (AVG() OVER PARTITION BY subject) to compare each student's marks to the average for their specific subject.

Example Scenario: Using a Students/Marks table, find students scoring above their subject's average.

SELECT student_id, subject, marks FROM (
  SELECT student_id, subject, marks,
  AVG(marks) OVER (PARTITION BY subject) AS subj_avg
  FROM Marks
) t WHERE marks > subj_avg;

Answer: Group employees by department, calculate the average salary per group, then order descending and limit to the top result (or use a subquery/window function to handle ties).

Example Scenario: Identify the top-paying department for a compensation benchmarking report.

SELECT dept_id, AVG(salary) AS avg_salary FROM Employees
GROUP BY dept_id ORDER BY avg_salary DESC LIMIT 1;

Answer: Self-join the Employees table so each employee row is matched with their manager's row (via manager_id = emp_id), then filter where the employee's salary exceeds the manager's salary.

Example Scenario: A classic 'employee earns more than boss' interview question.

SELECT e.emp_name AS employee, e.salary AS emp_salary, m.salary AS mgr_salary
FROM Employees e JOIN Employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

Answer: Divide each product's total sales by the overall total sales (using a window function SUM() OVER() with no partition, or a subquery for the grand total), then multiply by 100.

Example Scenario: Show each product's share of total company revenue.

SELECT product_id, SUM(amount) AS product_sales,
ROUND(SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (), 2) AS pct_of_total
FROM Orders GROUP BY product_id;

Answer: Use ROW_NUMBER() or DENSE_RANK() partitioned by category, ordered by total sales descending, inside a CTE, then filter for rank <= 3.

Example Scenario: A merchandising team wants the top 3 best-selling products per category.

WITH RankedProducts AS (
  SELECT category, product_name, SUM(quantity) AS total_qty,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(quantity) DESC) AS rnk
  FROM Orders o JOIN Products p ON o.product_id = p.product_id
  GROUP BY category, product_name
)
SELECT * FROM RankedProducts WHERE rnk <= 3;

Answer: Use LAG() to fetch the previous month's total, then calculate the percentage change between the current and previous month.

Example Scenario: Report monthly sales growth rate for a business dashboard.

SELECT month, total_sales,
ROUND((total_sales - LAG(total_sales) OVER (ORDER BY month)) * 100.0
  / LAG(total_sales) OVER (ORDER BY month), 2) AS growth_pct
FROM MonthlySales;

Answer: Compare each value to the next expected value using LEAD() or a self-join, and flag rows where the difference is greater than 1, indicating a gap in the sequence.

Example Scenario: Identify missing order_id values in an Orders table for data quality checks.

SELECT order_id, LEAD(order_id) OVER (ORDER BY order_id) - order_id AS gap
FROM Orders
HAVING gap > 1;

Answer: Use simultaneous assignment in a single UPDATE statement so both columns are swapped using their original values in one pass.

Example Scenario: Swap salary and bonus columns for all employees.

UPDATE Employees SET salary = bonus, bonus = salary;

Answer: Compare the hire_date column to the current date minus 30 days using date functions, filtering with WHERE.

Example Scenario: HR wants a report of very recent hires.

SELECT emp_name, hire_date FROM Employees WHERE hire_date >= CURRENT_DATE - INTERVAL '30 days';

Answer: LEFT JOIN Customers to Orders and use COUNT() on the Orders key (not COUNT(*)), so customers with no matching orders correctly show 0 instead of being excluded.

Example Scenario: A CRM report needs every customer represented, even inactive ones.

SELECT c.cust_name, COUNT(o.order_id) AS total_orders
FROM Customers c LEFT JOIN Orders o ON c.cust_id = o.cust_id
GROUP BY c.cust_name;

Answer: Use a window function MAX() OVER(PARTITION BY dept_id) to compute department max alongside each row, then filter where salary equals that max, or use a correlated subquery.

Example Scenario: HR wants to know the top earner in every department.

SELECT emp_name, dept_id, salary FROM (
  SELECT emp_name, dept_id, salary,
  MAX(salary) OVER (PARTITION BY dept_id) AS max_sal
  FROM Employees
) t WHERE salary = max_sal;

Answer: Use an UPDATE statement with a JOIN (or a correlated subquery, depending on the database) so the update logic can reference columns from a related table.

Example Scenario: Increase salary by 10% for employees in the 'Sales' department.

UPDATE Employees e
JOIN Departments d ON e.dept_id = d.dept_id
SET e.salary = e.salary * 1.10
WHERE d.dept_name = 'Sales';

Answer: LEFT JOIN Products to Orders and filter for rows where the Orders key is NULL, indicating no matching order exists for that product.

Example Scenario: Inventory team wants to identify dead stock.

SELECT p.product_id, p.product_name FROM Products p
LEFT JOIN Orders o ON p.product_id = o.product_id
WHERE o.order_id IS NULL;

Answer: Use the 'gaps and islands' technique: subtract a row number (via ROW_NUMBER ordered by date) from the actual date; consecutive dates produce the same difference, which can then be grouped to find streaks.

Example Scenario: Identify a customer's longest consecutive daily ordering streak for a loyalty program.

WITH Ordered AS (
  SELECT cust_id, order_date,
  order_date - ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY order_date) * INTERVAL '1 day' AS grp
  FROM Orders
)
SELECT cust_id, grp, COUNT(*) AS streak_days
FROM Ordered GROUP BY cust_id, grp;

Answer: Use PERCENTILE_CONT(0.5) as a window/aggregate function if supported, or manually rank rows and average the middle value(s) for odd/even row counts.

Example Scenario: HR wants the median (not average) salary per department to avoid outlier skew.

SELECT dept_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept_id) AS median_salary
FROM Employees;

Answer: AVG() automatically ignores NULL values in its calculation by default — it does not treat them as zero. To include them as zero explicitly, wrap the column with COALESCE(column, 0) first.

Example Scenario: Calculate average bonus where some employees have no bonus recorded (NULL) versus employees with an actual 0 bonus.

SELECT AVG(bonus) AS avg_ignoring_null, AVG(COALESCE(bonus, 0)) AS avg_treating_null_as_zero
FROM Employees;

Answer: Aggregate distinct users per month, then use a self-join or LAG() across the monthly aggregated table to compare counts month over month.

Example Scenario: A product analytics team tracks month-over-month active user retention.

WITH Monthly AS (
  SELECT DATE_TRUNC('month', activity_date) AS month, COUNT(DISTINCT user_id) AS active_users
  FROM UserActivity GROUP BY 1
)
SELECT month, active_users, LAG(active_users) OVER (ORDER BY month) AS prev_month_users
FROM Monthly;

Answer: Insert the qualifying rows into an archive table first, verify the insert succeeded, and only then delete them from the original table — ideally wrapped in a single transaction for safety.

Example Scenario: Move orders older than 3 years into an OrdersArchive table.

BEGIN TRANSACTION;
INSERT INTO OrdersArchive SELECT * FROM Orders WHERE order_date < CURRENT_DATE - INTERVAL '3 years';
DELETE FROM Orders WHERE order_date < CURRENT_DATE - INTERVAL '3 years';
COMMIT;

Answer: Two ranges overlap when one range's start is before the other's end AND its end is after the other's start — this condition can be checked with a self-join on that logic.

Example Scenario: A hotel booking system needs to detect double-booked rooms.

SELECT a.booking_id, b.booking_id FROM Bookings a
JOIN Bookings b ON a.room_id = b.room_id AND a.booking_id < b.booking_id
WHERE a.start_date < b.end_date AND a.end_date > b.start_date;

Answer: Use DATEDIFF() (SQL Server/MySQL) or simple subtraction of DATE types (PostgreSQL) to get the number of days between two date values.

Example Scenario: Calculate how many days each order took to ship.

SELECT order_id, DATEDIFF(ship_date, order_date) AS days_to_ship FROM Orders;

Answer: Use MIN() and MAX() aggregate functions on the order date column, grouped by customer.

Example Scenario: Understand customer lifecycle by finding their first and most recent purchase.

SELECT cust_id, MIN(order_date) AS first_order, MAX(order_date) AS last_order
FROM Orders GROUP BY cust_id;

Answer: Group by student, and use a HAVING clause with conditional aggregation (e.g., SUM of a CASE WHEN passing) to ensure every single subject was below the passing threshold.

Example Scenario: Using a Students/Marks table, flag students who failed every subject they took.

SELECT student_id FROM Marks
GROUP BY student_id
HAVING SUM(CASE WHEN marks >= 40 THEN 1 ELSE 0 END) = 0;

Answer: Sum marks per student, then rank students within each class/section using RANK() or ROW_NUMBER() and filter for the top rank.

Example Scenario: Using Students and Marks tables, find the topper of each section.

WITH Totals AS (
  SELECT s.section, s.student_id, SUM(m.marks) AS total_marks
  FROM Students s JOIN Marks m ON s.student_id = m.student_id
  GROUP BY s.section, s.student_id
),
Ranked AS (
  SELECT *, RANK() OVER (PARTITION BY section ORDER BY total_marks DESC) AS rnk FROM Totals
)
SELECT * FROM Ranked WHERE rnk = 1;

Answer: A Database is typically optimized for OLTP — fast, transactional, day-to-day operations on current data. A Data Warehouse is optimized for OLAP — storing large volumes of historical, integrated data from multiple sources for analysis and reporting, usually using denormalized star/snowflake schemas.

Example Scenario: A retail company's live order system is a database; its multi-year sales analytics platform is a data warehouse.

-- Data warehouse fact table example
CREATE TABLE FactSales (sale_id INT, date_key INT, product_key INT, customer_key INT, amount DECIMAL);

Answer: Write queries that check for orphan foreign keys, unexpected NULLs in required fields, duplicate primary keys, or values outside expected ranges/formats, often combined into a data quality checklist run periodically.

Example Scenario: Check for Orders referencing a cust_id that doesn't exist in Customers.

SELECT o.order_id, o.cust_id FROM Orders o
LEFT JOIN Customers c ON o.cust_id = c.cust_id
WHERE c.cust_id IS NULL;

Answer: Use ROW_NUMBER() partitioned by customer and ordered by order date descending, then filter for row number equal to 2.

Example Scenario: A marketing team wants each customer's second-most-recent purchase for a re-engagement campaign.

WITH Ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY order_date DESC) AS rn
  FROM Orders
)
SELECT * FROM Ranked WHERE rn = 2;

Answer: Calculate the department's average and standard deviation of salary, then flag employees whose salary falls beyond a chosen number of standard deviations (e.g., 2) from the mean.

Example Scenario: HR compliance wants to flag unusually high or low salaries within each department for review.

SELECT e.emp_name, e.salary, d.avg_sal, d.stddev_sal
FROM Employees e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_sal, STDDEV(salary) AS stddev_sal
  FROM Employees GROUP BY dept_id
) d ON e.dept_id = d.dept_id
WHERE ABS(e.salary - d.avg_sal) > 2 * d.stddev_sal;