Deck 5: Multiple Table Queries

Question 1

You can use the EXISTS operator to retrieve data from more than one table.

Answer 1

True

Question 2

When a subquery involves a table listed in the outer query, the subquery is called a(n) _____.

A) inner outer subquery
B) correlated subquery
C) reverse subquery
D) parameter subquery

Answer 2

B

Question 3

In which type of join will all rows from the table listed second in the query be included regardless of whether they match rows from the table listed first in the query?

A) left inner join
B) left outer join
C) right outer join
D) right inner join

Answer 3

C

Question 4

To retrieve data from multiple tables in a query, you can use a subquery containing the _____.

A) AND operator
B) IN operator
C) OR operator
D) NOT operator

Question 5

You create an alias by typing the name of the table, pressing the _____, and then typing the name of the alias.

A) Tab key
B) Backspace key
C) Enter key
D) Spacebar

Question 6

A condition is true if it satisfies any value (one or more) produced by a subquery that is preceded by the _____.

A) IS SOME operator
B) ALL operator
C) SOME operator
D) ANY operator

Question 7

You can join tables by using a condition in the _____.

A) FROM clause
B) WHERE clause
C) SELECT clause
D) JOIN clause

Question 8

The _____ of two tables is a table containing every row that is in either the first table, the second table, or both tables.

A) intersection
B) union
C) minus
D) difference

Question 9

When is it necessary to qualify a column name while joining tables in MySQL?

A) It is necessary when the tables have identical column names.
B) It is necessary when the tables have different column names.
C) It is never necessary when you join tables in MySQL.
D) It is necessary every time you join tables in MySQL.

Question 10

When using a DBMS without an optimizer, the formulation of a query can make a difference in the speed with which the query is executed.

Question 11

The ____ of two tables is the set of all rows that are in the first table but that are not in the second table.

A) union
B) inner join
C) intersection
D) difference

Question 12

You can use the IN operator to retrieve data from multiple tables.

Question 13

The _____ of two tables is a table containing all rows that are in both tables.

A) intersection
B) union
C) difference
D) minus

Question 14

To create a condition that is true if one or more rows are obtained when the subquery is executed, you can precede that subquery with which operator?

A) IS NULL
B) IS TRUE
C) IS EXIST
D) EXISTS

Question 15

The product of two tables is formally called the _____.

A) inner join
B) outer join
C) SQL product
D) Cartesian product

Question 16

Nina is writing a MySQL query to list the descriptions of items in the CUPCAKES table, which are identified by PROD_ID values. The ORDER_LINE table includes product and quantity information from bakery orders, which are identified by ORDER_NUM values. What can she add to the following query to limit the list to descriptions of cupcakes purchased through order 342? SELECT DESCRIPTION FROM CUPCAKES WHERE

A) PROD_ID IN(ORDER_LINE WHERE(ORDER_NUM = '342'));
B) EXISTS (PROD_ID WHERE (ORDER_LINE.ORDER_NUM = '342'));
C) PROD_ID IN(SELECT PROD_ID FROM ORDER_LINE WHERE(ORDER_NUM = '342'));
D) PROD_ID EXISTS(SELECT PROD_ID FROM ORDER_LINE WHERE(ORDER_NUM = '342'));

Question 17

In a nested query, the outer query is evaluated first.

Question 18

In which type of join are all rows from the table listed first in the query included, regardless of whether they match rows from the table listed second in the query?

A) left inner join
B) left outer join
C) right inner join
D) right outer join

Question 19

You can use the ALL and ANY operators with subqueries to produce a single column of numbers.

Question 20

To qualify a column name, precede the name of the column with the name of the table, followed by a(n) _____.

A) comma (,)
B) period (.)
C) percent sign (%)
D) asterisk (*)

Question 21

Suppose you have an ADDRESS_BOOK table containing the first and last names, addresses, and birthdays of many people you know. How can you write a query to display the names of pairs of your acquaintances who have the same birthday?

A) create a new set using ADDRESS_BOOK
B) perform an outer join on ADDRESS_BOOK
C) self-join ADDRESS_BOOK to itself
D) create a second BIRTHDAY table

Question 22

There are three set operations: union, intersection, and difference. Define each of these operations. Which are supported by Oracle?

Question 23

Describe the types of outer joins.

Question 24

One use case for an alias is a query that joins a table to itself, known as a(n) _____.

A) inner join
B) outer join
C) union
D) self-join

Question 25

When tables are listed in the FROM clause of an SQL query, you can give them alternate names, also known as _____.

A) sets
B) qualifiers
C) groups
D) aliases

Question 26

When a query includes the clause FROM CLIENT A, CLIENT B, SQL will _____.

A) perform an inner join
B) perform an outer join
C) treat it as a query of two tables, A and B
D) treat it as a union of two tables, A and B

Question 27

Which element belongs in the blank if you want to display data from all rows with a credit limit greater than the credit limit of one or more category B cardholders? WHERE (CREDIT_LIMIT > _____ (SELECT CREDIT_LIMIT FROM CARDHOLDER WHERE CATEGORY = 'B'))

A) ANY
B) ALL
C) UNION
D) INTERSECT

Question 28

Which statement about table joins in SQL is true?

A) Self-joins usually do not include WHERE or ORDER BY clauses.
B) You cannot perform a self-join on a table's primary key column.
C) Joins must be performed between at least two different tables.
D) SQL has no problem treating one table with two aliases as two tables.

Question 29

What steps should you take to construct the SQL command to join (relate) tables?

Question 30

What steps should you take to construct a detailed query in a step-by-step fashion?

Question 31

What is the term for the letter S or the letter D in the following query? ​
SELECT S.SERVER_ID, S.LAST_NAME, D.DINER_ID, D.LAST_NAME
FROM SERVER S, DINER D
WHERE (S.SERVER_ID = D.SERVER_ID)

A) alias
B) operator
C) qualifier
D) set

Question 32

Which statement about nested queries in SQL is FALSE?

A) In small databases, there is not a significant time difference between nested subqueries and table joins.
B) Typically, nested subqueries perform faster than queries that join tables.
C) A nested query is a subquery within a subquery.
D) Executing each subquery in a nested query produces a temporary table.

Question 33

What is the benefit of using an alias in MySQL?

A) It improves performance.
B) It simplifies complex queries.
C) It prevents confusion of columns from different tables.
D) It is required for calculations.

Question 34

Why do you use the ANY and ALL operators? What is the difference between the two operators?