CS 4420 Intro to Database Systems Final Chapter 7

A function has only input parameters but can return multiple values.

False

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

False

An equi-join is a join in which one of the duplicate columns is eliminated in the result table.

False

DBA_USERS contains comments on all tables in an Oracle database.

False

EXISTS takes a value of false if the subquery returns an intermediate result set.

False

MULTISET is similar to the table datatype.

False

Specifying the attribute names in the SELECT statement will make it easier to find errors in queries and also correct for problems that may occur in the base system.

False

The natural join is very rarely used.

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.

False

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

OLAP functions.

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:

Persistent Stored Modules.

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

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 s2 Where s1.salesman_id != s2.salesman_id);

True

User-defined data types:

can have defined functions and methods.

SQL-invoked routines can be:

functions or procedures.

Embedded SQL consists of:

hard-coded SQL statements included in a program written in another language.

Dynamic SQL:

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

The following code would include: Select Customer_T.CustomerID, Order_T.CusomterID, 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

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.

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

________ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows.

Exists

IF-THEN-ELSE logical processing cannot be accomplished within an SQL statement.

False

Persistent Sorted Modules are extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions.

False

Subqueries can only be used in the WHERE clause.

False

Transaction integrity commands are not used to identify whole units of database changes that must be completed in full for the database to retain integrity.

False

User-defined functions can improve system performance because they will be processed as sets rather than individually, thus reducing system overhead.

False

A correlated subquery is executed once for each iteration through the outer loop.

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.

True

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

True

Establishing IF-THEN-ELSE logical processing within an SQL statement can now be accomplished by using the CASE keyword in a statement.

True

Figuring out what attributes you want in your query before you write the query will help with query writing.

True

Triggers have three parts: the event, the condition, and the action.

True

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.

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.

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

DBA_USERS