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

Lab Price = $23
Please feel free to send us your queries at: support@iqrajavaid.com
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)
1:1
1:M
M:1
M:N
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)
Entity
Referential
Relational
Logical

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;
ALTER TABLE products DROP;
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)
BETWEEN
NULL
LIKE
IN

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

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)
UNIQUE
NOT NULL
FOREIGN KEY
PRIMARY KEY

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)
table
column
database
alter

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

Question 9. 9. (TCO 7) Which join syntax is required if the primary key and foreign key have different names? (Points : 4)
Natural join
JOIN USING
JOIN ON
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)
%M
%m
%b
%mon

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

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

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
psuedocode

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?
CREATE TABLE Department
(
deptID INTEGER Primary Key,
deptName VARCHAR(10) NOT NULL
); (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)
One
Two
Three
Four

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)
Correlated
Uncorrelated
Inner
Outer

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

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)

detailquestions_erd

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)

detailquestions_erd

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)

detailquestions_erd

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

detailquestions_erd

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)

detailquestions_erd

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)

detailquestions_erd

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)

detailquestions_erd

Relevant Material
Instructions
* 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 support@iqrajavaid.com.
* 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 support@iqrajavaid.com 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 support@iqrajavaid.com.
* 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 support@iqrajavaid.com
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: support@iqrajavaid.com

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply