Chapter 6 an Introduction to Structured Query Language (SQL)

Assignment 2SQL Advanced SQL

Total points: 50 (each query is worth 5 points)

This assignment should be completed individually. For each problem, submit your SQL statement and a screen shot of the SQL results in a single Word document or pdf file. Submit the file via eLearning.

I recommend creating a new userand workspace named after your netid, log in as that user and load the database script (provided in this week's assignment folder). Before you attempt to write any SQL queries, familiarize yourself with the database structure and data. I have provided a relational diagram and sample data for this database.

Write queries to address each of the problems below. Submit both the SQL statements and the screen prints of the outputs from Oracle.(2 points for the screen shots). Be sure the workspace name is included in your screen shots!!!

Entertainment Agency ERD

1. Write a query that will update the commission rate by .02 for agents who were hired in the past 20 years or more.

2. Provide a list of agents who have had 5 or more engagements – list their first name and last name. (4 rows)

1. Show all the constraints on the ENGAGEMENTS table. Briefly describe each constraint.

1. Which customers prefer rock music styles? Write a query that will display the customer firstname, customer last name, and the style name for those customers who have some type of Rock preference.(3 rows)

1. How many engagements has each entertainer performed? Display the entertainer stage name, and the total number of engagements as Engagements, order by engagement number in ascending order.(11 rows)

1. How many engagements do we have planned in each upcoming month this year? Write a query that will display all future engagements, the month, and the count of engagements per month. Order the results by month.(9 rows)

1. Provide a list of customers that do not have an engagement scheduled. This can be done with a subquery and an outer join. 5 Bonus pts if you show both techniques. (2 rows)

1. How long is each engagement in February set to run? (10 rows)

1. Show me an alphabetical list of entertainers based in Bellevue, Redmond, or Woodinville. Show their stage name, phone number and city. (7 rows)

1. Show each agents name, the sum of the contract price for the engagements booked, and the agent’s total commission. Format the number as shown. Hint, you must multiple the sum of the contract prices by the agent’s commission rate.

Extra Credit:

EC1. Show me all musical styles and the customers who prefer those styles. Display the styleid, style name, customer id and customer first and last name.

41 rows returned

EC2. Produce a list of customers who like contemporary music together with a list of entertainers who play contemporary music. Hint, you will need a UNION and JOINs.

1