Chapter 6: Database Management

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

A procedure is:

Called by name

A function has only input parameters but can return multiple values. (T/F)

False

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

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

OLAP functions

Triggers have three parts: the event, the condition, and the action. (T/F)

True

User-defined data types:

can have defined functions and methods.

SQL-invoked routines can be:

functions or procedures

While triggers run automatically, ________ do not and have to be called.

routines

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

subquery

The UNION clause is used to:

Combine the output from multiple queries into a single result table

IF-THEN-ELSE logical processing cannot be accomplished within an SQL statement. (T/F)

False

It is better not to have a result set identified before writing GROUP BY and HAVING clauses for a query. (T/F)

False

Subqueries can only be used in the WHERE clause. (T/F)

False

The following code is an example of a correlated subquery. SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState,CustomerPostalCodeFROM Customer_T WHERE Customer_T.CustomerID =(SELECT Order_T.CustomerID FROM Order_T WHERE OrderID = 1008); (T/F)

False

User-defined functions can improved system performance because they will be processed as sets rather than individually, thus reducing system overhead. (T/F)

False

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

A procedure is run by calling it by its name. (T/F)

True

Constraints are a special case of triggers. (T/F)

True

Correlated subqueries are less efficient than queries that do not use nesting. (T/F)

True

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

The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T LEFT OUTER JOIN Order_T ONCustomer_T.CustomerID = Order_T.CustomerID;

All rows of the Customer_T table regardless of matches with the Order_T Table

A join operaton:

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

The natural join is very rarely used. (T/F)

False

Triggers can be used to ensure referential integrity, enforce business rules, create audit trails, replicate tables, but cannot call other triggers. (T/F)

False

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

False

A base table is the underlying table that is used to create views. (T/F)

True

When EXISTS or NOT EXISTS is used in a subquery, the select list of the subquery will usually just select all columns as a placeholder because it doesn't matter which columns are returned. (T/F)

True

The outer join syntax does not apply easily to a join condition of more than ________ tables.

Two

The MERGE command:

allows one to combine the INSERT and UPDATE operations.

What would the following view contain for values? Create view CustomerOrders asSelect CustID, Count(*) as TotOrders, Sum(ordertotal) as Value From customer inner join sale on customer.customer_id = sale.customer_id;

A listing of the customer ID as well as the total number of orders and the total amount spent by the customer

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 who did not place an order during the month (their total will be zero).

The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T RIGHT OUTER JOIN Order_T ONCustomer_T.CustomerID = Order_T.CustomerID;

All rows of the Order_T table regardless of matches with the customer_T table

If the DBA wishes to describe all tables in the database, which data dictionary view should be accessed in Oracle?

DBA_TABLES

A subquery in which processing the inner query depends on data from the outer query is called a codependent query. (T/F)

False

Combining a table with itself results in a faster query. (T/F)

False

EXISTS takes a value of false if the subquery returns an intermediate result set. (T/F)

False

MULTISET is similar to the table datatype. (T/F)

False

An operation to join a table to itself is called a(n):

Self-join

A dynamic query is created by the user. (T/F)

True

A join in which the joining condition is based on equality between values in the common column is called an equi-join. (T/F)

True

A materialized view is not persistent. (T/F)

True

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. (T/F)

True

A trigger is a named set of SQL statements that are considered when a data modification occurs. (T/F)

True

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

True

In order to find out what customers have not placed an order for a particular item, one might use the NOT qualifier along with the IN qualifier. (T/F)

True

Joining tables or using a subquery may produce the same result. (T/F)

True

SQL allows one to calculate linear regressions, moving averages, and correlations without moving the data outside of the database. (T/F)

True

The UNION clause is used to combine the output form multiple queries into a single result table. (T/F)

True

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 s2WHERE s1.Salesman_ID != s2.Salesman_ID); (T/F)

True

The following statement is an example of: CREATE TABLE Customer_t ( CustNmbr number(11,0), CreditLimit number(6,2), CustStart date, CustEnd date, PERIOD for Custperiod(CustStart,CustEnd));

an application time period table

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 materialized view is/are:

copies or replica of data based on queries

One major advantage of the outer join is that:

information is not lost.

The ________ DBA view shows information about all users of the database in Oracle.

DBA_USERS