188.61.145.205 at 18:10, 18 September 2012

2012-09-18T18:10:20Z

188.61.145.205 at 18:09, 18 September 2012

2012-09-18T18:09:54Z

Connor at 08:56, 18 July 2012

2012-07-18T08:56:42Z

Connor at 11:40, 11 July 2012

2012-07-11T11:40:03Z

Connor at 11:39, 11 July 2012

2012-07-11T11:39:40Z

Connor at 11:38, 11 July 2012

2012-07-11T11:38:22Z

Connor: Created page with "

The Music database

This tutorial introduces the notion of a join. The music has two tables: album and track.

album('''asin''..."

2012-07-11T11:34:27Z

Created page with "<h3>The Music database</h3> <p>This tutorial introduces the notion of a join. The music has two tables: <code>album</code> and <code>track</code>. </p> album('''asin''..."

New page

<h3>The Music database</h3>

<p>This tutorial introduces the notion of a join. The music
has two tables: <code>album</code> and <code>track</code>.
</p>
album('''asin''', title, artist, price, release, label, rank)
track('''<i>album</i>, dsk, posn''', song)
<p>
[[Album]]
<h2>How to do joins.</h2>

The phrase <code>FROM album JOIN track ON album.asin=track.album</code> represents the join of the tables <code>album</code> and
<code>track</code>. This <code>JOIN</code> has one row for every track. In addition to the track fields (<code>album</code>,
<code>disk</code>, <code>posn</code> and <code>song</code>) it includes the details of the corresponding <code>album</code>
(<code>title</code>, <code>artist</code> ...).

<div class='qu'>
Find the <code>title</code> and <code>artist</code>
who recorded the
<code>song</code> <code>'Alison'</code>.
<source lang='sql' class='def'>
SELECT title, artist
FROM album JOIN track
ON (album.asin=track.album)
WHERE song = 'Alison'
</source>

<source lang='sql' class='ans'>
SELECT title, artist
FROM album JOIN track
ON (album.asin=track.album)
WHERE song = 'Alison'
</source>
</div>

<div class='qu'>
Which <code>artist</code> recorded the <code>song</code>
<code>'Exodus'</code>?
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT artist
FROM album JOIN track ON (asin=album)
WHERE song = 'Exodus'
</source>
</div>

<div class='qu'>
Show the <code>song</code> for each <code>track</code> on
the <code>album</code> <code>'Blur'</code>
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT song
FROM album JOIN track ON (asin=album)
WHERE title = 'Blur'
</source>
</div>

We can use the aggregate functions and <code>GROUP BY</code> expressions on the joined table.
<div class='qu'>
For each <code>album</code> show the <code>title</code>
and the total number of <code>track</code>.
<source lang='sql' class='def'>
SELECT title, COUNT(*)
FROM album JOIN track ON (asin=album)
GROUP BY title
</source>

<source lang='sql' class='ans'>
SELECT title, COUNT(*)
FROM album JOIN track ON (asin=album)
GROUP BY title
</source>
</div>

<div class='qu'>
For each <code>album</code> show the <code>title</code>
and the total
number of tracks containing the word <code>'Heart'</code>
(albums with no such tracks need not be shown).
<div class='hint' title='hint'>
Use song LIKE '%Heart%' to find the songs that include the word <code>Heart</code>
</div>
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT title, COUNT(*)
FROM album JOIN track ON (asin=album)
WHERE song LIKE '%Heart%'
GROUP BY title
</source>
</div>

<div class='qu'>
A "title track" is where the <code>song</code> is the
same as the <code>title</code>. Find the title tracks.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT song
FROM album JOIN track ON (asin=album)
WHERE song = title
</source>
</div>

<div class='qu'>
An "eponymous" album is one where the title is
the same as the artist (for example the album
<code>'Blur'</code> by the band <code>'Blur'</code>).
Show the eponymous albums.
<div class='hint' title='hint'>
You only need to access one table in this example - so don't use the <code>JOIN.</code>
</div>
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT title
FROM album
WHERE artist = title
</source>
</div>

<div class='qu'>
Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up.
<div class='hint' title='hint'>
The <code>HAVING</code> clause can be used outside of the <code>GROUP BY.</code>
</div>
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT song, COUNT(DISTINCT asin)
FROM album JOIN track ON asin=album
GROUP BY song
HAVING COUNT(DISTINCT asin)>2
</source>
</div>

<div class='qu'>
A "good value" album is one where the price per track is less than 50 pence.
Find the good value album - show the title, the price and the number of tracks.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT title, price, COUNT(song)
FROM album JOIN track ON asin=album
GROUP BY title, price
HAVING price/COUNT(song) < 0.50
</source>
</div>

<div class='qu'>
Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby
clocks up 101 tracks.
<div class='imper'>List albums so that the album with the most tracks is first.
Show the title and the number of tracks</div>
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT title, COUNT(asin)
FROM album JOIN track ON asin=album
GROUP BY asin,title
ORDER BY 2 DESC
</source>
</div>

← Older revision		Revision as of 08:56, 18 July 2012
Line 172:		Line 172:
	</source>		</source>
	</div>		</div>
		+
		+	[[More_JOIN_operations \|Movie Database tutorial]]

@@ Line 7: / Line 7: @@
   track('''<i>album</i>, dsk, posn''', song)
    <p>
-[sqlzoo.net/w/index.php/Album More details about about the database]
+[http://sqlzoo.net/w/index.php/Album More details about about the database]
 <h2>How to do joins.</h2>

@@ Line 7: / Line 7: @@
   track('''<i>album</i>, dsk, posn''', song)
    <p>
-[[More details about about the database]]
+[sqlzoo.net/w/index.php/Album More details about about the database]
 <h2>How to do joins.</h2>

@@ Line 2: / Line 2: @@
    <p>This tutorial introduces the notion of a join. The music
-   has three tables: <code>album</code> and <code>track</code>.
+   has two tables: <code>album</code> and <code>track</code>.
    </p>
   album('''asin''', title, artist, price, release, label, rank)

Music Tutorial - Revision history