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
|
select name from instructor where salary between 100 and 100000;
(select course_id from section where semester = "Fall" and year = 2009)
union
(select course_id from section where semester = "Spring" and year = 2010);
create table result select course_id from section where semester = "Fall" and year = 2009;
create table result2 select course_id from section where semester = "Spring" and year = 2010;
select course_id from result inner join result2 using (course_id)
select course_id from result inner join result2 using (course_id);
select a.course_id from result2 as a left join result as b on a.course_id = b.course_id where b.course_id is null;
select count(distinct ID) from teaches where semester = "Spring" and year = 2010;
select dept_name, avg(salary) as avg_salary from instructor;
select dept_name, avg(salary) as avg_salary from instructor group by dept_name;
select dept_name, count(distinct ID) from instructor natural join teaches where semester = "Spring" and year = 2010 group by dept_name;
select dept_name, avg(salary) from instructor having avg(salary) > 42000;
select sum(salary) from instructor;
select course_id from result natural join result2;
select distinct course_id from section where semester = "Fall" and year = 2009
and course_id in (select course_id from section where semester = "Spring" and year = 2010);
select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = "Biology";
select name from instructor where salary > some (select salary from instructor where dept_name = "Biology")
select dept_name from instructor group by dept_name
having max(salary) > all (select max(salary) from instructor group by dept_name);
select course_id from section as S where semester = "Fall" and year = 2009
and exists (select * from section as T where semester = "Spring" and year = 2010 and S.course_id = T.course_id);
select dept_name, asshole from (select dept_name, avg(salary) as asshole from instructor group by dept_name)
as T where asshole > 4200;
with max_budget(value) as (select max(budget) from department) select budget from department, max_budget where department.budget = max_budget.value;
with dept_total(dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name),
dept_total_avg(value) as (select avg(value) from dept_total) select dept_name from dept_total, dept_total_avg where dept_total.value >= dept_total_avg.value;
|