CIS 331 – Database Management Using SQL

Week 6 Homework - Due: Week 7

Name ¬¬¬

1. Review Questions – page 207 (A Guide to MySQL – Chapter 7)
Questions 1 - 19 (type out the answers)

2. Premier Products – pages 207 - 209 (A Guide to MySQL – Chapter 7) Exercises 1 – 9 (create in MySQL)
Except where indicated, use MySQL Query Browser to perform each operation and print the results.

3. Henry Books – pages 209 (A Guide to MySQL – Chapter 7) Exercises 1 – 9 (create in MySQL)
Except where indicated, use MySQL Query Browser to perform each operation and print the results.

Estimated Completion Time: ___________ Comments: _______________________________

Lab Assignment Grade:
GRANT ALL ON PART TO ASTON;
GRANT UPDATE (ALL) ON ORDERS, ORDER_LINE TO KELLY, MORGAN;
GRANT UPDATE (PRICE) ON PART TO JAMES;
GRANT DELETE (ALL) ON CUSTOMERS TO DANIELSON;
GRANT SELECT (CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP ) ON CUSTOMER TO PUBLIC;
GRANT INDEX ON ORDERS TO PEREZ;
GRANT ALTER ON PART TO WASHINGTON;
GRANT ALL ON ORDERS TO GRINSTEAD;

5. REVOKE ALL FROM ASTON
6. mysql> CREATE INDEX PART_INDEX1 ON ORDER_LINE(PART_NUM);
Query OK, 8 rows affected (0.58 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX PART_INDEX2 ON PART(CLASS);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX PART_INDEX3 ON PART(CLASS, WAREHOUSE);
Query OK, 10 rows affected (0.42 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX PART_INDEX4 ON PART(CLASS DESC, WAREHOUSE);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> DROP INDEX PART_INDEX3 ON PART;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

SHOW TABLES;
SHOW COLUMNS FROM PART;
SHOW INDEX FROM PART;
SHOW GRANTS FOR ASHTON;

mysql>