Joining Data from Multiple Tables
In Oracle12c, tables can be linked through which clause(s)?
both b and c
When two tables that share more than one common column are being joined, the JOIN...____________________ keywords are normally used in the FROM clause to join the tables.
USING
The outer join operator can only be used in the ____________________ clause.
WHERE
A full outer join cannot be created in the ____________________ clause.
WHERE
Structure of the ORDERS table Structure of the CUSTOMERS table Which of the following queries will display data from both the ORDERS and CUSTOMERS tables?
all of the above
Which of the following queries will display data from both the ORDERS and CUSTOMERS tables?
all of the above
The outer join operator in the WHERE clause cannot be used with which of the following operators?
both a and b
Which of the following terms refers to a column with equivalent data that exists in two or more tables?
common column
The outer join operator is placed on the side of the joining condition that has ____________________ rows.
deficient or missing or NULL
Which of the following types of joins is created by matching equivalent values in each table?
equality
Which of the following types of joins is created by matching equivalent values in each table?
equality join
A join based upon a column from each table containing equivalent data is known as a(n) ____.
equality join
An inequality join refers to a join that is used to link a table to a copy of itself.
false
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 or join conditions
A column qualifier is used to indicate the table containing the column being referenced.
true
The number of joining conditions required to join tables is always one less than the number of tables being joined.
true
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
Which of the following SQL statements will display the title of all books that have had multiple copies requested in a single order?
SELECT title FROM books NATURAL JOIN orderitems WHERE qty > 1;
How many joining conditions will be required in an SQL statement that is used to determine the gift that corresponds to each book in the BOOKS table?
1
Structure of the CUSTOMERS table Structure of the ORDERS table Structure of the ORDERITEMS table Structure of the BOOKS table 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?
2
The ____________________ JOIN keyword can be used to create a Cartesian join.
CROSS
Which of the following keywords is used to create a Cartesian join?
CROSS JOIN
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
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
The ____________________ set operator is used to display the results that were returned by the first query that were not also returned by the second query.
MINUS
A table alias cannot be assigned in the FROM clause if which of the following keywords is used to join tables?
NATURAL JOIN
The ____________________ keywords create a join automatically between two tables, based on columns with matching names.
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
____________________ operators are used to combine the results of multiple queries.
Set
A Cartesian join usually results from the user omitting the joining condition that links two or more tables together.
TRUE
A column qualifier indicates the column containing the data being referenced.
FALSE
Structure of the PUBLISHER table Which of the following SQL statements will display the name of each publisher that publishes a book classified in the COMPUTER category?
SELECT UNIQUE name FROM books NATURAL JOIN publisher WHERE category = 'COMPUTER';
Structure of the BOOKS table Structure of the PUBLISHER table Which of the following SQL statements will display the title of each book in the BOOKS table and the name of its publisher?
SELECT title, name FROM publisher NATURAL JOIN books;
Structure of the PROMOTION table Structure of the BOOKS table Structure of the ORDERITEMS table How many joining conditions will be required in an SQL statement that is used to determine the gift that corresponds to each book in the BOOKS table?
1
If the first table in a Cartesian join has five rows and the second table has three rows, the results will consist of ____________________ rows.
15 or fifteen
Structure of the CUSTOMERS table Structure of the ORDERS table Structure of the ORDERITEMS table Structure of the BOOKS table To display the name of each customer and the title of each book purchased by the customers would require how many join conditions?
3
To display the name of each customer and the title of each book purchased by the customers would require how many join conditions?
3
A table alias can consist of a maximum of ____ characters.
30
A table alias can have a maximum of ____________________ characters.
30 of thirty
In a Cartesian join, linking a table that contains 10 rows to a table that contains 9 rows will result in ____ rows being displayed in the output.
90
Which of the following keywords is used to create an equality join?
ALL OF THE ABOVE
Which of the following keywords is used to create an equality join?
ALL OF THE ABOVE
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
A column qualifier indicates the column containing the data being referenced. _________________________
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
If a Cartesian join is used to link table A which contains five rows to table B which contains eight rows, there will be 13 rows in the results.
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 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
The JOIN keyword is used in the WHERE clause to indicate the tables that should be joined or linked
FALSE
The JOIN keyword is included in which of the following clauses?
FROM
If you are joining four tables in a SELECT statement, three joining conditions will be required. _________________________
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?
FROM orders o, customers c
The ____________________ keyword can be included in the FROM clause to link tables.
JOIN
Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column?
JOIN...ON
If you are attempting to join two tables that have multiple common columns, which of the following JOIN keywords should be used to specify how the tables should be linked?
JOIN...USING
Data stored in separate tables can be reconstructed through the use of
JOINS OR JOIN CONDITIONS
Structure of the BOOKS table Structure of the PUBLISHER table Which of the following SQL statements will display the name of each publisher that publishes a book classified in the COMPUTER category?
SELECT UNIQUE name FROM books NATURAL JOIN publisher WHERE category = 'COMPUTER';
Which of the following is a valid SQL statement?
SELECT c.customer#, order#, orderdate, shipdate FROM customers c, orders c WHERE c.customer# = o.customer#;
Structure of the ORDERS table Structure of the CUSTOMERS table Which of the following is a valid SQL statement?
SELECT c.customer#, order#, orderdate, shipdate FROM customers c, orders c WHERE c.customer# = o.customer#;
Structure of the ORDERS table Structure of the CUSTOMERS table 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;
Structure of the PROMOTION table Structure of the BOOKS table Structure of the ORDERITEMS table 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;
Contents of the PROMOTION table Structure of the BOOKS table 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';
Structure of the ORDERS table Structure of the CUSTOMERS table 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';
Structure of the CUSTOMERS table Structure of the ORDERS table Structure of the ORDERITEMS table Structure of the BOOKS table 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 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#;
Structure of the ORDERS table Structure of the CUSTOMERS table 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, orders WHERE orders.customer# (+) = customers.customer#;
Structure of the CUSTOMERS table Structure of the ORDERS table Structure of the ORDERITEMS table Structure of the BOOKS table Which of the following SQL statements will display the title of all books that have had multiple copies requested in a single order?
SELECT title FROM books NATURAL JOIN orderitems WHERE qty > 1;
Structure of the BOOKS table Structure of the PUBLISHER table 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 BOOKS table 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 types of joins refers to joining a table to itself?
SELF-JOIN
_____ operators are used to combine the results of multiple queries.
SET
A table alias is assigned in the FROM clause.
TRUE
Set operators are used to combine the results of multiple queries.
TRUE
The INTERSECT set operator only displays the rows returned by both queries.
TRUE
The NATURAL JOIN keywords can be used to link two tables that have a commonly named and defined column.
TRUE
The number of joining conditions required to join tables is always one less than the number of tables being joined.
TRUE
When combining the results of two SELECT statements with the UNION keyword, duplicate rows are suppressed in the results.
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 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
When two tables that share more than one common column are being joined, the JOIN...____________________ keywords are normally used in the FROM clause to join the tables.
USING
Structure of the CUSTOMERS table Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?
both a and b
Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?
both a and b
Structure of the ORDERS table Structure of the CUSTOMERS table 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#;
both a and b
Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?
both a and b
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 terms refers to a column with equivalent data that exists in two or more tables?
common column
A column qualifier indicates the column containing the data being referenced. _________________________
false
A column qualifier is separated from the column name with a colon.
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) non-equality join is also known as an equijoin, inner join, or simple join. _________________________
false
A(n) non-equality join is when a table is joined to itself. _________________________
false
A(n) outer join can be created by not including a joining condition in a SELECT statement.
false
An outer join only lists rows that contain a match in both tables.
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
An outer join operator consists of a minus sign enclosed in parentheses, (-).
false
Data stored in multiple tables can be combined through the use of an ORDER BY clause
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 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 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 JOIN...USING keywords are used to join two tables that do not have a commonly named and defined column. _________________________
false
The NATURAL JOIN keywords can be used to create non-equality joins.
false
The ON clause can be used only if the tables being joined have a common column with the same name. _________________________
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
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
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
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
A join that is based upon data having equivalent data in common columns is known as a(n) ____________________ join.
inner or equality or equijoin or simple
A join that is based upon data having equivalent data in common columns is known as a(n) ____________________ join.
inner or equality or equijoin or simple
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
The ____________________ set operator is used to display the rows returned by both SELECT statements.
intersect
If you are attempting to join two tables that have multiple common columns, which of the following JOIN keywords should be used to specify how the tables should be linked?
join...using
A table alias cannot be assigned in the FROM clause if which of the following keywords is used to join tables?
natural join
A(n) ____________________ join is used when the related columns between two tables cannot be joined through an equal sign.
non-equality or nonequality
Contents of the PROMOTION table Structure of the BOOKS table 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
In which of the following examples is the ORDERS table used as a column qualifier?
orders.order#
In which of the following examples is the ORDERS table used as a column qualifier?
orders.order#
To display rows from one table that do not have a corresponding row in the other table, you must create a(n) ____________________ join.
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 or (+) or +
Which of the following types of joins refers to joining a table to itself?
self-join
____________________ 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(n) ____________________ is an alternate name temporarily assigned to a table.
table alias
A(n) ____________________ is an alternate name temporarily assigned to a table.
table alias or alias
A Cartesian join can be created by not including a joining condition in the WHERE clause of a SELECT statement. _________________________
true
A(n) Cartesian Join replicates each row from the first table with every row from the second table.
true
A(n) Cartesian Join replicates each row from the first table with every row from the second table. _________________________
true
A(n) inner join will only include rows that have matching rows in the other table
true
A(n) inner join will only include rows that have matching rows in the other table. _________________________
true
By default, the JOIN keyword creates an inner join. _________________________
true
By default, use of the JOIN keyword creates an inner join.
true
Column qualifiers must be included in the WHERE clause if the columns used to join the tables have the same column names.
true
Equality, non-equality, and self-joins are all categorized as inner joins.
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
Set operators are used to combine the results of multiple queries.
true
Tables can be joined in the FROM clause or the WHERE clause of a SELECT statement. _________________________
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 outer join operator is placed on the side of the comparison that is deficient or is missing the matching rows.
true
The outer join operator is placed on the side of the joining condition that references the table containing the deficient rows. _________________________
true
When a self-join is created, each copy of the table must be assigned a table alias.
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 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;
Structure of the CUSTOMERS table Structure of the ORDERS table Structure of the ORDERITEMS table Structure of the BOOKS table Which of the following will display all books that were published at least three years before they were ordered?
SELECT title FROM orders o, orderitems oi, books b WHERE b.isbn = oi.isbn AND oi.order# = o.order# AND (orderdate-pubdate)/365 >= 3;
Which of the following is used to create an outer join in a WHERE clause?
outer join operator (+)