Exam #2 Review - Exam #2 this thursday - I will post the Exam #2 schema on Wednesday. - I will hold extended office hours on wednesday, 2 -> until no one shows up. Topics on the exam -- SQL - joins (nulls, like), group by/having, union/except, left join - simple operations, "set subtraction queries" , complex queries involving subqueries - insert/update/delete - contraints/transactions/foreign keys/triggers - access control, views SQL Block: SELECT R.a, S.b FROM WHERE GROUP BY HAVING UNION // EXCEPT // UNION ALL //EXCEPT ALL SELECT S.c, T.d FROM WHERE GROUP BY HAVING ORDER BY a, b LIMIT SQL Block with subqueries WITH abc AS (SELECT/FROM/WHERE) SELECT R.a, S.b FROM abc WHERE 3 = (select count(*) From abc) GROUP BY HAVING count(*) = (select count(*) from abc) UNION // EXCEPT // UNION ALL //EXCEPT ALL SELECT S.c, T.d, (select count(*) from abc where d = t.d) FROM abc WHERE T.d not in (select d from abc) GROUP BY HAVING ORDER BY a, b LIMIT ----------- Artists(id, name, bio, birthYear) Albums(id, name, releaseDate) Songs(id, name, lyrics) Recordings(id, trackNum, length, songId, albumId) Characteristics(id, name) RecordingHasCharacteristics(recordingId, characteristicId) AlbumsReleasedBy(albumId, artistId) SongComposedBy(songId, artistId) -- music SongWrittenBy(songId, artistId) -- lyrics Find all artists who have written or composed at least one of the songs they recorded. Return the id, name of the artist and the songs that they recorded and contributed to. select a.id, a.name, r.songid from recordings r , albumsreleasedby ar , songcomposedby s1 , artists a where r.d = ar.albumid and r.songid = s1.songid and ar.artistid = s1.artistid and ar.artistid = a.id union select a.id, a.name, r.songid from recordings r , albumsreleasedby ar , songwrittenby s1 , artists a where r.d = ar.albumid and r.songid = s1.songid and ar.artistid = s1.artistid and ar.artistid = a.id ; select distinct a.id, a.name, r.songid from recordings r , albumsreleasedby ar , songcomposedby s1 , songwrittenby s2 , artists a where r.d = ar.albumid and (r.songid = s1.songid or r.songid = s2.songid) and ar.artistid = s1.artistid and ar.artistid = a.id Find songs that are composed and written by completely different groups of people, i.e. no artist has both written and composed the songs returned. (You can assume that for each song there is at least one tuple in SongComposedBy and SongWrittenBy each.) all songs - songs with common artist select id from songs except select s1.songid from songcomposedby s1, songwrittenby s2 where s1.artistid = s2.artistid and s1.songid = s2.songid ; select id from songs where id not in ( select s1.songid from songcomposedby s1, songwrittenby s2 where s1.artistid = s2.artistid and s1.songid = s2.songid ) with common as (select s1.songid from songcomposedby s1, songwrittenby s2 where s1.artistid = s2.artistid and s1.songid = s2.songid ) select id from songs where id not in (select songid from common) ; Find albums released in the last year (i.e. last 365 days). For each album, return its id, the associated characteristics for that album (through recordings) and the total number of recordings for each characteristic (i.e. return albumid, characteristicid and count). select a.id, rc.characteristicid , count(rc.id) from albums a join recordings r on a.id = r.albumid left join RecordingHasCharacteristics rc on r.id = rc.recordingid where now() - a.releasedate <= 365 group by a.id, rc.characteristicid ; For each song, return its id, the total number of times it has been recorded and the number of different artists that have recorded the song. select s.id , count(distinct r.id) as numrecordings , count(distinct a.artistid) as numartists from songs s left join recordings r on s.id = r.songid left join albumsreleasedby a on r.albumid = a.artistid group by s.id ; ----------- Artists(id, name, bio, birthYear, numalbums) Albums(id, name, releaseDate) Songs(id, name, lyrics) Recordings(id, trackNum, length, songId, albumId) AlbumsBy(albumId, artistId) SongComposedBy(songId, artistId) -- music SongWrittenBy(songId, artistId) -- lyrics 1. Find all artists who have recorded at least 5 songs that were recorded before they were born. select art.id from albumsby a, recordings r , recordings r2, albums a2 , artists art where a.albumid = r.albumid and r2.songid = r.songid and a2.id = r2.albumid and a.artistid = art.id and art.birthyear > a2.releasedate group by art.id having count(distinct r.songid) >= 5; 2. For each song, find the earliest recording of that song and the total number of artists who have been involved in its creation (either as composer or lyricist). with alla as ( select songid, artistid from songcomposedby union select songid, artistid from songwrittenby) select s.songid , min(a.releasedate) , count(distinct al.artistid) from songs s , recordings r , alla al , albums a where s.songid = r.songid and s.songid = al.songid and a.albumid = r.albumid group by s.songid; 3. Find the artists who have written the highest number of songs. Return their name and bio. with nums as ( select artistid, count(*) as num from songwrittenby group by artistid) select artistid from nums where num = (select max(num) from nums); with nums as ( select count(*) as num from songwrittenby group by artistid order by num desc limit 1) select artistid from songwrittenby group by artistid having count(*) = (select num from nums); with nums as ( select artistid, count(*) as num from songwrittenby group by artistid) select artistid from nums where num >=ALL (select num from nums); 4. Delete all song information for songs that have never been recorded. delete from songcomposedby where songid not in (select songid from recordings) ; delete from songwrittenby where songid not in (select songid from recordings) ; delete from songs where id not in (select songid from recordings) ; 5. Update the artist relation for artists born in the 21st century and set the numalbums attribute to the number of albums that they have recorded. update artists set numalbums = (select count(albumid) from albumsby a where a.artistid = artists.id) where birthyear >= 2000 ; 16. Delete all albums released before 1940 and has no recordings in the database. delete from albums where releasedate < date '1/1/1940' and id not in (select albumid from recordings); 7. Find all albums that have a non-consecutive track number. Return their id and the track no before the missing track number. select distinct r.albumid , r.tracknum from recordings r , recordings r2 where r.albumid = r2.albumid and r.tracknum < r2.tracknum and not exists (select * from recordings r3 where r3.albumid = r2.albumid and r3.trackno > r.tracknum and r3.trackno < r2.tracknum) ; select r.albumid from recordings r group by r.albumid having count(distinct r.tracknum) <> max(r.tracknum) ; 8. Find songs who are written and composed by the same set of artists. Return their id and name. with alla as ( select s1.songid, count(*) as numa from songcomposedby s1, songwrittenby s2 where s1.songid = s2.songid and s1.artistid = s2.artistid group by s1.songid ) select songid from alla where numa = (select count(*) from songwrittenby s1 where s1.songid = songid) and numa = (select count(*) from songcomposedby s1 where s1.songid = songid) ;