Database Mgmt Final

all of the above (blank spaces, special symbols, characters in lower or mixed case)

A column alias must be contained within double quotation marks (" ") if it contains which of the following?

True

A simple view is based upon a subquery that references only one table and doesn't contain any group functions, expressions, or a GROUP BY clause.

single quotation marks (' ')

A string literal must be enclosed in ____.

False

A subquery can only be nested in the WHERE or HAVING clause of the outer query.

NATURAL JOIN

A table alias cannot be assigned in the FROM clause if which of the following keywords is used to join tables?

False

A view can be dropped or deleted using the DELETE VIEW command. _________________________

​set operator

A(n) ____ is used to combine the results of two queries.

ALTER TABLE orderitems ADD FOREIGN KEY (isbn) REFERENCES books(isbn);

Based on the structure of the ORDERITEMS table, which of the following commands will make certain that the ISBN entered actually exists in the ISBN column of the BOOKS table?

none of the above

Based on the structure of the PROMOTION table, which of the following commands will add a PRIMARY KEY constraint to the GIFT column?

Line 4

Based upon the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT title, pubid, cost, retail 2 FROM books 3 WHERE (pubid, cost) 4 (SELECT pubid, cost) 5 FROM books 6 WHERE pubid = 3);

none of the above

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

SELECT MAX(retail) FROM books WHERE pubid = 3;

Based upon the contents of the BOOKS table, which of the following will display the retail price of the most expensive book provided by publisher 3?

SELECT COUNT(DISTINCT pubid) FROM books;

Based upon the contents of the BOOKS tables, which of the following SQL statements will return the number of different publishers represented in the table?​

SELECT order# FROM orders WHERE SUBSTR(shipzip, 1, 3) = 323;

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?

SELECT shipstate,COUNT(*) FROM orders GROUP BY shipstate;

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

none of the above

Based upon the contents of the PUBLISHER table, which of the following SELECT statements will display the publisher's name first in the results?

SELECT * FROM publisher;

Based upon the contents of the PUBLISHER table, which of the following is a valid SQL statement?​

SELECT name, contact FROM publisher WHERE pubid IN (1, 2, 5)

List name and contact of publishers whose pubid is either 1, 2, or 5

CHECK

The ____ constraint requires that a specific condition be met before a record can be added to a table.

IN

The ____ operator indicates that the records processed by the outer query must match one of the values returned by the subquery.

SET

The column to be updated by the UPDATE command is specified in the ____ clause.

inline view

The following SQL statement contains what type of subquery? SELECT b.title, b.retail, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category;

single-row

The following SQL statement contains which type of subquery? SELECT title, retail, (SELECT AVG(retail) FROM books) FROM books;

WHERE

The row(s) to be updated by the UPDATE command is specified by the ____ clause.

​2

To display the name of each customer and the ISBN of each book purchased by the customers would require how many joins in the FROM clause of the SQL statement?

3

To display the name of each customer and the title of each book purchased by the customers would require how many join conditions?

SELECT ISBN, title FROM books WHERE title LIKE '_A%' AND title LIKE '___N%' ORDER BY title DESC

Use a search pattern to find any book title with "A" for the second letter and "N" for the fourth letter. List each book's ISBN and title. Sort the list by title in descending order.

SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE customer#=1013);

​Based on the contents of the CUSTOMERS table, which SQL statement will display the customers residing in the same state as customer#1013?

SELECT * FROM orders CROSS JOIN customers;

​Which SQL statement will return the same results as the following SQL statement? SELECT * FROM orders, customers;

CROSS JOIN

​Which of the following keywords is used to create a Cartesian join?

The UNIQUE constraint allows NULL values.

The UNIQUE constraint differs from the PRIMARY KEY constraint in what way?

none of the above

Based on the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT isbn, title 2 FROM books 3 WHERE pubid = 4 (SELECT pubid 5 FROM books 6 WHERE title = 'SHORTEST POEMS') 7 AND retail-cost > 8 (SELECT AVG(retail-cost) 9 FROM books);

none of the above

Based on the contents of the BOOKS table, which of the following SQL statements will display the title of all books published by the publisher of SHORTEST POEMS?

SELECT SUM(retail-cost) FROM books WHERE pubid = 4;

Based on the contents of the BOOKS table, which of the following SQL statements will return the total profit generated by books provided by publisher 4?​

​SELECT pubid, AVG(retail-cost) "Average Profit" FROM books GROUP BY pubid;

Based on the contents of the BOOKS table, which of the following is a valid SQL statement?​

SELECT MIN(pubdate) FROM books;

Based on the contents of the BOOKS table, which of the following will display the date of the book with the earliest publication date?

​SELECT COUNT(*) FROM orders WHERE shipdate IS NULL;

Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?

DELETE FROM promotion WHERE gift = 'BOOKMARKER';

Based on the contents of the PROMOTION table, which of the following commands will delete only the row for the Free Bookmark from the table?

SELECT b.title, p.contact, p.phone FROM books b, publisher p WHERE b.pubid = p.pubid;

Create a list that displays the title of each book and the name and phone number of the contact at the publisher's office for reordering each book.

select title, fname, lname from books join bookauthor using (isbn) join author using (authorid);

Create a list that displays the title of each book and the name of the author.

SELECT c.firstname, c.lastname, o.order# FROM customers c, orders o WHERE c.customer# = o.customer# AND o.shipdate IS NULL ORDER BY o.orderdate;

Determine which orders haven't yet shipped and the name of the customer who placed the order. Sort the results by the date on which the order was placed.

SELECT b.title, o.order#, c.state FROM books b, orders o, orderitems i, customers c WHERE c.customer# (+) = o.customer# AND o.order# (+) = i.order# AND i.isbn (+) = b.isbn;

Display a list of all books in the books table. If a book has been ordered by a customer, also list the corresponding order number and the state in which the customer resides.

SELECT *FROM orders WHERE orderdate < '04-Apr-09' AND shipdate IS NULL

Finds all orders placed before April 5, 2009 that haven't yet shipped

WHERE

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

PRIMARY KEY

Only one ____ constraint can exist for each table.

FROM

The JOIN keyword is included in which of the following clauses?

all of the above (numeric, date, character)

The MAX function can be used with which type of columns?

SELECT p.deliveryoption, SUM(l.quantitiesordered * a.albumprice)/SUM(l.quantitiesordered) "Average" FROM PHYSICAL_ORDER p , ORDER_LINE l , ALBUM a WHERE p.p_orderid = l.p_orderid and l.albumid = a.albumid GROUP BY p.deliveryoption;

What are the average prices of albums delivered by Free, Standard, and Expedite shipping?

SELECT p.gift FROM books b, promotion p WHERE b.retail BETWEEN p.minretail AND p.maxretail AND b.title = 'SHORTEST POEMS';

What give will a customer who orders the book "SHORTEST POEMS" receive? Use the actual book retail value to determine the gift.

SELECT s.songtitle, a.artistname, sum(p.playcount) play FROM ORDER_RECORD o, PLAY_HISTORY p, WRITES w, SONG s, ARTIST a WHERE o.orderid = p.d_orderid and p.songid = s.songid and w.songid = s.songid and w.artistid = a.artistid GROUP BY s.songtitle, a.artistname ORDER BY play DESC;

What is the most played song? List the top 10 list of songs with song titles, artist names, and play counts in descending order of the play count

The Customer_Name and telephone of all customers living in either Boston, New York or Denver

What result set is returned from the following query? select customer_name, telephone from customers where city in ('Boston','New York','Denver');

The Item_No of all orders that had more than 10 items

What result set will the following query return? select Item_No from Order_V where quantity > 10;

The Item_No and description for all items weighing between 101 and 199

What result set will the following query return? select item_No, description from item where weight > 100 and weight < 200;

​single quotation marks

When the INSERT command is being used to enter data into a non-numeric column, the data must be enclosed in ____.

SELECT a.albumtitle, a.releasedate, sum(l.quantitiesordered) quant FROM ALBUM a, ORDER_RECORD o, ORDER_LINE l WHERE l.p_orderid = o.orderid and l.albumid = a.albumid GROUP BY a.albumtitle, a.releasedate ORDER BY quant DESC;

Which album has been sold most? List the top 10 list of albums with album titles, release dates, and quantities ordered in descending order of the quantities ordered.

SELECT lastname, firstname, state FROM customers WHERE state = 'NJ'

Which customers live in New Jersey? List each customer's last name, first name, and state

COMMIT

Which keyword permanently saves changed data in a table?

SELECT customer# FROM customers MINUS SELECT customer# FROM orders;

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

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%';

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

SELECT title, retail-cost FROM books;

Which of the following SQL statements will display the profit generated by each book currently stored in the BOOKS table?

SELECT title FROM customers JOIN orders USING (customer#) JOIN orderitems USING (order#) JOIN books USING (isbn) WHERE customer# = 1003;

Which of the following SQL statements will display the title of the books ordered by customer# 1003?

INSERT INTO promotion (gift, minretail, maxretail) VALUES ('FREE BOOK', 75.01, 89.99);

Which of the following SQL statements will insert a new row into the PROMOTION table?

both a and c (SELECT lastname, firstname, order# FROM orders RIGHT OUTER JOIN customers USING (customer#); SELECT lastname, firstname, order# FROM orders FULL OUTER JOIN customers USING (customer#);)

Which of the following SQL statements will list the name of each customer stored in the customers table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?

SELECT lastname, firstname FROM customers NATURAL JOIN orders WHERE orderdate = '12-APR-03';

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

ALTER TABLE promotion ADD CONSTRAINT orderitems_minretail_uk UNIQUE (minretail);

Which of the following commands will add a UNIQUE constraint to the MINRETAIL column of the PROMOTION table?​

ALTER TABLE customers ADD (firstorderdate DATE);

Which of the following commands will add a new column named FIRST ORDER DATE to the CUSTOMERS table to store the date that the customer first placed an order with the company?

​ALTER TABLE customersMODIFY (city VARCHAR2(20), lastname VARCHAR2(14));

Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?

complex view

Which of the following describes a type of view that is based on a subquery that retrieves or derives data from one or more tables, and may also contain functions or grouped data?

​UPPER

Which of the following functions can be used to convert a character string to upper-case letters?

LENGTH

Which of the following functions determines the number of characters in a character string?

​An asterisk can be used as the argument for the COUNT function to include NULL values in the results.

Which of the following is a correct statement?

none of the above

Which of the following is a valid SELECT statement?

CREATE TABLE newname (colA NUMBER, colB DATE);

Which of the following is a valid SQL statement?

SELECT title, retail-cost FROM books;

Which of the following is a valid SQL statement?

FROM orders o, customers c

Which of the following is an example of assigning "o" as a table alias for the ORDERS table in the FROM clause?

*

Which of the following is the wildcard operator in SQL statements?

outer join operator (+)

Which of the following is used to create an outer join in a WHERE clause?

​SUBSTR

Which of the following is used to return a portion of a character string?

​( )

Which of the following must be used to separate a subquery from the outer query?

=ANY

Which of the following operators is the equivalent of the IN comparison operator?

all of the above (IN, ANY, ALL)

Which of the following operators is used with a multiple-row subquery?

both a & b (SELECT customer#, lastname, firstname, order# FROM customers JOIN orders USING (customer#); SELECT customer#, lastname, firstname, order# FROM customers NATURAL JOIN orders;)

Which of the following queries will return the same results as the following SQL statement? SELECT c.customer#, lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer#;

Views are database objects that actually store data.

Which of the following statements about views is incorrect?

DESCRIBE ORDERS

Which of the following statements can be used to view the structure of the ORDERS table?

SELECT customer#, city, REPLACE(state, 'FL', 'FLORIDA') FROM customersWHERE state = 'FL';

Which of the following statements will display the value of FL assigned to the state column as FLORIDA?

​||

Which of the following symbols can be used to combine data from different columns into one column of output?

SELECT a.artistname, sum(playcount) as play FROM PLAY_HISTORY p, SONG s, WRITES w, ARTIST a WHERE p.songid = s.songid and s.songid = w.songid and w.artistid = a.artistid group by a.artistname;

Who is the most-played artist?

DML

________ is a set of commands used to update and query a database.