Module 3 Assignment: SQL Problems Involving Multiple Tables and Subqueries

Purpose

In this Module 3 Assignment, you will be required to answer Structured Query Language (SQL) problems involving multiple tables and subqueries. The SQL techniques you will need to apply are covered within Chapters 9, 10, and 11 of the required textbook.

Assignment Instructions

This assignment includes multiple table queries using the sample databases attached to the SQL Server implementation installed in Module 1(order number 1823134) Remember to add the personalized comment as noted at the beginning of Module 1 before each of your answer submissions of SQL code and corresponding result set. Failure to include the required comment with each question will result in a grade of zero. 

 

Assignment Requirements

Remember to make sure the correct database is active before executing a SQL query.  

Module 3 Questions: Multiple Table Queries

Use the Sales Order Database

1. Provide a result set of the customers’ first and last name and phone numbers for all customers who have ordered clothing. Make sure duplicate values are not included in the result set.

2. Provide a result set of the product name and quantity on hand for all products that have not been ordered.

3. Provide a result set of the employees’ first and last names for all employees that have sold accessories. Be sure not to include duplicates in your result set. Sort the result set in alphabetical order by employee last name.

4.Provide a result set of the product name and description for all products ordered by a vendor that has ‘bike’ somewhere in the vendor’s name.

5. Provide the name of the product that is the most expensive we sell that is purchased from ProFormance.

6. Provide a result set of the product names we sell that are purchased from a vendor that does not have a web page.

7. Provide a result set of the vendor names of all the vendors that sell us products that are bikes.

Use Entertainment Agency Database

8. Provide a result set of all the entertainers’ stage names and the count of each entertainer’s engagements.

9. Provide a result set of all the first and last names of all customers  who have a preference for salsa and have booked an engagement. Make sure there are no duplicate values in the result set.

10. Provide a result set of the stage names of entertainers who have played an engagement for customers from Bellevue. Make sure there are no duplicate values in the result set.

11. Provide a result set of the entertainers’ stage names for all entertainers who have not been booked to an engagement. Use School Scheduling Database

12. Provide a result set of all the first and last names of the staff members and the count that each teaches.

13. Provide the first and last names of the students enrolled in a class that begins at 11 a.m. Sort the result set by student’s last name in alphabetical order.

14. Provide a result set of all the first and last names of the faculty who teach in a classroom without a phone available. Make sure there are no duplicate values in the result set.

15. Provide a result set of the first and last names of all students who attend a class in a classroom that has a capacity > 80. Use Bowling League Database

16. Provide a result set of the first and last names of the bowlers with a count of the games each bowled.

17. Provide a result set of the first and last names of the bowlers who have bowled a game with a raw score of 200 or more. Please remove duplicates from the result set and sort the result set in alphabetical order by last name.

18. Provide a result set of the team names and a count of the number of matches each team has won.