[Main Page]      [Literature]      [Software]      [People]      [Conferences]      [Courses]      [Web-DB]

Homework Assignment #4:    Basic Queries in Algebra and SQL

Exercise 4 (12 Points)

Consider a database with information about classical music CDs. It consists of the following tables:

• Composer(CNo, Last, First, Born, Died)
• Piece(PNo, CNo, Title, Tune, Opus)
• CD(CDNo, Name, Producer, Discs, Minutes)
• Recording(CDNo, PNo, Orchestra, Conductor)

Please write relational algebra expressions to answer the following queries. Note that SQL*Plus does not understand relational algebra, so you cannot test your queries.

    a)

Print all conductors from whom there are recordings in the database.

    b)

Which CDs have over 60 minutes runtime per disc? Please print the name of the CD, the number of discs, and the total runtime. ("Minutes" is the total runtime, "Discs" is the number of CDs, e.g. 2 for a double CD.)

    c)

Which CDs contain a music piece by Prokofiev? ("Prokofiev" is the last name of the composer.) Print number and name of the CD.

Now write the following queries in SQL. You should try to test your queries in SQL*Plus, but it is not required (for this homework). If you want, you can submit a handwritten solution:

    d)

As a). Please make sure that there are no duplicates in the output.

    e)

As b). Please sort the output by the number of CDs as main criterion, and the total runtime as secondary criterion (larger numbers first).

    f)

Please print composer numbers and piece titles for all pieces which contain "Sinfon" or "Symphony" as a substring in their title.

Due Date:

Stefan Brass (sbrass@sis.pitt.edu), February 2, 2000

Original URL: http://www2.sis.pitt.edu/~sbrass/db00_2/h4_algeb.html   [HTML 3.2 Checked]