-
Notifications
You must be signed in to change notification settings - Fork 0
/
exp3.sql
105 lines (105 loc) · 3.82 KB
/
exp3.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
-- 1. Find the total number of instructors who teach a course in the Spring 2018 semester.
SELECT COUNT(id) FROM teacher WHERE teaches_year=2018;
+--------------------+
| count(distinct id) |
+--------------------+
| 6 |
+--------------------+
1 row in set (0.000 sec)
-- 2. Find the number of tuples in the teaches relation
SELECT COUNT(*) FROM teacher;
+----------+
| count(*) |
+----------+
| 16 |
+----------+
1 row in set (0.000 sec)
-- 3. Find the average salary of instructors in each department
SELECT dept_name, AVG(salary) FROM instructor GROUP BY dept_name;
+-----------+-------------+
| dept_name | avg(salary) |
+-----------+-------------+
| biology | 69000.0000 |
| comp.sci | 77333.3333 |
| elec.eng | 80000.0000 |
| finance | 85000.0000 |
| history | 61000.0000 |
| music | 40000.0000 |
| physics | 91000.0000 |
+-----------+-------------+
7 rows in set (0.000 sec)
-- 4. Find the names and average salaries of all departments whose average salary is greater than 42000
select dept_name, avg(salary) from instructor group by dept_name having avg(salary)>42000;
+-----------+-------------+
| dept_name | avg(salary) |
+-----------+-------------+
| biology | 69000.0000 |
| comp.sci | 77333.3333 |
| elec.eng | 80000.0000 |
| finance | 85000.0000 |
| history | 61000.0000 |
| physics | 91000.0000 |
+-----------+-------------+
6 rows in set (0.000 sec)
-- 5. Name all instructors whose name is neither “Mozart” nor Einstein”.
SELECT name FROM instructor WHERE name NOT IN ('Mozart', 'Einstein');
+-------+------------+-----------+--------+
| id | name | dept_name | salary |
+-------+------------+-----------+--------+
| 10101 | srinivasan | comp.sci | 65000 |
| 10211 | smith | biology | 66000 |
| 10212 | tom | biology | NULL |
| 12121 | wu | finance | 90000 |
| 32343 | el said | history | 60000 |
| 33456 | gold | physics | 87000 |
| 45565 | katz | comp.sci | 75000 |
| 58583 | califeri | history | 62000 |
| 76543 | singh | finance | 80000 |
| 76766 | crick | biology | 72000 |
| 83821 | brandt | comp.sci | 92000 |
| 98345 | kim | elec.eng | 80000 |
+-------+------------+-----------+--------+
12 rows in set (0.000 sec)
-- 6. Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.
select name from instructor where salary>any (select salary from instructor where dept_name = 'Biology');
+-------+----------+-----------+--------+
| id | name | dept_name | salary |
+-------+----------+-----------+--------+
| 12121 | wu | finance | 90000 |
| 22222 | einstein | physics | 95000 |
| 33456 | gold | physics | 87000 |
| 45565 | katz | comp.sci | 75000 |
| 76543 | singh | finance | 80000 |
| 76766 | crick | biology | 72000 |
| 83821 | brandt | comp.sci | 92000 |
| 98345 | kim | elec.eng | 80000 |
+-------+----------+-----------+--------+
8 rows in set (0.000 sec)
-- 7. Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.
SELECT name FROM instructor WHERE salary > ALL (SELECT salary FROM instructor WHERE dept_name = 'Music');
'Srinivasan'
'Surae'
'Smith'
'Wu'
'Einstein'
'El Said'
'Gold'
'Katz'
'Califieri'
'Singh'
'Crick'
'Brandt'
'Kim'
-- 8. Find the average instructors’ salaries of those departments where the average salary is greater than 42,000.
select avg(salary) from instructor group by dept_name having avg(salary)>42000;
+-----------+-------------+
| dept_name | avg(salary) |
+-----------+-------------+
| biology | 69000.0000 |
| comp.sci | 77333.3333 |
| elec.eng | 80000.0000 |
| finance | 85000.0000 |
| history | 61000.0000 |
| physics | 91000.0000 |
+-----------+-------------+
6 rows in set (0.000 sec)