Practice creating well-formed tables, and doing aggregate and subquery SQL.
1. Create a document into which you can type text and paste images using MS Word, open office Word, or a plain text editor like notepad++ or sublime.
2. Write your name and the date at the top of the document.
3. Complete Exercise 2.40 parts A, B, C, D, E on p. 92 of the text. Use MySQL Workbench to create and populate a STUDENT table as specified in the problem. (Remember, you must click Apply before data you enter will be checked for errors.) -- Name your table STUDENT0 to keep it from being confused with the Student table we are using in demos in class.
For question A, make a screenshot of the ‘edit table data’ screen on the table you construct in MySQLWorkbench and paste the screenshot into your document.
For part E in both 2.40 and 2.41, you must specify the well-formed relations using the book’s notation for describing a relation:
TableName(field1, field2, field3, etc.)
Primary key fields underlined.
Foreign key fields italicized.
Hints:
· In both of these exercises from the text, remember each relation you write as an answer to one of the questions must meet all the rules for a relation.
· The phrase “Assume that the values of SiblingName are the names of all of a given student's brothers and sisters;” in the question does not mean you can place multiple sibling names in single cell. Multiple values in a single cell violates the rules of a relation.
· In non-well-formed relations, you will have to enter duplicate data.
· In questions 2.40a), b), and 2.41a), b), c), it is NOT LEGAL to add fields to the table the problem has specified.
4. Complete Exercise 2.41 parts A, B, C, E on p. 92 of the text (you do not need to do part D). Use MySQL Workbench to create and populate a new version of the STUDENT table as specified in the problem. (Name this new table STUDENT1.)
For questions A, B, C, make screenshots of the ‘edit table data’ screen on the table you construct in MySQLWorkbench and paste the screenshot into your document.
5. Cut and paste the questions below into your document:
1. Show a count of the total number of animals in the Animal table.
2. From the Animal table, show the name of each category and a count of the number of animals in this category.
3. Show the DateBorn of the oldest animal.
4. Show the DateBorn of the oldest registered animal.
5. Show a list of all categories and breeds of animals such that the query result has no duplicate records.
6. Show the OrderId, AnimalID and Cost of all animals whose name start with the letter ‘G’. (Hint: you need a subquery for this problem.)
7. Show all fields from the Sale table for sales made by employees hired before August 1, 1998 ordered by EmployeeID. (Hint: specify a date literal as ‘yyyy-mm-dd’)
8. Show all fields from the Sale table for sales to customers living in Hawaii. (Hint: Need a subquery with a subquery for this one. Note: In mysqlWorkbench, both the edit table operation and select * from table query display only the first 1000 records of a table, so you will have to say select * from City where State = ‘HI’ to see the Cities in Hawaii.)
Here is a zip file containing initialization files for the version of the petStore database used in this lab. Use the same process you did in lab1 to unpack, edit and use these files to initialize the lab 2 petStore database. (Here is the link to the lab1 instructions. Remember to edit the path to each .csv file shown in initPetStoreLab2.sql. You may use file | open SQL script in MySQL workbench to read the file into an SQL window after you have edited it rather than copying and pasting the script from your editor to MySQL Workbench.)
6. In MySQL Workbench, create SQL statements that will
answer the questions shown above. Paste the SQL below the question it
answers in your document.
Please do NOT preface your table names with your database name, e.g.,
submit queries containing table name Animal NOT usr_nordquip_0.Animal.
Fully qualified queries fail when I try to execute them.
7. Submit your assignment on moodle by clicking on the Lab 2 assignment. NOTE: You can continue to add to your assignment as long as you click the Edit Submission button, but you must click the Submit Assignment button, then the ‘my own work’ checkbox, then the Continue button to finalize your submission.
2. Here is the page in the book showing questions 2.40 and 2.41.
· Note the underlined field names denote the primary key for the relation.
· When multiple fields are underlined, it means the primary key is a composite key.
