Module 7
Database and MySQL Assignment 7
Due no later than 11:59 PM CST on Friday of Module 7
Instructions:
Answer the following questions with an SQL Query using the “classicmodels” database. You will
place the SQL code for your query, and a screenshot of the query correctly executing in a
Microsoft Word document. Be sure your Word document begins with the course name, your
name, your LWC ID number, the assignment name and number (Database Assignment Module
7), and the instructor number. Be sure to submit your assignment before the due date.
Query 1
List the product lines that contain “Cars”.
Query 2
List all payments greater than twice the average payment.
Query 3
List the names of employees with first names “Barry” or “Larry”.
Query 4
List the names of products sold at less than 80% of MSRP.
Query 5
Which orders have a value of more than $5,000? You will create a view called
“OrderValue” which will contain the order number and the total value of the order. You
will need to use a group by command. Output the code to create the view and use the
“select * from OrderValue” command to output the contents of the view. Then use the
view to answer the query. Output the code for the query as well as the output of the query.
Query 6
Answer the same question “Which orders have a value of more than $5,000?”
WITHOUT using a view. It is not as difficult as you think. You need to add something to
Query 7
Compute the commission for each sales representative, assuming the commission is 4%
of the value of an order. Sort by employee first name and last name. Create a view called
“Commissions” which will output the sales rep number and the total commission. To do
this, you will have to join the view you created in query 5 with the orders, customers, and
employees tables. Call the field containing the commission amount “Commission”. You
will need a group by command in your code. Output the code to create the view as well
as the output when the view is run. Then create a query to join the Commissions view to
the employees table to output the final results.
Query 8
Create a query to output a directory which lists the first and last names of each employee
and the first and last names of their boss.
Query 9
Which office has the most employees? Create a view called “OfficeEmployees” which
will hold the office code and the number of employees at that office. Output the code to
create the view as well as the contents of the view. Then use the view and the offices
table to output the city of the office having the greatest number of employees. Output the
query code and the results of the query.
Query 10
Who reports to Mary Patterson?