Databases Reference
In-Depth Information
6.
To avoid a Cartesian product, a query with four tables must have at least how many join conditions
between tables?
Answer:
A query with four tables must have at least three join conditions to avoid a Cartesian product.
7.
To return all the rows in one table regardless of whether any rows in another table match on the join
condition, you would use what kind of a join?
Answer:
An outer join returns all rows in one table regardless of whether any rows in another table match
on the join condition.
8.
What is the symbol used to signify an outer join in a pre-Oracle9
i
query?
Answer:
A (+) is used to signify an outer join in a pre-Oracle9
i
query.
9.
A full outer join uses what SQL set operator in a pre-Oracle9
i
database query?
Answer:
A full outer join uses the UNION set operator in a pre-Oracle9
i
query.
10.
A primary key in one table would frequently be joined to what in a second table?
Answer:
A primary key in one table would frequently be joined to a foreign key in a second table.
Chapter 6
1.
A subquery is allowed in which parts of a SQL SELECT statement?
Answer:
A subquery is allowed in the SELECT clause, the FROM clause, and the WHERE clause.
2.
True or false: A correlated subquery references a table in the SELECT clause.
Answer:
False, the correlated subquery references a column in the main query.
3.
Which set operator will not remove duplicate rows from the result of a compound query?
Answer:
UNION ALL will not remove duplicate rows from the result of a compound query.
4.
What characteristics of the columns in a compound query using INTERSECT must match?
Answer:
The number of columns and their datatypes must match in a compound query using INTERSECT.
The lengths of the columns and the names do not need to match.
5.
How are NULL values handled using set operators in a compound UNION query?
Answer:
NULL values in one query are considered equal to NULL values in the other query, for the purposes
of eliminating duplicates in a UNION.
6.
Why are ROLLUP and CUBE the preferred methods for generating subtotals and grand totals for an
aggregate query?
Answer:
ROLLUP and CUBE need to make only one pass over the source table(s). Other methods, such as using
a UNION between two similar queries, will make more than one pass.
7.
Which operators can be used to compare a column to a single-row subquery?
Answer:
The following operators can be used to compare a column to a single-row subquery: =, !=, >, <,
>=, and <=.
