Exam this thursday

select
student_id
, grade
, case when grade = 'A' then 4
when grade = 'B' then 3
when grade = 'A' then 3.7
when grade = 'B' then 2.7
when grade = 'B+' then 3.4
when grade is null then null
else 0
end
FROM
transcript ;
select
day
, time
, count(*)
, avg(price)
from
events
group by grouping sets
((day, time), (day), (time)) ;
select
day
, time
, count(*)
, avg(price)
from
events
group by rollup
(day, time) ;
Roll up (A,B,C)
> (A,B,C), (A,B), (A), ()
select
day
, time
, count(*)
, avg(price)
from
events
group by cube
(day, time) ;
Cube (A,B) > (A,B), (A), (B), ()

Homework #6

 Only return the call to create the function

SQL Review
~~~~~~~~~~~
SELECT A.b, count(*)
FROM A, B, C
WHERE A.a = 5
and B.b = C.c
and upper(A.b) = 'ABC'
GROUP BY
A.b, B.d
HAVING
count(*) >4
and count(distinct b.c) = count(distinct c.d)
select r.a,r.b from r
union
select s.c,s.d from s
order by a,b
Scalar query:
select
r.baker
 uncorrelated subquery
, (select count(*) from episodes where viewers7day > 9) as totalepisodes
, count(*) numcompeted
 correlated subquery
, (select count(*) from episodes e, technicals t2 where e.id = t2.episodeid
and t2.baker = r.baker and e.viewers7day > 9)
from
bakers r, technicals t
where
r.baker = t.baker
group by
r.baker ;
select
r.baker
 uncorrelated subquery
, (select count(*) from episodes where viewers7day > 9) as totalepisodes
, count(*) numcompeted
, count(e.id) nummorethan9
from
bakers r
join technicals t on r.baker = t.baker
left join episodes e on t.episodeid = e.id and e.viewers7day > 9
group by
r.baker ;
Find contestants who never lost
select baker from bakers
except
select baker from results where result = 'eliminated' ;
select baker
from bakers
where
baker not in (select baker from results where result = 'eliminated') ;
select b.baker
from bakers b left join results r
on b.baker = r.baker and r.result = 'eliminated'
where
r.baker is null ;
select b.baker
from bakers b
where 0 = (select count(*) from results r
where r.baker = b.baker and r.result = 'eliminated') ;
select b.baker
from bakers b
where not exists (select 1 from results r
where r.baker = b.baker and r.result = 'eliminated') ;

For ALL queries
requirements(major, courseid)
students(rin, major)
transcript(rin, courseid, grade)
Find students who fullfilled all requirements.
For each student,
check that there does not exist a required course
that they did not complete!
select
s.rin
from
students s
where
not exists (select *
from requirements r
where r.major = s.major
and not exists (select 1 from transcript t
where t.rin = s.rin
and t.courseid = r.courseid
and t.grade is not null));
requirements(major, courseid)
students(rin, major) key: rin
transcript(rin, courseid, grade)
find all students for whom number of requirements that they completed
is equal to the total number of requirements that they have.
select
s.rin
from
students s
, requirement r
, transcript t
where
s.rin = t.rin
and t.courseid = r.courseid
and r.major = s.major
and (t.grade is not null and t.grade <> 'F')
group by
s.rin
, s.major
having
count(*) = (select count(*) from requirements r2
where r2.major = s.major);
attr in ( query )
attr not in ( query )
exists ( query )
not exists ( query )
not exists ( select 1 .... )

insert into relation values ()
insert into relation select .... ;
update relation
set attr1 = value1, attr2 = value2
where condition ;
update baker
set numwins = (select count(*) from results r where r.result = 'star baker'
and r.baker = baker.baker)
where age < 40 ;
delete from relation
where 0 = (select ...) ;