Information Systems Information Systems MIS 3306 Assignment
FaizLakhani
MIS3306Module7-2Exercise1.docxMIS 3306 Database Management Systems
Module 7-2 Exercise
Required for Grading (Type Your Name Below):
I am (type your full name) and I complete this assignment following the UHD academic integrity policy.
Read Before Starting this Exercise:
· Prerequisite: Must complete the Module 7-1 Exercise before working on this exercise.
· Use the DB_M7 database from the Module 7-1 exercise. Using other databases will result in errors or wrong answers.
· ALL the SQL answers can be found in the textbook Chapter 7. Slight modifications on column names or values may be needed.
· Keep in mind that the database server will not keep a copy of your SQL codes. Therefore, please save your SQL codes as SQL script files (*.sql), for your own reference.
· Your answer is required when you see the red answer box like the box below.
|
Answer here: <<This is an example. Answer whenever you see this.>>
|
· Answer all the SQL query questions like the examplary answer here.
· The answer contains both the codes and the result.
· The answer meets the grading requirement.
· The answer is clear (readable).
|
Answer here: Grading requirement: 1. Include both the code and the result data (1 point for each). 2. The result should include your name (0 point when the result is wrong).
|
You must use the DB_M7 database, which a vendor-product database from Module 7-1 Exercise. The ERD and the data dictionary are shown below.
|
Symbol |
Meaning |
|
|
Primary key |
|
|
Foreign key |
|
|
Column (Not null) |
|
|
Column (Could be null) |
|
|
Attribute (CUS_CODE) and its data type (INT(11)) |
|
TABLE |
COLUMN |
Content |
TYPE |
PK or FK |
FK REFERENCE |
|
PRODUCT |
P_CODE |
Product code |
VARCHAR(10) |
PK |
|
|
|
P_DESCRIPT |
Product description |
VARCHAR(35) |
|
|
|
|
P_INDATE |
Stocking date |
DATETIME |
|
|
|
|
P_QOH |
Units available |
SMALLINT(6) |
|
|
|
|
P_MIN |
Minimum units |
SMALLINT(6) |
|
|
|
|
P_PRICE |
Product price |
DECIMAL(8,2) |
|
|
|
|
P_DICSOUNT |
Discount rate |
DECIMAL(5,2) |
|
|
|
|
V_CODE |
Vendor code |
INT(11) |
FK |
VENDOR(V_CODE) |
|
VENDOR |
V_CODE |
Vendor code |
INT(11) |
PK |
|
|
|
V_NAME |
Vendor name |
VARCHAR(30) |
|
|
|
|
V_CONTACT |
Contact person |
VARCHAR(50) |
|
|
|
|
V_AREACODE |
Phone area code |
CHAR(3) |
|
|
|
|
V_PHONE |
Phone number |
CHAR(8) |
|
|
|
|
V_STATE |
State |
CHAR(2) |
|
|
|
|
V_ORDER |
Previous order |
CHAR(1) |
|
|
PART I: Use and Check the Database
1. If you have closed Workbench earlier and just reopen to continue your work, you need to “use” the database before executing commands into the database. Execute the following code.
2. Check the data of your PRODUCT table. The result should list your name design computer as the first row. If your PRODUCT table does not include your name design product, you should rebuild the DB_M7 database.
PART II: Aggregate Processing:
COUNT, MIN, MAX, SUM, AVG, GROUP BY, and HAVING
Textbook 7-7
3. COUNT
Calculate the total number of products.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each).
<<Paste your image here>>
|
4. COUNT
Determine the number of products having a price that is less than $40.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each).
<<Paste your image here>>
|
5. COUNT
How many different vendors are in the PRODUCT table?
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each).
<<Paste your image here>>
|
6. MIN and MAX
Retrieve the highest and lowest prices In the PRODUCT table in a single query.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each).
<<Paste your image here>>
|
7. SUM
Find the total value of all items carried in inventory.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each).
<<Paste your image here>>
|
8. AVG
Compute the average price of the products.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each).
<<Paste your image here>>
|
9. GROUP BY
Calculate the average price of the products provided by each vendor.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each).
<<Paste your image here>>
|
10. GROUP BY and JOIN
List the vendor code, vendor name, the number of products for each vendor, and the average product price for each vendor.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each). 2. The result should include your name.
<<Paste your image here>>
|
11. GROUP BY and JOIN
List the vendor code, vendor name, the total of quantity on hand for each vendor, the number of products for each vendor, and the average product price for each vendor.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each). 2. The result should include your name.
<<Paste your image here>>
|
12. HAVING
Generate a listing of the number of products in the inventory supplied by each vendor. LIMIT THE LISTING TO PRODUCTS WHOSE PRICE AVERAGE GREATER THAN $40.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each).
<<Paste your image here>>
|
13. HAVING
Reorder and execute the following SQL statement.
· FROM PRODUCT JOIN VENDOR ON PRODUCT.V_CODE=VENDOR.V_CODE
· GROUP BY V_CODE
· HAVING SUM(P_QOH*P_PRICE)>1000
· ORDER BY SUM(P_QOH*P_PRICE) DESC;
· SELECT PRODUCT.V_CODE, V_NAME, SUM(P_QOH*P_PRICE) AS TOTALCOST
· WHERE P_DISCOUNT=0
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each). 2. The result should include your name.
<<Paste your image here>>
|
PART II: Subqueries
Textbook 7-7
14. WHERE Subqueries
List the product codes, product names, and prices with a price greater than or equal to the average product price.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each). 2. The result should include your name.
<<Paste your image here>>
|
15. IN Subqueries
List the product codes, product names, and prices for the products whose vendor’s area codes are in 615 or 713.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each). 2. The result should include your name.
|
16. IN Subqueries
List the vendor codes and names for the vendors whose product prices are greater than $40.
|
Answer here: Grading requirement: 1. Include both the code and the result data (1.5 point for each). 2. The result should include your name.
<<Paste your image here>>
|
16
