Midterm Exam for 100 points due Sunday 05/12/13Page 1 of 8

Name: ______

Deadline to submit Midterm Exam Results is Sunday 05/12/2013 – No late submission will be accepted for credit.

Please insert your answer on the line (left of each question) in UPPER CASE letter.

Example: ___A____. Only answers provided on the assigned line will be checked for credit.

.

Provide your answer for all 20 questions on this original copy of the test.

Each question is for 5 points for total of 100 points.

Identify the choice that best completes the statement or answers the question.

____1.To indicate which database table contains the data to be selected by a query, the table name should be listed in the ____ clause.

a. / SELECT / c. / WHERE
b. / FROM / d. / HAVING

____2.If a column alias contains a blank space, it must be enclosed in ____.

a. / single quotation marks (' ') / c. / asterisks (* *)
b. / double quotation marks (" ") / d. / percent signs (% %)

Contents of the PUBLISHER table

____3.Based on the contents of the PUBLISHER table, which of the following SQL statements will change the phone number for Printing Is Us to 800-714-8321?

a. / UPDATE publisher REPLACE phone WITH '800-714-8321'
WHERE pubid = 1;
b. / UPDATE publisher REPLACE phone = '800-714-8321'
WHERE pubid = 1;
c. / UPDATE publisher SET phone = '800-714-8321'
WHERE pubid = 1;
d. / none of the above

Contents of the BOOKS table

____4.Based upon the contents of the BOOKS table, which of the following is a valid SQL statement?

a. / SELECT "The retail price of " || title || " is " retail FROM books;
b. / SELECT * || is currently in our inventory FROM books;
c. / SELECT title, || retail "Price" FROM books;
d. / SELECT isbn || CHR(10) || title FROM books;

____5.Based upon the contents of the BOOKS table, which of the following SQL statements will display the retail price for two copies of each book currently in inventory?

a. / SELECT * FROM books;
b. / SELECT title, retail+retail FROM books;
c. / SELECT title, retail^2 FROM books;
d. / none of the above

____6.Which of the following operators can be used to retrieve rows containing NULL values in a specific column?

a. / = / c. / NOT
b. / LIKE / d. / none of the above

____7.Which of the following operators can be used to combine search conditions?

a. / AND / c. / IS NOT NULL
b. / = / d. / none of the above

Contents of BOOK table

____8.Based upon the contents of the BOOKS table, which of the following SQL statements will retrieve all books published by the publisher assigned Pubid 1?

a. / SELECT * FROM books WHERE pubid LIKE "1";
b. / SELECT * FROM books WHERE pubid = 1;
c. / SELECT * FROM books WHERE pubid > 1;
d. / both a and b

____9.Based upon the contents of the BOOKS table, which of the following queries will retrieve all books published in 2001?

a. / SELECT * FROM books
WHERE pubdate BETWEEN '01-JAN-01' AND '31-DEC-01';
b. / SELECT * FROM books
WHERE pubdate LIKE '%01';
c. / SELECT * FROM books
WHERE pubdate >= '01-JAN-01' AND pubdate <= '31-DEC-01';
d. / all of the above

Contents of the ORDERS table

____10.Which of the following queries will display all orders that were not shipped for at least three days after the order was received?

a. / SELECT * FROM orders WHERE shipdate-orderdate => 3;
b. / SELECT * FROM orders WHERE shipdate-orderdate >= 3;
c. / SELECT * FROM orders WHERE NOT shipdate-orderdate => 3;
d. / SELECT * FROM orders WHERE NOT shipdate-orderdate >= 3;

____11.Based upon the contents of the ORDERS table, which of the following SQL statements will display the number of days between the date an order was placed and the date it was shipped?

a. / SELECT order#, TO_CHAR(shipdate-orderdate, '99') FROM orders
WHERE shipdate IS NOT NULL;
b. / SELECT order#, MONTHS_BETWEEN(shipdate-orderdate, '99') FROM orders
WHERE shipdate IS NOT NULL;
c. / SELECT order#, ADD_MONTHS(shipdate-orderdate, '99') FROM orders
WHERE shipdate IS NOT NULL;
d. / SELECT order#, DAYS_BETWEEN(shipdate-orderdate, '99') FROM orders
WHERE shipdate IS NOT NULL;

____12.Based upon the contents of the ORDERS table, which of the following SQL statements will display only those orders shipped to the zip code zone that begins with 323?

a. / SELECT order#, SUBSTR(shipzip, 1, 323)
FROM orders;
b. / SELECT order#, SUBSTR(shipzip, 1, 323)
FROM orders
WHERE shipzip = 323;
c. / SELECT order#
FROM orders
WHERE shipzip = SUBSTR(shipzip, 1, 323);
d. / SELECT order#
FROM orders
WHERE SUBSTR(shipzip, 1, 3) = 323;

____13.Based upon the contents of the ORDERS table, which of the following will display how many orders were shipped to each state?

a. / SELECT shipstate, COUNT(*)
FROM orders;
b. / SELECT shipstate, COUNT(customer#)
FROM orders;
c. / SELECT shipstate, COUNT(*)
FROM orders
HAVING COUNT(*) >0;
d. / SELECT shipstate, COUNT(*)
FROM orders
GROUP BY shipstate;

Structure of the ORDERS table

Structure of the CUSTOMERS table

____14.Which of the following SQL statements will return the names of all customers who placed an order on April 12, 2003?

a. / SELECT lastname, firstname
FROM customers
WHERE orderdate = '12-APR-03';
b. / SELECT lastname, firstname
FROM customers NATURAL JOIN orders
WHERE orderdate = '12-APR-03';
c. / SELECT lastname, firstname
FROM customers JOIN orders ON (customer#)
WHERE orderdate = '12-APR-03';
d. / both a and b

____15.Which of the following SQL statements will display all customers who have not recently placed an order?

a. / SELECT customer# FROM customers
UNION
SELECT customer# FROM orders;
b. / SELECT customer# FROM orders
MINUS
SELECT customer# FROM customers;
c. / SELECT customer# FROM orders
INTERSECT
SELECT customer# FROM customers;
d. / SELECT customer# FROM customers
MINUS
SELECT customer# FROM orders;

Structure of the CUSTOMERS table

Structure of the ORDERS table

Structure of the ORDERITEMS table

Structure of the BOOKS table

____16.Which of the following SQL statements will display the names of all customers who have purchased a copy of E-BUSINESS THE EASY WAY?

a. / SELECT lastname, firstname
FROM customers NATURAL JOIN books
WHERE title LIKE '%BUSI%';
b. / SELECT lastname, firstname
FROM customers c, books b, orders o, orderitems oi
WHERE title LIKE '%BUSI%';
c. / SELECT lastname, firstname
FROM customers c, books b, orders o, orderitems oi
WHERE c.customer# = o.customer# AND o.order# = oi.order# AND
oi.isbn = b.isbn AND title LIKE '%BUSI%';
d. / none of the above

Structure of the BOOKS table

Structure of the PUBLISHER table

____17.Which of the following SQL statements will display the title of each book in the BOOKS table and the name of its publisher?

a. / SELECT title, name
FROM publisher, books;
b. / SELECT title, name
FROM publisher NATURAL JOIN books;
c. / SELECT title, name
FROM publisher, books
WHERE p.pubid = b.pubid;
d. / both b and c

____18.Which of the following returns one row of results for each record processed?

a. / single-row function / c. / aggregate function
b. / multiple-row function / d. / all of the above

____19.Functions that return one result per group of rows are called ____ functions.

a. / group / c. / aggregate
b. / multiple-row / d. / all of the above

____20.If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed first.

a. / HAVING / c. / WHERE
b. / GROUP BY / d. / ORDER BY