IT2351-Assignment 1 Solved

1)     MySQL Workbench: Review the options and directions under “Instructor Tutorials / Accessing MySQL Workbench / Loading Textbook Files / Publisher PDFs” to gain access to MySQL Workbench.  Once you have completed your chosen process, take a screenshot of MySQL Workbench open on your computer or VM and name the image

   

2)     Textbook files and databases (if you loaded the textbook and database files without issue you can skip b-c) :  

   

a)      Check out the “create_databases” script file (this is a text file with an .sql file extension which associates it with MySQLWorkbench.)  If you open the file in notepad you will see that it looks like this (you can also open it in MySQL Workbench):  

   

 Note that the first group of lines are comments.  You will be commenting your scripts in a similar manor for your assignment submission.

 

b)     Use the steps detailed at the end of each appendix and then followed-up in chapter two to load the textbook files on your computer and databases into MySQL Workbench. The zip file with all these files can be found under the “Instructor Tutorials” link once again. A few things to keep in mind particularly if you are using the VM:

i)       If you are asked for a password use: password unless you changed it along the way.

ii)      The directions indicate to open the create_databases script in MySQL Workbench however, if you see “fetching data” messages in the “Schemas” window you may need to load the databases one at a time.  There is a script in the folder for each individual database. The issue may also be solved by closing and re-opening MySQL Workbench.

iii)    If you open a sql file by double-clicking on it which will open it in MySQL Workbench.  It is likely that your run button and other will be greyed out.  To fix this select the “Reconnect to DBMS” button which is directly below the “Tools” menu choice.  To avoid another connection (you will see another tab at the top) select “File / Open SQL script” from the menu to open your files in Workbench.

iv)    After running a database creation script, you may not see it in the “Schemas” window.  

Select the refresh button:   

  

c)       Once you load and run the script the “schema” window in MySQL Workbench will look like:   

 

 

 

 

    

  

  

 

d)     Edit the comment line that starts with “used for IT2351” with your name and the current semester.  Take a screen shot and name the image:  

 

3)     Queries using the OM database. :

Double-click on the om database so that it is bold (be sure to go through chapters 2 and 3 to prepare for the exercises).  This means that it is the database currently being used.  You can also issue the command “use om;” in the SQL window.  

 

a)      Create a query to select customer_first_name, customer_last_name, customer_city and customer_state from the customers table sorted in ascending order by state and then city.  Be sure to add comment lines at the top with your name and the purpose of the query. Take a screen shot of the result (be sure to include the entire query code – you can put it on multiple lines if necessary) as well as the portion of output that shows in the window.  Name your image   Here is an example:

  

It is never too early to check out the “Action Output” at the bottom.  If you have issues with your code it may have squigly lines under to indicate syntax errors or, you may see an error message in the output window.  If you have a problem and would like assistance from me be sure to include a screenshot as above as well as the saved sql (use the save script file button second from the left in the sql window.)

 

b)     Create a query to show the title, artist, and unit_price from the items table where the unit_price is 16 sorted by unit_price.  Be sure to include comments as in part a, take a screen shot and name the image Assignment1Part3B.   

 

c)      Using aliases: Create a query showing: customer_first_name as “First Name”, customer_last_name as “Last Name”, customer_city as “City” and customer_state as “State” from the customers table. Sort by last name and then first name.  Be sure to include comments as in part a, take a screen shot and name the image Assignment1Part3C.

 

d)     Arithmetic expressions: Create a query showing customer_id, order_date, shipped_date and the difference between these two dates from the orders table (adjust your calculation so that you output is positive numbers).  Provide an alias for each column naming the difference column: “Days_to_Ship” (the significance of the underscore is to use the alias in sorting).  Sort your output by customer_id and then by Days_to_Ship, take a screen shot, include comments, and name your image: Assignment1Part3D. Keep this query on your screen for the next part.

 

e)     As you can see queries start to get a bit lengthy, so it is good practice to save them either by saving the script file or creating a snippet. Following the steps on page 57 / Figure 2-8 create a snippet of your part d query.  Take a screen shot showing the snippet and name the image Assignment1Part3E.

 

f)       Next, save the script using the save button and name the sql file Assignment1PartFScript.sql. As you continue to develop queries you will often want to save the script file and/or create a snippet so that you can revisit and edit the code.

 

g)      Concat function: Use the concat function to show customer names as “Name” sorted by last name and then first name (fields from the listed table do not need to be included in a select statement to use them in sorting.  Include comments, take a screen shot and name your file Assignment1PartG.

 

h)     Date_Format function: Edit your query in part d to format the three date fields m/d/y. Include comments, take a screen shot and name your file Assignment1PartH.