KEMBAR78
Full SQL 100 Queries With Tables Colored | PDF | Databases | Data Management
Scribd
Upload
27 views13 pages

Full SQL 100 Queries With Tables Colored

The document provides a comprehensive SQL syllabus that includes the creation of tables for employees, departments, and orders, along with various examples of Common Table Expressions (CTEs) for data analysis. It covers topics such as ranking salaries, categorizing salaries, counting employees per department, and retrieving recent hires and orders. Each CTE is designed to demonstrate specific SQL functionalities and analytical techniques relevant to employee and order data.

Uploaded by

jawarimandiofficial
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
27 views13 pages

Full SQL 100 Queries With Tables Colored

The document provides a comprehensive SQL syllabus that includes the creation of tables for employees, departments, and orders, along with various examples of Common Table Expressions (CTEs) for data analysis. It covers topics such as ranking salaries, categorizing salaries, counting employees per department, and retrieving recent hires and orders. Each CTE is designed to demonstrate specific SQL functionalities and analytical techniques relevant to employee and order data.

Uploaded by

jawarimandiofficial
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 13

Complete SQL Syllabus with Examples (Colored)

CREATE and INSERT Statements

-- TABLE CREATION

CREATE TABLE employees (


emp_id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
salary INT,
gender VARCHAR(10),
hire_date DATE
);

CREATE TABLE departments (


department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);

CREATE TABLE orders (


order_id INT PRIMARY KEY,
emp_id INT,
order_date DATE,
order_amount DECIMAL(10, 2)
);

-- DATA INSERTION

INSERT INTO departments VALUES


(101, 'Sales'),
(102, 'HR'),
(103, 'IT'),
(104, 'Finance');

INSERT INTO employees VALUES


(1, 'Alice', 101, 70000, 'Female', '2022-01-10'),
(2, 'Bob', 102, 65000, 'Male', '2021-07-22'),
(3, 'Charlie', 103, 60000, 'Male', '2020-03-15'),
(4, 'Diana', 104, 72000, 'Female', '2019-11-01'),
(5, 'Ethan', 101, 75000, 'Male', '2023-02-17'),
(6, 'Fiona', 103, 68000, 'Female', '2022-06-30');

INSERT INTO orders VALUES


(1001, 1, '2023-04-01', 500.00),
(1002, 1, '2023-04-05', 1200.00),
(1003, 2, '2023-04-07', 700.00),
(1004, 3, '2023-04-09', 950.00),
(1005, 4, '2023-04-11', 1100.00),
(1006, 5, '2023-04-13', 1300.00),
(1007, 5, '2023-04-14', 800.00),
Complete SQL Syllabus with Examples (Colored)

(1008, 6, '2023-04-15', 650.00);

1. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

2. CTE to find employees hired in the last 2 years

WITH recent_hires AS (SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '2
years') SELECT * FROM recent_hires;

3. CTE to find employees hired in the last 2 years

WITH recent_hires AS (SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '2
years') SELECT * FROM recent_hires;

4. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

5. CTE to find employees hired in the last 2 years

WITH recent_hires AS (SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '2
years') SELECT * FROM recent_hires;

6. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
e.department_id = d.department_id;

7. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

8. CTE to find employees who never placed any order

WITH ordered_emps AS (SELECT DISTINCT emp_id FROM orders) SELECT * FROM employees WHERE emp_id NOT
IN (SELECT emp_id FROM ordered_emps);

9. CTE to count employees per department and filter departments with more than 1 employee
Complete SQL Syllabus with Examples (Colored)

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

10. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

11. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

12. CTE to calculate average salary and list employees above it

WITH avg_salary_cte AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);

13. CTE to find employees who never placed any order

WITH ordered_emps AS (SELECT DISTINCT emp_id FROM orders) SELECT * FROM employees WHERE emp_id NOT
IN (SELECT emp_id FROM ordered_emps);

14. CTE to get total order amount per employee

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


emp_id) SELECT * FROM total_orders;

15. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

16. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

17. CTE to find employees hired in the last 2 years

WITH recent_hires AS (SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '2
years') SELECT * FROM recent_hires;

18. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
Complete SQL Syllabus with Examples (Colored)

AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

19. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

20. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

21. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

22. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

23. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

24. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

25. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

26. CTE to calculate average salary and list employees above it

WITH avg_salary_cte AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);

27. CTE to get total order amount per employee


Complete SQL Syllabus with Examples (Colored)

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


emp_id) SELECT * FROM total_orders;

28. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

29. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

30. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
e.department_id = d.department_id;

31. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

32. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

33. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

34. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
e.department_id = d.department_id;

35. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;
Complete SQL Syllabus with Examples (Colored)

36. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

37. CTE to find employees hired in the last 2 years

WITH recent_hires AS (SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '2
years') SELECT * FROM recent_hires;

38. CTE to get total order amount per employee

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


emp_id) SELECT * FROM total_orders;

39. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

40. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

41. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

42. CTE to calculate average salary and list employees above it

WITH avg_salary_cte AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);

43. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

44. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;
Complete SQL Syllabus with Examples (Colored)

45. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
e.department_id = d.department_id;

46. CTE to calculate average salary and list employees above it

WITH avg_salary_cte AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);

47. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

48. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

49. CTE to calculate average salary and list employees above it

WITH avg_salary_cte AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);

50. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

51. CTE to calculate average salary and list employees above it

WITH avg_salary_cte AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);

52. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

53. CTE to calculate average salary and list employees above it

WITH avg_salary_cte AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);
Complete SQL Syllabus with Examples (Colored)

54. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

55. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

56. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

57. CTE to get total order amount per employee

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


emp_id) SELECT * FROM total_orders;

58. CTE to calculate average salary and list employees above it

WITH avg_salary_cte AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);

59. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

60. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

61. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
e.department_id = d.department_id;

62. CTE to get total order amount per employee

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


Complete SQL Syllabus with Examples (Colored)

emp_id) SELECT * FROM total_orders;

63. CTE to find employees hired in the last 2 years

WITH recent_hires AS (SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '2
years') SELECT * FROM recent_hires;

64. CTE to get total order amount per employee

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


emp_id) SELECT * FROM total_orders;

65. CTE to find employees hired in the last 2 years

WITH recent_hires AS (SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '2
years') SELECT * FROM recent_hires;

66. CTE to get total order amount per employee

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


emp_id) SELECT * FROM total_orders;

67. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

68. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

69. CTE to find employees who never placed any order

WITH ordered_emps AS (SELECT DISTINCT emp_id FROM orders) SELECT * FROM employees WHERE emp_id NOT
IN (SELECT emp_id FROM ordered_emps);

70. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

71. CTE to find employees who never placed any order


Complete SQL Syllabus with Examples (Colored)

WITH ordered_emps AS (SELECT DISTINCT emp_id FROM orders) SELECT * FROM employees WHERE emp_id NOT
IN (SELECT emp_id FROM ordered_emps);

72. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

73. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

74. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

75. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
e.department_id = d.department_id;

76. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

77. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

78. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

79. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;
Complete SQL Syllabus with Examples (Colored)

80. CTE to get total order amount per employee

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


emp_id) SELECT * FROM total_orders;

81. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

82. CTE to find employees who never placed any order

WITH ordered_emps AS (SELECT DISTINCT emp_id FROM orders) SELECT * FROM employees WHERE emp_id NOT
IN (SELECT emp_id FROM ordered_emps);

83. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
e.department_id = d.department_id;

84. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

85. CTE to find employees hired in the last 2 years

WITH recent_hires AS (SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '2
years') SELECT * FROM recent_hires;

86. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

87. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

88. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;
Complete SQL Syllabus with Examples (Colored)

89. CTE to categorize salary into high, medium, low

WITH salary_category AS (SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary
BETWEEN 60000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees) SELECT * FROM
salary_category;

90. CTE to count employees per department and filter departments with more than 1 employee

WITH emp_counts AS (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY


department_id) SELECT * FROM emp_counts WHERE emp_count > 1;

91. CTE to rank orders by amount within each employee

WITH ranked_orders AS (SELECT *, RANK() OVER (PARTITION BY emp_id ORDER BY order_amount DESC) AS
rank_amount FROM orders) SELECT * FROM ranked_orders;

92. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

93. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

94. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
e.department_id = d.department_id;

95. CTE to retrieve latest order per employee using ROW_NUMBER()

WITH ranked_orders AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY order_date DESC)
AS rn FROM orders) SELECT * FROM ranked_orders WHERE rn = 1;

96. CTE to find department-wise salary rank of employees

WITH ranked_salaries AS (SELECT name, department_id, salary, RANK() OVER (PARTITION BY


department_id ORDER BY salary DESC) AS rank_in_dept FROM employees) SELECT * FROM ranked_salaries;

97. CTE to calculate department average and compare each employee's salary

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY


department_id) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON
Complete SQL Syllabus with Examples (Colored)

e.department_id = d.department_id;

98. CTE to get total order amount per employee

WITH total_orders AS (SELECT emp_id, SUM(order_amount) AS total_amount FROM orders GROUP BY


emp_id) SELECT * FROM total_orders;

99. CTE to find employees who never placed any order

WITH ordered_emps AS (SELECT DISTINCT emp_id FROM orders) SELECT * FROM employees WHERE emp_id NOT
IN (SELECT emp_id FROM ordered_emps);

100. CTE to find employees who never placed any order

WITH ordered_emps AS (SELECT DISTINCT emp_id FROM orders) SELECT * FROM employees WHERE emp_id NOT
IN (SELECT emp_id FROM ordered_emps);

You might also like