Repository navigation
Expand file tree
/
Copy pathAlekhya_Nallala_SQL_CTE_Assignment.py
More file actions
129 lines (101 loc) · 2.55 KB
/
Copy pathAlekhya_Nallala_SQL_CTE_Assignment.py
File metadata and controls
129 lines (101 loc) · 2.55 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
Assignment:
Assuming the table is named tfi and it has these columns:
• emp_name
• dept
• salary
Create a CTE to find the average salary of each department.
WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_salary
FROM tfi
GROUP BY dept
)
SELECT *
FROM dept_avg;
Create a CTE to find the maximum salary of each department.
WITH dept_max AS (
SELECT dept, MAX(salary) AS max_salary
FROM tfi
GROUP BY dept
)
SELECT *
FROM dept_max;
-- Create a CTE that calculates the average salary for each department. Then display only departments
-- whose average salary is greater than 70000
WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_salary
FROM tfi
GROUP BY dept
)
SELECT *
FROM dept_avg
WHERE avg_salary > 70000;
-- Using a CTE, find departments having more than 5 employees.
WITH dept_emp_count AS (
SELECT dept, COUNT(*) AS employee_count
FROM tfi
GROUP BY dept
)
SELECT *
FROM dept_emp_count
WHERE employee_count > 5;
-- Create a CTE containing the total salary paid by each department. Display the department with
-- a total salary greater than 500000
WITH dept_total_salary AS (
SELECT dept, SUM(salary) AS total_salary
FROM tfi
GROUP BY dept
)
SELECT *
FROM dept_total_salary
WHERE total_salary > 500000;
-- Using a CTE, find the highest salary in each department.
WITH dept_highest_salary AS (
SELECT dept, MAX(salary) AS highest_salary
FROM tfi
GROUP BY dept
)
SELECT *
FROM dept_highest_salary;
-- Create a CTE containing the average salary of each department. Then join it with tfi and display:
-- emp_name, dept, salary, avg_salary
WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_salary
FROM tfi
GROUP BY dept
)
SELECT
t.emp_name,
t.dept,
t.salary,
d.avg_salary
FROM tfi t
JOIN dept_avg d
ON t.dept = d.dept;
-- Find only the employees whose salary is greater than their department's average salary.
WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_salary
FROM tfi
GROUP BY dept
)
SELECT
t.emp_name,
t.dept,
t.salary
FROM tfi t
JOIN dept_avg d
ON t.dept = d.dept
WHERE t.salary > d.avg_salary;
-- Using a CTE, find employees whose salary is LESS than their department's average salary
WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_salary
FROM tfi
GROUP BY dept
)
SELECT
t.emp_name,
t.dept,
t.salary
FROM tfi t
JOIN dept_avg d
ON t.dept = d.dept
WHERE t.salary < d.avg_salary;