SQL Query: Aggregate Functions and Group By

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.

Prepare an SQL statement to answer each of the following.

• List all composers born in the 17^th 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).
• For these composers born in the 17^th 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 17^th century?List all the answers in one SQL statement.
• 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) ]
• 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.
• 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.
• Write two SQL statements: the first one should determine the minimum age (i.e., the minimumof 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.)
• Write an SQL statement to determine how many music pieces there are in the music librarywith 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.
• 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.
• 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.
• 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.
• The Minutes column of the CD table has the music playing time of each CD Pack in minutes.
• 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.
• 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.
• 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.)
• 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.
• 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.

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.

Prepare a report – MS Word document – answer each of the questions above and show the SQL statements used in each case, along with the results by executing the SQL statements.

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.