Case Study 2 of 3: SQL with Pine Valley Furniture Company Database

Business Intelligence and Analytics

Your assignment is to write and execute the SQL queries that will produce the data (record sets) requested below using the Pine Valley Furniture database in Microsoft Access. For ease of reference as you work on this assignment, please print out a copy of the Pine Valley Furniture Database Description document or use a split screen. After you are able to run your SQL statement and get the desired result, copy your

1. SQL statement(as text), and
2. the Recordset(your output/results) from each SQL statement

When you have finished, please submit this Word document (including your SQL statements and output) in Canvas. You may want to use the Snipping Tool to copy the recordset (output).

1. List customer ID, customer name, order ID, and order date, for all customers who have placed orders.

Query: SELECT c.customer_id, c.customer_name, o.order_id, o.order_date

FROM customer_t c

INNER JOIN order_t o ON c.customer_id = o.customer_id;

Result:

Hint: You need to include 2 tables in your FROM clause and you will need 1 JOIN

1. List customer ID, customer name, order ID, order date, product ID and the quantity of each product ordered, for all customers who have placed orders.

Query:

Hint: You need to include 3 tables in your FROM clause and you will need 2 JOINS

1. List customer ID, customer name, order ID, order date, product name, quantity ordered, and price of each product, for all customers who have placed orders.

Hint: You need to include 4 tables in your FROM clause and you will need 3 JOINS

1. List the customer name, city, and state of customers who ordered products 3 or 4. Hint: You need to include 3 tables in your FROM clause and you will need 2 JOINS
2. List the customer name, order date, and product description for those customers who have purchased a Computer Desk.

Hint: You need to include 4 tables in your FROM clause and you will need 3 JOINs

1. Which products were ordered before 10/22/2008? List the product name, the quantity ordered, and the order date for all orders placed before 10/22/2008. Hint: Remember to use # sign as a delimiter for the date (order_date

1. Write a query that will list the customer_name for orders that were placed before 10/22/2008.

Extra Credit (worth 1 pt) Now write a nested query that will list the customer_name for orders that were placed before 10/22/2008