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 ...) ;