SQL statements

I need SQL statements for each of the queries below (in MS Access). Only one statement for each query (Total is 17 commands).
.
.
.
We have the same Music CD library, with the same database for the catalog information
of the CDs available. The database consists of the following tables (along with schema):
Composer(CNo, Last, First, Born, Died)
Piece(PNo, CNo, Title, Tune, Opus)
CD(CDNo, Name, Producer, Discs, Minutes)
Recording(CDNo, PNo, Orchestra, Conductor)
The underlined column names indicate the primary key in each table. The columns named CNo,
PNo, CDNo are therefore also foreign keys, each individually CNo referring to the Composer
table, PNo to the Piece table, and CDNo the CD table. In the CD table, Discs is the number of
discs in the CD pack, and Minutes the total play time of the CD pack. To do this assignment
using MS Access, download the database a2.accdb from the course web site. To use Oracle,
download the SQL script file a2.sql to open it in SQL*Plus to run it (using the start command:
start a2.sql;). The SQL Script will create the tables and load the data into the tables.
Prepare an SQL statement to answer each of the following.
1. List all composers born in the 17th century that is, born between 1600 and 1699. List the First
name, the Last name, and the number of years the composer lived. The result should be listed
in the ascending order of number of years lived. If two composers lived the same years, then list
them in alphabetical order, by the Last name, and then by the First name (if same Last name).
2. For these composers born in the 17th century, what is the average age (i.e., number of years
lived) What is maximum age, the minimum age How many composers were born in the 17th
century List all the answers in one SQL statement.
3. We can use the expression 1+floor(Born/100) to get the century in which the composer was
born. List all the composers whose last name begins with the letter M; list the first name, the
last name, and the century in which the composer was born. List them in the descending order
of the year born. [ Note: For MS Access, use 1+Int(Born/100) ]
4. List the number of composers born in each century, included in our database. The result of this
SQL statement should have two columns: the century, and the number of composers born in that
century. Order the result list by the century.
5. List the average age the composers lived for each century considering a composer born in that
century as lived for that century – list the average age along with the number of composers
counted in calculating the average age. The result of this SQL statement therefore should have
3 columns, the century, average age, and number of composers counted in calculating the
average age. Order the result by the century lived for.
6. Write two SQL statements: the first one should determine the minimum age (i.e., the minimum
of the number of years) a composer lived, among all the composers in our database; the second
one should make use of the result obtained from the first one, to list the first and last names of
any composer who died at that minimum age. (If there are more composers than one who died at
that same age, the SQL statement should list all of them.)
7. Write an SQL statement to determine how many music pieces there are in the music library
with the title containing Concerto grosso (that is, Title like %Concerto grosso% note that for
MS Access, the wildcard matching character is * instead of %). List just the number of music
pieces.
8. Write an SQL statement to determine how many music pieces there are in the library with the
title not containing Concerto grosso. List just the number of music pieces.
Peter Y. Wu
Office: Wheatley 320
E-mail : wu@rmu.edu
INFS 6240 Database Management System (Section B)
Assignment 2 due September 15, 2016
9. Note that although most of the CD Packs contain exactly one disc, some CD Packs may contain
more than one disc. Find the maximum number of discs per CD pack in the Music CD Library.
List just the maximum number.
10. Categorize the CD Packs by the number of discs in the CD Pack. Count how many CD Packs
are there in each category. List the number of discs per pack, and the number of CD Packs for
that category. List in the ascending order of number of discs per CD Pack.
11. The Minutes column of the CD table has the music playing time of each CD Pack in minutes.
Write an SQL statement to find the total music playing time of all the CD Packs in the library.
List just the number of minutes.
12. The Minutes column of the CD table has the music playing time of each CD Pack in minutes.
Categorize the CD Packs by the music playing time of the CD Pack into these categories:
Category 1 has less than 100 minutes music playing time; Category 2 has at least 100 minutes
but less than 200 minutes; Category 3 has at least 200 minutes but less than 300 minutes; and so
on. Write an SQL statement to count the number of CD Packs in each category. List the
Categories and the count of CD Packs, order by the Category number.
13. In our music library, how many Recordings are there with music performed by orchestras
conducted by Alfred Scholz (i.e., conductor = Alfred Scholz) Write an SQL statement to list
just the number of recordings.
14. In our music library, how many CD Packs are there with music performed by orchestras
conducted by Alfred Scholz Write an SQL statement to list just the number of CD Packs. (If
we use MS Access, we may have to use a sub-Query as a table in the SQL statement.)
15. Different music pieces (with different PNo) may have the exact same title, list the titles of these
music pieces. List these music titles, along with the number of versions (of music pieces) there
are sharing the same title. List the results by descending order of the number of versions.
16. Different music pieces may have the exact same title, list the titles of these music pieces only if
they are composed by the same composer. List these music titles, along with the number of
versions (of music pieces) with the same title and by the same composer.
17. [***] Different music pieces may have the exact same title, list the titles of these music pieces
only if they are composed by the different composers. List just these music titles.
[***] indicates a hard or tricky question