Chapter 9 Oracle 12c

A table alias can have a maximum of _________________ characters.

30

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

CROSS JOIN

Which of the following types of joins refers to results consisting of each row from the first table being replicated from every row in the second table?

Cartesian join

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

FROM

A cross join between two tables, containing four rows each, will display eight rows in its output.

False

A table alias can be assigned in the FROM clause, even when tables are being joined using the NATURAL JOIN keywords.

False

A(n) outer join can be created by not including a joining condition in a SELECT statement.

False

Equality, non-equality, and self joins are broadly categorized as outer joins.

False

The NATURAL JOIN keywords can be used to create non-equality joins.

False

When combining the results of two SELECT statements with the MINUS keyword, duplicate rows are suppressed in the results.

False

To display rows from one table that do not have a corresponding row in the other table, you must create a(n) ______________ join.

OUTER

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

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

A Cartesian join usually results from the user omitting the joining condition that links two or more tables together.

True

If a Cartesian join is used to link table A which contains 2 rows to table B which contains 8 rows, there will be sixteen rows in the results.

True

Set operators are used to combine the results of multiple queries.

True

The outer join operator is placed on the side of the joining condition that references the table containing the deficient rows.

True

The __________ set operator is used to display the combined results returned by multiple SELECT statements.

UNION ALL

A full outer join cannot be created in the __________ clause.

WHERE

Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled THE WOK WAY TO COOK?

none of the above

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

set operator

The outer join operator in the WHERE clause cannot be used with which operators?

IN OR

The _______ set operator is used to display the rows returned by both SELECT statements.

INTERSECT

Which set operator can be used to make certain that only rows returned by both queries are displayed in the results?

INTERSECT

Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled SHORTEST POEMS?

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

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 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 return the names of all customers who placed an order on April 12, 2003?

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

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, order# FROM customers c LEFT OUTER JOIN orders o ON c.customer# = o.customer#;

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

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 books that have not been ordered recently?

SELECT title FROM books MINUS SELECT title FROM books NATURAL JOIN orderitems;

Which of the following SQL statements will display the title and cost of each book stored in the BOOKS table, as well as the name of the contact person and the phone number to call to reorder the book?

SELECT title, cost, contact, phone FROM publisher JOIN books USING (pubid);

Which of the following will display the title, publication date, and publisher name of each book in the BUSINESS category?

SELECT title, pubdate, name FROM publisher JOIN books USING (pubid) WHERE category ='BUSINESS';

A(n) ___________________ join is required when a table must be joined to itself.

SELF