MIS 385 ASSIGNMENT 4 (SQL 1)



UNIVERSITY OF DAYTON

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 receicving help from the instructor.

Single Table Queries using the Pine Valley Furniture Database – These will be simple queries that you can try out in SQL in MS Access. 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. In addition to those I assign, I want you to ask yourselves two questions not asked in my list, and generate queries to answer them, using the SUM function apropriately. 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.

This assignment is to be completed and submitted ELECTRONICALLY by emailing it to (salisbury@udayton.edu, 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 (or 100/100 and 10/100 if you prefer) if you get the bonus right. The assignment is worth 30 out of 175 for homework, or 3% of your course mark.

Be advised that, if you can’t figure one out, you can try QBE for now (and, having run that go to the SQL window); having said that, do not take QBE as a crutch as on the final you will be writing queries out in SQL (important for some of the web-enabled database stuff we’ll look at later). To complete the assignment, you should submit the results of your query, as well as the actual query you wrote. 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. Your question generating a query using SUM.

10. List the product ID, description, finish and standard_price for all products whose cost is greater than the average cost of all products.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download