Complete SQL Syllabus with Examples (Colored)
Create and Insert Sample Tables
-- Create Employees table
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
salary DECIMAL(10,2),
gender VARCHAR(10),
hire_date DATE
);
-- Insert data into Employees
INSERT INTO employees (emp_id, name, department_id, salary, gender, hire_date) VALUES
(1, 'Alice', 101, 75000, 'Female', '2021-03-01'),
(2, 'Bob', 102, 62000, 'Male', '2020-06-15'),
(3, 'Carol', 101, 68000, 'Female', '2019-07-30'),
(4, 'David', 103, 72000, 'Male', '2022-01-12'),
(5, 'Eva', 102, 50000, 'Female', '2021-09-25');
-- Create Departments table
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
-- Insert data into Departments
INSERT INTO departments (department_id, department_name) VALUES
(101, 'Sales'),
(102, 'Marketing'),
(103, 'IT');
-- Create Orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
emp_id INT,
order_amount DECIMAL(10,2),
order_date DATE
);
-- Insert data into Orders
INSERT INTO orders (order_id, emp_id, order_amount, order_date) VALUES
(1, 1, 1200.00, '2023-01-10'),
(2, 2, 300.50, '2023-01-12'),
(3, 1, 450.00, '2023-02-10'),
(4, 3, 2000.00, '2023-02-15'),
(5, 5, 600.00, '2023-03-05');
Complete SQL Syllabus with Examples (Colored)
30 Intermediate to Advanced SQL Questions with Queries
1. List all employees earning above average salary:
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
2. Find departments with more than one employee:
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 1;
3. Show highest salary per department:
SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id;
4. Retrieve employees along with their department names:
SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department_id =
d.department_id;
5. Employees who have placed at least one order:
SELECT DISTINCT e.name FROM employees e JOIN orders o ON e.emp_id = o.emp_id;
6. Total order amount by employee:
SELECT emp_id, SUM(order_amount) AS total_sales FROM orders GROUP BY emp_id;
7. Rank employees by salary within departments:
SELECT name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank FROM employees;
8. Get second highest salary:
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
9. Get departments with average salary > 60000:
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > 60000;
10. Count male and female employees per department:
SELECT department_id,
SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS males,
SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS females
FROM employees GROUP BY department_id;
11. Employees who haven't placed any orders:
SELECT name FROM employees WHERE emp_id NOT IN (SELECT emp_id FROM orders);
12. Employees hired after 2021-01-01:
SELECT * FROM employees WHERE hire_date > '2021-01-01';
13. Show latest order per employee:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC) AS rn FROM orders
) t WHERE rn = 1;
14. Average order amount per month:
Complete SQL Syllabus with Examples (Colored)
SELECT EXTRACT(MONTH FROM order_date) AS month, AVG(order_amount) FROM orders GROUP BY month;
15. Categorize employees by salary group:
SELECT name, salary,
CASE
WHEN salary > 70000 THEN 'High'
WHEN salary BETWEEN 60000 AND 70000 THEN 'Medium'
ELSE 'Low'
END AS salary_level FROM employees;
16. Calculate running total of order amounts:
SELECT order_id, emp_id, order_amount,
SUM(order_amount) OVER (ORDER BY order_date) AS running_total FROM orders;
17. Number of days between hire date and today:
SELECT name, CURRENT_DATE - hire_date AS days_in_company FROM employees;
18. Retrieve all orders with employee and department info:
SELECT o.order_id, o.order_amount, e.name, d.department_name
FROM orders o JOIN employees e ON o.emp_id = e.emp_id
JOIN departments d ON e.department_id = d.department_id;
19. Top 2 salaries per department:
WITH ranked_salaries AS (
SELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk FROM employees
)
SELECT * FROM ranked_salaries WHERE rnk <= 2;
20. Get employee who made the highest order:
SELECT e.name, o.order_amount FROM orders o
JOIN employees e ON o.emp_id = e.emp_id
WHERE o.order_amount = (SELECT MAX(order_amount) FROM orders);
21. Total salary per gender:
SELECT gender, SUM(salary) FROM employees GROUP BY gender;
22. Use CTE to filter employees in Sales earning over 65000:
WITH sales_emps AS (
SELECT * FROM employees WHERE department_id = 101
)
SELECT * FROM sales_emps WHERE salary > 65000;
23. Number of employees hired each year:
SELECT EXTRACT(YEAR FROM hire_date) AS year, COUNT(*) FROM employees GROUP BY year;
24. Self-join to show manager-employee relation (assume manager_id exists):
-- Assuming manager_id in employees
SELECT e.name AS employee, m.name AS manager
FROM employees e JOIN employees m ON e.manager_id = m.emp_id;
Complete SQL Syllabus with Examples (Colored)
25. Non-equi join simulation - range-based salary classification (hypothetical):
-- Assuming a salary_grade table
-- SELECT e.name, g.grade FROM employees e JOIN salary_grade g ON e.salary BETWEEN g.min_sal AND
g.max_sal;
26. Find employee(s) with the most orders:
SELECT emp_id FROM orders GROUP BY emp_id ORDER BY COUNT(*) DESC LIMIT 1;
27. Compare each employee's salary to department average:
SELECT name, salary, department_id,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary FROM employees;
28. Find duplicated employee names (if any):
SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) > 1;
29. Employees earning same as someone else in another department:
SELECT e1.name, e1.salary FROM employees e1
WHERE EXISTS (
SELECT 1 FROM employees e2 WHERE e1.salary = e2.salary AND e1.department_id != e2.department_id
);
30. Employees who made orders > 1000 and are in IT:
SELECT e.name, o.order_amount FROM employees e
JOIN orders o ON e.emp_id = o.emp_id
WHERE o.order_amount > 1000 AND e.department_id = 103;
