1. Identify primary key and foreign key for each of the following tables. Orders
ID: 3595345 • Letter: 1
Question
1. Identify primary key and foreign key for each of the following tables. Orders Customers OH 1001 5/1/2015 0023 1002 5/3/2015 0023 1003 5/3/2015 0018 1004 5/6/2015 0021 0018 0019 0021 NW MGM Universal 0 NE OrderDetails Products P# P# Unit Shipped Unit 1001 1001 1001 1002 1002 1003 10 20100090 20100091 20100093 20100092 20100093 20100092 20100092 20100093 20100090 8.99 12.99 9.52 12.99 9.52 12.99 12.49 9.52 8.99 20100090 20100091 American Beauty 20100092 20100093 Animal Kingdom 8.99 11.99 12.49 9.52 Lord of the Ring The Mask 10 21 25 2. Linking Multiple Tables for Query For each of the following query, identify 1) which tables need to be linked or if not linked what table is used 2) which field(s) are used to link the tables 1. Display the P# and PName of products bought on order 1001 2. How many different products were bought on order 1001? 3. How many units in total are ordered in order 1001? How many are shipped? 4. What is the total order amount of O#1001 ? What is the total shipped amount? 5. How many total units are purchased by Customer 0023? How many are shipped? 6. How many units of product 20100093 are purchased by customer 0023? 7. What is the total shipped amount customer 0023 paid for product 20100093? 8. "Short ship" happens when the shipped quantity is less than the order quantity. Display the of orders where there is any "short ship". 9(Continue from #8) Display the Date where the short ship happens. 10. (Continue from #8) Display the name of the customers who experienced short ship.Explanation / Answer
IN ORDERS TABLE O# IS PRIMARY KEY
IN CUSTOMERS C# IS PRIMARY KEY
IN ORDER DETAILS O#,P# ARE PRIMARY KEYS
IN PRODUCTS P# IS THE PRIMARY KEY
1) HERE PRODUCTS AND ORDER DETAILS MUST BE LINKED. THE LINKING WILL BE DONE ON BASED OF O# IN ORDERDETAILS TABLE
2) NO TABLES NEEDED TO JOIN ONLY ORDERDETAILS IS ENOUGH
3) NO TABLES NEEDED TO JOIN ONLY ORDERDETAILS IS ENOUGH
4) NO TABLES NEEDED TO JOIN ONLY ORDERDETAILS IS ENOUGH
5) HERE ORDERS AND ORDERDETAILS MUST BE JOINED. THIS WILL BE JOINED ON BASED OF O#
6) HERE ORDERS AND ORDERDETAILS MUST BE JOINED. THIS WILL BE JOINED ON BASED OF O#
7) HERE ORDERS AND ORDERDETAILS MUST BE JOINED. THIS WILL BE JOINED ON BASED OF O#
8) NO TABLES NEEDED TO JOIN ONLY ORDERDETAILS IS ENOUGH
9) HERE ORDERS AND ORDERDETAILS MUST BE JOINED. THIS WILL BE JOINED ON BASED OF O#
10) HERE ORDERS,CUSTOMERS AND ORDERDETAILS MUST BE JOINED. THIS WILL BE JOINED ON BASED OF O# BETWEEN ORDERS AND ORDERDETAILS. BETWEEN CUSTOMER AND ORDERS BASED ON CUST#
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.