asdfsa #2
What results would the following SQL statement produce? select owner, table_name from dba_tables where table_name = 'CUSTOMER';
A listing of the owner of the customer table
In which of the following situations would one have to use an outer join in order to obtain the desired results?
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).
________ use the result of the outer query to determine the processing of the inner query.
Correlated subqueries
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);
Correlated subquery.
In order to embed SQL inside of another language, the ________ statement must be placed before the SQL in the host language.
EXEC SQL
________ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows.
Exists
EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows.
TRUE
The ________ operator is used to combine the output from multiple queries into a single result table.
UNION
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;
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 RIGHT OUTER JOIN Order_T ON Customer_T.CustmerID = Order_T.CustomerID;
all rows of the Order_T Table regardless of matches with the Customer_T Table.
A join in which the joining condition is based on equality between values in the common columns is called a(n):
both equi-join and natural join.
In order for two queries to be UNION-compatible, they must:
both output compatible data types for each column and return the same number of rows.
A join operation:
causes two tables with a common domain to be combined into a single table or view.
The UNION clause is used to:
combine the output from multiple queries into a single result table.
In SQL, a(n) ________ subquery is a type of subquery in which processing the inner query depends on data from the outer query.
correlated
A ________ is a temporary table used in the FROM clause of an SQL query.
derived table
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;
equi-join
SQL-invoked routines can be:
functions or procedures.
A procedure is:
given a reserved SQL name.
Embedded SQL consists of:
hard-coded SQL statements included in a program written in another language.
One major advantage of the outer join is that:
information is not lost.
Dynamic SQL
is used to generate appropriate SQL code on the fly as an application is processing.
Explicit commands to manage transactions are needed when:
multiple SQL commands must be run as part of a transaction.
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):
natural join.
The most commonly used form of join operation is the:
natural join.
The following code would include: SELECT customer_t.customerID, Order_t.customerID, customerName, orderID FROM customer_t, Order_T WHERE customer_T.customerID = order_t.customerID;
only rows that match both Customer_T and Order_T Tables.
A join in which rows that do not have matching values in common columns are still included in the result table is called a(n):
outer join.
While triggers run automatically, ________ do not and have to be called.
routines
All of the following are advantages of SQL-invoked routines EXCEPT:
security.
A type of join where a table is joined to itself is called a(n):
self-join.
An operation to join a table to itself is called a(n):
self-join.
An interactive command that can be used to dynamically control a user session for appropriate integrity measures is:
set autocommit.
SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query.
subquery
A type of query that is placed within a WHERE or HAVING clause of another query is called a:
subquery.
User-defined transactions can improve system performance because:
transactions are processed as sets, reducing system overhead.
A named set of SQL statements that are considered when a data modification occurs are called:
triggers.
The outer join syntax does not apply easily to a join condition of more than ________ tables.
two
All of the following are guidelines for better query design EXCEPT:
use a lot of self-joins.
Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by
using the CASE keyword in a statement.
