Complete SQL Syllabus with Examples (Colored)
1. BASIC SQL - Questions with Examples
SELECT, WHERE, ORDER BY, GROUP BY, HAVING
Q1. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters after aggregation.
Example:
-- Get employees with salary > 50000
SELECT * FROM employees WHERE salary > 50000;
-- Get departments with AVG salary > 50000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
Q2. Retrieve top 5 highest-paid employees:
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;
Q3. Count number of orders per customer:
SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id;
Basic JOINS (INNER, LEFT, RIGHT, FULL)
Q4. INNER JOIN - customers with orders
SELECT c.customer_name, o.order_id FROM customers c INNER JOIN orders o ON c.customer_id =
o.customer_id;
Q5. LEFT JOIN - customers with or without orders
SELECT c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id =
o.customer_id;
Q6. FULL JOIN - all customers and all orders
SELECT c.customer_name, o.order_id FROM customers c FULL OUTER JOIN orders o ON c.customer_id =
o.customer_id;
Create & Use Tables
Q7. Create a new table:
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
Q8. Insert multiple rows:
Complete SQL Syllabus with Examples (Colored)
INSERT INTO employees (emp_id, name, department_id, salary, hire_date)
VALUES
(1, 'Alice', 101, 60000, '2022-01-10'),
(2, 'Bob', 102, 55000, '2022-03-05');
2. INTERMEDIATE SQL - Questions with Examples
Aggregate Functions
Q9. Total revenue from sales:
SELECT SUM(amount) AS total_revenue FROM sales;
Q10. Max & Min salary per department:
SELECT department_id, MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees GROUP BY
department_id;
Subqueries
Q11. Employees earning more than average:
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Q12. Second highest salary:
SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM
employees);
CTEs and CASE
Q13. Top 3 orders per customer using ROW_NUMBER:
WITH ranked_orders AS (
SELECT customer_id, order_id, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders
)
SELECT * FROM ranked_orders WHERE rn <= 3;
Q14. Categorize salaries:
SELECT name, salary,
CASE
WHEN salary >= 70000 THEN 'High'
WHEN salary >= 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_group
FROM employees;
Q15. Use CASE inside SUM (conditional aggregation):
SELECT department_id,
SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees GROUP BY department_id;
Complete SQL Syllabus with Examples (Colored)
3. ADVANCED SQL - Questions with Examples
Advanced Joins
Q16. Self-join to find manager-employee pairs:
SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id =
m.emp_id;
Q17. Non-equi join - price within range:
SELECT p.product_id, d.discount FROM products p JOIN discounts d ON p.price BETWEEN d.min_price
AND d.max_price;
Window Functions
Q18. Rank employees by salary per department:
SELECT name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank FROM employees;
Q19. Find 2nd order of each customer:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders
)
SELECT * FROM cte WHERE rn = 2;
Q20. Use LEAD and LAG:
SELECT product_id, month, sales,
LAG(sales) OVER (PARTITION BY product_id ORDER BY month) AS prev_month_sales,
LEAD(sales) OVER (PARTITION BY product_id ORDER BY month) AS next_month_sales
FROM sales_data;
Indexing & Data Manipulation
Q21. Create an index on email:
CREATE INDEX idx_email ON users(email);
Q22. View execution plan (PostgreSQL):
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'abc@example.com';
Q23. Update city name:
UPDATE customers SET city = 'NYC' WHERE city = 'New York';
Q24. Delete duplicate rows:
DELETE FROM users WHERE id NOT IN (
SELECT MIN(id) FROM users GROUP BY email
);
Q25. Insert from another table:
INSERT INTO archive_orders (order_id, customer_id, amount)
Complete SQL Syllabus with Examples (Colored)
SELECT order_id, customer_id, amount FROM orders WHERE order_date < '2023-01-01';
