CD Library exercise

Introduction

This document provides information about a library of CDs that will be used as examples and exercises in the workshops.

Contents

Sample data

This data was prepared by analysing the contents of several CDs in the library. The heading of each section is the title of the CD. The rest of the information is probably self-evident. Ask if you are not sure.

Bach in Pembroke

Identifier: SKCD1
Publisher: Sidney Kenderdine
Organist: Sidney Kenderdine
Instrument: Pembroke College Chapel, Cambridge
Date: 1997
Composer: Johann Sebastian Bach

Tracks:

  1. Prelude and ...
  2. Fugue in E minor (The Wedge), BWV 548
  3. Prelude and...
  4. Fugue in A, BWV 536
  5. Nun komm der Heiden Heiland, BWV 659
  6. Nun komm der Heiden Heiland, BWV 660
  7. Nun komm der Heiden Heiland, BWV 661
  8. In dulci jubilo, BWV 608
  9. Valet will ich dir geben, BWV 736
  10. Herzlich tu mich verlangen, BWV 727
  11. Komm, Gott Schopfer, heiliger Geist, BWV 667
  12. Duet No 3 in G, BWV 804
  13. Wachet auf, ruft uns die Stimme, BWV 645
  14. Wo soll ich fliehen hin?, BWV 646
  15. Wer nur den lieben Gott lasst walten, BWV 647
  16. Meine Seele erhebet den Herren, BWV 648
  17. Ach bleib bei uns, Herr Jesu Christ, BWV 649
  18. Kommst du nun, Jesu, vom Himmel herunter, BWV 650
  19. Fugue in E flat (St Anne), BWV 552ii

Romsey Abbey, the 1858 Walker Organ

Identifier: SSD.201,
Publisher: Sarum Sound
Organist: Jeffrey Williams
Instrument: Romsey Abbey
Date: 1997

Tracks:

  1. Sonata No. 3 in A major, by J L Felix Mendelssohn-Bartholdy
  2. Tierce en Taille from Messe des Paroisses, by Francois Couperin
  3. Voluntary in C, opus 1, by John Stanley
  4. Intermezzo on an Irish air, by Charles Villiers Stanford
  5. Tuba Tune in D Major, by Craig Sellar Lang
  6. Interlude in C, by Walford Davies
  7. Le banquet celeste, by Olivier Messiaen
  8. Psalm Prelude set 1 no 3, by Herbert Howells
  9. Fantasia in G, BWV 572, by Johann Sebastian Bach

Full Stops: Arthur Wills at the Organ of Ely Cathedral

Identifier: CDE 84305
Publisher: Meridian Records
Organist: Arthur Wills
Instrument: Ely Cathedral
Date: 1995

Tracks:

  1. Marche Pontificale, by Charles-Marie Widor
  2. Adagio in B minor, by Tomaso Albinoni, realised by Remo Giazzoto
  3. Ride of the Valkyries, by Richard Wagner
  4. Voluntary for double organ, by Henry Purcell
  5. Voluntary in A, by John Blow
  6. Echo Voluntary in G, by John Blow
  7. Variations on Amazing Grace, by Arthur Wills
  8. The Storm, by Nicolas Jacques Lemmens

Organ Music from Beverley Minster

Identifier: PHI CD 152,
Publisher: Amphion
Organist: Alan Spedding
Instrument: Beverley Minster, Peter Conacher and Co., Huddersfield
Date: 1998

Tracks:

  1. Fanfare, by Jaak Nikolaas Lemmens
  2. Praludium and Fuga in A minor, Bux WV 153, by Dietrich Buxtehude
  3. Trio in E flat, by Johann Ludwig Krebs
  4. Chorale Prelude: Jesu, Leiden Pein und Tod, by Johann Caspar Vogler
  5. Voluntary VIII, Opus 5, by John Stanley
  6. Introduction, Cornet and Echo, by Matthias Hawdon
  7. Troisieme Fantaisie, Op 157, by Camille Saint-Saens
  8. Praludium, Opn 65, II/7, by Max Reger
  9. Trio, by Ludwig van Beethoven
  10. Prelude on The Old Hundredth, by Charles Hubert Hastings Parry
  11. Prelude in F sharp minor, by Louis Vierne
  12. Toccata - Carillon, by Alan Spedding

Organ, PCMS: Best of 2000

Identifier: SK.R67a,
Publisher: Sidney Kenderdine
Date: 2000

Tracks:

  1. Maria Zart,
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Anon
  2. Komm, heiliger Geist, Herre Gott, BWV 651
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Johann Sebastian Bach
  3. Herr Jesu Christ, dich zu uns wend, BWV 655
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Johann Sebastian Bach
  4. O Lamm Gottes unschuldig, BWV 656
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Johann Sebastian Bach
  5. Allein Gott in der Hoh' sei Ehr', BVW 663
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Johann Sebastian Bach
  6. Allein Gott in der Hoh' sei Ehr', BVW 664
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Johann Sebastian Bach
  7. Jesus Christus, unser Heiland, BWV 665
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Johann Sebastian Bach
  8. Jesus Christus, unser Heiland, BWV 666
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Johann Sebastian Bach
  9. Vor deinen thron tret' ich, BWV 668
    S Kenderdine on the organ in Pembroke College Chapel, Cambridge
    Johann Sebastian Bach
  10. Kreutzer Sonata, Op 47, Adagio Sostenuto; Presto
    Rebecca Smith, violin
    Jonathan Beatty, piano
    Ludwig van Beethoven
  11. Kreutzer Sonata, Op 47, Andante con Variazione
    Rebecca Smith, violin
    Jonathan Beatty, piano
    Ludwig van Beethoven
  12. Kreutzer Sonata, Op 47, Presto
    Rebecca Smith, violin
    Jonathan Beatty, piano
    Ludwig van Beethoven

Richard Wagner: Favourite Overtures and Orchestral Music from the Operas

Identifier: RRC2014A
Publisher: Regis Records Ltd
Orchestra: The Philharmonia Orchestra
Conductor: Yuri Simonov
Date:
Composer: Richard Wagner

Tracks:

  1. Prelude (Parsifal)
  2. Ride of the Valkyries (Der Ring des Niebelungen)
  3. Forest Murmurs (Der Ring des Niebelungen)
  4. Siegfried's Rhine Journey (Der Ring des Niebelungen)
  5. Siegfried's Funeral Music (Der Ring des Niebelungen)
  6. Prelude and Liebestod (Tristan und Isolde)

Richard Wagner: Favourite Overtures and Orchestral Music from the Operas

Identifier: RRC2014B
Publisher: Regis Records Ltd
Orchestra: The Philharmonia Orchestra
Conductor: Yuri Simonov
Date:
Composer: Richard Wagner

Tracks:

  1. Rienzi, Overture
  2. Der fliegende Hollander, Overture
  3. Tannhauser, Overture
  4. Lohengrin, Prelude to Act 1
  5. Lohengrin, Prelude to Act 3
  6. Die Meistersinger von Nurnberg, Overture

Twentieth century music for Holy Week

Identifier: MHCD27
Publisher: Classical Chorus
Conductor: David Truslove
Choir: Romsey Choral Society
Organ: Elizabeth Burgess
Date: 2002

Tracks:

  1. Ubi caritas from Quatre Motets
    Maurice Durufle
  2. Tota pulchra es from Quatre Motets
    Maurice Durufle
  3. Tu es Petrus from Quatre Motets
    Maurice Durufle
  4. Tantum ergo from Quatre Motets
    Maurice Durufle

  5. Prelude from Prelude and Fugue sur le nom d'Alain
    Maurice Durufle
  6. Fugue from Prelude and Fugue sur le nom d'Alain
    Maurice Durufle

  7. Lo the full final sacrifice
    Gerald Finzi

  8. Requiem and Kyrie from Requiem
    John Rutter
  9. Out of the deep from Requiem
    John Rutter
  10. Pie Jesu from Requiem
    John Rutter
  11. Sanctus from Requiem
    John Rutter
  12. Agnus Dei from Requiem
    John Rutter
  13. The Lord is my shepherd from Requiem
    John Rutter
  14. Lux aeterna from Requiem
    John Rutter

Fidelio, Act 1

Identifier: 8.660070
Publisher: Naxos
20 tracks
Conductor: Michael Halasz
Orchestra: Nicolaus Esterhazy Sinfonia
Chorus: Hungarian Radio Chorus
Composer: Ludwig van Beethoven

Fidelio, Act 2

Identifier: 8.660071
Publisher: Naxos
10 tracks
Conductor: Michael Halasz
Orchestra: Nicolaus Esterhazy Sinfonia
Chorus: Hungarian Radio Chorus
Composer: Ludwig van Beethoven

Back to the table of contents.

Table definitions

It is proposed to model some of this data in the following tables.

The SQL statements to set up and fill these tables is in the LCDLib SQL procedure.

Disk

Person

Composer

Back to the table of contents.

Exercises

Exercise 1

Draw a diagram to show the tables, their attributes, and how they are linked. Keep this diagram up to date as more tables are added in the following exercises.

Exercise 2

Run the LCDLib SQL procedure to set up and fill the tables.

Use the following SQL statements to examine the data:

No.QueryIntention
1 SELECT * FROM Disk; Displays the contents of the Disk table.
2 SELECT * FROM Disk ORDER BY id; Displays the contents of the Disk table in identifier order.
3 SELECT title FROM Disk ORDER BY id; Displays the titles of the disks in identifier order.
4 SELECT * FROM Person ORDER BY id; Displays the contents of the Disk table in identifier order.
5 SELECT forenames, surname FROM Person ORDER BY surname; Displays the forenames and surnames of the contents of the Person table in surname order.
6
SELECT Disk.id, title, surname FROM Disk, Person, Composer
WHERE Disk.id = Composer.disk AND Composer.person = Person.id;
Displays the identifier, title, and composers associated with each disk.
7
SELECT surname FROM Person, Composer 
WHERE Person.id = Composer.person;
Displays the surnames of all the composers.
8
SELECT DISTINCT forenames, surname FROM Person, Composer 
WHERE Person.id = Composer.person ORDER BY surname;
Displays the forenames and surnames of all the composers in order of surname.

Back to the table of contents.

Exercise 3

Write a reusable SQL procedure to define a new table called Performer with the following attributes:

Write INSERT statements to add the information about performers from the sample data.

There is a sample solution to this exercise.

Back to the table of contents.

Exercise 4

Write a reusable SQL procedure to define a new table called Conductor with the following attributes:

Write INSERT statements to add the information about conductors from the sample data.

There is a sample solution to this exercise.

Exercise 5

Use the ALTER TABLE statement (CB 6.3.3) to add a numeric column called tracks to the Disk table. This is intended to record the number of tracks on a disk. Use the DESCRIBE command to check your table definition.

Exercise 6

These exercises use only one table.

  1. Write and execute a SELECT statement to list the titles of the disks in title order without repetitions. (There should be 9 rows, as two of the disks have the same title.)
  2. List all the instrument types found in the Performer table, without repetitions. (There are just three.)
  3. List the identifiers of the disks that have performers, and the number of performers on each disk. Use Disk and Performers as the headings of the columns.
  4. List the identifiers of the disks that have conductors, and the number of conductors on each disk. Use Disk and Conductors as the headings of the columns.

Exercise 7

This exercise uses two tables.

  1. Devise and execute a query to list all the performers in order of surname.
  2. Devise and execute a query to list the surnames of all the organists.
  3. Write a SELECT statement to list (in surname order) the forenames and surname of each composer, and the number of disks containing works by that composer.

Exercise 8

This exercise uses three tables.

  1. Devise and execute a query to list the identifiers of the disks of organ music and the surnames of the organists on those disks.
  2. List the identifiers of all the CDs that contain a work by Johann Sebastian Bach.
  3. List all the identifiers and titles of the disks that contain a work for organ.
  4. List all the identifiers and titles of the disks for a given composer (use a variable for the surname).

Back to the table of contents.


Last updated on 12 September 2006.