Exam 7: Advanced SQL

A natural join is the same as an equi-join, except that it is performed over matching columns that have been defined with the same name, and one of the duplicate columns is eliminated.

A new set of analytical functions added in SQL:2008 is referred to as:

User-defined transactions can improve system performance because:

All of the following are advantages of SQL-invoked routines EXCEPT:

The following SQL statement is an example of a correlated subquery. select first_name, last_name, total_sales from salesman s1 where total_sales > all (select total_sales from salesman s2 where s1.salesman_id != s2.salesman_id);

Using an outer join produces this information: rows that do not have matching values in common columns are not included in the result table.

The ________ operator is used to combine the output from multiple queries into a single result table.

________ use the result of the outer query to determine the processing of the inner query.

A procedure is run by calling it by its name.

All of the following are new data types added in SQL:2008 EXCEPT:

What is a derived table? When is it used? Can you describe any situations where you would have to use it over a subquery in the WHERE clause?

A type of query that is placed within a WHERE or HAVING clause of another query is called a:

The UNION clause is used to:

Extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions are called:

The MERGE command:

A transaction is the complete set of closely related update commands that must all be done, or none of them done, for the database to remain valid.

SQL statements can be included in another language, such as C or Java.

When is it better to use a subquery over using a join?

RDBMSs store database definition information in system-created tables which can be considered a data dictionary.

Joining tables or using a subquery may produce the same result.