DBMS 130

M07 Hands-On Assignment - Limiting Data Using Comparison Operators

In this assignment you are going to create multi-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_Compare.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. See the file sample_script.PNG for suggested script format.

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.

(6 points each, 48 points total)

1. The manager of DJ's on Demand would like a query of all the CD titles and years of CD's that were produced before the year 2000.
2. Write a query to a list of all the DJ's on Demand CD titles that have a as the second letter in the title.
3. Using only the less than, equal, or greater than operators, rewrite the following query:

   • SELECT code, low_range, high_range FROM d_packages WHERE low_range BETWEEN 2501 and 10000;

4. Write a query to find out which venues DJ's on Demand is scheduled to have events that are NOT in private homes.
5. Write a query to find out what play list item does does not have any comments.
6. Using only the between operator, rewrite the following query:

   • SELECT title FROM d_cds WHERE year >= 1998 AND year <= 2000;

7. Write a query to a list of all the DJ's on Demand CD title and producer name that were produced in either the year 2000 or 2004.
8. The manager of DJ's on Demand would like to know the partners (first name, last name) who do NOT have an authorized expense amount, order the output by first name.

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_Compare.sql - this file should contain all of your scripts.
3. Click the Submit Assignment button to submit.