-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQLModifications.sql
152 lines (114 loc) · 4.12 KB
/
SQLModifications.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
/**************************************************************
INSERT, DELETE, AND UPDATE STATEMENTS
Works for Postgres
SQLite doesn't support All
MySQL doesn't allow deletes from Apply with subquery on
Apply (then College deletion behaves differently)
**************************************************************/
/**************************************************************
Insert new college
**************************************************************/
insert into College values ('Carnegie Mellon', 'PA', 11500);
/**************************************************************
Have all students who didn't apply anywhere apply to
CS at Carnegie Mellon
**************************************************************/
/*** First see who will be inserted ***/
select *
from Student
where sID not in (select sID from Apply);
/*** Then insert them ***/
insert into Apply
select sID, 'Carnegie Mellon', 'CS', null
from Student
where sID not in (select sID from Apply);
/*** Admit to Carnegie Mellon EE all students who were turned down
in EE elsewhere ***/
/*** First see who will be inserted ***/
select *
from Student
where sID in (select sID from Apply
where major = 'EE' and decision = 'N');
/*** Then insert them ***/
insert into Apply
select sID, 'Carnegie Mellon', 'EE', 'Y'
from Student
where sID in (select sID from Apply
where major = 'EE' and decision = 'N');
/**************************************************************
Delete all students who applied to more than two different
majors
**************************************************************/
/*** First see who will be deleted ***/
select sID, count(distinct major)
from Apply
group by sID
having count(distinct major) > 2;
/*** Then delete them ***/
delete from Student
where sID in
(select sID
from Apply
group by sID
having count(distinct major) > 2);
/*** Delete same ones from Apply ***/
/*** NOTE SOME SYSTEMS DISALLOW ***/
delete from Apply
where sID in
(select sID
from Apply
group by sID
having count(distinct major) > 2);
/**************************************************************
Delete colleges with no CS applicants
**************************************************************/
/*** First see who will be deleted ***/
select * from College
where cName not in (select cName from Apply where major = 'CS');
/*** Then delete them ***/
delete from College
where cName not in (select cName from Apply where major = 'CS');
/**************************************************************
Accept applicants to Carnegie Mellon with GPA < 3.6 but turn
them into economics majors
**************************************************************/
/*** First see who will be updated ***/
select * from Apply
where cName = 'Carnegie Mellon'
and sID in (select sID from Student where GPA < 3.6);
/*** Then update them ***/
update Apply
set decision = 'Y', major = 'economics'
where cName = 'Carnegie Mellon'
and sID in (select sID from Student where GPA < 3.6);
/**************************************************************
Turn the highest-GPA EE applicant into a CSE applicant
**************************************************************/
/*** First see who will be updated ***/
select * from Apply
where major = 'EE'
and sID in
(select sID from Student
where GPA >= all
(select GPA from Student
where sID in (select sID from Apply where major = 'EE')));
/*** Then update them ***/
update Apply
set major = 'CSE'
where major = 'EE'
and sID in
(select sID from Student
where GPA >= all
(select GPA from Student
where sID in (select sID from Apply where major = 'EE')));
/**************************************************************
Give everyone the highest GPA and smallest HS
**************************************************************/
update Student
set GPA = (select max(GPA) from Student),
sizeHS = (select min(sizeHS) from Student);
/**************************************************************
Accept everyone
**************************************************************/
update Apply
set decision = 'Y';