Exam 6: Advanced SQL

An SQL query that implements an outer join will return rows that do not have matching values in common columns.

The UNION clause is used to:

What strategies can be used to write queries that run more efficiently?

The following code is an example of a(n): SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T.CustomerID;

Triggers can be used to ensure referential integrity, enforce business rules, create audit trails, and replicate tables, but cannot call other triggers.

In SQL, a(n) ________ subquery is a type of subquery in which processing the inner query depends on data from the outer query.

A routine is a named set of SQL statements that are considered when a data modification occurs.

A procedure is run by calling it by its name.

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?

SQL allows one to calculate linear regressions, moving averages, and correlations without moving the data outside of the database.

In which of the following situations would one have to use an outer join in order to obtain the desired results?

One major advantage of the outer join is that:

The following query will execute without errors. SELECT Customer.Customer_Name, Salesman.Sales_Quota FROM Customer WHERE Customer.Salesman_ID = (SELECT Salesman_ID WHERE Lname = 'SMITH');

EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows.

Correlated subqueries are less efficient than queries that do not use nesting.