MIS 3306 Database Management Systems Module 7-1 Exercise

Read Before Starting this Assignment:

 

· The Module 8 Exercise is not a prerequisite for this exercise.

 

· Do not use the database from Module 8 exercise. Using the database here 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.>>

 

·

 

You will build a vendor-product database and retrieve data from it. 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: Prepare the Database

 

1. Create a database and use the database.

 

· Do not use the M8 database/script for this exercise (and vice versa). Using wrong databases will result in error messages. The databases were slightly modified for their particular learning objectives.

 

· Open your Workbench. Connect to the local instance (database server).

 

· Click File Open SQL Script…, or click  to open the “DB_M7_Table&Data.sql” script.

 

· Click  to execute the script. The script creates a database “DB_M7” with tables and data.

 

Note: If you double click the SQL script, your Workbench will only open the file but will not connect to the database server. You have to follow the steps above to connect to the database server and open the script.

 

2. Insert data with your name

 

· Type the following code in the SQL query editor at the end of the “DB_M7_Table&Data.sql” script. Replace “yourname” with your first name and last name. This is required for grading. The following are the codes that you should modify.

 

INSERT INTO VENDOR VALUES(11111, yourname in apostraphe, ‘UHD’, ‘713’, ‘221-8000’, ‘TX’, ‘Y’);

 

INSERT INTO PRODUCT VALUES(‘111UHD’, yourname design in apostrophe, ‘2026-11-11’, 100, 10, 999.99, 0, 11111);

 

The codes should look like this in Workbench after you type and modify. Replace my names with your names.

 

 

 

Note: Have to put values within apostrophes when the data format is characters or dates. No apostrophe is needed when the data format is integer or decimal.

 

· Highlight the two INSERT INTO statements and click  to execute

 

· Refresh the schemas and expand it to list the tables (like the figure below).

 

Text  Description automatically generated

 

· Right click the PRODUCT table and click “Select Rows – Limit 1000”. You will see the data of your PRODUCT table. The result should list your name design computer as the first row. Use the snipping tool or Grab and take a screenshot (like the figure below).

 

Graphical user interface, text, application  Description automatically generated

 

Answer here:

 

Grading requirement: The image should clearly show your name in the first row.

 

<<Paste your image here>>

 

PART II: The SELECT Statement

 

Textbook 7-3

 

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.

 

 

 

Alternatively, you can choose the DB_M7 database in Workbench, right click and choose “ set as default schema”.

 

3. Answer all the SQL query questions like the exemplary answer here.

 

· The answer contains both the codes and the result.

 

· The answer meets the grading requirement.

 

· The answer is clear (readable).

 

List product code, description, unit price, and quantity on hand from the product table. (You do not need to answer this question).

 

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

 

 

 

4. Using column aliases

 

List product code, description, unit price, and quantity on hand from the product table. Show the description as “DESCRIPTION”, the unit price as “UNIT PRICE” and the quantity on hand as “QTY”.

 

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

 

<<Paste your image here>>

 

5. Using computed columns

 

List product description, quantity on hand, unit price, and the total value of each of the products in inventory. Make the output 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).

 

<<Paste your image here>>

 

6. Listing unique values

 

List the different vendor codes in the product table. Vendor codes should not repeat.

 

Answer here:

 

Grading requirement: 1. Include both the code and the result data (1 point for each). 2. The result should include your vendor code (0 point when the result is wrong).

 

<<Paste your image here>>

 

PART III: The FROM Clause and JOIN

 

Textbook 7-4

 

7. JOIN USING syntax (supported in Oracle and MySQL)

 

Perform a join of the product (left) and the vendor (right) table. List only product code, vendor code, and vendor name. Use the JOIN USING syntax and use the V_CODE to join.

 

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

 

<<Paste your image here>>

 

8. JOIN ON syntax

 

Perform a join of the product (left) and the vendor (right) table. List only product code, vendor code, and vendor name. Use the JOIN ON syntax and the V_CODE to join.

 

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

 

<<Paste your image here>>

 

9. Outer joins (left)

 

Perform a join of the product (left) and the vendor (right) table. List only product code, vendor code, and vendor name for all products, including the products with no matching vendors.

 

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

 

<<Paste your image here>>

 

10. Outer joins (right)

 

Perform a join of the product (left) and the vendor (right) table. List only product code, vendor code, and vendor name for all products, including the vendors with no matching products.

 

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

 

<<Paste your image here>>

 

11. Joining tables with an alias

 

Perform a join of the product (left) and the vendor (right) table. List only product description, product price, vendor name, vendor area code, and vendor phone. Use the JOIN ON syntax and the V_CODE to join. Use alias names for the table names.

 

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

 

<<Paste your image here>>

 

PART IV: The ORDER BY Clause

 

Textbook 7-5

 

12. The ORDER BY clause: Ascending order

 

List product code, description, unit price, and quantity on hand from the product table. List the content by price in ascending order.

 

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

 

<<Paste your image here>>

 

13. The Order By clause: Descending order

 

List product code, description, unit price, and quantity on hand from the product table. List the content by price in descending order.

 

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

 

<<Paste your image here>>

 

PART V: The WHERE Clause

 

Textbook 7-6

 

14. Selecting rows with conditional restrictions: Equal to

 

List product description, quantity on hand, unit price, and vendor code from the product table, with a vendor code of 11111.

 

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

 

<<Paste your image here>>

 

15. Selecting rows with conditional restrictions: Not equal to

 

List product description, quantity on hand, unit price, and vendor code from the product table, with a vendor code other than 11111.

 

Answer here:

 

Grading requirement: 1. Include both the code and the result data (1 point for each). 2. The result should not include your name (0 point when the result is wrong).

 

<<Paste your image here>>

 

16. Selecting rows with conditional restrictions: Greater than

 

List product description, quantity on hand, minimum stock, and unit price from the product table, with the unit price greater than 100.

 

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

 

<<Paste your image here>>

 

17. Selecting rows with conditional restrictions: For date/time data type

 

List product description, quantity on hand, minimum stock, unit price, and stocking date from the product table, with the stocking date on or after January 1, 2026 (in MySQL, the date format is YYYY-MM-DD).

 

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

 

<<Paste your image here>>

 

PART VI: The Logical Operators: AND, OR, and NOT

 

Textbook 7-6

 

18. The OR operator

 

List product description, quantity on hand, unit price, and vendor code from the product table, with a vendor code of 21225 or 11111.

 

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

 

<<Paste your image here>>

 

19. The AND operator

 

List product description, quantity on hand, minimum stock, and unit price from the product table, with the unit price greater than 100 and quantity on hand greater than 10.

 

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

 

<<Paste your image here>>

 

20. Using more than one operator

 

List product description, quantity on hand, minimum stock, and unit price from the product table. The result should meet both of the following two conditions:

 

· The V_CODE is either 21225 or 11111.

 

· The P_PRICE is greater than 40.

 

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

 

<<Paste your image here>>

 

21. For the two operators AND and OR…

 

Answer here:

 

The DBMS executes the ______ operator before the ______ operator, when no parenthesis presents.

 

22. The NOT operator

 

List all columns form the product table, for products whose vendor code is not 21344. Must use the NOT operator.

 

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

 

<<Paste your image here>>

 

PART VII: Special Operators: BETWEEN, IN, LIKE, and IS NULL

 

Textbook 7-6

 

23. Using BETWEEN

 

List all columns form the product table, for products whose prices are between $100 and $1000. Use BETWEEN.

 

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

 

<<Paste your image here>>

 

24. Using IN

 

List all columns form the product table, for products whose vendor code is 21225 or 11111. Use IN.

 

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

 

<<Paste your image here>>

 

25. Using LIKE

 

List name, contact, area code, and phone number from the vendor table, with a contact name beginning with Smith.

 

Answer here:

 

Grading requirement: 1. Include both the code and the result data (1 point for each). 2. The result should not include your name (0 point when the result is wrong).

 

<<Paste your image here>>

 

26. Using IS NULL

 

Find the product whose V_CODE does not contain a value. List its product code, description, and vendor code.

 

Answer here:

 

Grading requirement: 1. Include both the code and the result data (1 point for each). 2. The result should not include your name (0 point when the result is wrong).

 

<<Paste your image here>>

We offer the bestcustom writing paper services. We have done this question before, we can also do it for you.

Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.