-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathselectqueries.sql
121 lines (89 loc) · 3.36 KB
/
selectqueries.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
/**************************************************************
BASIC SELECT STATEMENTS
Works for SQLite, MySQL, Postgres
**************************************************************/
/**************************************************************
IDs, names, and GPAs of students with GPA > 3.6
**************************************************************/
select sID, sName, GPA
from Student
where GPA > 3.6;
/*** Same query without GPA ***/
select sID, sName
from Student
where GPA > 3.6;
/**************************************************************
Student names and majors for which they've applied
**************************************************************/
select sName, major
from Student, Apply
where Student.sID = Apply.sID;
/*** Same query with Distinct, note difference from algebra ***/
select distinct sName, major
from Student, Apply
where Student.sID = Apply.sID;
/**************************************************************
Names and GPAs of students with sizeHS < 1000 applying to
CS at Stanford, and the application decision
**************************************************************/
select sname, GPA, decision
from Student, Apply
where Student.sID = Apply.sID
and sizeHS < 1000 and major = 'CS' and cname = 'Stanford';
/**************************************************************
All large campuses with CS applicants
**************************************************************/
select cName
from College, Apply
where College.cName = Apply.cName
and enrollment > 20000 and major = 'CS';
/*** Fix error ***/
select College.cName
from College, Apply
where College.cName = Apply.cName
and enrollment > 20000 and major = 'CS';
/*** Add Distinct ***/
select distinct College.cName
from College, Apply
where College.cName = Apply.cName
and enrollment > 20000 and major = 'CS';
/**************************************************************
Application information
**************************************************************/
select Student.sID, sName, GPA, Apply.cName, enrollment
from Student, College, Apply
where Apply.sID = Student.sID and Apply.cName = College.cName;
/*** Sort by decreasing GPA ***/
select Student.sID, sName, GPA, Apply.cName, enrollment
from Student, College, Apply
where Apply.sID = Student.sID and Apply.cName = College.cName
order by GPA desc;
/*** Then by increasing enrollment ***/
select Student.sID, sName, GPA, Apply.cName, enrollment
from Student, College, Apply
where Apply.sID = Student.sID and Apply.cName = College.cName
order by GPA desc, enrollment;
/**************************************************************
Applicants to bio majors
**************************************************************/
select sID, major
from Apply
where major like '%bio%';
/*** Same query with Select * ***/
select *
from Apply
where major like '%bio%';
/**************************************************************
Select * cross-product
**************************************************************/
select *
from Student, College;
/**************************************************************
Add scaled GPA based on sizeHS
Also note missing Where clause
**************************************************************/
select sID, sName, GPA, sizeHS, GPA*(sizeHS/1000.0)
from Student;
/*** Rename result attribute ***/
select sID, sName, GPA, sizeHS, GPA*(sizeHS/1000.0) as scaledGPA
from Student;