CIS336 Week 8 Final Exam latest 2018 June

Week 8 Final Exam

Question 1

(TCO 2) A _____ relationship must be implemented by creating a new entity that has 1:M relationships with the two original entities.

1:1

1:M

M:1

M:N

Question 2

(TCO 2) Referential _____ dictates that the foreign key must contain values that match the primary key in the related table or must contain null.

uniqueness

model

integrity

attribute

Question 3

(TCO 8) Which command would complete this statement to remove the row in the products table with a pcode value of 440?

_____ FROM products WHERE pcode = 440;

DROP

REMOVE

DELETE

ROLLBACK

Question 4

(TCO 6) The special operator used to check whether an attribute value is within a range of values is _____.

BETWEEN

NULL

LIKE

IN

Question 5

(TCO 2) A _____ key is a key that is composed of more than one attribute.

primary

foreign

composite

domain

Question 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.

UNIQUE

NOT NULL

FOREIGN KEY

PRIMARY KEY

Question 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.

table

column

database

alter

Question 8

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

COUNT

MAX

MAXIMUM

SUM

Question 9

(TCO 7) The following SQL statement is an example of what type of join syntax?

SELECT pcode, pdescript, vname FROM product JOIN vendor ON product.vcode = vendor.vcode;

Set operator

Implicit inner join syntax

Explicit inner join syntax

Natural join

Question 10

(TCO 6) When using the MySQL DATE_FORMAT function, which code displays a three-letter month name?

%M

%m

%b

%mon

Question 11

(TCO 3) Which keyword is used to require that a value be entered for the attribute?

FOREIGN KEY

DEFAULT

NOT NULL

AUTO_INCREMENT

Question 12

(TCO 2) When identifying potential relationships for an ERD, look for the _____ in the business narrative.

adjectives

adverbs

nouns

verbs

Question 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 _____.

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

(TCO 2) A table is not in 1NF if _____.

it has a primary key defined

all of the key attributes are defined

there are repeating groups in the table

all attributes are dependent on the primary key

Question 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?

Question 16

(TCO 4) A(n) _____ provides a graphical description of a data model.

entity relationship diagram

data dictionary

business rule

psuedocode

Question 17

(TCO 3) What constraint cannot be applied at column level?

Foreign key

Not null

Single field primary key

Composite primary key

Question 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 Primary Key INTEGER ,

deptName NOT NULL VARCHAR(10)

);

You must use curly braces, not parentheses.

The data type must immediately follow the field name.

The keywords cannot be in uppercase.

Nothing is wrong.

Question 19

(TCO 6) Given a table orders with fields for orderid, orderdate, and shipdate, which query will display the orderid and shipdate, displaying ‘NOT SHIPPED’ for those orders that have not shipped?

SELECT orderid, IFNULL(orderdate, ‘NOT SHIPPED’) FROM orders;

SELECT orderid, IFNULL(shipdate, ‘NOT SHIPPED’) FROM orders;

SELECT orderid, shipdate, ‘NOT SHIPPED’ WHERE shipdate IS NULL;

SELECT orderid, shipdate, ‘NOT SHIPPED’ WHERE shipdate IS NOT NULL;

Question 20

(TCO 6) Which WHERE clause will return data on all employees whose last name starts with ‘Smith’?

WHERE lastname LIKE ‘Smith

WHERE lastname LIKE ‘Smith%’

WHERE lastname LIKE ‘%Smith%’

WHERE lastname LIKE ‘%Smith

Question 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?

One

Two

Three

Four

Question 22

(TCO 7) A right join returns _____.

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

(TCO 7) Which operator can be used to test that one or more rows are returned by the subquery?

IS NULL

IS NOT NULL

EXISTS

NOT EXISTS

Question 24

(TCO 7) Which of the following statements is correct?

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.

Question 25

(TCO 9) Assuming the SELECT statement below is a proper query, which is true regarding the following statement?

CREATE VIEW example AS

SELECT vendor_name, SUM(invoice_total) AS “Invoice Sum”

FROM vendors JOIN invoices ON vendors.vendor_id = invoices.vendor_id

GROUP BY vendor_name ORDER BY vendor_name;

It will fail because the GROUP BY clause is not allowed in a view.

It will fail because SUM function is not allowed in a view.

It will fail because ORDER BY clause is not allowed in a view.

It will succeed.

Question 26

(TCO 6) Write a query to display the customerid, last name and first name for every customer whose last name begins with the letter D.

Question 27

(TCO 6) Display all of the information in the customer table for customers in California (use CA) with a zero balance.

Question 28

(TCO 7) Write a query using JOINS to list the orderid, orderdate, customer last name, customer first name for any orders made with a sales rep with the last name of JONES (use all caps).

Question 29

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

Question 30

(TCO 7) Using a subquery, list the firstname and lastname of all customers with an order that has not shipped.