Database Systems
Lab 7
Important Notes:
• The question in this lab is based on the ZAGI database from the previous lab.
• For questions that require writing/running SQL query, take a snapshot. The snapshot MUST have the following:
o Font size 18
o Part of your desktop background.
o The date time of your computer.
o The SQL code, output and the output action.
• Snapshot that does not contain ALL the above will not be graded
• A sample of an acceptable snapshot is below.
• Snipping tools:
o Windows OS: snipping tool
o Mac: use Command + Shift + 4
• All the answers should be placed in the provided answers sheet (can be downloaded on Canvas)
• This is an individual assignment (i.e. group work will be assigned a grade of zero)
Use MySQL Workbench to login to MySQL Server using the connection you used to do the previous lab.
Write the SQL queries/statements that accomplish the following tasks in the ZAGI Retail Company Sales Department Database (for each question you need to take a snapshot, see above for details):
Note: Questions (1-8) are from Chapter 5 in textbook:
1. E5.1.8
2. E5.1.9
3. E5.1.10
4. E5.1.20
5. E5.1.21
6. E5.1.22
7. E5.1.24
8. E5.1.25
9. Write SQL statement to add a new column named CustPhone to the CUSTOMER table; make sure to choose appropriate datatype.
10. Write SQL query to update the values in column you created in Question 9 with the value zero (i.e. all customers will have the value 0 as their phone number.
11. Write SQL statement to drop the column you created in Question 9.
12. Write SQL query to display the productid and productname for the products with no productprice in the PRODUCT table. Note that your query may not return any rows if all products have prices.