· 8 years ago · Dec 07, 2017, 05:18 AM
1Skip to content
2Help save net neutrality! A free, open internet is once again at stake—and we need your help.
3
4This repository
5Search
6Pull requests
7Issues
8Marketplace
9Explore
10 @gvikei
11 Sign out
120
131 33 gpdowning/cs373
14 Code Pull requests 0 Projects 0 Insights
15cs373/examples/Subqueries.sql
16b106537 27 days ago
17@gpdowning gpdowning another commit
18
19274 lines (237 sloc) 7.87 KB
20-- --------------
21-- Subqueries.sql
22-- --------------
23
24use test;
25
26-- -----------------------------------------------------------------------
27drop table if exists Student;
28drop table if exists Apply;
29drop table if exists College;
30
31-- -----------------------------------------------------------------------
32create table Student (
33 sID int,
34 sName text,
35 GPA float,
36 sizeHS int);
37
38create table Apply (
39 sID int,
40 cName text,
41 major text,
42 decision boolean);
43
44create table College (
45 cName text,
46 state char(2),
47 enrollment int);
48
49-- -----------------------------------------------------------------------
50insert into Student values (123, 'Amy', 3.9, 1000);
51insert into Student values (234, 'Bob', 3.6, 1500);
52insert into Student values (320, 'Lori', null, 2500);
53insert into Student values (345, 'Craig', 3.5, 500);
54insert into Student values (432, 'Kevin', null, 1500);
55insert into Student values (456, 'Doris', 3.9, 1000);
56insert into Student values (543, 'Craig', 3.4, 2000);
57insert into Student values (567, 'Edward', 2.9, 2000);
58insert into Student values (654, 'Amy', 3.9, 1000);
59insert into Student values (678, 'Fay', 3.8, 200);
60insert into Student values (765, 'Jay', 2.9, 1500);
61insert into Student values (789, 'Gary', 3.4, 800);
62insert into Student values (876, 'Irene', 3.9, 400);
63insert into Student values (987, 'Helen', 3.7, 800);
64
65insert into Apply values (123, 'Berkeley', 'CS', true);
66insert into Apply values (123, 'Cornell', 'EE', true);
67insert into Apply values (123, 'Stanford', 'CS', true);
68insert into Apply values (123, 'Stanford', 'EE', false);
69insert into Apply values (234, 'Berkeley', 'biology', false);
70insert into Apply values (321, 'MIT', 'history', false);
71insert into Apply values (321, 'MIT', 'psychology', true);
72insert into Apply values (345, 'Cornell', 'bioengineering', false);
73insert into Apply values (345, 'Cornell', 'CS', true);
74insert into Apply values (345, 'Cornell', 'EE', false);
75insert into Apply values (345, 'MIT', 'bioengineering', true);
76insert into Apply values (543, 'MIT', 'CS', false);
77insert into Apply values (678, 'Stanford', 'history', true);
78insert into Apply values (765, 'Cornell', 'history', false);
79insert into Apply values (765, 'Cornell', 'psychology', true);
80insert into Apply values (765, 'Stanford', 'history', true);
81insert into Apply values (876, 'MIT', 'biology', true);
82insert into Apply values (876, 'MIT', 'marine biology', false);
83insert into Apply values (876, 'Stanford', 'CS', false);
84insert into Apply values (987, 'Berkeley', 'CS', true);
85insert into Apply values (987, 'Stanford', 'CS', true);
86
87insert into College values ('Berkeley', 'CA', 36000);
88insert into College values ('Cornell', 'NY', 21000);
89insert into College values ('Irene', 'TX', 25000);
90insert into College values ('MIT', 'MA', 10000);
91insert into College values ('Stanford', 'CA', 15000);
92insert into College values ('UT', 'TX', 36000);
93
94-- -----------------------------------------------------------------------
95select * from Student;
96select * from Apply;
97select * from College;
98
99-- -----------------------------------------------------------------------
100-- ID, name, and GPA of students who applied in CS
101
102-- this is not right, why?
103select "*** #1a ***";
104select sID, sName, GPA
105 from Student
106 inner join Apply using (sID)
107 where major = 'CS';
108
109-- this is right
110select "*** #1b ***";
111select distinct sID, sName, GPA
112 from Student
113 inner join Apply using (sID)
114 where major = 'CS';
115
116-- this is also right, subquery, with in
117select "*** #1c ***";
118select sID, sName, GPA
119 from Student
120 where sID in
121 (select sID
122 from Apply
123 where major = 'CS');
124
125-- this is also right, subquery, with in and distinct
126select "*** #1d ***";
127select sID, sName, GPA
128 from Student
129 where sID in
130 (select distinct sID
131 from Apply
132 where major = 'CS');
133
134-- -----------------------------------------------------------------------
135-- GPA of students who applied in CS
136
137-- this is not right, why?
138select "*** #2a ***";
139select GPA
140 from Student
141 inner join Apply using (sID)
142 where major = 'CS'
143 order by GPA desc;
144
145-- this is still not right, why?
146select "*** #2b ***";
147select distinct GPA
148 from Student
149 inner join Apply using (sID)
150 where major = 'CS'
151 order by GPA desc;
152
153-- this is right, subquery, with in
154select "*** #2c ***";
155select GPA
156 from Student
157 where sID in
158 (select sID
159 from Apply
160 where major = 'CS')
161 order by GPA desc;
162
163-- this is right, subquery, with in and distinct
164select "*** #2d ***";
165select GPA
166 from Student
167 where sID in
168 (select distinct sID
169 from Apply
170 where major = 'CS')
171 order by GPA desc;
172
173-- -----------------------------------------------------------------------
174-- ID of students who have applied in CS but not in EE
175
176-- this is not right, why?
177select "*** #3a ***";
178select sID
179 from Student
180 where
181 sID in (select sID from Apply where major = 'CS')
182 and
183 sID in (select sID from Apply where major != 'EE');
184
185-- this is right, subquery, with in and not in
186select "*** #3b ***";
187select sID
188 from Student
189 where
190 sID in (select sID from Apply where major = 'CS')
191 and
192 sID not in (select sID from Apply where major = 'EE');
193
194-- this is also right, subquery, with in
195select "*** #3c ***";
196select distinct sID
197 from Apply
198 where
199 (major = 'CS')
200 and
201 sID not in (select sID from Apply where major = 'EE');
202
203-- -----------------------------------------------------------------------
204-- colleges with another college in the same state
205
206-- inner join
207select "*** #4a ***";
208select R.cName, R.state
209 from College as R
210 inner join College as S
211 where (R.cName != S.cName) and
212 (R.state = S.state);
213
214-- subquery, with exists
215select "*** #4b ***";
216select cName, state
217 from College as R
218 where exists
219 (select *
220 from College as S
221 where (R.cName != S.cName) and
222 (R.state = S.state));
223
224-- subquery, with group by and having
225select "*** #4c ***";
226select cName, state
227 from College
228 natural join
229 (select State
230 from College
231 group by State
232 having count(State) > 1) as T;
233
234-- -----------------------------------------------------------------------
235-- colleges with highest enrollment
236
237-- subquery, with not exists
238select "*** #5a ***";
239select cName, enrollment
240 from College as R
241 where not exists
242 (select *
243 from College as S
244 where R.enrollment < S.enrollment);
245
246-- subquery, with all
247select "*** #5b ***";
248select cName, enrollment
249 from College
250 where enrollment >= all
251 (select enrollment
252 from College);
253
254-- -----------------------------------------------------------------------
255-- students with highest GPA
256
257-- this is not right, why?
258select "*** #6a ***";
259select sID, sName, GPA
260 from Student as R
261 where not exists
262 (select *
263 from Student as S
264 where R.GPA < S.GPA);
265
266-- this is right
267select "*** #6b ***";
268select sID, sName, GPA
269 from Student as R
270 where
271 not exists
272 (select *
273 from Student as S
274 where R.GPA < S.GPA)
275 and
276 (GPA is not null);
277
278-- this is also right, subquery, with all
279select "*** #6c ***";
280select sID, sName, GPA
281 from Student
282 where GPA >= all
283 (select GPA
284 from Student
285 where GPA is not null);
286
287-- -----------------------------------------------------------------------
288drop table if exists Student;
289drop table if exists Apply;
290drop table if exists College;