Database Languages
eXercise #6
Questions on the
MUSIC Database
The music database
(Download an Access2000 version
of this database; alternatively, interrogate the online
version of this database) is defined as follows:
ALBUMS
| albname |
artist |
media |
pdate |
recco |
sernum |
| char(25) |
char(20) |
char(2) |
dec(4) |
char(10) |
char(15) |
|
|
NOT NULL |
|
|
NOT NULL |
|
|
|
|
|
KEY |
TRACKS
| sernum |
song |
| char(15) |
char(60) |
| NOT NULL |
NOT NULL |
| KEY |
KEY |
|
PERFORMERS
| sernum |
artist |
instrument |
| char(15) |
char(20) |
char(25) |
| NOT NULL |
NOT NULL |
NOT NULL |
| KEY |
KEY |
KEY |
|
Set A
The ALBUMS
Table:
(Write the query and the number of rows in the answer table)
- List all
of the titles of albums that are 'CD's
- List all
of the artists in the albums table (NO REPEATS)
- List all
details of albums 10 years or more old
- List Album
Names of albums by Brian Eno
- List the
artist of all albums that were either published in 1980 or 1981
- List serial
numbers of albums that were published by EMI
- List Publishing
companies represented in the albums table (NO REPEATS), in alphabetical
order
- List all
Album titles that are not CDs and are at least 3 years old
The PERFORMERS
Table:
(Write the query and the number of rows in the answer table)
- List the
names of all artists who play Bass on an album
- List all
instruments (NO REPEATS) that Brian Eno plays
- List those
people who don't play Bass on any album
Nasty Questions:
(Write the query and the number of rows in the answer table)
These will become
'trivial' later
- List all
tracks on the Violent Femmes self titled album
- List all
artists featured on U2's 'Achtung Baby'
Set B
(Write the query and the number of rows in the answer table)
- List all details
of Albums that are not 'CD's [Albums]
- List the number
of Albums that were published before 1985 [Albums]
- List the Titles
and Artists banded by Recording company [Albums]
- List the instruments
played by 'Brian Eno' [Performers]
- List Song Title
and Serial Number of any song about an 'axe' [Tracks]
- List the age
(in years) of the oldest Album in the table [Albums]
- List the Album
Title and Publishing date of all Albums releases by 'Peter Gabriel',
that were NOT released through the 'Charisma' Recording Company [Albums]
- If each Album
in the albums cost $20, list the total cost of the listed Albums [Albums]
- List the number
of different medias recorded in the table [Albums]
- List a Chronological
listing of albums (ie. oldset to youngest) [Albums]
- List the Serial
Number of the album with the greatest number of songs [Tracks]
Set C
Single Table Operations:
(Write the query and the number of rows in the answer table)
From the ALBUMS
table, get SQL to display the following data:
- the alb_name
and artist of those albums either on CD or CA that were produced between
1970 and 1980
- the artist where
the artists name begins with a 'P' (try where artist like 'P%')
- the pdate and
recco of all self-titled albums
From the TRACKS
table, get SQL to display the following data:
- all songs that
begin with the word 'the'
- the sernum of
all albums that have the word 'axe' in them
Multi-Table Operations:
Get SQL to display
the following information:
- a complete track
listing of Pink Floyd's album 'The Dark Side Of The Moon
- the name of the
album that has a track called 'texarkana'
- a track listing
of albums by Pink Floyd that were published before 1973
- the population
of Cuba in 1952 (as per the census taken that year), and their names
