Chap 7 DRM

derived table

A ________ is a temporary table used in the FROM clause of an SQL query. Question options: A) view table B) derived table C) correlated subquery D) trigger

both A and C.

A join in which the joining condition is based on equality between values in the common columns is called a(n): Question options: A) natural join. B) unilateral join. C) equi-join. D) both A and C.

causes two tables with a common domain to be combined into a single table or view.

A join operation: Question options: A) causes two tables with a common domain to be combined into a single table or view. B) causes two disparate tables to be combined into a single table or view. C) brings together data from two different fields. D) is used to combine indexing operations.

natural join.

A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n): Question options: A) inner join. B) equi-join. C) natural join. D) multivariate join.

triggers

A named set of SQL statements that are considered when a data modification occurs are called: Question options: A) triggers. B) trapdoors. C) stored procedures. D) treatments.

self-join.

A type of join where a table is joined to itself is called a(n): Question options: A) self-join. B) ternary join. C) unnatural join. D) pinned join.

use a lot of self-joins.

All of the following are guidelines for better query design EXCEPT: Question options: A) write simple queries. B) understand how indexes are used in query processing. C) use a lot of self-joins. D) retrieve only the data that you need

is used to generate appropriate SQL code on the fly as an application is processing.

Dynamic SQL: Question options: A) is used to generate appropriate SQL code on the fly as an application is processing. B) creates a less flexible application. C) is not used widely on the Internet. D) is quite volatile.

using the CASE keyword in a statement.

Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by: Question options: A) using the immediate if statement. B) using a subquery. C) using the CASE keyword in a statement. D) using the if-then-else construct.

EXEC SQL

In order to embed SQL inside of another language, the ________ statement must be placed before the SQL in the host language. Question options: A) EXEC SQL B) SQL SQL C) RUN SQL D) GET SQL

A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero).

In which of the following situations would one have to use an outer join in order to obtain the desired results? Question options: A) A report is desired that lists all customers and the total of their orders. B) A report is desired that lists all customers who placed an order. C) A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero). D) There is never a situation that requires only an outer join.

subquery

SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query. Question options: A) subquery B) union C) grouping D) joining

allows one to combine the INSERT and UPDATE operations.

The MERGE command: Question options: A) is always a single table operation. B) allows one to combine the INSERT and DELETE operations. C) allows one to combine the INSERT and UPDATE operations. D) joins 2 tables together.

combine the output from multiple queries into a single result table.

The UNION clause is used to: Question options: A) combine the output from multiple queries into a single result table. B) find all rows that do not match in two tables. C) find all rows that are in one table, but not the other. D) join two tables together to form one table

equi-join.

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; Question options: A) equi-join. B) subquery. C) Full Outer JOIN. D) Right Outer JOIN.

Subquery

The following code is an example of a: SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode FROM Customer_T WHERE Customer_T.CustomerID = (SELECT Order_T.CustomerID FROM Order_T WHERE OrderID = 1008); Question options: A) Subquery. B) Correlated subquery. C) JOIN. D) FULL OUTER JOIN.

all rows of the Customer_T Table regardless of matches with the Order_T Table.

The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T LEFT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID; Question options: A) all rows of the Order_T Table regardless of matches with the Customer_T Table. B) only rows that don't match both Customer_T and Order_T Tables. C) only rows that match both Customer_T and Order_T Tables. D) all rows of the Customer_T Table regardless of matches with the Order_T Table.

routines

While triggers run automatically, ________ do not and have to be called. Question options: A) routines B) trapdoors C) selects D) updates

Exists

________ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows. Question options: A) Extents B) Having C) In D) Exists

Correlated subqueries

________ use the result of the outer query to determine the processing of the inner query. Question options: A) Inner subqueries B) Correlated subqueries C) Subqueries D) Outer subqueries