23 Jul MIS 3306 Database Management Systems Module 7-2 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).
A picture containing graphical user interface Description automatically generated
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))
Diagram Description automatically generated
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.
Graphical user interface, text, application Description automatically generated
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>>
