Question 1.1. (TCO 2) Which statement is FALSE regarding a primary key? (Points : 3)
Only parent tables must have a primary key.
The values must be unique.
It can be made up of multiple attributes.
The value cannot be NULL.

Question 2.2. (TCO 2) _____ integrity ensures that all primary key values are unique and no part of a primary key may be null. (Points : 3)
Referential
Entity
Data
Logical

Question 3.3. (TCO 2) A(n) _____ in a business rule usually translates to an entity in the data model. (Points : 3)
noun
verb
adjective
adverb

Question 4.4. (TCO 4) A(n) ____ provides a detailed description of all attributes in the ERD including their datatypes. (Points : 3)
entity relationship diagram
data dictionary
business rule
pseudocode

Question 5.5. (TCO 2) A _____ primary key is often used in an associative or linking table. (Points : 3)
composite
foreign
bridge
linking

Question 6.6. (TCO 4) In the figure below, the OrderLineItems entity is there to implement what type of relationship between Orders and Products?
(Points : 3)
One to one
One to many
Many to many
No relationship

Question 7.7. (TCO 2) When designing a database, you should _____. (Points : 3)
make sure entities are in normal form before table structures are created
create table structures then normalize the database
only normalize the database when performance problems occur
consider more important issues, such as performance, before normalizing

Question 8.8. (TCO 2) The term first normal form (1NF) describes the tabular format in which all of the following are true except ______. (Points : 3)
all of the key attributes are defined
there are no repeating groups in the table
all attributes are dependent on the primary key
no attributes are dependent on the primary key

Question 9.9. (TCO 2) A table is not in 1NF if _____. (Points : 3)
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 10.10. (TCO 2) A _____ data model describes the entities and attributes in as much detail as possible without concern for the implementation details. (Points : 3)
logical
physical
conceptual
network

Week 3 : Creating the Physical Model – Quiz

Question 1. (TCO 3) The _____ column attribute is used to indicate that a value is required for this field. (Points : 3)
DEFAULT
AUTO_INCREMENT
REQUIRED
NOT NULL

Question 2. (TCO 5) The _____ keyword is used in an ALTER statement to add a new column to an existing table. (Points : 3)
MODIFY
ADD
INSERT
DROP

Question 3. (TCO 3) All of the following values can be stored in a column that’s defined as DECIMAL(6,2) except _____. (Points : 3)
-245.88
0
4532.99
24568.02

Question 4. (TCO 3) The DATETIME data type in MySQL can store _____. (Points : 3)
dates only
times only
dates and times
None of the above

Question 5. (TCO 3) What constraint can only be applied at table level or with an ALTER statement? (Points : 3)
Foreign key
Not null
Single field primary key
Composite primary key

Question 6. (TCO 3) When you build a script for creating tables in a database, _____. (Points : 3)
the order that you create the tables does not matter
you must create child tables before parent tables
you must create parent tables before child tables
the primary key column must be created first in each table

Question 7. (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 : 3)
You must use curly braces, not parentheses.
The comma after NOT NULL must be removed.
The keywords cannot be in upper case.
Nothing is wrong.

Question 8. (TCO 3) What data type is best to store the state abbreviations such as CA? (Points : 3)
INTEGER
VARCHAR
DECIMAL
CHAR

Question 9. (TCO 5) Which statement will add a new column named ORDERDATE to the CUSTOMERS table? (Points : 3)
CREATE COLUMN orderdate, DATE ADD to customers;
ALTER TABLE customers ADD COLUMN orderdate DATE;
ALTER TABLE customers ADD orderdate DATE;
MODIFY TABLE customers ADD orderdate DATE;

Question 10. (TCO 8) When you write an INSERT statement with a column list, which columns can be omitted from the column list? (Points : 3)
AUTO_INCREMENT only
DEFAULT only
NOT NULL only
AUTO_INCREMENT, DEFAULT, and fields that allow NULL

Question 1. (TCO 6) The four main clauses of the SELECT statement must be coded in the following order.

SELECT, FROM, ORDER BY, WHERE

SELECT, WHERE, ORDER BY, FROM

SELECT, FROM, WHERE, ORDER BY

SELECT, ORDER BY, FROM, WHERE

Question 2. (TCO 6) Which operator would be most appropriate to determine books with a retail price in the range of $50 to $100?

LIKE

IN

BETWEEN

>

Question 3. (TCO 6) Which code returns the date field HIREDATE in the format: Friday, April 5th, 2015?

DATE_FORMAT(HIREDATE, ‘%W, %m %d, %Y’)

DATE_FORMAT(HIREDATE, ‘%a, %M %D, %Y’)

DATE_FORMAT(HIREDATE, ‘%W, %M %D, %Y’)

DATE_FORMAT(HIREDATE, ‘%a, %m %d, %y’)

Question 4. (TCO 6) Given a books table with fields for title, retail and cost, which of the following will display the new retail price of each book as 20% more than its current retail price along with a heading?

SELECT title, retail * 1.2 AS “New Price” FROM books;

SELECT title, retail + 1.2 AS “New Price” FROM books;

SELECT title, retail * 0.2 AS “New Price” FROM books;

SELECT title, retail * 1.02 AS “New Price” FROM books;

Question 5. (TCO 8) The column to be updated by the UPDATE command is specified in the _____ clause.

WHERE

MODIFY

ALTER

SET

Question 6. (TCO 6) Given a table orders with fields for orderid, orderdate, and shipdate, which query will display the ordered for only those orders that have not shipped?

SELECT orderid 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 7. (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 8. (TCO 6) Given a books table with fields of title, cost, and retail, what if anything is wrong with the following query (assuming all fields exist)?
SELECT * FROM books ORDER BY retail WHERE cost > 20;

You must sort and filter on the same field.

You cannot use ORDER BY and WHERE in the same query.

The ORDER BY clause must always be last.

Nothing is wrong.

Question 9. (TCO 8) You can delete one or more rows in a table by using the _____ command.

UPDATE

DROP

DELETE

ALTER

Question 10. (TCO 6) Given a books table with fields: title, category (such as ‘SPORTS’), cost, retail; what condition will return ‘COMPUTER’ books with a retail price between $50 and $100 ?

WHERE retail > 50 AND < 100 AND category = ‘COMPUTER’ WHERE category = ‘COMPUTER’ AND (retail BETWEEN 100 and 50) WHERE category = ‘COMPUTER’ AND retail < 50 AND retail > 100
WHERE category = ‘COMPUTER’ AND ( retail BETWEEN 50 AND 100)

1. (TCO 7) The explicit join syntax is defined as _____. (Points : 3)
NATURAL JOIN
JOIN ON
JOIN USING
WHERE clause
Question 2. 2. (TCO 7) If Table A, which contains five rows is joined to Table B, which has eight rows without a valid join condition, the resulting cartesian product would have _____ rows. (Points : 3)
five
eight
13
40

Question 3. 3. (TCO 7) Given the two tables described below, which of the following JOIN queries (if any) is not correct?
customer: customerid(PK), lastname, firstname
order: orderid(PK), orderdate, customerid(FK) (Points : 3)
SELECT lastname, orderdate from customer JOIN order ON customer.customerid = order.customerid;
SELECT lastname, orderdate from customer JOIN order USING (customerid);
SELECT lastname, orderdate from customer, order WHERE customer.customerid = order.customerid;
All of the above
(All of the above are correct)

Question 4. 4. (TCO 7) If you assign an alias to one table in a join, you have to (Points : 3)
assign them to all of the tables.
use that alias to refer to the table throughout your query.
Qualify every column name in the query.
All of the above

Question 5. 5. (TCO 7) A right join returns (Points : 3)
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 6. 6. (TCO 7) When you use the USING keyword for a join (Points : 3)
the join cannot be an outer join.
the join cannot be based on more than one column.
the join must be based on a column or columns that have the same name in both tables.
the join must be an inner join.

Question 7. 7. (TCO 7) 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?

(Points : 3)
One
Two
Three
Four

Question 8. 8. (TCO 7) Consider the ERD below. Which query will return the first and last name of each customer whose order was placed after January 1, 2014?

(Points : 3)
SELECT firstname, lastname from customer, order WHERE orderdate > ‘2014-01-01’;
SELECT firstname, lastname from customer JOIN order WHERE orderdate > ‘2014-01-01’;
SELECT firstname, lastname from customer JOIN order USING (customerID) WHERE orderdate > ‘2014-01-01’;
SELECT firstname, lastname from customer WHERE orderdate > ‘2014-01-01’

Question 9. 9. (TCO 7) Consider the ERD below. What tables will be needed to create a report showing the orderid and date of any orders with products having a price more than $50?

(Points : 3)
order only
order, orderline, product
order, product only
product only

Question 10. 10. (TCO 7) Consider the ERD below. Note that there are some products produced in-house that do not require a vendor, thus their vendorID is NULL. Also note that not all vendors currently supply products. Which query would be used to generate a report showing vendorname and productname, and will include ALL products even if they do not currently have a vendor?

(Points : 3)
SELECT vendorname, productname FROM vendor LEFT JOIN product;
SELECT vendorname, productname FROM vendor RIGHT JOIN product;
SELECT vendorname, productname FROM vendor LEFT JOIN product ON vendor.vendorID = product.vendorID;
SELECT vendorname, productname FROM vendor RIGHT JOIN product ON vendor.vendorID = product.vendorID;

(TCO 7) Which is NOT an aggregate function? (Points : 3)
SUM
COUNT
ROUND
MAX

Question 2.2. (TCO 7) Which of the following is the correct order for the indicated clauses? (Points : 3)
WHERE, HAVING, GROUP BY
HAVING, WHERE, GROUP BY
GROUP BY, HAVING, ORDER BY
ORDER BY, GROUP BY, HAVING

Question 3.3. (TCO 7) Given the books table described below, which of the following will display each category along with the average price of books in that category?
books: bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK) (Points : 3)
SELECT category, AVG(retail) FROM books;
SELECT AVG(retail) FROM books GROUP BY category;
SELECT category, AVG(retail) FROM books GROUP BY category;
SELECT category, AVG(retail) FROM books GROUP BY category, retail;

Question 4.4. (TCO 7) Given the books table described below, which of the following will list retail price of the most expensive book in the COMPUTER category?
books: bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK) (Points : 3)
SELECT MAX(retail) FROM books GROUP BY category;
SELECT MAX(retail) FROM books WHERE category = ‘COMPUTER’;
SELECT MAX(retail) FROM books HAVING category = ‘COMPUTER’;
SELECT retail FROM (SELECT MAX(retail) FROM books);

Question 5.5. (TCO 7) Which of the values listed below can be returned by a subquery referenced as follows?
WHERE vendorid = (subquery) (Points : 3)
A single value
A column of one or more rows
A table
A subquery can’t be used in this way.

Question 6.6. (TCO 7) What type of subquery is executed only once for the entire query? (Points : 3)
Correlated
Uncorrelated
Inner
Outer

Question 7.7. (TCO 7) Given the two tables described below, which of the following queries will display the names of the customers whose orders have shipped?
customer: with the fields customerid(PK), lastname, firstname
order: with the fields orderid(PK), orderdate, shipdate, customerid(FK) (Points : 3)

SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE shipdate = orderdate);

SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE orderID IS NULL);

SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE shipdate IS NULL);

SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE shipdate IS NOT NULL);

Question 8.8. (TCO 7) Given the two tables described below, which of the following queries will display number of orders shipped?
customer: with the fields customerid(PK), lastname, firstname
order: with the fields orderid(PK), orderdate, shipdate, customerid(FK) (Points : 3)
SELECT COUNT(shipdate) FROM order;
SELECT COUNT(*) FROM order WHERE shipdate IS NULL;
SELECT COUNT(*) FROM order WHERE orderdate IS NOT NULL;
SELECT COUNT(shipdate) FROM order GROUP BY customerid;

Question 9.9. (TCO 7) Given the books table described below, which of the following will display the average retail price of books?
books: bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK) (Points : 3)
SELECT COUNT(retail) FROM books;
SELECT SUM(retail) FROM books;
SELECT AVG(retail) FROM books;
SELECT AVG(retail) FROM books GROUP BY category;

Question 10.10. (TCO 7) Which of these scenarios would justify the usage of a subquery? (Points : 3)
When we need to sum up values
When we need to convert string data into date or number values
When we need to select rows from a table with a condition that depends on the data from a different table
No way to tell without an ERD