M06 DBMS 130 Querying data from a table

Instructions:

In this assignment you are going to create single table select statements to query the tables that you created a previous assignment. You will use the SQL Workshop - SQL Scripts page to create a single script file to complete the assignment.

Save your script to a file named LastName_FirstName_SingleTable.sql (where LastName is your last name and FirstName is your first name). Once you create the script in APEX, you will have the ability to save it and download for submission. Your script should be well-formatted and should contain comments regarding what the particular section of the script is supposed to do.

Complete the following objectives using APEX:

Background Information:

You have been contracted by a small company named DJ's on Demand to create a database for storing information on their jobs, event venues, partners, clients, and music catalog.

Assignment Deliverable:

Create separate select statements to query data to answer each question. Be sure to place a single-line comment before each question to identify the question being answered.

(4 points each, 48 points total)

1. The owners of DJ's on Demand would like a query that returns of all items in their D_CDs table with the following column headings:
   1. Inventory Item, CD Title, Music Producer, and Year Purchased
2. Write a query to return the title of the oldest recording in the D_CDS table. Your query should return only 1 row.
3. Write a query to return the producer information from the D_CDS table for the CD titled "Carpe Diem".
4. The manager of DJ's on Demand would like a query of all the CD titles and years of CD's that were produced by "Old Town Records".
5. Write a query that will list the titles and years of all the DJ's on Demand's CD's that were NOT produced in the year 2000.
6. Write a query to display the location type and comments for all DJ's on Demand venues that are "Private Home".
7. Write a query to a list of all information for DJ's On Demand track listings for CD Number 91.
8. The manager of DJ's on Demand would like a list of partners (first name, last name), order the output by last name.
9. Write a query that lists the titles of songs in the DJ's on Demand inventory that are type code 77, 12, or 1; order by title.
10. Write a query that will return all the DJ's on Demand CD information (title, year) ordered by year and title.
11. Write a query to list the DJ's on Demand songs ordered (descending) by title. Use the alias "Our Collection" for the song title.
12. Write a query to return a DISTINCT list of producers for the CD's DJ's on Demand has in their collection.

See the rubric for the points breakdown. This assignment is worth 48 points total.

Submitting your Assignment:

1. Click the Submit Assignment button.
2. Click the Choose File button and upload the following files:
   1. LastName_FirstName_SingleTable..sql - this file should contain all of your scripts.
3. Click the Submit Assignment button to submit.

sample script not needed