CIS336 Final Exam New – Study Guide in MYSQL – Instant Delivery – Perfect Solution

Lab Price = $23
Please feel free to send us your queries at:
P.S: This is just a study guide. The questions may not appear exactly like this.
Payment methods

Add to Cart

Buy Now

View Cart

Problem Statement

Question 1. 1. (TCO 2)
A _____ relationship must be implemented by creating a new entity that has 1:M relationships with the two original entities. (Points: 4)
Question 2. 2. (TCO 2) _____ integrity dictates that the foreign key must contain values that match the primary key in the related table or must contain null. (Points : 4)

Question 3. 3. (TCO 8) Which command is used to remove a table named products in MySQL? (Points : 4)
DROP TABLE products;
REMOVE TABLE products;
DELETE TABLE products;

Question 4. 4. (TCO 6) The special operator used to check if a value matches one of a list of specific values is _____. (Points : 4)

Question 5. 5. (TCO 2) A _____ key is a key that is composed of more than one attribute. (Points : 4)

Question 6. 6. (TCO 3) When looking at a relationship between two tables on an ERD, the child table can be identified by the presence of a _____ constraint. (Points : 4)

Question 7. 7. (TCO 3) When a constraint is created at the _____ level in a CREATE TABLE command, the constraint definition is simply included as part of the attribute definition. (Points : 4)

Question 8. 8. (TCO 7) The SQL aggregate function that determines the highest value in a given column is _____. (Points : 4)

Question 9. 9. (TCO 7) Which join syntax is required if the primary key and foreign key have different names? (Points : 4)
Natural join
Any can be used

Question 10. 10. (TCO 6) When using the MySQL DATE_FORMAT function, which code displays a full month name? (Points : 4)

Question 11. 11. (TCO 3) Which keyword is used to require that a value be entered for the attribute? (Points : 4)

Question 12. 12. (TCO 2) When identifying potential entities for an ERD, look for the _____ in the business narrative. (Points : 4)

Question 13. 13. (TCO 2) To apply the second normal form, you move columns that don’t depend on the entire primary key to another table and establish a relationship between the two tables. This _____. (Points : 4)
reduces redundancy but makes maintenance more difficult
reduces redundancy and makes maintenance easier
increases redundancy but makes maintenance easier
increases redundancy but makes the data more consistent

Question 14. 14. (TCO 2) A _____ means that a nonprime attribute is functionally dependent on only part of a table’s primary key but not the entire primary key. (Points : 4)
partial dependency
transitive dependency
repeating group
primary key

Question 15. 15. (TCO 4) In a typical online store scenario, which of the following diagrams best represents the relationship between customers and orders placed by customers? (Points : 4)

Question 15

Question 16. 16. (TCO 4) A(n) _____ provides a graphical description of a data model. (Points : 4)
entity relationship diagram
data dictionary
business rule

Question 17. 17. (TCO 3) Which statement is false regarding the creation of a foreign key constraint at table level or with an ALTER statement? (Points : 4)
The parent table must already exist.
The field must already be defined in the table.
The PK and FK fields must have the same name.
The PK and FK fields may have different names.

Question 18. 18. (TCO 3) You are creating a table called Department with fields for the primary key DeptID (Integer) and department name (VARCHAR). What (if anything) is wrong with the following code?
deptID INTEGER Primary Key,
); (Points : 4)
You must use curly braces, not parentheses.
The field names must be all lowercase.
The keywords cannot be in uppercase.
Nothing is wrong.

Question 19. 19. (TCO 6) Given a table orders with fields for orderid, orderdate, and shipdate, which query will display the orderid for only those orders that have shipped? (Points : 4)
SELECT ordered FROM orders WHERE shipdate <> orderdate;
SELECT orderid FROM orders WHERE shipdate IS NOT NULL;
SELECT orderid FROM orders WHERE shipdate IS NULL;
SELECT orderid FROM orders;

Question 20. 20. (TCO 6) Which WHERE clause will return data on all employees whose last name starts with ‘Smith’? (Points : 4)
WHERE lastname LIKE ‘Smith
WHERE lastname LIKE ‘Smith%’
WHERE lastname LIKE ‘%Smith%’
WHERE lastname LIKE ‘%Smith

Question 21. 21. (TCO 7) Consider the ERD below. How many tables would be required to create a query to display the description of each product ordered along with the first and last name of the customer that ordered it?

Question 21

(Points : 4)

Question 22. 22. (TCO 7) A full outer join returns _____. (Points : 4)
rows in the left table that don’t satisfy the join condition
unmatched rows from both the left and right tables
rows in the right table that don’t satisfy the join condition
the Cartesian product of the two tables

Question 23. 23. (TCO 7) What type of subquery is executed once for each row processed by the main query? (Points : 4)

Question 24. 24. (TCO 7) If your SELECT clause contains both aggregate and non-aggregate functions, _____. (Points : 4)
All non-aggregate columns must be included in a WHERE clause.
All non-aggregate columns must be included in a GROUP BY clause.
All aggregate and non-aggregate columns must be included in a GROUP BY clause.
All aggregate columns must be included in a WHERE clause.

Question 25. 25. (TCO 9) Which of the following is not a benefit provided by using a view? (Points : 4)
Views can be used to update multiple tables with a single statement.
You can use views to limit the exposure of the tables in your database to certain rows.
Views can be used to hide the complexity of query operations.
You can code views that join tables.

Page 3
Question 1. 1. (TCO 6) Write a query to list the customer first name, last name as a single field with a heading of Customer along with the balance sorted by balance from lowest to highest.

(Points : 10)

2. (TCO 6) Write a query to display the customer number, last name and first name for every customer represented by sales rep 15 or sales rep 20.
(Points : 10)


3. (TCO 6) Display all of the information from the part table for parts with a retail price below 100 and zero on hand.
(Points : 10)


4. (TCO 7) Write a query to display the orderid, order date, customer last name and firstname for all orders that have not shipped.

(Points : 10)


5. (TCO 7) Write a query to display the average retail price of all products formatted to display with two decimal places.
(Points : 10)


6. (TCO 7) Write a query using JOINS to list the orderid, partid, and part description and units on hand or every order that has not shipped.
(Points : 10)


7. (TCO 7) Write a query to display each sales reps ID and the total number of customers that they have. Only include those reps that have more than 10 customers.
(Points : 10)


8. (TCO 7) Using a subquery, list the partid and description of all parts where the number of units on hand is greater than the average of units on hand for all parts.
(Points : 10)


Relevant Material
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Lab Price = $23
Please feel free to send us your queries at:

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply