-
Notifications
You must be signed in to change notification settings - Fork 0
/
18 Queries with Solutions.sql
287 lines (253 loc) · 12.5 KB
/
18 Queries with Solutions.sql
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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
--######################################################################--
--# #--
--# SQL Queries for Company Database [28.APR.2020] #--
--# Author: Paul Panaitescu #--
--# #--
--######################################################################--
--|---------------------------|--
--| List of Queries |--
--|---------------------------|--
-- 1: Retrieve the name and address of all employees who work for the "Research" department
-- 2: Retrieve the name of each employee who has a dependent with the same gender as the employee
-- 3: Retrieve the names of employees who have no dependents
-- 4: Retrieve the names of all employees who are managers and who have at least one dependent
-- 5: Retrieve the Social Security numbers of all employees who work on project numbers 1, 2, or 3
-- 6: Retrieve the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary
-- 7: Retrieve the sum of the salaries of all employees of the "Research" department,
-- as well as the maximum salary, the minimum salary, and the average salary in this department
-- 8: Retrieve the total number of employees in the company
-- 9: Retrieve the total number of employees in the "Research" department
-- 10: Retrieve the number of distinct salary values in the database
-- 11: Retrieve the names of all employees who have two or more dependents
-- 12: Retrieve for each department, the number of employees it has, and their average salary according to their gender
-- 13: Retrieve for each project, its number, its name, and the number of employees who work on that project
-- 14: Retrieve for each department, its department number, the combined salary in that department, and the number of employees who work in that department
-- 15: Retrieve for each project on which more than two employees work,
-- the project number, the project name, and the number of employees who work on the project
-- 16: Retrieve for each project, the project number, the project name,
-- and the number of employees from the 'administration' who work on the project
-- 17: Retrive the total number of employees whose salaries exceed $32000 in each department which more than two employees
-- 18: Create a view that contains the info about a project name, number of employees working on it, and total salary of them
--|--------------------------------|--
--| Solutions for Queries |--
--|--------------------------------|--
----------------------------------------------------------------------------------------------------------------
-- 1: Retrieve the name and address of all employees who work for the "Research" department
----------------------------------------------------------------------------------------------------------------
-- 1.1.
USE company;
SELECT Fname, Lname, Address
FROM employee
WHERE Dno IN (SELECT Dnumber
FROM department
WHERE Dname = "Research" );
-- 1.2.
USE company;
SELECT Fname, Lname, Address
FROM employee, department
WHERE Dname = 'Research' AND Dnumber = Dno;
----------------------------------------------------------------------------------------------------------------
-- 2: Retrieve the name of each employee who has a dependent with the same gender as the employee
----------------------------------------------------------------------------------------------------------------
-- 2.1.
USE company;
SELECT Fname, Lname
FROM employee e
WHERE Ssn IN (SELECT Essn
FROM dependent d
WHERE e.Sex = d.Sex );
-- 2.2.
SELECT e.Fname, e.Lname
FROM employee AS e, dependent AS d
WHERE e.Ssn = d.Essn AND e.Sex = d.Sex;
----------------------------------------------------------------------------------------------------------------
-- 3: Retrieve the names of employees who have no dependents
----------------------------------------------------------------------------------------------------------------
-- 3.
USE company;
SELECT Fname, Lname
FROM employee e
WHERE Ssn NOT IN (SELECT Essn
FROM dependent d )
----------------------------------------------------------------------------------------------------------------
-- 4: Retrieve the names of all employees who are managers and who have at least one dependent
----------------------------------------------------------------------------------------------------------------
-- 4.
USE company;
SELECT Fname, Lname
FROM employee e
WHERE Ssn IN (SELECT Mgr_ssn
FROM department d1, dependent d2
WHERE d1.Mgr_ssn = d2.Essn );
----------------------------------------------------------------------------------------------------------------
-- 5: Retrieve the Social Security numbers of all employees who work on project numbers 1, 2, or 3
----------------------------------------------------------------------------------------------------------------
-- 5.1.
USE company;
SELECT DISTINCT Essn
FROM works_on
WHERE Pno IN (1,2,3);
-- 5.2.
USE company;
SELECT Ssn
FROM employee e
WHERE Ssn IN (SELECT Essn
FROM works_on w
WHERE w.Pno IN (SELECT Pnumber
FROM project p
WHERE p.Pnumber = '1' OR p.Pnumber = '2' OR p.Pnumber = '3'));
----------------------------------------------------------------------------------------------------------------
-- 6: Retrieve the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary
----------------------------------------------------------------------------------------------------------------
-- 6.1.
USE company;
SELECT SUM(e.salary) AS Sum_of_salaries,
MAX(e.salary) AS Max_of_salaries,
MIN(e.salary) AS Min_of_salaries,
AVG(e.salary) AS Avg_of_salaries
FROM employee e;
-- 6.2.
USE company;
SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM employee;
----------------------------------------------------------------------------------------------------------------
-- 7: Retrieve the sum of the salaries of all employees of the "Research" department,
-- as well as the maximum salary, the minimum salary, and the average salary in this department
----------------------------------------------------------------------------------------------------------------
-- 7.1.
USE company;
SELECT SUM(e.salary) AS Sum_of_salaries,
MAX(e.salary) AS Max_of_salaries,
MIN(e.salary) AS Min_of_salaries,
AVG(e.salary) AS Avg_of_salaries
FROM employee e
WHERE Dno IN (SELECT Dnumber
FROM department
WHERE Dname = "Research" );
-- 7.2.
USE company;
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO = DNUMBER AND DNAME = 'Research';
----------------------------------------------------------------------------------------------------------------
-- 8: Retrieve the total number of employees in the company
----------------------------------------------------------------------------------------------------------------
-- 8.1.
USE company;
SELECT COUNT(e.Ssn) AS Total_number_of_employees
FROM employee e
-- 8.2.
SELECT COUNT(*)
FROM employee;
----------------------------------------------------------------------------------------------------------------
-- 9: Retrieve the total number of employees in the "Research" department
----------------------------------------------------------------------------------------------------------------
-- 9.1.
USE company;
SELECT COUNT(e.Ssn) AS Total_number_of_employees
FROM employee e
WHERE Dno IN (SELECT Dnumber
FROM department
WHERE Dname = "Research" );
-- 9.2.
USE company;
SELECT COUNT(*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO = DNUMBER AND DNAME = 'Research';
----------------------------------------------------------------------------------------------------------------
-- 10: Retrieve the number of distinct salary values in the database
----------------------------------------------------------------------------------------------------------------
-- 10.
USE company;
SELECT COUNT(DISTINCT e.salary)
FROM employee e
----------------------------------------------------------------------------------------------------------------
-- 11: Retrieve the names of all employees who have two or more dependents
----------------------------------------------------------------------------------------------------------------
-- 11.1.
SELECT Fname, Lname
FROM employee e
WHERE (SELECT COUNT(*)
FROM dependent d
WHERE e.Ssn = d.Essn) >= 2;
-- 11.2.
USE company;
SELECT Fname, Lname
FROM employee e
WHERE e.Ssn IN (SELECT d.Essn
FROM dependent d
GROUP BY d.Essn
HAVING COUNT(*) >= 2 );
----------------------------------------------------------------------------------------------------------------
-- 12: Retrieve for each department, the number of employees it has, and their average salary according to their gender
----------------------------------------------------------------------------------------------------------------
-- 12.
USE company;
SELECT e.Dno AS Dep_number,
COUNT(e.Ssn) AS Number_of_employees,
AVG (e.Salary) AS Average_salary,
e.Sex AS Gender
FROM Employee AS e
GROUP BY e.Dno, e.Sex;
----------------------------------------------------------------------------------------------------------------
-- 13: Retrieve for each project, its number, its name, and the number of employees who work on that project
----------------------------------------------------------------------------------------------------------------
-- 13.
USE company;
SELECT Pnumber, Pname, COUNT(Pno) AS Number_of_employees
FROM Project, Works_on
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname;
----------------------------------------------------------------------------------------------------------------
-- 14: Retrieve for each department, its department number, the combined salary in that department, and the number of employees who work in that department
----------------------------------------------------------------------------------------------------------------
-- 14.
USE company;
SELECT Dno, SUM(Salary), COUNT(*) AS Number_of_employees
FROM Employee
GROUP BY Dno;
----------------------------------------------------------------------------------------------------------------
-- 15: Retrieve for each project on which more than two employees work,
--the project number, the project name, and the number of employees who work on the project
----------------------------------------------------------------------------------------------------------------
-- 15.
USE company;
SELECT Pnumber, Pname, COUNT(*) AS Number_of_employees
FROM Project, Works_on
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname
HAVING COUNT(*) > 2;
----------------------------------------------------------------------------------------------------------------
-- 16: Retrieve for each project, the project number, the project name,
--and the number of employees from the 'administration' who work on the project
----------------------------------------------------------------------------------------------------------------
-- 16.
USE company;
SELECT Pnumber, Pname, COUNT(*) AS Number_of_employees
FROM Project, Works_on, Department
WHERE Pnumber = Pno AND Dnumber = Dnum AND Dname = 'Administration'
GROUP BY Pnumber, Pname
----------------------------------------------------------------------------------------------------------------
-- 17: Retrieve the total number of employees whose salaries exceed $32000 in each department which more than two employees
----------------------------------------------------------------------------------------------------------------
-- 17.
USE company;
SELECT Dnumber, COUNT(*) AS Number_of_employees
FROM Department, Employee
WHERE Dnumber = Dno AND Salary > 32000 AND Dno IN (SELECT Dno
FROM Employee
GROUP BY Dno
HAVING COUNT(*) > 2)
GROUP BY Dnumber;
----------------------------------------------------------------------------------------------------------------
-- 18: Create a view that contains the info about a project name, number of employees working on it, and total salary of them
----------------------------------------------------------------------------------------------------------------
-- 18.
USE company;
-- DROP VIEW Project_info;
CREATE VIEW Project_info
AS SELECT Pname, COUNT(*) AS Number_of_employees, SUM(Salary)
FROM Project, Works_on, Employee
WHERE Pnumber = Pno AND Ssn = Essn
GROUP BY Pname;
----------------------------------------------------------------------------------------------------------------