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>>

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.