Mysql Exercise

MySQL Exercise

Remarks: This exercise is intended to help students write simple SQL join statements. Students are responsible for other aspects of SQL as well. But, because the ability to write a simple join statement is such a fundamental skill, it merits special attention.

Scenario: In our imaginary use case, we have an inventory of items (Halloween Masks that look like famous US Presidents). A customer may make a purchase from our online store. Our system is to collect order information, and then print an invoice to be included with the order when it is shipped. The printed invoice has data from several different entities printed on it: the customer, the order, and the employee who fulfilled the order. The invoice contains line item detail indicating each product ordered, the quantity ordered, and the product’s price.

Technical Details: There are companion files to this document – helpWithSQL-Setup.txt, helpWithSQL.txt and (to be posted at a later date) helpWithSQL-Solution.txt.

The file helpWithSQL-Setup.txt will be run against your own database on the class server. The file helpWithSQL-Setup.txt contains all the create table statements necessary to implement invoice generating system and populate those tables with some data.

This setup file is an implementation of this ERD diagram:

And this schema:

To do:

1.  Using credentials assigned to you on the MIS community gradebook, log into your account on http://www.mis3501.xyz/phpmyadmin

2.  Click on the “change password” button. Set your password to something the is easy for you to remember, but hard for others to guess. Do not share your password with other students. Choose a new password, not used on other systems.

3.  Click on the database schema assigned to you.

4.  Choose the Import option from the phpMyAdmin interface. Then click “Choose File”.

5.  Select the helpWithSQL-Setup.txt file you downloaded and then click the “Go” button.

6.  Open helpWithSQL-Setup.txt in Notepad++ (or TextWrangler). Observe how an order is created. The first step in the purchase process is the creation of a unique order number. After that, one or more products are added as line items in the order. Close helpWithSQL-Setup.txt.

7.  Choose the SQL option from the phpMyAdmin interface. Type in the following commands (one at a time) then click “go” after each one.

a.  select * from `Order`;

b.  select * from Product;

c.  select * from LineItem;

8.  Now open helpWithSQL.txt in Notepad++ or TextWranger. The file helpWithSQL.txt contains several questions. You are asked to write a number of successively more difficult select statements.

9.  Composing your answers to the questions in Notepad++ or TextWrangler. Test each one of your answers by copy / pasting your SQL statement into phpMyAdmin and running it.

10.  When you are all done, save the changes you made to helpWithSQL.txt and email this file as an attachment to your instructor.
Give your email message a subject of “MIS3501 MySQL Exercise”