· 9 years ago · Jan 19, 2017, 09:42 AM
1create database if not exists aggregation;
2use aggregation;
3
4drop table if exists College;
5drop table if exists Student;
6drop table if exists Apply;
7
8CREATE TABLE College (
9 cName VARCHAR(255) PRIMARY KEY,
10 state VARCHAR(255),
11 enrollment INT
12);
13CREATE TABLE Student (
14 sID INT PRIMARY KEY,
15 sName VARCHAR(255),
16 GPA DOUBLE,
17 sizeHS INT
18);
19CREATE TABLE Apply (
20 sID INT PRIMARY KEY,
21 cName VARCHAR(255),
22 major VARCHAR(255),
23 decision TEXT
24);
25
26
27insert into Student values (123, 'Amy', 3.9, 1000), (234, 'Bob', 3.6, 1500), (345, 'Craig', 3.5, 500),
28(456, 'Doris', 3.9, 1000), (567, 'Edward', 2.9, 2000), (678, 'Fay', 3.8, 200), (789, 'Gary', 3.4, 800),
29(987, 'Helen', 3.7, 800), (876, 'Irene', 3.9, 400), (765, 'Jay', 2.9, 1500), (654, 'Amy', 3.9, 1000),
30(543, 'Craig', 3.4, 2000);
31
32insert into College values ('Stanford', 'CA', 15000), ('Berkeley', 'CA', 36000), ('MIT', 'MA', 10000), ('Cornell', 'NY', 21000);
33
34insert into Apply values (123, 'Stanford', 'CS', 'Y'), (124, 'Stanford', 'EE', 'N'), (125, 'Berkeley', 'CS', 'Y'),
35(126, 'Cornell', 'EE', 'Y'), (234, 'Berkeley', 'biology', 'N'), (345, 'MIT', 'bioengineering', 'Y'), (346, 'Cornell', 'bioengineering', 'N'),
36(347, 'Cornell', 'CS', 'Y'), (348, 'Cornell', 'EE', 'N'), (678, 'Stanford', 'history', 'Y'), (987, 'Stanford', 'CS', 'Y'), (988, 'Berkeley', 'CS', 'Y'),
37(876, 'Stanford', 'CS', 'N'), (877, 'MIT', 'biology', 'Y'), (878, 'MIT', 'marine biology', 'N'), (765, 'Stanford', 'history', 'Y'),
38(766, 'Cornell', 'history', 'N'), (767, 'Cornell', 'psychology', 'Y'), (543, 'MIT', 'CS', 'N');
39
40insert into College values ('UGR', 'And', 50000);
41insert into Student values (712, 'John', 3.2, 2000), (713, 'Anne', 3.2, 2500);
42
43
44
45
46
47/**
48 La media de GPA de todos los estudiantes
49**/
50
51SELECT
52 AVG(GPA)
53FROM
54 Student;
55
56/**
57 El GPA mas bajo de todos los estudiantes donde major es CS
58**/
59
60SELECT
61 AVG(GPA)
62FROM
63 Student
64WHERE
65 sID IN (SELECT
66 sID
67 FROM
68 Apply
69 WHERE
70 major = 'CS');
71
72/**
73 Universidades con mas de 15000 alumnos
74**/
75
76SELECT
77 COUNT(*)
78FROM
79 College
80WHERE
81 enrollment > 15000;
82
83/**
84 Numero de estudiantes matriculados en Cornell
85**/
86
87SELECT
88 COUNT(DISTINCT sID)
89FROM
90 Apply
91WHERE
92 cName = 'Cornell';
93
94/**
95 Estudiantes con el mismo conteo de GPA y sizeHS
96**/
97
98SELECT
99 *
100FROM
101 Student S1
102WHERE
103 (SELECT
104 COUNT(*)
105 FROM
106 Student S2
107 WHERE
108 S2.sID <> S1.sID AND S2.GPA = S1.GPA) = (SELECT
109 COUNT(*)
110 FROM
111 Student S2
112 WHERE
113 S2.sID <> S1.sID
114 AND S2.sizeHS = S1.sizeHS);
115
116/**
117 Cantidad de GPA de alumnos que se matriculan a CS que excede a los
118 que no lo hace.
119**/
120
121SELECT DISTINCT
122 (SELECT
123 AVG(GPA) AS avgGPA
124 FROM
125 Student
126 WHERE
127 sID IN (SELECT
128 sID
129 FROM
130 Apply
131 WHERE
132 major = 'CS')) - (SELECT
133 AVG(GPA) AS avgGPA
134 FROM
135 Student
136 WHERE
137 sID NOT IN (SELECT
138 sID
139 FROM
140 Apply
141 WHERE
142 major = 'CS')) AS d
143FROM
144 Student;
145
146/**
147 Numero de matriculas de cada universidad
148**/
149
150SELECT
151 cName, COUNT(*)
152FROM
153 Apply
154GROUP BY cName;
155
156/**
157 Matriculaciones a universidades por estado
158**/
159
160SELECT
161 state, SUM(enrollment)
162FROM
163 College
164GROUP BY state;
165
166/**
167 Minimo y maximo de las GPA de las matriculas Major
168**/
169
170SELECT
171 cName, major, MIN(GPA), MAX(GPA)
172FROM
173 Student,
174 Apply
175WHERE
176 Student.sID = Apply.sID
177GROUP BY cName , major;
178
179
180/**
181 Numero de matriculas de cada estudiante
182**/
183
184SELECT
185 Student.sID, COUNT(DISTINCT cName)
186FROM
187 Student,
188 Apply
189WHERE
190 Student.sID = Apply.sID
191GROUP BY Student.sID;
192
193/**
194 Numero de matriculas de cada estudiante incluyendo 0 a aquellos
195 que no han solicitado ninguna.
196**/
197
198SELECT
199 Student.sID, COUNT(DISTINCT cName)
200FROM
201 Student,
202 Apply
203WHERE
204 Student.sID = Apply.sID
205GROUP BY Student.sID
206UNION SELECT
207 sID, 0
208FROM
209 Student
210WHERE
211 sID NOT IN (SELECT
212 sID
213 FROM
214 Apply);
215
216/**
217 Universidades con menos de 5 matriculas
218**/
219
220SELECT
221 cName
222FROM
223 Apply
224GROUP BY cName
225HAVING COUNT(DISTINCT sID) < 5;
226
227/**
228 Majors cuyas matriculas estan por debajo de la media
229**/
230
231SELECT
232 major
233FROM
234 Student,
235 Apply
236WHERE
237 Student.sID = Apply.sID
238GROUP BY major
239HAVING MAX(GPA) < (SELECT
240 AVG(GPA)
241 FROM
242 Student);
243
244/**
245 Mostrar los alumnos y la universidad a la que han mandado una solicitud
246**/
247SELECT
248 student.*, apply.cName
249FROM
250 student
251 LEFT JOIN
252 apply ON (student.sID = apply.sID);
253
254/**
255 Nombre de las universidades que no han sido solicitadas por ningún alumno
256 Sin y Con Join
257**/
258SELECT
259 college.cName
260FROM
261 college
262WHERE
263 college.cName NOT IN (SELECT
264 apply.cName
265 FROM
266 apply);
267
268SELECT
269 college.cName
270FROM
271 college
272 LEFT JOIN
273 apply ON (college.cName = apply.cName)
274WHERE
275 apply.cName IS NULL;