Assignment 2.5A In this assignment, we walk you
Assignment 2.5A
In this assignment, we walk you through joins which use more than two tables.
Here’s what you are going to do for this assignment.
· Review this document. There are several parts to this document.
o Part 1 is instructions only. There is nothing to turn in, but it contains important information.
o Part 2 of this document is called the TURN IN part. This contains something you should turn in for this assignment.
· Go through this document and write SQL to answer all the questions listed below. As part of your SQL, you will capture screenshots and paste them where indicated.
· Refer to this document as you complete the online quiz which verifies your homework.
· You are welcome to use the quiz results to update your homework file.
· When you have completed the online quiz, submit the TURN IN portion of this document in the Assignment dropbox.
· Remember, you can always reach out for help to your FA or instructor if your SQL is not behaving for you.
· Your completed deliverables for this assignment are:
o The TURN IN section of this document, submitted to the LEO dropbox
o The completed online quiz results
· There is no deliverable of a SQL script for this assignment. You are welcome to create one for your own records, but the TURN IN section of this document will suffice for your faculty.
· And, you probably already know this by now, but you can always reach out to your FA for assistance.
Purpose: get more practice with multi-table SQL queries. We will practice
· Complex joins
· LEFT and FULL joins
· Order by and Group by
Part 0 – Get into your Folder
We will continue to work in the “Week2” folder inside your “DATA620” folder. Download everything into this folder and run it from there.
Part 1 – Fire up the Warehouse database
1. Go ahead and fire up MySQL Workbench and connect to your Azure MySQL database.
2. Download and run the following scripts. They will install a new database schema called “warehouse” which contains information about your customers, your products, your employees, and your shipments.
a. Warehouse_setup.sql – this will create a database schema called “warehouse”
b. Warehouse_insert.sql – this will populate your database with information about your customers, products, employees, and shipments. (Note there may be a numeric suffix on the end of your sql, so its name may be ‘warehouse_insert_v04.sql’ for example. The information in your database may or may not be exactly the same as in this example. That’s OK.)
c. You can make sure that all tables are properly populated by executing;
SELECT * FROM warehouse.<replace-table-name>;
Where <replace-table-name> is in {customer, employee, product, shipment}
d. The insert SQL also contains one query at its bottom, which should give you a list of the company names and shipments. The first one should be from ABC Incorporated, with an idShipment of 1200 and a Quantity of 9.
3. Let’s reverse engineer this database to see what the EER looks like.
a. Remember how? Database -> Reverse Engineer and then click “next” until it gives you the EER. I needed to drag my tables a little bit apart so I could see them all.
b. You can export your diagram using File -> Export -> Export as PNG. You can also grab a screenshot.
c. Verify you can see one primary key for each table. The primary key is denoted by a golden key icon.
d. Verify you understand the relationships. You should be able to see that a customer, product, and employee can exist independently.
e. You should also be able to see that a shipment ties a quantity ordered with the customer who ordered it, the product which was ordered, and the employee who fulfilled the order. A shipment also contains a Scantag and a Reference Number, two internal notations the company sometimes uses.
4. You may want to refresh your memory about how to do three-table joins. In the WWW3 Schools SQL instructions, you can see the “SQL Inner Join” on the left menu, and halfway down that page there’s a note about how to JOIN three tables.
5. There is nothing to turn in from this part.