Extra Queries 1 to 9 against the SQL, Bugs, and Rock 'n' Roll Database

Solution Notes

-- Q1-Q9
-- Martin Schwenke 1997
-- cae March 2002
-- YYYY in dates
-- cae Oct 2006
-- formatting

SET ECHO ON
SET FEEDBACK 1

SET LINESIZE 132
SET PAGESIZE  45


/* Q1: List the title of each album. */

select	title
from	album;


/* Q2: List the family and given names of each musician born in the
       1940s (that is, between 1940 and 1949, inclusive). */

select	family_name, given_name
from	musician
where	birth_date >= '01-JAN-1940'
and	birth_date < '01-JAN-1950';


/* Q3: How many musicians were born in the 1950s? */

select	count(*)
from	musician
where	birth_date >= '01-JAN-1950'
and	birth_date < '01-JAN-1960';


/* Q4: List the given name of each musician who doesn't have a family
       name.  Your query should not use "LIKE", "=", "!=" or "<>"! */

select	given_name
from	musician
where	family_name is null;


/* Q5: List the album identifiers and titles of each album released on
       the label called "Atlantic". */

select	album_id, title
from	album
where	label = 'Atlantic';


/* Q6: List the artist name and title of each album released on the
       label called "CBS". */

select	name, title
from	artist, album
where	artist.artist_id = album.artist_id
and	label = 'CBS';


/* Q7: List the family and given names of musicians who played on an
       album which was released in or after 1990. */

select	distinct family_name, given_name
from	musician m, gig g, album al
where	m.musician_id = g.musician_id
and	g.album_id = al.album_id
and	year >= 1990;


/* Q8: List the family and given name of each musician who has played
       on an album by the "Rolling Stones".  Use a join, *not* a
       subquery. */

select	distinct family_name, given_name
from	musician m, gig g, album al, artist ar
where	m.musician_id = g.musician_id
and	g.album_id = al.album_id
and	al.artist_id = ar.artist_id
and	name = 'Rolling Stones';


/* Q9: List the family and given name of each musician who has played
       on an album by the "Rolling Stones".  Use only subqueries involving
       "IN".  Do not use joins or subqueries involving "EXISTS". */

-- IN tests for values in an intermediate table

select	family_name, given_name
from	musician
where	musician_id in
	(select	musician_id
	 from	gig
	 where	album_id in
		(select	album_id
		 from	album
		 where	artist_id in
			(select	artist_id
			 from	artist
			 where	name = 'Rolling Stones')));

[Top]