-
Notifications
You must be signed in to change notification settings - Fork 1
/
proj1.sql
219 lines (189 loc) · 5.94 KB
/
proj1.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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
-- Before running drop any existing views
DROP VIEW IF EXISTS q0;
DROP VIEW IF EXISTS q1i;
DROP VIEW IF EXISTS q1ii;
DROP VIEW IF EXISTS q1iii;
DROP VIEW IF EXISTS q1iv;
DROP VIEW IF EXISTS q2i;
DROP VIEW IF EXISTS q2ii;
DROP VIEW IF EXISTS q2iii;
DROP VIEW IF EXISTS q3i;
DROP VIEW IF EXISTS q3ii;
DROP VIEW IF EXISTS q3iii;
DROP VIEW IF EXISTS q4i;
DROP VIEW IF EXISTS q4ii;
DROP VIEW IF EXISTS q4iii;
DROP VIEW IF EXISTS q4iv;
DROP VIEW IF EXISTS q4v;
-- Question 0
CREATE VIEW q0(era)
AS
SELECT MAX(era)
from pitching
;
-- Question 1i
CREATE VIEW q1i(namefirst, namelast, birthyear)
AS
SELECT namefirst, namelast, birthyear -- replace this line
from people
where weight > 300
;
-- Question 1ii
CREATE VIEW q1ii(namefirst, namelast, birthyear)
AS
SELECT namefirst, namelast, birthyear -- replace this line
from people
where namefirst like '% %'
order by namefirst asc, namelast asc
;
-- Question 1iii
CREATE VIEW q1iii(birthyear, avgheight, count)
AS
SELECT birthyear, avg(height), count(*) -- replace this line
from people
group by birthyear
order by birthyear asc
;
-- Question 1iv
CREATE VIEW q1iv(birthyear, avgheight, count)
AS
SELECT birthyear, avgheight, count
from q1iii
where avgheight > 70
order by birthyear asc
;
-- Question 2i
CREATE VIEW q2i(namefirst, namelast, playerid, yearid)
AS
SELECT namefirst, namelast, people.playerid, yearid -- replace this line
from people, HallOfFame
where people.playerid = HallOfFame.playerid and inducted = 'Y'
order by yearid desc, people.playerid asc
;
-- Question 2ii
CREATE VIEW q2ii(namefirst, namelast, playerid, schoolid, yearid)
AS
SELECT namefirst, namelast, q2i.playerid, collegeplaying.schoolid, q2i.yearid -- replace this line
from q2i, schools, collegeplaying
where q2i.playerid = collegeplaying.playerid and
collegeplaying.schoolid = schools.schoolid and
schools.state = 'CA'
order by q2i.yearid desc, collegeplaying.schoolid asc, q2i.playerid asc
;
-- Question 2iii
CREATE VIEW q2iii(playerid, namefirst, namelast, schoolid)
AS
SELECT q2i.playerid, namefirst, namelast, collegeplaying.schoolid -- replace this line
from q2i
left join collegeplaying
on q2i.playerid = collegeplaying.playerid
order by q2i.playerid desc, collegeplaying.schoolid asc
;
-- Question 3i
CREATE VIEW q3i(playerid, namefirst, namelast, yearid, slg)
AS
select people.playerid, namefirst, namelast, yearid,
1.0*(batting.H+batting.H2B+batting.H3B*2+batting.HR*3)/batting.AB as slg
from people, batting
-- replace this line
where people.playerid = batting.playerid and batting.AB>50
order by slg desc, yearid asc, people.playerid asc
limit 10
;
-- Question 3ii
CREATE VIEW q3ii(playerid, namefirst, namelast, lslg)
AS
with x as(select playerid, sum(H) as H, sum(H2B) as H2B, sum(H3B) as H3B, sum(HR) as HR, sum(AB) as AB
FROM Batting
group by playerid)
SELECT people.playerid, namefirst, namelast,
1.0*(x.H+x.H2B+x.H3B*2+x.HR*3)/x.AB as lslg
from people, x
where people.playerid = x.playerid and x.AB > 50
order by lslg desc, people.playerid asc
limit 10
-- replace this line
;
-- Question 3iii
CREATE VIEW q3iii(namefirst, namelast, lslg)
AS
with x as(select playerid, sum(H) as H, sum(H2B) as H2B, sum(H3B) as H3B, sum(HR) as HR, sum(AB) as AB
FROM Batting
group by playerid),
y as (select playerid, AB, 1.0*(H+H2B+H3B*2+HR*3)/AB as lslg
from x),
z as(select lslg
from y
where playerid = "mayswi01")
SELECT namefirst, namelast, y.lslg -- replace this line
from people, y, z
where people.playerid = y.playerid and y.AB>50 and y.lslg > z.lslg
;
-- Question 4i
CREATE VIEW q4i(yearid, min, max, avg)
AS
SELECT yearid, min(salary), max(salary), avg(salary) -- replace this line
from salaries
group by yearid
;
-- Question 4ii
CREATE VIEW q4ii(binid, low, high, count)
AS
with x as (select min, max from q4i where yearid=2016),
y as (select * from salaries where yearid=2016),
binid as (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)),
z as (select column1 as c, min+1.0/10.0*column1*(max-min) as low, min+1.0/10.0*(column1+1)*(max-min) as high
from x, binid),
p as (select column1 as c, salary
from x, binid, y
where ((salary>=min+1.0/10.0*column1*(max-min) and salary<min+1.0/10.0*(column1+1)*(max-min)) or (c=9 and salary=max) ))
select z.c, low, high, count(p.c)
from z
left outer join p
on z.c = p.c
group by z.c
;
-- Question 4iii
CREATE VIEW q4iii(yearid, mindiff, maxdiff, avgdiff)
AS
-- with x as (select * from salaries order by yearid asc),
with x as (SELECT yearid,
min-lag(min) over (order by yearid),
max-lag(max) over (order by yearid),
avg-lag(avg) over (order by yearid)
from q4i)-- replace this line
select * from x where yearid!=1985
;
-- Question 4iv
CREATE VIEW q4iv(playerid, namefirst, namelast, salary, yearid)
AS
with x as (SELECT people.playerid as id, namefirst, namelast, salary, yearid -- replace this line
from people, salaries
where not EXISTS
(select salary from salaries as s2 where s2.yearid=2000 and s2.salary>salaries.salary)
and yearid = 2000
and people.playerid = salaries.playerid),
y as (select people.playerid as id, namefirst, namelast, salary, yearid
from people, salaries
where not exists
(select salary from salaries as s2 where s2.yearid=2001 and s2.salary>salaries.salary)
and yearid = 2001
and people.playerid = salaries.playerid
)
select * from x
union
select * from y
order by yearid
;
-- Question 4v
CREATE VIEW q4v(team, diffAvg) AS
with x as(select allstarfull.teamid as id, salary
from salaries, allstarfull
where salaries.teamid = allstarfull.teamid
and salaries.yearid=2016
and salaries.playerid=allstarfull.playerid
and allstarfull.yearid=2016)
SELECT id, max(salary)-min(salary)
from x
group by id -- replace this line
;