BCIS 5420 FINAL

DML

INSERT INTO is an example of ________ code.

Where

In an SQL statement, which of the following parts states the conditions for row selection?

Both output compatible data types for each column and return the same number of rows

In order for two queries to be UNION compatible they must

EXEC SQL

In order to embed SQL inside of another language, the ___________ statement must be placed before the SQL in the host language

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

Secondary Key

One field or combination of fields for which more than one record may have the same combination of values is called a

Information is not lost

One major advantage of the outer join is that

Primary and Foreign Keys

Referential Integrity Constraints are generally established between:

Set Theory

Relational databases are heavily based on the mathematical concept of

Referential Integrity

Requiring a CustomerID to exist in a Primary table before it can exist in a foreign table is an example of:

Subquery

SQL provides the ____________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query

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

True

The Content of dynamic views is generated when they are referenced

data integrity

The Storage format for each attribute from the logical data model is chosen to maximize __________ and minimize storage space

TRUE

The UNION clause is used to combine the output from multiple queries into a single result table

Schema

The ______ is the structure that contains descriptions of objects such as tables and views created by users.

no primary key attribute can be null

The entity integrity rule states that:

FROM

The first part of an SQL query to be read is the ________ statement.

Natural Join

The most commonly used form of join operation is the

Transactions are processed as sets, reducing system overhead

User-defined transactions can improve system performance because:

Deletes all records from customer_t where the state is equal to HI

What does the SQL statement do Delete from Customer_T where state = 'HI';

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

What is the following SQL an example of SELECT Customer_T.CustomerID, CustomerName, OrderID FROM Customer_T LEFT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;

No two rows in a relation are identical

Which of the following are properties of relations?

Count

Which of the following counts ONLY rows that contain a value?

The SELECT clause is processed before the ORDER BY clause.

Which of the following is true of the order in which SQL statements are evaluated?

Hashed

Which type of file is easiest to update

DCL (Data Control Language)

_____ Is a set of commands used to control a database, including security.

Data Integrity

________ Is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated

Deletion

__________problems are encountered when removing data with transitive dependencies

Drop

any create command may be reversed by using a __________ Command

DML (Data Manipulative "manage" Language)

is a set of commands used to update ad query a database

DDL (Data Definition "design" Language)

is typically used during the physical design phase of the development process.

alter table

the SQL command ___________ adds one or more new columns to and existing table.

combine the output from multiple queries into a single result table

the UNION clause is used to

UNION

the _________________ operator is used to combine the output from multiple queries into a single result table

determinant

the attribute on the left-hand side of the arrow in a functional dependency is the

identify each attribute and its characteristics

the first in a series of steps to follow when creating a table is to

E.F. Codd

the main concept of relational databases was published in 1970 by

*

which is the wild card operator in SQL statements

Pointer

A _______________ is a field of data used to locate a related field or record

The key must indicate the rows position in the table

A candidate key must satisfy all of the following conditions EXCEPT

Functional Dependency

A constraint between two attributed is called a

True

A database table is defined using the data definition language (DDL)

Data Type

A detailed coding scheme recognize by system software for representing organizational data is called a

extra space and update time

A disadvantage of partitioning is

Integrity Constraints

A domain definition consists of all of the following components EXCEPT

Logical Specifications

A form of database specification which maps conceptual requirements is called

Transitive Dependency

A functional dependency between two or more nonkey attributes is called a

Outer Join

A join in which rows that do not have matching values in common columns are still included in the result table is called a

Equi-join and Natural Join

A join in which the joining condition is based on equality between values in the common columns is called a

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

A join operation

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

Parallel query processing

A method that speeds query processing by running a query at the same time against several partitions of a table using multiprocessors is called

Enterprise Key

A primary key whose value is unique across all relations is called a

Second Normal Form

A relation that contains no multivalued attributes and has nonkey attributes solely dependent on the primary key but contains transitive dependencies is in which normal form

Subquery

The following code is an example of SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode FROM Customer_T WHERE Customer_T.CustomerID= (Select Order_T.CustomerID From Order_T Where OrderID = 1008);

Fourth Normal Form

The normal form which deals with multivalued dependencies is called

Boyce-Codd normal form

The normal form which removes any remaining functional dependencies because there was more than one primary key for the same nonkeys is called

two

The outer join syntax does not apply easily o a join condition of more than _______ Tables

Field

The smallest unit of application data recognized by system software is a

Distinct

To eliminate duplicate rows in a query, the ________ qualifier is used in the SQL select command.

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

What is the following SQL an example of SELECT Customer_T.CustomerID, CustomerName, OrderID FROM Customer_T RIGHT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;

Equi-join

What is the following SQL an example of 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

What is the output of the SQL SELECT Customer_T.CustomerID, Order_T.CustomerID,CustomerName, OrderID FROM Customer_T, Order_T Where Customer_T.CustomerID=Order_T.CustomerID;

A listing of the owner of the customer table

What results would the following SQL statement produce SELECT Owner, Table_Name FROM Dba_Tables Where Table_Name = 'CUSTOMER';

Selects all the fields from the Customer table for each row with a customer labeled "Best"

What will SQL code do? Select * From Customer Where Cust_Type = "Best"

The average Standard_Price of all products in Product_V

What will SQL code do? Select Avg(standard_price) as average from Product_V;

How many products have product descriptions in the product table

What will SQL code do? Select Count(Product_Description) from Product_T;

The total price of all products that are of type wood

What will SQL code do? Select sum(Standard_price) as Total_Price from Product_V where Product_Type = 'WOOD';

The Item_No of all orders that had more than 10 items

What will SQL statement do? Select Item_No from Order_V where quantity >10;

The Item_No and description for all items weighting between 101 and 199

What will SQL statement do? Select Item_No, description from item where weight > 100 and weight <200

A listing of all drivers who made more than 2 deliveries as well as a count on the number of deliveries

What will be returned? Select Driver_No, count(*) as num_deliveries from deliveries group by driver_no having count(*)>2;

First normal form

When all multivalued attributed have been removed from a relation, it is said to be in:

Composite

When there are two Primary keys referenced in a table they are called?

Select min(Standard_Price) from Product_V;

Which of the following will produce the minimum of all standard prices?

Sequential

Which type of file is most efficient with storage space

EXISTS

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

Vertical

__________Partitioning distributed the columns of a table into several separate physical records

True

A Transaction is the complete set of closely related update commands that must all be done, or none of them done, for the database to remain valid

Dynamic

A _________ view is materialized when referenced

Derived table

A __________ is a temporary table used in the FROM clause of an SQL query

Normalized relations

A requirement to begin designing physical files and databases is:

Referential integrity constraint

A rule that states that each foreign key value must match a primary key value in the other relation is called the:

Relation

A two-dimensional table of data sometimes is called a

subquery

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

use a lot of self-joins

All of the following are guidelines for better query design EXCEPT

Use a lot of storage space

All of the following are objectives when selecting a data type EXCEPT

Maximize Storage space

All of the following are the main goals of normalization EXCEPT

Boolean

All of the following are valid datatypes in Oracle 11g Except

Foreign Key

An Attribute in a relation of a database that serves as the primary key of another relation in the same database is called

Homonym

An Attribute that may have more than one meaning is called a

Blob

An appropriate datatype for adding sound clip would be

False

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

Join Index

An index on columns from two or more tables that come from the same domain of values is called a

Transaction Volumes

Database access frequencies are estimated from

Descriptions

Designing physical files requires__________of where and when data are used in various ways

Horizontal Partitioning

Distributing the rows of data into separate files is called

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

Dynamic SQL

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

Embedded SQL consists of