IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
IIS home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 

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)

  1. List all of the titles of albums that are 'CD's
  2. List all of the artists in the albums table (NO REPEATS)
  3. List all details of albums 10 years or more old
  4. List Album Names of albums by Brian Eno
  5. List the artist of all albums that were either published in 1980 or 1981
  6. List serial numbers of albums that were published by EMI
  7. List Publishing companies represented in the albums table (NO REPEATS), in alphabetical order
  8. 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)

  1. List the names of all artists who play Bass on an album
  2. List all instruments (NO REPEATS) that Brian Eno plays
  3. 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
  1. List all tracks on the Violent Femmes self titled album
  2. List all artists featured on U2's 'Achtung Baby'

Set B

(Write the query and the number of rows in the answer table)

  1. List all details of Albums that are not 'CD's [Albums]
  2. List the number of Albums that were published before 1985 [Albums]
  3. List the Titles and Artists banded by Recording company [Albums]
  4. List the instruments played by 'Brian Eno' [Performers]
  5. List Song Title and Serial Number of any song about an 'axe' [Tracks]
  6. List the age (in years) of the oldest Album in the table [Albums]
  7. List the Album Title and Publishing date of all Albums releases by 'Peter Gabriel', that were NOT released through the 'Charisma' Recording Company [Albums]
  8. If each Album in the albums cost $20, list the total cost of the listed Albums [Albums]
  9. List the number of different medias recorded in the table [Albums]
  10. List a Chronological listing of albums (ie. oldset to youngest) [Albums]
  11. 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:

  1. the alb_name and artist of those albums either on CD or CA that were produced between 1970 and 1980
  2. the artist where the artists name begins with a 'P' (try where artist like 'P%')
  3. the pdate and recco of all self-titled albums

From the TRACKS table, get SQL to display the following data:

  1. all songs that begin with the word 'the'
  2. the sernum of all albums that have the word 'axe' in them

Multi-Table Operations:

Get SQL to display the following information:

  1. a complete track listing of Pink Floyd's album 'The Dark Side Of The Moon
  2. the name of the album that has a track called 'texarkana'
  3. a track listing of albums by Pink Floyd that were published before 1973
  4. the population of Cuba in 1952 (as per the census taken that year), and their names

Solutions

wonko@wonko.info
©Copyright t 1992..2017+. Edition 25.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.