CIS 336 Final Exam mostly tested questions with answers testing papers
1. Consider the ERD below. How many tables would be required to create a query to display the customer first and last name along with orderd
...
CIS 336 Final Exam mostly tested questions with answers testing papers
1. Consider the ERD below. How many tables would be required to create a query to display the customer first and last name along with orderdate of each order they placed?
One
Two
Three
Four
2. Which of the following statements is correct? (Points : 4)
WHERE operates on groups formed by aggregate functions and HAVING operates on individual rows.
WHERE can only be used along with HAVING.
HAVING can only be used along with WHERE.
WHERE operates on individual rows and HAVING operates on groups formed by aggregate functions.
3. You can code views that _____. (Points : 4)
join tables
summarize data
usesubqueries and functions
All of the above
4. The customer table’s primary key is CustomerID. It has no null entries, and all values are unique. This is an example of _____ integrity. (Points : 4)
entity
referential
relational
logical
5. In the following command, the primary key constraint is created at the _____ level.
CREATE TABLE vendors
(
vendorID INT,
vendorName VARCHAR(20),
CONSTRAINT vendor_pk PRIMARY KEY (vendorID)
); (Points : 4)
table
column
database
alter
6. Which join syntax is required if the primary key and foreign key have different names?
Natural join
JOIN USING
JOIN ON
Any can be used
7. To be in the third normal form, _____. (Points : 4)
each non-key column must depend only on the primary key
each non-key column must not depend on the primary key
each non-key column must contain repeating values
All of the above
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)
Option #2
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?
(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)
SELECT CONCAT(FirstName,’ ’ ,LastName) AS ‘Customer’, Balance
FROM Customer
ORDER BY Balance ASC;
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)
SELECT CustomerID, LastName, FirstName FROM Customer WHERE RepID IN (15,20);
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)
SELECT *FROM Part WHERE Retail < 100 AND UnitsOnHand = 0;
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)
SELECT OrderID, OrderDate, LastName, FirstName
FROM Order JOIN Customer Using(CustomerID)
WHERE ShipDate IS NULL;
5. (TCO 7) Write a query to display the average retail price of all products formatted to display with two decimal places.
(Points : 10)
SELECT ROUND(AVG(Retail),2) FROM Part;
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)
SELECT o.OrderID, p.PartID, p.Description, p.UnitsOnHand
FROM Order o
Left Join OrderLine ol USING (OrderID)
Left Join Part p USING (PartID)
WHERE o.ShipDate IS NULL;
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)
SELECT RepID, COUNT(CustomerID) AS ‘Total Customer’ FROM SalesRep
INNER JOIN Customer USING(RepID)
GROUP BY RepID
HAVING COUNT(CustomerID) > 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)
SELECT p.PartID, p.Description
FROM Part p
WHERE p.UnitsOnHand> (SELECT AVG(UnitsOnHand) FROM Part);
[Show More]
 answers.png)
