Description

Assignment #6

IMPORTANT: These instructions supersede any instructions given in your textbook.  Be sure to follow these instructions.  Contact the instructor prior to the due date if anything is not clear.

The following exercises are to be addressed using the MySQL database and the MySQL Workbench tool.  To begin your work, you may wish to drop and recreate fresh copies of the exercise databases, using the SQL Scripts provided in the course resources, if you have made any changes to the data since the databases were created.

Each exercise requires that you compose a new query using the query editor window in MySQL Workbench.  When you have completed an exercise, and are satisfied that your query is correct, you are to take a screen shot of the MySQL Workbench screen, making sure that the entire text of the query code is visible, and as much of the  result set as possible is also displayed.  Screen shots are to be cropped and size so as to be readable.  Paste a copy of the screen shot graphic into your answer sheet, followed by a copy of the TEXT of your query.  Number each answer to correspond with the exercise number.  A sample appears below:

Sample Problem:

1.       List the Customer Name and Rep number for all customers in the city of Fullton, CA.

Sample Answer:

When you have completed the assignment, upload your answer sheet (Microsoft Word format is required -- .docx or .doc format) to the appropriate assignment in the online course shell, by the due date.

Important Reminders: ONLY those fields that are specified (or otherwise specifically required by the problem statement) should be listed (visible) in your query results.  Answers must be complete and correct in all details.  Keep in mind that solutions must be logically correct, not merely “coincidentally” correct with respect to the particular data in the exercise databases.  For example, if the problem directs you to list all fields in the employee table where the employee last name is “Walton”, you may coincidentally find that SELECT * FROM EMPLOYEE WHERE LNAME LIKE ‘Walt%’ returns only employees with last name “Walton”, because of the coincidence that the only employee last name in the database with the first four letters “W,a,l,t” happens to be “Walton”.  However, this is logically incorrect, as it would also return “Waltmire”, “Walte”, “Walter” and other surnames beginning with “Walt” if they existed.  In such cases, the logically incorrect answer will be marked wrong, even though (for the given sample data set), only the records for “Walton” were returned.

Using the TAL Distributors MySQL database and MySQL Workbench, create the queries to solve the following:

1.       Using a join query (implicit or explicit join, as you prefer), list the number and name of all customers represented by Megan Gradey.

2.       Give the item number, description, and on-hand value (OnHand * Price) for each item in category GME. Be sure to name the calculated field, "On-hand Value".

3.       Using SQL similar to the example shown below, create and populate a new table named Toy to contain the columns ItemNum, Description, OnHand, and Price for all rows on which the category is TOY.  NOTE: MySQL does not currently implement a “SELECT INTO TABLE” query construct.  However, there is an alternative construct. Example:

create table mynewtable (select * from myoldtable)

You would replace the existing select query now residing within the parentheses with the select query satisfying the problem description, and replace “mynewtable” with the name of the table you wish to create: toy.  Note: be sure your screenshot includes the output window showing the results of successfully running your SQL.  It should be similar to:

4.       Create an update query to change the description of item DL51 to “Classic Train Set” in the Toy table.  Note: be sure your screenshot includes both the complete SQL  code AND the output window showing the results of successfully running your SQL.

5.       Create a query to delete every row in the Toy table in which the price is greater than $120.  Note: be sure your screenshot includes both the complete SQL  code AND the output window showing the results of successfully running your SQL.

Using the Solmaris Condominium Group MySQL database and MySQL Workbench database, create the queries to solve the following:

6.       Using the BETWEEN operator, list the location number and unit number for each condo whose condo fees are between $200 and $300 (inclusive) per month.

7.       Using the IN operator, list the owner number and last name for all owners who live in Florida (FL), Georgia (GA), or South Carolina (SC).

8.       List the location number, unit number, square footage, and condo fee for all units. Sort the results by condo fee within the square footage. Hint: make SqrFt the major sort key, and CondoFee the minor sort key.

9.       Create a query using COUNT and GROUP BY to show how many two-bedroom condos are located at each location.  Note: Display only the LocationNum, and the Count.

10.   Using the SUM function, calculate the total condo fees Solmaris receives each month.  Note: display only the sum.