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