Information Systems Information Systems MIS 3306 Assignment
MIS 3306 Database Management Systems
Module 7-1 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 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)) |
|
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).
· 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).
|
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>>
|
16