Databases Reference
In-Depth Information
8.
A compound query that needs to find only the rows that are the same between the two queries should use
the __________ set operator.
Answer:
INTERSECT
9.
True or false: The IN operator cannot be used with a single-row subquery.
Answer:
False, using IN with a single-row subquery would be equivalent to using =.
10.
Put the set operators UNION, UNION ALL, INTERSECT, and MINUS in order of precedence.
Answer:
All of those operators have equal precedence and are evaluated left to right in a compound query.
11.
What can be used to change the precedence of a pair of queries in a compound query with more than two queries?
Answer:
As with any other part of a SQL query, parentheses may be used to change the evaluation order of
the set operators.
Chapter 7
1.
A COMMIT occurs under which three conditions within a transaction?
Answer:
A COMMIT occurs from an explicit COMMIT command, after a DDL or DCL command is executed,
or when a SQL*Plus or iSQL*Plus session is exited normally.
2.
Under what circumstances can a foreign key column not match the defined primary key value in the parent table?
Answer:
A foreign key column may not match the defined primary key value in the parent table when the
foreign key column allows NULL values and is NULL.
3.
True or false: A CHECK constraint cannot check for NULL values.
Answer:
False, a CHECK constraint can use IS NULL and IS NOT NULL to check for the existence of NULL values
in one or more columns of the table.
4.
How are PRIMARY KEY constraints and UNIQUE constraints different? List two ways.
Answer:
PRIMARY KEY constraints do not allow NULL values, and there can be only one primary key per table.
5.
What are the three conditions that may be specified, either implicitly or explicitly, on a foreign key column
when the primary key column in the parent table is deleted?
Answer:
By default, the row in the parent table will not be deleted if rows exist in the child table that have
a foreign key referencing the parent table's primary or unique key. Alternatively, the child table's foreign
key may be set to NULL (SET NULL), or the entire row in the child table may be deleted if a parent row is
deleted (CASCADE).
6.
Write a CHECK constraint that ensures MAX_SALARY is at least 10,000 more than MIN_SALARY.
Answer:
This constraint ensures MAX_SALARY is at least 10,000 more than MIN_SALARY: check (max_salary
- 10000 > min_salary)
7.
What statement will allow a partial rollback of certain DML statements within a transaction?
Answer:
The ROLLBACK TO SAVEPOINT <savepoint>; statement will allow a partial rollback of certain
DML statements.
