ITEC 3335
Database Development
Fall 2017
Homework #3
(1) Install MySQL in your computer. You may also install xampp instead of just MySQL. Install the 'Big' PVFC database of the textbook in MySQL and call the database 'bigpvfc'. You may execute the sql file, bigpvfc.sql (provided separately), in MySQL console.
Provide the MySQL commands for the following queries.
(a) All customer information for those living in New York (NY).
+------------+-------------------+--------------------+--------------+---------------+--------------------+
| CustomerId | CustomerName | CustomerAddress | CustomerCity | CustomerState | CustomerPostalCode |
+------------+-------------------+--------------------+--------------+---------------+--------------------+
| 3 | Home Furnishings | 1900 Allard Ave | Albany | NY | 12209-1125 |
| 8 | Dunkins Furniture | 7700 Main St | Syracuse | NY | 31590 |
| 9 | A Carpet | 434 Abe Dr | Rome | NY | 13440 |
| 16 | ABC Furniture Co. | 152 Geramino Drive | Rome | NY | 13440 |
+------------+-------------------+--------------------+--------------+---------------+--------------------+
4 rows in set (0.00 sec)
(b) List the product id, description and standard price of all products with a standard price between $200.0 to $400.0.
+-----------+-------------------------+----------------------+
| ProductID | ProductDescription | ProductStandardPrice |
+-----------+-------------------------+----------------------+
| 2 | Birch Coffee Tables | 200.0000 |
| 5 | Writer's Desk | 325.0000 |
| 9 | 96" Bookcase | 225.0000 |
| 10 | 96" Bookcase | 200.0000 |
| 14 | Writer's Desk | 300.0000 |
| 17 | High Back Leather Chair | 362.0000 |
| 21 | Pine End Table | 256.0000 |
+-----------+-------------------------+----------------------+
7 rows in set (0.00 sec)
(c) List the names and birthdates of employees without missing information in their birthdates.
+------------+----------------+---------------------+
| EmployeeId | EmployeeName | EmployeeBirthDate |
+------------+----------------+---------------------+
| 123-44-345 | Phil Morris | 1957-01-05 00:00:00 |
| 332445667 | Lawrence Haley | 1963-08-15 00:00:00 |
| 454-56-768 | Robert Lewis | 1964-08-25 00:00:00 |
| 555955585 | Mary Smith | 1969-05-06 00:00:00 |
+------------+----------------+---------------------+
4 rows in set (0.00 sec)
(d) List some information of raw materials made of walnut with a width of 9 or more in the following format.
+--------------+-------------------------------+-----------+-------+----------+--------+
| MaterialId | MaterialName | Thickness | Width | Material | price |
+--------------+-------------------------------+-----------+-------+----------+--------+
| 1-1/21010WAL | 1-1/2in X 10in X 10ft Walnut | 1-1/2 | 10 | Walnut | 67.5 |
| 1-1/21012WAL | 1-1/2in X 10in X 12ft Walnut | 1-1/2 | 10 | Walnut | 81 |
| 1-1/21016WAL | 1-1/2in X 10in X 16ft Walnut | 1-1/2 | 10 | Walnut | 108 |
...
| 61216WAL | 6in X 12in X 16ft Walnut | 6 | 12 | Walnut | 648 |
| 6124WAL | 6in X 12in X 4ft Walnut | 6 | 12 | Walnut | 162 |
| 6128WAL | 6in X 12in X 8ft Walnut | 6 | 12 | Walnut | 324 |
+--------------+-------------------------------+-----------+-------+----------+--------+
130 rows in set (0.00 sec)
(e) List some information of raw materials with a standard price more than or equal to $1000.0 or less than or equal to $0.1 in the following format.
+------------+----------------------------+-----------+-------+-----------------+---------+
| MaterialId | MaterialName | Thickness | Width | Material | Price |
+------------+----------------------------+-----------+-------+-----------------+---------+
| 101016CHE | 10in X 10in X 16ft Cherry | 10 | 10 | Cherry | 1000 |
| 101216CHE | 10in X 12in X 16ft Cherry | 10 | 12 | Cherry | 1200 |
| 101216WAL | 10in X 12in X 16ft Walnut | 10 | 12 | Walnut | 1080 |
...
| NUT3/8 | Upholstery Tack 3/8in | NULL | NULL | Upholstery Tack | 0.01875 |
| NUT5/8 | Upholstery Tack 5/8in | NULL | NULL | Upholstery Tack | 0.02875 |
| NUT7/8 | Upholstery Tack 7/8in | NULL | NULL | Upholstery Tack | 0.04375 |
+------------+----------------------------+-----------+-------+-----------------+---------+
94 rows in set (0.01 sec)
(f) List the product descriptions and their standard prices of all Oak finished product in the descending order of prices.
+-----------+----------------------+---------------+----------------------+---------------+---------------+-------+
| ProductID | ProductDescription | ProductFinish | ProductStandardPrice | ProductOnHand | ProductLineID | Photo |
+-----------+----------------------+---------------+----------------------+---------------+---------------+-------+
| 19 | 7' Grandfather Clock | Oak | 1100.0000 | 0 | 4 | NULL |
| 18 | 6' Grandfather Clock | Oak | 890.0000 | 0 | 4 | NULL |
| 12 | 8-Drawer Dresser | Oak | 800.0000 | 0 | 1 | NULL |
| 3 | Oak Computer Desk | Oak | 750.0000 | 0 | 1 | NULL |
| 11 | 4-Drawer Dresser | Oak | 500.0000 | 0 | 1 | NULL |
| 5 | Writer's Desk | Oak | 325.0000 | 0 | 2 | NULL |
| 10 | 96" Bookcase | Oak | 200.0000 | 0 | 3 | NULL |
| 8 | 48" Bookcase | Oak | 175.0000 | 0 | 3 | NULL |
+-----------+----------------------+---------------+----------------------+---------------+---------------+-------+
8 rows in set (0.00 sec)
The solutions should be put into a single text file, with proper description. Name the file: H3_<<Your name>>_<<Your Student ID>>.sql. Your TA should be able to execute your sql file directly for grading.
As usual, submit the homework through Blackboard.