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'));

12MULTIPLE TABLE QUERIES
4. Provide a result set of the entertainer’s stage name for all entertainers who have not
been booked to an engagement.
Comments: The query displays the entertainer’s stage name where the entertainers have
not been booked. The query uses inner join, WHERE clause and a subquery using tables
entertainers and engagements.
SQL CODE:
SELECT EntStageName
FROM Entertainers
Where entertainerid not in (select entertainerid from engagements);

13MULTIPLE TABLE QUERIES
School Scheduling Database
1. Provide a result set of all the first and last names of the staff members and the count
that each teaches.
Comments: The query displays the staffs name and the number of subjects they teach.
The query uses inner join for the results. The tables uses are staff, faculty_classes, classes
and subjects.
SQL CODE:
SELECT Staff.StfFirstName, Staff.StfLastname, Count(Subjects.SubjectID) AS
CountOfSubjectID
FROM Subjects INNER JOIN (Staff INNER JOIN (Classes INNER JOIN
Faculty_Classes ON Classes.ClassID = Faculty_Classes.ClassID) ON Staff.StaffID =
Faculty_Classes.StaffID) ON Subjects.SubjectID = Classes.SubjectID
GROUP BY Staff.StfFirstName, Staff.StfLastname;

14MULTIPLE TABLE QUERIES
2. Provide the first and last name of the students enrolled in a class that begins at 11
am. Sort the result set by student’s last name in alphabetical order.
Comments: The query displays the entertainer’s stage name where the entertainers have
not been booked. The query uses inner join where the tables used are student,
student_schedules and classes.
SQL CODE:
SELECT Students.StudFirstName, Students.StudLastName, Classes.StartTime
FROM Classes INNER JOIN (Students INNER JOIN Student_Schedules ON
Students.StudentID = Student_Schedules.StudentID) ON Classes.ClassID =
Student_Schedules.ClassID

15MULTIPLE TABLE QUERIES
WHERE (((Classes.StartTime)=#12/30/1899 11:00:0#)) order by students.studlastname;
3. Provide a result set of all the first and last names of the faculty who teach in a
classroom without a phone being available. Make sure there are no duplicate values
in the result set.
Comments: The query displays the staff who teaches in a class room where the phone is
not available. The query uses inner join and WHERE clause where the tables used are
classes, class_rooms, faculty_classes and staff.
SQL CODE:
SELECT distinct Staff.StfFirstName, Staff.StfLastname
FROM Staff INNER JOIN ((Class_Rooms INNER JOIN Classes ON
Class_Rooms.ClassRoomID = Classes.ClassRoomID) INNER JOIN Faculty_Classes ON
Classes.ClassID = Faculty_Classes.ClassID) ON Staff.StaffID = Faculty_Classes.StaffID
WHERE (((Class_Rooms.PhoneAvailable)=False));

16MULTIPLE TABLE QUERIES
4. Provide a result set of all student’s first and last names that attend a class in a
classroom that has a capacity > 80.
Comments: The query displays the student details who have their classes in a classroom
having the capacity more than 80. Inner join and WHERE clause is used.
SQL CODE:
SELECT distinct Students.StudFirstName, Students.StudLastName
FROM Students INNER JOIN ((Class_Rooms INNER JOIN Classes ON
Class_Rooms.ClassRoomID = Classes.ClassRoomID) INNER JOIN Student_Schedules
ON Classes.ClassID = Student_Schedules.ClassID) ON Students.StudentID =
Student_Schedules.StudentID
WHERE (((Class_Rooms.Capacity)>80));

17MULTIPLE TABLE QUERIES
Bowling League Database
1. Provide a result set of the first and last name of the bowlers with a count of the
games each bowled.
Comments: The query displays the bowler ddetails and the count of their bowling where
the count is used and grouped by students’ first and last names. Inner join is used to
merge the tables.
SQL CODE:
SELECT Bowlers.BowlerFirstName, Bowlers.BowlerLastName,
Count(Bowler_Scores.MatchID) AS CountOfMatchID
FROM Bowlers INNER JOIN Bowler_Scores ON Bowlers.BowlerID =
Bowler_Scores.BowlerID
GROUP BY Bowlers.BowlerFirstName, Bowlers.BowlerLastName;

18MULTIPLE TABLE QUERIES
2. Provide a result set of the first and last name of the bowlers that 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.
Comments: The query displays the bowers who have the raw score more than 200.
However the data does not have any of these results.
SQL CODE:
SELECT Bowlers.BowlerFirstName, Bowlers.BowlerLastName
FROM Bowlers INNER JOIN Bowler_Scores ON Bowlers.BowlerID =
Bowler_Scores.BowlerID

19MULTIPLE TABLE QUERIES
WHERE (((Bowler_Scores.RawScore)>200));
3. Provide a result set of the team name and a count of the number of matches the
team has won.
Comments: The query displays count of total matches and the team itself. The tables
used are ,match_games and team table. Each team has won21 matches.
SQL CODE:
SELECT t.teamname, count(mg.matchid) as TotaalWinning
FROM Match_Games mg, Teams t where t.teamid=mg.winningteamid group by
t.teamname;
Recipes Database
1. Provide a result set of the types of recipes and the count of recipes in each type. Sort
the result set in descending alphabetical order.

20MULTIPLE TABLE QUERIES
Comments: The query displays the recipe and its type given in the recipe_classes. Along
with inner join Count Function has been used with the group by clause.
SQL CODE:
SELECT Recipe_Classes.RecipeClassDescription, Count(Recipes.RecipeID) AS
CountOfRecipeID
FROM Recipe_Classes INNER JOIN Recipes ON Recipe_Classes.RecipeClassID =
Recipes.RecipeClassID
GROUP BY Recipe_Classes.RecipeClassDescription;
2. Provide a result set of the recipe titles for all recipes that have any of their
measurements that use ‘to taste’.
Comments: The query displays the measurement of the recipe where the
measurementdescription is having the string ‘to taste’.
SQL CODE:
SELECT Recipes.RecipeTitle, Measurements.MeasurementDescription
FROM Measurements INNER JOIN (Recipes INNER JOIN Recipe_Ingredients ON
Recipes.RecipeID = Recipe_Ingredients.RecipeID) ON
Measurements.MeasureAmountID = Recipe_Ingredients.MeasureAmountID

21MULTIPLE TABLE QUERIES
WHERE (((Measurements.MeasurementDescription) Like '*to taste*'));