[ExamVT13] Block 4: SQL Queries

[Niklas Broberg]

============================================

Schema reposted from previous block for convenience:

Tracks(trackId, title, length)

 length > 0

Artists(artistId, name)

Albums(albumId, title, yearReleased)

TracksOnAlbum(album, trackNr, track)

 album -> Albums.albumId

 track -> Tracks.trackId

 (album, track) unique

 trackNr > 0

Participates(track, artist)

 track -> Tracks.trackId

 artist -> Artists.artistId

Users(username, email, name)

 email unique

Playlists(user, playlistName)

 user -> Users.username

InList(user, playlist, number, track)

 (user, playlist) -> Playlists.(user, playlistName)

 track -> Tracks.trackId

PlayLog(user, time, track)

 user -> Users.username

 track -> Tracks.trackId

 (user, time) unique

Use the relations for the music site from [above] when answering the following problems. When you are asked to list all X, you need only return the key attributes of X.

============================================

4A (4p)

(i) (2p)

Write an SQL query that lists all artists appearing on any album released this year (2013).

(ii) (2p)

Write an SQL query that lists, for each user, how many playlists that user has.

============================================

4B (6p)

Write an SQL query that lists, for each track, its trackId and title, together with the number of times that track has been played, and the number of distinct users that have played it.

============================================

4C (8p)

Write an SQL query that finds the title, length and album title of the longest track in the database. If the track appears on more than one album, list the album where it appeared first. If more than one track of the same length qualifies, list the one that was released first, as given by the album it appears on. If there is still a tie, list all such tracks.

============================================

[Milica Bijelovic]

I have really hit a wall on the C part here.
I have to select the three title, length and album. But where do ut the CASE statement? Is it in the top select or do  do a subquery. In that case if  find the longest track in a subquery do I count them there or on top? Also I'm not really sure whether I shoud use HAVING here or if I should just group by title in the end? So basically:

SELECT title,length, album (CASE here?)
FROM Tracks T, TracksOnAlbum TOA, Album A
WHERE TOA.track=T.TrackID AND (?Do i use count here,In that case how?)length=(SELECT MAX(length) FROM TracksOnAlbum, Tracks WHERE A.albumId=TOA.album)
GROUP BY title
HAVING ???

I have a similar problem in 5C where I don't really know how to use distinct and count and max and how I translate distinct into relational algebra, is it even necessary?

[Niklas Broberg]

General hint: Use sequencing (WITH) to break the problem down into smaller parts. Trying to write a complex query as a single monolithic beast is rarely, if ever, the best way.

To solve 4C, here's what I would do:

1. Write a subquery that finds the longest tracks in Tracks. Let's call the result R1.

2. Write a subquery that joins R1 with the tables that contain all the other information we need: TracksOnAlbum and Albums, specifically, to find the years that albums were released. Let's call the result R2.

3. Write the main query that finds the track(s) that was released earliest, i.e. have the lowest value for 'yearReleased' in R2.

Regarding 5C, depending on how you write it, you can either use [delta](X) to remove duplicates in X (translates to SELECT DISTINCT * FROM X), or if you want to use it with an aggregation function, use DISTINCT as you would in SQL, i.e. COUNT(DISTINCT x) -> y.

HTH, Niklas

--
You received this message because you are subscribed to the Google Groups "tda357-vt2014" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tda357-vt201...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Niklas Broberg]

Correct answers:

 

============================================

4A (4p)

(i) (2p)

Write an SQL query that lists all artists appearing on any album released this year (2013).

SELECT P.artist

FROM Participates P, Album A, TracksOnAlbum T

WHERE P.track = T.track AND T.album = A.albumId AND yearRealeased = '2013';

 

(ii) (2p)

Write an SQL query that lists, for each user, how many playlists that user has.

SELECT user, COUNT(playlistName)

FROM Playlists

GROUP BY user;

 

============================================

4B (6p)

Write an SQL query that lists, for each track, its trackId and title, together with the number of times that track has been played, and the number of distinct users that have played it.

SELECT trackId, title, COUNT(*), COUNT(DISTINCT user)

FROM PlayLog, Tracks

WHERE track = trackId

GROUP BY trackId, title;

============================================

4C (8p)

Write an SQL query that finds the title, length and album title of the longest track in the database. If the track appears on more than one album, list the album where it appeared first. If more than one track of the same length qualifies, list the one that was released first, as given by the album it appears on. If there is still a tie, list all such tracks.

WITH

  R1 AS (

    SELECT trackId, title AS trackTitle, length

    FROM Tracks

    WHERE length = (SELECT MAX(length) FROM Tracks)

  ),

  R2 AS (

    SELECT *

    FROM R1, TracksOnAlbum T, Albums A

    WHERE R1.trackId = T.track AND A.albumId = T.album)

  )

SELECT trackTitle, length, title AS albumTitle

FROM R2

WHERE yearReleased = (SELECT MIN(yearReleased) FROM R2);