UNIVERSITY OF DAYTON
MIS 385/MBA 664 ASSIGNMENT 4 (SQL 1 – Single Table Queries)
See syllabus for due date.
This assignment is an individual assignment – you are to do it yourself, only receiving help from the instructor.
Single Table Queries using the Pine Valley Furniture Database – These will be simple queries that you can try out using MySQL. First, you need to run the code to build the PVFC database in your MySQL instance. The .sql file is available on the course website (right next to this assignment). Please be advised that you will need to open it using a text editor if you want to work on it; this said you should be able to just run it (it worked on the MySQL instance on my office machine). Then do the following:
For at least some of them the syntax will come pretty much from what you might find in the text examples, but even in that case the practice of putting them in will be useful. The questions may not specifically tell you which table to look at (although it should be reasonably); that’s part of learning to translate a need for information into a query that can generate meaningful data from the database. There’s also a bonus you can try out. The SQL is fairly simple for this assignment (save the bonus, and that one’s not bad either) but this way you can figure out how to navigate around MySQL. Remember – you must have the syntax right on what you turn into me.
This assignment is to be completed and submitted ELECTRONICALLY by emailing it to (, subject line = DATABASE ASSIGNMENT 4). After completing each query, you should CUT AND PASTE the text of the SQL statement for that query into this Word document (after each question).
Max score is 10/10, plus 1/10, total 11/10 if you get the bonus right.
The tables are fairly small, so you should be able to self-check to see if your queries are working right. The answers will have all fields from the given table, unless otherwise stated.
1. List all customers in for Pine Valley Furniture from Florida.
2. Add yourself as a customer to the Pine Valley database.
3. Update the price of 8-drawer desks to $900.
4. We just got a notice of a recall on Entertainment Centers (apparently the doors are falling off and hitting children on the head). First, find out what the product ID is by running a query against product_t. Note: DO NOT use the product ID in the where clause for this.
5. Apparently every Entertainment Center up to and including Order #1005 is OK, but after that we’ve got problems. Now find all orders for the product you found in #4 (actually it’s product #4, but part of the point here is to just do queries) taken after order # 1005. Note: in this one you will have to use the product ID to do only a one-table query.
6. Find all products finished to look like Ash, and generate two lists; ordered by price from lowest to highest, and by price from highest to lowest.
7. List the Average, Highest and Lowest prices and number of products for each product line – give the fields in the query results a meaningful name (e.g. Average_Price for the average price) of your choosing. List in descending order by average price.
8. List the Average, Highest and Lowest prices and number of products for each product line – give the fields in the query results a meaningful name (e.g. Average_Price for the average price) of your choosing. List in descending order by average price, but only do this for product lines having fewer than 3 products.
9. Create a table named worksin_t with the following parameters:
EmployeeID text with ten characters, which will be a primary key
WorkCenterID with 12 characters, also a primary key
Since this is an intersection table in a many-to-many relationship both will need to be foreign keys referencing their respective tables (Employee_T and WorkCenter_T).
It will also need to do a cascade delete if records are removed from a parent table.
Insert a record from the Employees and WorkCenters in the database into this table.
10. Write a query that sums quantity of items in all orders. Hint: order_line_t.
11. BONUS: List the product ID, description, finish and standard_price for all products whose cost is greater than the average cost of all products.