-
Notifications
You must be signed in to change notification settings - Fork 0
/
exp23.sql
125 lines (124 loc) · 4.63 KB
/
exp23.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
show databases;
use exp;
show tables;
select * from instructor;
+-------+------------+-----------+--------+
| id | name | dept_name | salary |
+-------+------------+-----------+--------+
| 10101 | srinivasan | comp.sci | 65000 |
| 10211 | smith | biology | 66000 |
| 10212 | tom | biology | NULL |
| 12121 | wu | finance | 90000 |
| 15151 | mozarat | music | 40000 |
| 22222 | einstein | physics | 95000 |
| 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 |
+-------+------------+-----------+--------+
14 rows in set (0.001 sec)
select * from teacher;
+-------+-----------+--------+----------+------+
| id | course_id | sec_id | semester | year |
+-------+-----------+--------+----------+------+
| 10101 | CS-101 | 1 | fall | 2017 |
| 10101 | CS-315 | 1 | spring | 2018 |
| 10101 | CS-347 | 1 | fall | 2017 |
| 12121 | FIN-201 | 1 | spring | 2018 |
| 15151 | MU-199 | 1 | spring | 2018 |
| 22222 | PHY-101 | 1 | fall | 2017 |
| 10101 | CS-101 | 1 | spring | 2018 |
| 32343 | HIS-351 | 1 | spring | 2018 |
| 45565 | CS-319 | 1 | spring | 2018 |
| 45565 | CS-319 | 1 | spring | 2017 |
| 76766 | BIO-101 | 1 | summer | 2018 |
| 76766 | BIO-301 | 1 | summer | 2017 |
| 83821 | CS-190 | 1 | spring | 2017 |
| 83821 | CS-190 | 2 | spring | 2017 |
| 83821 | CS-319 | 2 | spring | 2018 |
| 98345 | EE-181 | 1 | spring | 2017 |
+-------+-----------+--------+----------+------+
16 rows in set (0.000 sec)
-- 1 Order the tuples in the instructors relation as per their salary.
select * from instructor order by salary;
+-------+------------+-----------+--------+
| id | name | dept_name | salary |
+-------+------------+-----------+--------+
| 10212 | tom | biology | NULL |
| 15151 | mozarat | music | 40000 |
| 32343 | el said | history | 60000 |
| 58583 | califeri | history | 62000 |
| 10101 | srinivasan | comp.sci | 65000 |
| 10211 | smith | biology | 66000 |
| 76766 | crick | biology | 72000 |
| 45565 | katz | comp.sci | 75000 |
| 98345 | kim | elec.eng | 80000 |
| 76543 | singh | finance | 80000 |
| 33456 | gold | physics | 87000 |
| 12121 | wu | finance | 90000 |
| 83821 | brandt | comp.sci | 92000 |
| 22222 | einstein | physics | 95000 |
+-------+------------+-----------+--------+
14 rows in set (0.000 sec)
-- 2 Find courses that ran in Fall 2017 or in Spring 2018
select course_id from teacher where (semester = 'fall' and teaches_year = '2017') or (semester = 'spring' and teaches_year='2018');
+-----------+
| course_id |
+-----------+
| CS-101 |
| CS-315 |
| CS-347 |
| FIN-201 |
| MU-199 |
| PHY-101 |
| HIS-351 |
| CS-319 |
+-----------+
8 rows in set (0.000 sec)
-- 3 Find courses that ran in Fall 2017 and in Spring 2018
select course_id from teacher where (semester = 'fall' and teaches_year = '2017') and (semester = 'spring' and teaches_year='2018');
+-----------+
| course_id |
+-----------+
| CS-315 |
| FIN-201 |
| MU-199 |
| CS-101 |
| HIS-351 |
| CS-319 |
| CS-319 |
+-----------+
7 rows in set (0.000 sec)
-- 4 Find courses that ran in Fall 2017 but not in Spring 2018
select course_id from teacher where (semester = 'fall' and teaches_year = '2017') not in (semester = 'spring' and teaches_year='2018');
+-----------+
| course_id |
+-----------+
| CS-101 |
| CS-347 |
| PHY-101 |
+-----------+
3 rows in set (0.000 sec)
-- 5 Insert following additional tuples in instructor :('10211', 'Smith', 'Biology', 66000), ('10212', 'Tom', 'Biology', NULL )
INSERT INTO instructor (ID, name, dept_name, salary) VALUES ('10211','Smith', 'Biology', 66000),
('1022','Tom', 'Biology', NULL);
-- 6 Find all instructors whose salary is null.
select * from instructor where salary is null;
+-------+------+-----------+--------+
| id | name | dept_name | salary |
+-------+------+-----------+--------+
| 10212 | tom | biology | NULL |
+-------+------+-----------+--------+
1 row in set (0.000 sec)
-- 7 Find the average salary of instructors in the Computer Science department.
select avg(salary) from instructor where dept_name = 'Comp. Sci.';
+------------+
| avg_salary |
+------------+
| 77333.3333 |
+------------+
1 row in set (0.000 sec)