CHAPTER 9: JOIN TABLES
Tables can be joined in the FROM clause or the WHERE clause of a SELECT statement.
TRUE
The INTERSECT set operator only displays the rows returned by both queries.
TRUE
The MINUS set operator is used to display rows that were uniquely returned by the first query in the set.
TRUE
The NATURAL JOIN keywords can be used to link two tables that have a commonly named and defined column.
TRUE
The most common type of join is an equijoin, which joins two or more tables together based upon the tables having equivalent data values in a common column.
TRUE
The number of joining conditions required to join tables is always one less than the number of tables being joined.
TRUE
The outer join operator is placed on the side of the joining condition that references the table containing the deficient rows.
TRUE
When combining the results of two SELECT statements with the UNION keyword, duplicate rows are suppressed in the results.
TRUE
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
Which of the following keywords is used to create a Cartesian join?
CROSS JOIN
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?
Correct SELECT lastname, firstname, order# FROM customers c LEFT OUTER JOIN orders o ON c.customer# = o.customer#;
A column qualifier indicates the column containing the data being referenced.
FALSE
A cross join between two tables, containing four rows each, will display eight rows in its output.
FALSE
A full outer join can be created by including an outer join operator on both sides of the linking condition stated in the WHERE clause.
FALSE
A table alias can be assigned in the FROM clause, even when tables are being joined using the NATURAL JOIN keywords.
FALSE
A table alias is assigned to a table in the WHERE clause.
FALSE
A(n) non-equality join is also known as an equijoin, inner join, or simple join.
FALSE
A(n) outer join can be created by not including a joining condition in a SELECT statement.
FALSE
An inequality join refers to a join that is used to link a table to a copy of itself.
FALSE
An outer join operator can be included in a FROM clause to list all rows from one table that do not have a corresponding row in the other table.
FALSE
Equality, non-equality, and self-joins are broadly categorized as outer joins.
FALSE
If a table alias is assigned in the SELECT clause, it must be used any time the table is referenced in that SQL statement.
FALSE
If you are joining five tables in a SELECT statement, five joining conditions will be required.
FALSE
If you are joining two tables in a SELECT statement, three joining conditions will be required.
FALSE
The JOIN keyword is used in the WHERE clause to indicate the tables that should be joined or linked.
FALSE
The JOIN keyword must be used in the WHERE clause of a SELECT statement.
FALSE
The JOIN...USING keywords are used to join two tables that do not have a commonly named and defined column.
FALSE
The ON clause can be used only if the tables being joined have a common column with the same name.
FALSE
The UNION set operator will not suppress rows that are returned by both queries.
FALSE
The USING clause must be used with the JOIN keyword when linking tables that do not contain a commonly named column.
FALSE
The outer join operator is used to combine the results of multiple SELECT statements.
FALSE
When combining the results of two SELECT statements with the MINUS keyword, duplicate rows are suppressed in the results.
FALSE
The JOIN keyword is included in which of the following clauses?
FROM
Which of the following is an example of assigning "o" as a table alias for the ORDERS table in the FROM clause?
FROM orders o, customers c
A(n) ____________________ outer join is necessary when you need rows returned from either table that do not have a matching record in the other table.
FULL
Which of the following set operators can be used to make certain that only the rows returned by both queries are displayed in the results?
INTERSECT
A table alias cannot be assigned in the FROM clause if which of the following keywords is used to join tables?
NATURAL JOIN
Which of the following can only be used to link tables that have a common column?
NATURAL JOIN
A self-join can only be specified in the FROM clause with the use of the JOIN...____________________ keywords.
ON
Using the JOIN...____________________ keywords to join two tables requires that a condition be specified to indicate how the tables are related.
ON
A full outer join can be created by including an outer join operator on both sides of the linking condition stated in the WHERE clause.
OUTER
To display rows from one table that do not have a corresponding row in the other table, you must create a(n) ____________________ join.
OUTER
The ____________________ operator is used to create an outer join in the WHERE clause of a SQL statement.
OUTER JOIN (+)
A column qualifier is separated from the column using which symbol?
PERIOD(.)
A column ____________________ indicates the table containing the column being referenced.
QUALIFIER
Which of the following SQL statements will display the gift or gifts that should be sent with order# 1003?
SELECT gift FROM promotion, orderitems oi, books b WHERE retail BETWEEN minretail AND maxretail AND oi.isbn = b.isbn AND order# = 1003;
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 publisher name, book title, and retail price of all books that cost more than $35.95?
SELECT name, title, retail FROM books NATURAL JOIN publisher WHERE cost > 35.95;
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);
Structure of the PUBLISHER table 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';
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
____________________ operators are used to combine the results of multiple queries.
SET
A(n) ____ is used to combine the results of two queries.
SET OPERATOR
A Cartesian join can be created by not including a joining condition in the WHERE clause of a SELECT statement.
TRUE
A table alias is assigned in the FROM clause.
TRUE
A(n) Cartesian Join replicates each row from the first table with every row from the second table.
TRUE
A(n) non-equality join is used when there are no equivalent rows of data in the tables that are being joined.
TRUE
Column qualifiers must be included in the WHERE clause if the columns used to join the tables have the same column names.
TRUE
If a Cartesian join is used to link table A which contains two rows to table B which contains eight rows, there will be sixteen rows in the results.
TRUE
If you are joining four tables in a SELECT statement, three joining conditions will be required.
TRUE
Which of the following set operators will display only the unique results of the combined SQL statements?
UNION
The ____________________ set operator is used to display the combined results returned by multiple SELECT statements.
UNION ALL
Which of the following set operators will display the results of the combined SQL statements without suppressing duplicate rows?
UNION ALL
A full outer join cannot be created in the ____________________ clause.
WHERE
The outer join operator can only be used in the ____________________ clause.
WHERE
In which of the following examples is the ORDERS table used as a column qualifier?
orders.order#
An outer join only lists rows that contain a match in both tables.
FALSE
Set operators are used to combine the results of multiple queries.
TRUE
Joins are classified as ____________________ joins if the results can only contain the rows that had matching values in each table, rather than rows being matched with NULL values.
INNER
The ____________________ set operator is used to display the rows returned by both SELECT statements.
INTERSECT
Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column?
JOIN...ON
Data stored in separate tables can be reconstructed through the use of ____________________.
JOINS
The outer join operator in the WHERE clause cannot be used with which of the following operators?
BOTH A AND B (IN & OR)
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?
BOTH A AND C
Which of the following SQL statements will display all customers who have not recently placed an order?
Correct SELECT customer# FROM customers MINUS SELECT customer# FROM orders;
A join that is based upon data having equivalent data in common columns is known as a(n) ____________________ join.
EQUALITY
A join based upon a column from each table containing equivalent data is known as a(n) ____.
EQUALITY JOIN
Which of the following types of joins is created by matching equivalent values in each table?
EQUALITY JOIN
A table alias can have a maximum of ____________________ characters.
30
