DATABASE AND CLIENT/SERVER

APPLICATIONS
EXERCISE 08 - SQL FOR NORTHWIND QUERIES

NAME: MARCO FABIAN SANCHEZ PEREZ

PURPOSE
The purpose of this assignment is to develop the SQL for the requested reports.
ACTIVITIES
Perform each of the following activities. If you have questions, issues, or doubts, please ask for help and do not just
guess.
1. The three reports requested by Glen North are:
a. Write and execute the SQL to list all customer information for company, last name, first name, city, and
state.
b. Write and execute the SQL to list all orders by “Customer A”, including the customer company, last
name, first name, order id, order date, product id(s), and totals for the order.
c. Write and execute the SQL to list all shipments using “Shipping Company A”, including the shipping
company name, last name, first name, order id.
2. Write the SQL to generate each of the above reports using your test database. Save the three SQL queries using
the names “c08a”, “c08b”, and “c08c”. Execute the SQL and verify that it provides the correct answers for your
test data.
a)
SELECT Company, LastName & ', ' & FirstName AS Name, City, StateProvince
FROM Customers
ORDER BY Company;

b)
SELECT Company, LastName & ', ' & FirstName AS Name, Orders.ID, OrderDate,
OrderDetails.ProductID, Orders.OrderTotal
FROM (Customers INNER JOIN Orders ON Customers.ID=Orders.CustomerID) INNER JOIN
OrderDetails ON Orders.ID=OrderDetails.OrderID
WHERE Company='Customer A'
 ORDER BY Orders.ID;

c)
SELECT Shippers.Company, LastName & ', ' & FirstName AS Name, Orders.ID
FROM Shippers INNER JOIN Orders ON Shippers.ID=Orders.ShipperID
WHERE Company='Shipping Company A'
ORDER BY Orders.ID;

3. Download the “C08_P01_Northwind.accdb” database. Note: this database is located in C08_P01_Baseline.zip.

Copy and paste your SQL into this database. Execute the SQL commands.
4. Copy and paste the three SQL queries and the results of running them against the “C08_P01_Northwind.accdb”
database below.
a)
SELECT Company, LastName & ', ' & FirstName AS Name, City, StateProvince
FROM Customers
ORDER BY Company;

Company Name City State Province

Customer A Bedecs, Anna Seattle WA

Customer B Gratacos Boston MA

Solsona,
Antonio
Customer C Axen, Thomas Los Angeles CA

Customer D Lee, Christina New York NY

Customer E O’Donnell, Minneapolis MN

Martin
Customer F Pérez-Olaeta, Milwaukee WI
Francisco
Customer G Xie, Ming-Yang Boise ID

Customer H Andersen, Portland OR

Elizabeth
Customer I Mortensen, Salt Lake City UT
Sven
Customer J Wacker, Roland Chicago IL
Customer K Krschne, Peter Miami FL

Customer L Edwards, John Las Vegas NV

Customer M Ludick, Andre Memphis TN

Customer N Grilo, Carlos Denver CO

Customer O Kupkova, Honolulu HI

Helena
Customer P Goldschmidt, San Francisco CA
Daniel
Customer Q Bagel, Jean Seattle WA
Philippe
Customer R Autier Miconi, Boston MA
Catherine
Customer S Eggerer, Los Angeles CA
Alexander
Customer T Li, George New York NY

Customer U Tham, Bernard Minneapolis MN

Customer V Ramos, Luciana Milwaukee WI

Customer W Entin, Michael Portland OR

Customer X Hasselberg, Salt Lake City UT

Jonas
Customer Y Rodman, John Chicago IL

Customer Z Liu, Run Miami FL

b)
SELECT Company, LastName & ', ' & FirstName AS Name, Orders.ID, OrderDate, OrderDetails.ProductID,
Orders.OrderTotal
FROM (Customers INNER JOIN Orders ON Customers.ID=Orders.CustomerID) INNER JOIN OrderDetails ON
Orders.ID=OrderDetails.OrderID
WHERE Company='Customer A'
ORDER BY Orders.ID;
 Company Name ID Order Date Product Order Total
Customer A Bedecs, Anna 1 6/12/2012 Northwind Traders $156.75
Boysenberry Spread
Customer A Bedecs, Anna 1 6/12/2012 Northwind Traders Olive Oil $156.75
Customer A Bedecs, Anna 4 7/3/2012 Northwind Traders Cherry Pie $20.88
Filling
Customer A Bedecs, Anna 5 7/3/2012 Northwind Traders Chicken $191.74
Soup
Customer A Bedecs, Anna 5 7/3/2012 Northwind Traders Cajun $191.74
Seasoning
Customer A Bedecs, Anna 5 7/3/2012 Northwind Traders Brownie $191.74
Mix

c)
SELECT Shippers.Company, LastName & ', ' & FirstName AS Name, Orders.ID
FROM Shippers INNER JOIN Orders ON Shippers.ID=Orders.ShipperID
WHERE Company='Shipping Company A'
ORDER BY Orders.ID;

Company Name ID
Shipping Company A , 2
Shipping Company A , 4
Shipping Company A , 5

5. Submit your work to the LMS.

a. Compress your database renaming it to ‘C08_P01_YourID_Exercise08.accdb’ file, replacing “YourID” with
your student identifier, into a zip archive file.
b. Upload your archive file to the LMS.