Multiple Table Queries Assignment: IT526, Databases, Spring 2024

This assignment presents a comprehensive set of SQL queries designed to manipulate and retrieve data from multiple tables within various database schemas. The solutions cover databases for sales orders, an entertainment agency, school scheduling, a bowling league, and recipes. Each query addresses specific data retrieval requirements, employing techniques such as inner joins, subqueries, and aggregate functions to extract relevant information. The queries demonstrate how to filter results based on specific criteria, sort data, and aggregate information to provide insightful reports. The assignment showcases practical applications of SQL in diverse business and organizational contexts, offering a valuable resource for students studying database management and SQL programming. The solutions are formatted with SQL code and comments explaining the logic and tables used for each query, providing a clear understanding of the database interactions and the expected results.

Running head: MULTIPLE TABLE QUERIES
MULTIPLE TABLE QUERIES
Name of the Student
Name of the University
Author note

1MULTIPLE TABLE QUERIES
Table of Contents
Sales Order Database.......................................................................................................................2
Entertainment Agency Database......................................................................................................9
School Scheduling Database..........................................................................................................12
Bowling League Database.............................................................................................................16
Recipes Database...........................................................................................................................18

2MULTIPLE TABLE QUERIES
Sales Order Database
1. Provide a result set of the customers’ first and last name and their phone number
for all customers who have ordered clothing. Make sure duplicate values are not
included in the result set.
Comments: The query uses the inner join between the tables to get the expected results.
The tables used are Customers, orders, order_details, products and category.
SQL CODE:
SELECT DISTINCT Customers.CustFirstName, Customers.CustLastName,
Customers.CustPhoneNumber
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN ((Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN Order_Details ON
Orders.OrderNumber = Order_Details.OrderNumber) ON Products.ProductNumber =
Order_Details.ProductNumber
WHERE (((Categories.CategoryDescription)="Clothing"));

3MULTIPLE TABLE QUERIES
2. Provide a result set of the product’s name and quantity on hand for all products
that have not been ordered.
Comments: The query uses sub query to get the expected products which are not
availbale in the order_detaills. The tables used are products and order_details.
SQL CODE:
SELECT Products.ProductName, Products.QuantityOnHand
FROM Products

4MULTIPLE TABLE QUERIES
WHERE (((Products.ProductNumber) Not In (select [Order_Details].[ProductNumber]
from [Order_Details])));
3. Provide a result set of the employees’ first and last name 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.
Comments: The query uses the inner join between the tables to get the expected results.
The tables used are employees, orders, order_details, products and category.
SQL CODE:
SELECT DISTINCT Employees.EmpFirstName, Employees.EmpLastName
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN ((Employees INNER JOIN Orders ON
Employees.EmployeeID = Orders.EmployeeID) INNER JOIN Order_Details ON
Orders.OrderNumber = Order_Details.OrderNumber) ON Products.ProductNumber =
Order_Details.ProductNumber
WHERE (((Categories.CategoryDescription)="accessories")) ORDER BY
Employees.EmpLastName;

5MULTIPLE TABLE QUERIES
4. Provide a result set of the product’s name and description for all products ordered
by a vendor that has a name that has ‘bike’ somewhere in their name.
Comments: The query uses the inner join between the tables to get the expected results
along with the ‘WHERE’ clause. The tables used are product_vendors, products and
vendors.
SQL CODE:
SELECT DISTINCT Products.ProductName, Products.ProductDescription
FROM vendors INNER JOIN (Products INNER JOIN Product_Vendors ON
Products.ProductNumber = Product_Vendors.ProductNumber) ON vendors.VendorID =
Product_Vendors.VendorID
WHERE (((vendors.vendname) Like '*Bike*'));

6MULTIPLE TABLE QUERIES
5. Provide the name of the product that is the most expensive we sell which is
purchased from ProFormance.

7MULTIPLE TABLE QUERIES
Comments: The query uses the inner join and subquery between the tables. The tables
used are product_vendors, products and vendors.
SQL CODE:
select products.productname as productname, products.retailprice as retailprice from
products, vendors, product_vendors where vendors.vendorid=product_vendors.vendorid
and product_vendors.productnumber=products.productnumber and
vendors.vendname='ProFormance' and products.retailprice =(SELECT Max(retailprice)
AS Expr1
FROM (select products.retailprice as retailprice from products, vendors, product_vendors
where vendors.vendorid=product_vendors.vendorid
and product_vendors.productnumber=products.productnumber and
vendors.vendname='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.
Comments: The query uses the inner join and null identifier for the VendWebPage
between the tables used are product_vendors, products and vendors.

8MULTIPLE TABLE QUERIES
SQL CODE:
SELECT DISTINCT Products.ProductName
FROM Vendors INNER JOIN (Products INNER JOIN Product_Vendors ON
Products.ProductNumber = Product_Vendors.ProductNumber) ON Vendors.VendorID =
Product_Vendors.VendorID
WHERE (((Vendors.VendWebPage) Is Null));

9MULTIPLE TABLE QUERIES
7. Provide a result set of the vendor name of all the vendors that sell us products that
are bikes.
Comments: The query uses the inner join and WHERE clause for the results between the
tables used are categories product_vendors, products and vendors.
SQL CODE:
SELECT distinct Vendors.VendName, Categories.CategoryDescription
FROM Vendors INNER JOIN ((Categories INNER JOIN Products ON
Categories.CategoryID = Products.CategoryID) INNER JOIN Product_Vendors ON
Products.ProductNumber = Product_Vendors.ProductNumber) ON Vendors.VendorID =
Product_Vendors.VendorID
WHERE (((Categories.CategoryDescription)='bikes'));
Entertainment Agency Database
1. Provide a result set of all the entertainer’s stage name and the count of the
entertainer’s engagements.
Comments: The query uses the inner join and null aggregate function between the tables
where the tables used are engagements and entertainers.

10MULTIPLE TABLE QUERIES
SQL CODE:
SELECT Entertainers.EntStageName, Count(Engagements.EngagementNumber) AS
CountOfEngagementNumber
FROM Entertainers INNER JOIN Engagements ON Entertainers.EntertainerID =
Engagements.EntertainerID
GROUP BY Entertainers.EntStageName;
2. Provide a result set of all the customer’s first and last name that have a preference
for salsa and have booked an engagement. Make sure there is no duplicate values in
the result set.
Comments: The query displays the customers who have salsa as their preferences. The
query has been calculated using inner join between customer, engagements, music_style
and music_preferneces.
SQL CODE:
SELECT distinct Customers.CustFirstName, Customers.CustLastName

11MULTIPLE TABLE QUERIES
FROM Musical_Styles INNER JOIN ((Customers INNER JOIN Engagements ON
Customers.CustomerID = Engagements.CustomerID) INNER JOIN Musical_Preferences
ON Customers.CustomerID = Musical_Preferences.CustomerID) ON
Musical_Styles.StyleID = Musical_Preferences.StyleID
WHERE (((Musical_Styles.StyleName)='Salsa'));
3. Provide a result set of entertainer’s stage name who have played an engagement for
customers from Bellevue. Make sure there are no duplicate values in the result set.
Comments: The query uses the inner join and WHERE clause between the tables
customer, engagements and entertainers.
SQL CODE:
SELECT DISTINCT Entertainers.EntStageName
FROM Entertainers INNER JOIN (Customers INNER JOIN Engagements ON
Customers.CustomerID = Engagements.CustomerID) ON Entertainers.EntertainerID =
Engagements.EntertainerID
WHERE (((Customers.CustCity)='Bellevue'));