63 SQL Interview Programs with Queries and Solutions
Interview preparation · Technical guide
63 SQL Interview Programs
Sixty-three SQL examples covering querying, aggregation, analytics, and data maintenance. Each non-portable example identifies its database dialect; date windows are illustrative.
The supplied source contains 63 examples, not 100. Queries primarily use SQL Server T-SQL; explicitly named alternatives use other dialects. Schemas, keys, data types, and transaction requirements must match your database. Queries have been statically reviewed, not run against a database.
1. Find Second Highest Salary
Find the second distinct, non-null salary, not simply the second employee row. This portable aggregate returns NULL when no second distinct salary exists.
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
2. Find Duplicate Records
-- Method 1: Using GROUP BY
SELECT column1, column2, COUNT(*) as count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
-- Method 2: Using ROW_NUMBER()
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) as rn
FROM table_name
) t WHERE rn > 1;
3. Delete Duplicate Records
SQL Server: partition by the columns that define a duplicate and retain the row with the smallest unique id. Inspect the selected rows and apply the delete in the intended transaction; a concurrent writer can otherwise change the duplicate set.
WITH duplicates AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY column1, column2 ORDER BY id) AS rn
FROM dbo.table_name
)
DELETE FROM duplicates WHERE rn > 1;
4. Swap Column Values
PostgreSQL or SQL Server: assignments use the old row values for this swap. The arithmetic “swap” in the source repeated a column assignment and could overflow. MySQL single-table assignment evaluation differs, so this statement should not be presented as a universal SQL swap.
UPDATE table_name
SET column1 = column2, column2 = column1;
5. Find Missing Numbers in Sequence
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Find missing numbers in a sequence
WITH RECURSIVE numbers AS (
SELECT MIN(id) as num FROM table_name
UNION ALL
SELECT num + 1 FROM numbers WHERE num < (SELECT MAX(id) FROM table_name)
)
SELECT n.num as missing_number
FROM numbers n
LEFT JOIN table_name t ON n.num = t.id
WHERE t.id IS NULL;
Advanced Joins and Subqueries
6. Self Join - Employee Manager Hierarchy
SELECT
e.employee_name,
e.salary,
m.employee_name as manager_name,
m.salary as manager_salary
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
7. Multiple Table Join with Aggregation
Pre-aggregate sales to one row per employee before joining, so employee counts and salary averages are not multiplied by sales rows. Keep the sales filter inside the aggregate to retain employees without sales.
WITH employee_sales AS (
SELECT employee_id, SUM(sales_amount) AS total_sales
FROM sales WHERE sale_date >= '2023-01-01'
GROUP BY employee_id
)
SELECT d.department_id, d.department_name,
COUNT(e.employee_id) AS employee_count,
AVG(CAST(e.salary AS DECIMAL(18,2))) AS avg_salary,
COALESCE(SUM(s.total_sales), 0) AS total_sales
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
LEFT JOIN employee_sales s ON s.employee_id = e.employee_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(e.employee_id) > 5;
8. Correlated Subquery - Find Employees with Above Average Salary
SELECT employee_name, salary, department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);
9. EXISTS vs IN Performance Comparison
EXISTS and IN can produce similar or identical execution plans. Neither is universally faster; inspect the plan and data distribution. NOT IN additionally has important NULL semantics.
-- Using EXISTS; compare actual plans
SELECT customer_name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= '2023-01-01'
);
-- Using IN
SELECT customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_date >= '2023-01-01'
);
10. Cross Join with Conditions
-- Generate all possible combinations
SELECT
p1.product_name as product1,
p2.product_name as product2,
p1.price + p2.price as total_price
FROM products p1
CROSS JOIN products p2
WHERE p1.product_id < p2.product_id
AND p1.category_id = p2.category_id;
Window Functions
11. Running Total
Use a deterministic order with a unique sale_id tie-breaker. A three-row average is not necessarily a three-day average.
SELECT order_date, sale_id, sales_amount,
SUM(sales_amount) OVER (ORDER BY order_date, sale_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(CAST(sales_amount AS DECIMAL(18,2))) OVER (ORDER BY order_date, sale_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_rows
FROM sales;
12. Rank Employees by Salary in Each Department
RANK leaves gaps after ties; DENSE_RANK does not. ROW_NUMBER needs a unique tie-breaker for repeatable row numbering.
SELECT
employee_name,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dense_rank,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id) as row_num
FROM employees;
13. Compare Current Row with Previous/Next
Aggregate by calendar date before comparing days. LAG/LEAD return the previous/next observed row, which may skip missing days; join a calendar table when every calendar day must appear.
WITH daily AS (
SELECT CAST(order_date AS DATE) AS sale_day, SUM(sales_amount) AS total
FROM sales GROUP BY CAST(order_date AS DATE)
)
SELECT sale_day, total,
LAG(total) OVER (ORDER BY sale_day) AS previous_observed_day_total,
LEAD(total) OVER (ORDER BY sale_day) AS next_observed_day_total
FROM daily;
14. Cumulative Distribution
SELECT
employee_name,
salary,
CUME_DIST() OVER (ORDER BY salary) as cumulative_distribution,
PERCENT_RANK() OVER (ORDER BY salary) as percentile_rank
FROM employees;
15. First and Last Value in Window
A unique tie-breaker makes the selected employee deterministic. A full-partition frame is necessary for LAST_VALUE to reach the end of the partition.
SELECT
department_id,
employee_name,
salary,
FIRST_VALUE(employee_name) OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id) as highest_paid,
LAST_VALUE(employee_name) OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_paid
FROM employees;
Data Aggregation and Grouping
16. Pivot Table - Sales by Month and Product
Dialect: MySQL 8+. Examples assume the stated columns and constraints exist.
SELECT
product_name,
SUM(CASE WHEN MONTH(order_date) = 1 THEN sales_amount ELSE 0 END) as jan_sales,
SUM(CASE WHEN MONTH(order_date) = 2 THEN sales_amount ELSE 0 END) as feb_sales,
SUM(CASE WHEN MONTH(order_date) = 3 THEN sales_amount ELSE 0 END) as mar_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
WHERE YEAR(order_date) = 2023
GROUP BY product_name;
17. Multiple Aggregations with Different Groupings
Dialect: MySQL 8+. Examples assume the stated columns and constraints exist.
SELECT
department_id,
COUNT(*) as total_employees,
AVG(salary) as avg_salary,
MAX(salary) as max_salary,
MIN(salary) as min_salary,
SUM(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) as high_earners
FROM employees
GROUP BY department_id
WITH ROLLUP;
18. Conditional Aggregation
SELECT
department_id,
COUNT(*) as total_employees,
COUNT(CASE WHEN gender = 'M' THEN 1 END) as male_count,
COUNT(CASE WHEN gender = 'F' THEN 1 END) as female_count,
AVG(CASE WHEN gender = 'M' THEN salary END) as male_avg_salary,
AVG(CASE WHEN gender = 'F' THEN salary END) as female_avg_salary
FROM employees
GROUP BY department_id;
19. Group by Time Intervals
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
SELECT
DATE_TRUNC('month', order_date) as month,
COUNT(*) as order_count,
SUM(sales_amount) as total_sales,
AVG(sales_amount) as avg_order_value
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
20. Hierarchical Aggregation
SELECT
region,
country,
city,
SUM(sales_amount) as total_sales,
GROUPING(region) as region_grouping,
GROUPING(country) as country_grouping,
GROUPING(city) as city_grouping
FROM sales
GROUP BY GROUPING SETS ((region, country, city), (region, country), (region), ());
Performance and Optimization
21. Query Optimization - Avoid SELECT *
-- Bad
SELECT * FROM employees WHERE department_id = 10;
-- Good
SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = 10;
22. Index Usage Analysis
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Check if indexes are being used
EXPLAIN ANALYZE
SELECT employee_name, salary
FROM employees
WHERE department_id = 10 AND salary > 50000;
-- Create composite index for better performance
CREATE INDEX idx_dept_salary ON employees(department_id, salary);
23. Partitioned Table Query
-- Query partitioned table efficiently
SELECT
order_date,
SUM(sales_amount) as daily_sales
FROM sales
WHERE order_date >= '2023-01-01'
AND order_date < '2023-02-01'
GROUP BY order_date;
24. Materialized View for Complex Aggregations
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Create materialized view
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
DATE_TRUNC('month', order_date) as month,
product_id,
SUM(sales_amount) as total_sales,
COUNT(*) as order_count
FROM sales
GROUP BY DATE_TRUNC('month', order_date), product_id;
-- Query materialized view
SELECT * FROM monthly_sales_summary WHERE month >= '2023-01-01';
25. Query with CTE for Readability
PostgreSQL. A CTE improves organization, not necessarily execution speed. Use numeric arithmetic and NULLIF to handle a zero preceding total.
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) as month,
SUM(sales_amount) as total_sales
FROM sales
WHERE order_date >= '2023-01-01'
GROUP BY DATE_TRUNC('month', order_date)
),
sales_growth AS (
SELECT
month,
total_sales,
LAG(total_sales) OVER (ORDER BY month) as prev_month_sales,
100.0 * (total_sales - LAG(total_sales) OVER (ORDER BY month)) / NULLIF(LAG(total_sales) OVER (ORDER BY month), 0) as growth_percentage
FROM monthly_sales
)
SELECT * FROM sales_growth WHERE growth_percentage > 10;
Data Manipulation
26. Bulk Update with CASE
UPDATE employees
SET salary = CASE
WHEN department_id = 1 THEN salary * 1.1
WHEN department_id = 2 THEN salary * 1.05
WHEN department_id = 3 THEN salary * 1.08
ELSE salary * 1.03
END
WHERE hire_date < '2020-01-01';
27. Insert with Conditional Logic
INSERT INTO employee_audit (employee_id, action, old_salary, new_salary, change_date)
SELECT
employee_id,
'SALARY_UPDATE',
salary as old_salary,
CASE
WHEN department_id = 1 THEN salary * 1.1
ELSE salary * 1.05
END as new_salary,
CURRENT_TIMESTAMP
FROM employees
WHERE department_id IN (1, 2);
28. Merge/Upsert Operation
PostgreSQL: ON CONFLICT requires a matching unique or primary-key constraint. The database arbitrates conflicting inserts. MySQL uses ON DUPLICATE KEY UPDATE, with different semantics; do not interchange the dialects.
INSERT INTO customers (customer_id, customer_name, email)
VALUES (1, 'John Doe', 'john@example.com')
ON CONFLICT (customer_id) DO UPDATE
SET customer_name = EXCLUDED.customer_name, email = EXCLUDED.email;
29. Data Archiving
PostgreSQL: a data-modifying CTE moves exactly the deleted rows into the archive in one statement. Explicit columns must match the real schema. A constraint failure rolls back the statement.
WITH moved AS (
DELETE FROM orders WHERE order_date < DATE '2022-01-01'
RETURNING order_id, customer_id, order_date, sales_amount
)
INSERT INTO orders_archive (order_id, customer_id, order_date, sales_amount)
SELECT order_id, customer_id, order_date, sales_amount FROM moved;
30. Data Validation and Cleanup
Dialect: MySQL 8+. Examples assume the stated columns and constraints exist.
-- Find and fix invalid email addresses
UPDATE customers
SET email = NULL
WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
-- Remove duplicate emails
UPDATE customers c1
JOIN (
SELECT email, MIN(customer_id) as min_id
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) c2 ON c1.email = c2.email AND c1.customer_id > c2.min_id
SET c1.email = NULL;
Advanced Analytics
31. Cohort Analysis
PostgreSQL: derive each user’s cohort from their first order over the full intended history. Use years and months together when computing the cohort offset; extracting only the month field loses full years. This shows observed retention months; use a calendar grid to include zero-activity months.
WITH first_orders AS (
SELECT user_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders GROUP BY user_id
), activity AS (
SELECT DISTINCT o.user_id, f.cohort_month,
DATE_TRUNC('month', o.order_date) AS order_month
FROM orders o JOIN first_orders f ON f.user_id = o.user_id
), offsets AS (
SELECT *,
(EXTRACT(YEAR FROM order_month) - EXTRACT(YEAR FROM cohort_month)) * 12
+ EXTRACT(MONTH FROM order_month) - EXTRACT(MONTH FROM cohort_month) AS month_number
FROM activity
), sizes AS (
SELECT cohort_month, COUNT(*) AS cohort_size FROM first_orders GROUP BY cohort_month
)
SELECT a.cohort_month, a.month_number, COUNT(*) AS retained_users,
s.cohort_size, ROUND(100.0 * COUNT(*) / NULLIF(s.cohort_size, 0), 2) AS retention_pct
FROM offsets a JOIN sizes s ON s.cohort_month = a.cohort_month
GROUP BY a.cohort_month, a.month_number, s.cohort_size
ORDER BY a.cohort_month, a.month_number;
32. RFM Analysis
Dialect: MySQL 8+. Examples assume the stated columns and constraints exist.
WITH rfm_scores AS (
SELECT
customer_id,
DATEDIFF(CURRENT_DATE, MAX(order_date)) as recency,
COUNT(*) as frequency,
SUM(sales_amount) as monetary,
NTILE(4) OVER (ORDER BY DATEDIFF(CURRENT_DATE, MAX(order_date)) DESC) as r_score,
NTILE(4) OVER (ORDER BY COUNT(*)) as f_score,
NTILE(4) OVER (ORDER BY SUM(sales_amount)) as m_score
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
GROUP BY customer_id
)
SELECT
customer_id,
r_score,
f_score,
m_score,
CONCAT(r_score, f_score, m_score) as rfm_score,
CASE
WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Champions'
WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
WHEN r_score >= 3 AND m_score >= 3 THEN 'Big Spenders'
WHEN f_score >= 3 AND m_score >= 3 THEN 'At Risk'
WHEN r_score >= 3 THEN 'Recent Customers'
WHEN f_score >= 3 THEN 'Frequent Customers'
WHEN m_score >= 3 THEN 'High Value'
ELSE 'Lost'
END as customer_segment
FROM rfm_scores;
33. Time Series Analysis
The query calculates moving averages over observed dates. Missing dates must be filled from a calendar for an actual seven-calendar-day window. Cast timestamps to dates and guard division by zero.
WITH daily_sales AS (
SELECT
CAST(order_date AS DATE) AS order_date,
SUM(sales_amount) as daily_total
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY CAST(order_date AS DATE)
),
moving_averages AS (
SELECT
CAST(order_date AS DATE) AS order_date,
daily_total,
AVG(daily_total) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma_7,
AVG(daily_total) OVER (ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as ma_30
FROM daily_sales
)
SELECT
order_date,
daily_total,
ma_7,
ma_30,
100.0 * (daily_total - ma_7) / NULLIF(ma_7, 0) as deviation_from_7d_avg
FROM moving_averages
ORDER BY order_date;
34. A/B Testing Analysis
Assume one independent observation per assigned user and a numeric 0/1 conversion flag. Cast the flag to avoid integer averaging. These are descriptive metrics, not a statistical significance test.
WITH ab_test_results AS (
SELECT
user_id,
variant,
conversion_flag,
revenue
FROM ab_test_data
WHERE test_date >= '2023-01-01'
),
variant_stats AS (
SELECT
variant,
COUNT(*) as sample_size,
SUM(conversion_flag) as conversions,
AVG(CAST(conversion_flag AS DECIMAL(18,6))) as conversion_rate,
AVG(revenue) as avg_revenue,
SUM(revenue) as total_revenue
FROM ab_test_results
GROUP BY variant
)
SELECT
variant,
sample_size,
conversions,
ROUND(conversion_rate * 100, 2) as conversion_rate_pct,
ROUND(avg_revenue, 2) as avg_revenue,
ROUND(total_revenue, 2) as total_revenue
FROM variant_stats
ORDER BY variant;
35. Customer Lifetime Value (CLV)
MySQL 8+: this is annualized historical revenue, not a validated customer lifetime-value estimate. Predictive CLV additionally needs a time horizon, retention/churn, margin, and discount assumptions.
WITH customer_metrics AS (
SELECT
customer_id,
COUNT(DISTINCT order_id) as total_orders,
SUM(sales_amount) as total_revenue,
MIN(order_date) as first_order,
MAX(order_date) as last_order,
DATEDIFF(MAX(order_date), MIN(order_date)) as customer_lifespan_days
FROM orders
WHERE order_date >= '2022-01-01'
GROUP BY customer_id
),
clv_calculation AS (
SELECT
customer_id,
total_orders,
total_revenue,
customer_lifespan_days,
total_revenue / NULLIF(customer_lifespan_days, 0) * 365 as annual_revenue,
total_revenue / NULLIF(total_orders, 0) as avg_order_value
FROM customer_metrics
)
SELECT
customer_id,
total_orders,
total_revenue,
ROUND(annual_revenue, 2) as annualized_revenue,
ROUND(avg_order_value, 2) as avg_order_value
FROM clv_calculation
ORDER BY annualized_revenue DESC;
Database Design
36. Normalized vs Denormalized Queries
-- Normalized approach
SELECT
o.order_id,
o.order_date,
c.customer_name,
p.product_name,
od.quantity,
od.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;
-- Denormalized approach (if order_summary table exists)
SELECT
order_id,
order_date,
customer_name,
product_name,
quantity,
unit_price
FROM order_summary;
37. Audit Trail Implementation
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Create audit table
CREATE TABLE employee_audit (
audit_id SERIAL PRIMARY KEY,
employee_id INT,
action VARCHAR(20),
old_values JSONB,
new_values JSONB,
changed_by VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create trigger for audit
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
INSERT INTO employee_audit (employee_id, action, old_values, new_values, changed_by)
VALUES (OLD.employee_id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), current_user);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO employee_audit (employee_id, action, old_values, changed_by)
VALUES (OLD.employee_id, 'DELETE', to_jsonb(OLD), current_user);
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER employee_audit_trigger
AFTER UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_employee_changes();
38. Soft Delete Implementation
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Add soft delete column
ALTER TABLE employees ADD COLUMN deleted_at TIMESTAMP NULL;
-- Soft delete query
UPDATE employees
SET deleted_at = CURRENT_TIMESTAMP
WHERE employee_id = 123;
-- Query excluding soft deleted records
SELECT * FROM employees WHERE deleted_at IS NULL;
-- Hard delete after soft delete
DELETE FROM employees WHERE deleted_at < '2022-01-01';
39. Hierarchical Data (Adjacency List)
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Create hierarchical table
CREATE TABLE categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100),
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);
-- Query hierarchy using CTE
WITH RECURSIVE category_tree AS (
SELECT category_id, category_name, parent_id, 0 as level
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, c.category_name, c.parent_id, ct.level + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT
LPAD('', level * 2, ' ') || category_name as hierarchy,
level
FROM category_tree
ORDER BY level, category_name;
40. Polymorphic Association
Dialect: PostgreSQL. This discriminator/id design cannot enforce a foreign key to several different tables. For strict referential integrity, use separate relationship tables or a shared parent table instead.
-- Create polymorphic table
CREATE TABLE attachments (
attachment_id SERIAL PRIMARY KEY,
file_name VARCHAR(255),
file_path VARCHAR(500),
attachable_type VARCHAR(50),
attachable_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Query attachments for different entities
SELECT
a.attachment_id,
a.file_name,
CASE a.attachable_type
WHEN 'Employee' THEN e.employee_name
WHEN 'Product' THEN p.product_name
WHEN 'Order' THEN CONCAT('Order #', o.order_id)
END as attached_to
FROM attachments a
LEFT JOIN employees e ON a.attachable_type = 'Employee' AND a.attachable_id = e.employee_id
LEFT JOIN products p ON a.attachable_type = 'Product' AND a.attachable_id = p.product_id
LEFT JOIN orders o ON a.attachable_type = 'Order' AND a.attachable_id = o.order_id;
Error Handling and Constraints
41. Check Constraints
PostgreSQL example. CHECK constraints reject FALSE but accept UNKNOWN, so add NOT NULL when required. A simple email pattern is only a format heuristic, not proof of deliverability. A changing “today” rule should be validated at write time rather than treated as an immutable property of a row.
ALTER TABLE employees ALTER COLUMN salary SET NOT NULL;
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);
ALTER TABLE employees ADD CONSTRAINT chk_email_basic
CHECK (email IS NULL OR email ~ '^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$');
42. Unique Constraints with NULL Handling
PostgreSQL permits multiple NULL values in an ordinary unique constraint by default; SQL Server commonly uses a filtered unique index to permit them. Include both predicates when excluding deleted rows and NULL emails.
-- Create unique constraint that allows multiple NULLs
CREATE UNIQUE INDEX idx_employee_email ON employees (email) WHERE email IS NOT NULL;
-- Or use partial unique index
CREATE UNIQUE INDEX idx_active_employee_email ON employees (email)
WHERE deleted_at IS NULL AND email IS NOT NULL;
43. Foreign Key with Cascade
-- Create foreign key with cascade delete
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE;
-- Create foreign key with cascade update
ALTER TABLE order_details
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON UPDATE CASCADE;
44. Transaction with Error Handling
SQL Server T-SQL: TRY/CATCH belongs around an explicit transaction. XACT_ABORT handles many run-time errors; rethrow the original error after rolling back an active transaction.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (1, CAST(SYSDATETIME() AS DATE), 100.00);
UPDATE customers SET total_orders = total_orders + 1 WHERE customer_id = 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
45. Data Validation Function
PostgreSQL uses !~ for a negated regular-expression match, not NOT REGEXP. This basic format check is not a full email validator. Register the trigger to make the function run on writes.
-- Create validation function
CREATE OR REPLACE FUNCTION validate_employee_data(
p_employee_name VARCHAR(100),
p_salary DECIMAL(10,2),
p_email VARCHAR(255)
) RETURNS BOOLEAN AS $$
BEGIN
-- Check employee name
IF p_employee_name IS NULL OR LENGTH(TRIM(p_employee_name)) = 0 THEN
RAISE EXCEPTION 'Employee name cannot be empty';
END IF;
-- Check salary
IF p_salary IS NULL OR p_salary <= 0 THEN
RAISE EXCEPTION 'Salary must be positive';
END IF;
-- Check email format
IF p_email IS NULL OR p_email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
RAISE EXCEPTION 'Invalid email format';
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
-- Use in trigger
CREATE OR REPLACE FUNCTION validate_employee_trigger()
RETURNS TRIGGER AS $$
BEGIN
PERFORM validate_employee_data(NEW.employee_name, NEW.salary, NEW.email);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Real-world Scenarios
CREATE TRIGGER validate_employee_before_write
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION validate_employee_trigger();
46. E-commerce Analytics
Dialect: MySQL 8+. Examples assume the stated columns and constraints exist.
-- Customer purchase patterns
WITH customer_purchase_patterns AS (
SELECT
customer_id,
COUNT(*) as total_orders,
AVG(sales_amount) as avg_order_value,
SUM(sales_amount) as total_spent,
MIN(order_date) as first_order,
MAX(order_date) as last_order,
DATEDIFF(MAX(order_date), MIN(order_date)) as customer_lifespan
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY customer_id
)
SELECT
CASE
WHEN total_orders = 1 THEN 'One-time'
WHEN total_orders BETWEEN 2 AND 5 THEN 'Occasional'
WHEN total_orders BETWEEN 6 AND 15 THEN 'Regular'
ELSE 'Frequent'
END as customer_type,
COUNT(*) as customer_count,
AVG(avg_order_value) as avg_order_value,
AVG(total_spent) as avg_total_spent
FROM customer_purchase_patterns
GROUP BY customer_type;
47. Inventory Management
MySQL 8+: filter recent sales inside the aggregate, then left join it to all products. Filtering the outer result would lose products that have only old sales.
SELECT p.product_id, p.product_name, p.current_stock,
CASE WHEN p.current_stock <= p.reorder_level THEN 'Reorder'
WHEN p.current_stock >= p.max_stock * 0.9 THEN 'Overstocked'
ELSE 'Normal' END AS stock_status,
COALESCE(s.units_sold, 0) AS units_sold_last_month
FROM products p
LEFT JOIN (
SELECT od.product_id, SUM(od.quantity) AS units_sold
FROM order_details od JOIN orders o ON o.order_id = od.order_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
GROUP BY od.product_id
) s ON s.product_id = p.product_id;
48. Employee Performance Analysis
Keep employees without qualifying orders by applying the date predicate in the LEFT JOIN.
-- Sales performance by employee
WITH employee_performance AS (
SELECT
e.employee_id,
e.employee_name,
e.department_id,
COUNT(DISTINCT o.order_id) as total_orders,
COALESCE(SUM(o.sales_amount), 0) as total_sales,
AVG(o.sales_amount) as avg_order_value,
COUNT(DISTINCT o.customer_id) as unique_customers
FROM employees e
LEFT JOIN orders o ON e.employee_id = o.employee_id
AND o.order_date >= '2023-01-01'
GROUP BY e.employee_id, e.employee_name, e.department_id
)
SELECT
ep.*,
d.department_name,
RANK() OVER (PARTITION BY ep.department_id ORDER BY ep.total_sales DESC) as dept_rank,
RANK() OVER (ORDER BY ep.total_sales DESC) as overall_rank
FROM employee_performance ep
JOIN departments d ON ep.department_id = d.department_id
ORDER BY ep.total_sales DESC;
49. Financial Reporting
PostgreSQL: include cost-only months with a FULL OUTER JOIN; profit margin is undefined when revenue is zero.
-- Monthly P&L statement
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) as month,
SUM(sales_amount) as revenue
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY DATE_TRUNC('month', order_date)
),
monthly_costs AS (
SELECT
DATE_TRUNC('month', expense_date) as month,
SUM(amount) as costs
FROM expenses
WHERE expense_date >= '2023-01-01'
GROUP BY DATE_TRUNC('month', expense_date)
)
SELECT
COALESCE(r.month, c.month) AS month,
COALESCE(r.revenue, 0) AS revenue,
COALESCE(c.costs, 0) as costs,
COALESCE(r.revenue, 0) - COALESCE(c.costs, 0) as profit,
(COALESCE(r.revenue, 0) - COALESCE(c.costs, 0)) / NULLIF(r.revenue, 0) * 100 as profit_margin
FROM monthly_revenue r
FULL OUTER JOIN monthly_costs c ON r.month = c.month
ORDER BY COALESCE(r.month, c.month);
50. Customer Segmentation
Dialect: MySQL 8+. Examples assume the stated columns and constraints exist.
-- RFM-based customer segmentation
WITH customer_rfm AS (
SELECT
customer_id,
DATEDIFF(CURRENT_DATE, MAX(order_date)) as recency,
COUNT(*) as frequency,
SUM(sales_amount) as monetary,
NTILE(5) OVER (ORDER BY DATEDIFF(CURRENT_DATE, MAX(order_date)) DESC) as r_score,
NTILE(5) OVER (ORDER BY COUNT(*)) as f_score,
NTILE(5) OVER (ORDER BY SUM(sales_amount)) as m_score
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
GROUP BY customer_id
),
customer_segments AS (
SELECT
customer_id,
r_score,
f_score,
m_score,
(r_score + f_score + m_score) / 3 as avg_score,
CASE
WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'VIP'
WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'High Value'
WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal'
WHEN m_score >= 4 THEN 'Big Spender'
WHEN r_score >= 4 THEN 'Recent'
WHEN f_score >= 4 THEN 'Frequent'
WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'At Risk'
ELSE 'Regular'
END as segment
FROM customer_rfm
)
SELECT
segment,
COUNT(*) as customer_count,
AVG(avg_score) as avg_rfm_score
FROM customer_segments
GROUP BY segment
ORDER BY avg_rfm_score DESC;
Advanced SQL Techniques
51. Recursive CTE for Bill of Materials
Dialect: PostgreSQL. This traverses a bill-of-materials hierarchy, not an exploded quantity calculation. Multiply quantities along each path to compute total requirements. The depth limit truncates deeper structures and does not validate cycles; use cycle detection for arbitrary data.
WITH RECURSIVE bom_tree AS (
SELECT
component_id,
parent_id,
component_name,
quantity,
1 as level,
CAST(component_name AS VARCHAR(1000)) as path
FROM components
WHERE parent_id IS NULL
UNION ALL
SELECT
c.component_id,
c.parent_id,
c.component_name,
c.quantity,
bt.level + 1,
CAST(bt.path || ' -> ' || c.component_name AS VARCHAR(1000))
FROM components c
JOIN bom_tree bt ON c.parent_id = bt.component_id
WHERE bt.level < 10
)
SELECT
LPAD('', level * 2, ' ') || component_name as hierarchy,
level,
path
FROM bom_tree
ORDER BY path;
52. Pivot and Unpivot Operations
SQL Server: conditional aggregation is a portable pivot pattern, while UNPIVOT is dialect-specific. Each aggregate below is restricted to one year.
SELECT product_id,
SUM(CASE WHEN MONTH(order_date) = 1 THEN sales_amount ELSE 0 END) AS jan_sales,
SUM(CASE WHEN MONTH(order_date) = 2 THEN sales_amount ELSE 0 END) AS feb_sales,
SUM(CASE WHEN MONTH(order_date) = 3 THEN sales_amount ELSE 0 END) AS mar_sales
FROM orders
WHERE order_date >= '20230101' AND order_date < '20240101'
GROUP BY product_id;
SELECT product_id, month_name, sales_amount
FROM product_sales_summary
UNPIVOT (sales_amount FOR month_name IN (jan_sales, feb_sales, mar_sales)) u;
53. Advanced Date Manipulation
PostgreSQL: generate calendar dates, then exclude weekends. Real business-day calendars also need holidays and location-specific working-day rules.
WITH days AS (
SELECT d::date AS day
FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL '1 day') d
)
SELECT day, ROW_NUMBER() OVER (ORDER BY day) AS business_day_number
FROM days WHERE EXTRACT(ISODOW FROM day) BETWEEN 1 AND 5
ORDER BY day;
54. Complex Data Validation
-- Validate data integrity across multiple tables
WITH validation_checks AS (
SELECT 'orphaned_orders' as check_name, COUNT(*) as issue_count
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
UNION ALL
SELECT 'negative_sales' as check_name, COUNT(*) as issue_count
FROM orders
WHERE sales_amount < 0
UNION ALL
SELECT 'future_orders' as check_name, COUNT(*) as issue_count
FROM orders
WHERE order_date > CURRENT_DATE
UNION ALL
SELECT 'duplicate_emails' as check_name, COUNT(*) as issue_count
FROM (
SELECT email, COUNT(*) as cnt
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) duplicates
)
SELECT
check_name,
issue_count,
CASE
WHEN issue_count = 0 THEN 'PASS'
ELSE 'FAIL'
END as status
FROM validation_checks
ORDER BY issue_count DESC;
55. Advanced Aggregation with Multiple Groupings
Aggregate line revenue, not the order header total after joining order details. Use GROUPING to distinguish a subtotal from a real NULL category. Allocate tax, shipping, and order-level discounts explicitly if those belong in this metric.
SELECT CASE WHEN GROUPING(d.department_name) = 1 THEN 'All Departments'
ELSE d.department_name END AS department,
CASE WHEN GROUPING(p.product_category) = 1 THEN 'All Categories'
ELSE p.product_category END AS category,
COUNT(DISTINCT o.customer_id) AS unique_customers,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(od.quantity * od.unit_price) AS line_revenue
FROM orders o
JOIN employees e ON e.employee_id = o.employee_id
JOIN departments d ON d.department_id = e.department_id
JOIN order_details od ON od.order_id = o.order_id
JOIN products p ON p.product_id = od.product_id
WHERE o.order_date >= '2023-01-01'
GROUP BY GROUPING SETS ((d.department_name, p.product_category),
(d.department_name), (p.product_category), ());
56. Query Plan Analysis
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT
c.customer_name,
COUNT(o.order_id) as order_count,
SUM(o.sales_amount) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2023-01-01'
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.sales_amount) > 1000
ORDER BY total_spent DESC;
57. Index Optimization
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Create composite indexes for common query patterns
CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);
CREATE INDEX idx_orders_date_amount ON orders(order_date, sales_amount);
CREATE INDEX idx_employee_dept_salary ON employees(department_id, salary DESC);
-- Create partial indexes for filtered queries
CREATE INDEX idx_active_orders ON orders(order_id) WHERE status = 'active';
CREATE INDEX idx_high_value_orders ON orders(order_id, sales_amount) WHERE sales_amount > 1000;
58. Partitioning Strategy
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Create partitioned table by date
CREATE TABLE orders_partitioned (
order_id INT,
customer_id INT,
order_date DATE,
sales_amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);
-- Create partitions for each month
CREATE TABLE orders_2023_01 PARTITION OF orders_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE orders_2023_02 PARTITION OF orders_partitioned
FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
59. Materialized View for Complex Aggregations
Dialect: PostgreSQL. Adapt syntax and indexing assumptions before using another database.
-- Create materialized view for daily sales summary
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
order_date,
COUNT(DISTINCT customer_id) as unique_customers,
COUNT(order_id) as total_orders,
SUM(sales_amount) as total_sales,
AVG(sales_amount) as avg_order_value
FROM orders
GROUP BY order_date;
-- Create index on materialized view
CREATE INDEX idx_daily_sales_date ON daily_sales_summary(order_date);
-- Refresh materialized view
REFRESH MATERIALIZED VIEW daily_sales_summary;
60. Query Rewriting for Better Performance
The second example is a correlated COUNT subquery, not EXISTS. Neither rewrite is universally faster; compare actual plans and an index on orders(customer_id).
-- Join-and-group form
SELECT
c.customer_name,
COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
-- Equivalent correlated COUNT form; measure performance
SELECT
c.customer_name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) as order_count
FROM customers c;
Data Quality and Governance
61. Data Completeness Check
Empty tables have no defined completeness percentage; return NULL rather than divide by zero. Empty strings still count as non-null.
-- Check for missing data
SELECT
'customers' as table_name,
COUNT(*) as total_rows,
COUNT(email) as non_null_emails,
COUNT(phone) as non_null_phones,
ROUND(COUNT(email) * 100.0 / NULLIF(COUNT(*), 0), 2) as email_completeness_pct,
ROUND(COUNT(phone) * 100.0 / NULLIF(COUNT(*), 0), 2) as phone_completeness_pct
FROM customers
UNION ALL
SELECT
'orders' as table_name,
COUNT(*) as total_rows,
COUNT(customer_id) as non_null_customers,
COUNT(sales_amount) as non_null_amounts,
ROUND(COUNT(customer_id) * 100.0 / NULLIF(COUNT(*), 0), 2) as customer_completeness_pct,
ROUND(COUNT(sales_amount) * 100.0 / NULLIF(COUNT(*), 0), 2) as amount_completeness_pct
FROM orders;
62. Data Consistency Validation
-- Validate referential integrity
SELECT
'orphaned_orders' as issue_type,
COUNT(*) as count
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
UNION ALL
SELECT
'orphaned_order_details' as issue_type,
COUNT(*) as count
FROM order_details od
LEFT JOIN orders o ON od.order_id = o.order_id
WHERE o.order_id IS NULL
UNION ALL
SELECT
'mismatched_totals' as issue_type,
COUNT(*) as count
FROM orders o
JOIN (
SELECT order_id, SUM(quantity * unit_price) as calculated_total
FROM order_details
GROUP BY order_id
) od ON o.order_id = od.order_id
WHERE ABS(o.total_amount - od.calculated_total) > 0.01;
63. Data Anomaly Detection
PostgreSQL: STDDEV computes sample standard deviation. A z-score is undefined for zero variance or insufficient observations. The cutoff is a heuristic and does not prove an anomaly.
-- Detect outliers using statistical methods
WITH sales_stats AS (
SELECT
AVG(sales_amount) as mean,
STDDEV(sales_amount) as std_dev
FROM orders
WHERE order_date >= '2023-01-01'
),
outliers AS (
SELECT
order_id,
sales_amount,
(sales_amount - s.mean) / NULLIF(s.std_dev, 0) as z_score
FROM orders o, sales_stats s
WHERE order_date >= '2023-01-01'
)
SELECT
order_id,
sales_amount,
z_score,
CASE
WHEN ABS(z_score) > 3 THEN 'Extreme Outlier'
WHEN ABS(z_score) > 2 THEN 'Outlier'
ELSE 'Normal'
END as outlier_type
FROM outliers
WHERE ABS(z_score) > 2
ORDER BY ABS(z_score) DESC;