You are given a database in a MS Access file called ‘DBP-e14-Cape-Codd’ that con

Question

You are given a database in a MS Access file called ‘DBP-e14-Cape-Codd’ that contains data collected in an outdoor sports retail store. The retail store hired you as a data analyst to answer questions in order to help them make business decisions that will help them grow. Write the SQL statement for each query and save them in MS Access as Q1, Q2,… Please submit the MS Access and MS Word to help speed the grading turnout! in Blackboard. (to help you answer these questions, you shoud practice running the practice SQL queries shown in the videoes). Query Question SQL Statement

7 List all records from retail_order and order_item tables for the year 2014. Sort by order number and SKU of Order_Item

8 For every department, show the average order item price

9 Which manager had the largest amount (quantity) of item-orders?

10 For every store (storeNumber), how many departments are there?

11 How much total money (extendedprice) did we make from each buyer?

12 How much total money (extendedprice) was each manager responsible for?

Explanation / Answer

1. SELECT SKU, SKU Description, (RTRIM(Buyer)&' in '&RTRIM(Department) as Sponsor) FROM SKU_DATA;

2. SELECT count(*) FROM CATALOG_SKU_2014 WHERE CatalogPage IS NOT NULL GROUP BY Department;

3. SELECT count(*) FROM SKU_DATA WHERE SKU != 302000 GROUP BY Department HAVING count(*) > 1;

4. SELECT * FROM RETAIL_ORDER r, ORDER_ITEM o WHERE r.OrderNumber = o.OrderNumber;

5. SELECT r.*, o.* FROM RETAIL_ORDER r, ORDER_ITEM o WHERE r.OrderNumber = o.OrderNumber;

6. SELECT * FROM RETAIL_ORDER r INNER JOIN ORDER_ITEM o ON r.OrderNumber = o.OrderNumber ORDER BY o.OrderNumber, o.SKU;

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.