Question

Assignment 2 Instructions

You need to access to the database and write SQL queries on the following site:

https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

1. Use INNER JOIN to create a report. In this report, list each customer’s CustomerID, CustomerName, and their each OrderID and each order’s OrderDate, if the customer has ever placed an order. You should use the Customers table and the Orders table in the database to complete this query.  

2. Use INNER JOIN to create a report. In this report, list each order’s OrderID, OrderDate, and each ProductID in each order. You should use the Orders table and the OrderDetails table in the database to complete this query.

3. Use OUTER JOIN to create a report. In this report, list the customer’s CustomerID and CustomerName if the customer has never placed an order. You should use the Customers table and the Orders table in the database to complete this task.

4. Use UNION to create a combined list to show SupplierID/ShipperID and SupplierName/ShipperName. You should use the Suppliers table and the Shippers table in the database to complete this query.

Please save the SQL query statements for each question into a Word document, and name the Word document as “MyAssignment2”. Please submit the document to myleo online before the deadline. Thank you!

Answer 1

1.

Select Customers.CustomerID, CustomerName, OrderID, OrderDate from Customers inner join Orders on Customers.CustomerID = Orders.CustomerID;

2.

Select Orders.OrderID,OrderDate,ProductID from Orders inner join OrderDetails on Orders.OrderID = OrderDetails.OrderID;

3.

Select CustomerID, CustomerName from Customers Full Outer Join Orders on Customers.CustomerID = Orders.CustomerID where Customers.CustomerID IS NULL or Orders.CustomerID IS NULL;

4.

Select SupplierID, SupplierName from Suppliers UNION Select ShipperID,ShipperName from Shippers;

Do ask if any doubt. Please up-vote.