Chapters 6,7,11 - Final

The wildcard operator in SQL statements is

*

What will be returned when the following SQL statement is executed? SELECT driver_no, count(*) as num_deliveries FROM deliveries WHERE state = 'MA' GROUP BY driver_no;

A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state

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

What does the following SQL statement do? UPDATE Product_T SET Unit_Price = 775 WHERE Product_ID = 7

Changes the unit price of Product 7 to 775

The technique for optimizing the internal performance of the relational data model:

Clustering Data

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

FROM

What command can produce Scalar and vector aggregates?

GROUP BY

A view may not be updated directly if it contains

HAVING clause

Which of the following finds all groups meeting stated conditions? Where Find Select Having

Having

_______ differs from array because it can contain duplicates.

MULTI-SET

The new data types added in SQL 2008

MULTISET, XML, BIGINT

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

Subquery

What results will be produced by the following SQL query? SELECT SUM(standard_price) as Total_Price FROM Product_V WHERE Product_Type = 'WOOD';

The total price of all products that are of type wood

The purpose for the SQL standard is

To specify syntax and semantics of SQL data definition and manipulation

EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows.

True

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

Two

The ________ operator is used to combine the output from multiple queries into a single result table.

UNION

Multiple values returned from an SQL query that includes an aggregate function are called:

Vector Aggregates

In an SQL statement, what part states the condition for the row selection?

WHERE

RANK and DENSE-RANK are examples of:

Windows Functions

Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? INSERT INTO store VALUES ('234 Park Street')

You must specify the fields to insert if you are only inserting some of the fields.

The MERGE command

allows one to combine the INSERT and UPDATE operations.

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 procedure is

called by a name

User Defined data types

can have defined functions and methods

A join operatoin

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

The benefits of a standardized relational language include:

cross system communication

Any create command may be reversed by using a(n) ________ command.

drop

The advantages of SQL-Invoked routines is

efficiency, sharability, flexibility

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.

Embedded SQL consists of:

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

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

identify each attribute and its characteristics

One major advantage of the outer join is

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

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

natural join and equi-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

Indexes are created in most RDBMS to

provide rapid random and sequential access to base-table data.

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

routines

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

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

subquery

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

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

Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by:

using the CASE keyword in a statement

What does the following SQL command do? INSERT INTO Customer_T VALUES (001, 'John Smith', '231 West', 'Boston', 'MA', '02115'):

Adds new record to the Customer_T

What does the following SQL statement do? ALTER TABLE Customer_T ADD (Type Varchar (2));

Alters the Customer_T table, and adds a field called "Type"

The command for creating a database is

Create Schema

The _______ DBA view shows information about all users of the database in oracle

DBA_USERS

What does the following SQL statement do? DELETE FROM Customer_T WHERE state = 'HI';

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

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

Derived 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;

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

Counts ONLY rows that contain a value COUNT or COUNT(*)

COUNT

_____ use the result of the outer query to determine the processing of the inner query.

Correlated subqueries

The SQL command ________ defines a logical table from one or more tables or views.

Create View

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

DBA_TABLES

To eliminate duplicate rows in a query, the _____ qualifier is used in the SQL SELECT command.

DISTINCT

INSERT INTO is an example of ________ code.

DML

_____ is a set of commands used to update and query a database.

DML

The main concept of relational database was published in 1970 by

E.F. Codd

Which of the following questions is answered by the SQL statement? SELECT COUNT (Product_Description) FROM Product_T;

How many products have product descriptions in the product table?

The most commonly used form of join operation is the:

Natural Join

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

OLAP Functions

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

ORDER BY

To get all the customers from Hawaii sorted together, which of the following would be used?

ORDER BY

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

The guidelines for a better query design are

Retrieve only the data that you need, Understand how indexes are used in query processing, Write simple queries.

A single value returned from an SQL query that includes an aggregate function is called a(n):

Scalar Aggregate

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

Schema

Which of the following will produce the minimum of all standard prices? A) Select min(standard_price) from Product_V; B)Select Standard_Price from min(Product_V); C) Select min(Standard_Price) from Product_V where Standard_Price = min(Standard_Price); D) Select standard_price from Product_V where Standard_Price = min;

Select min(standard_price) from Product_V;

What does the following SQL Statement do? SELECT * FROM Customer WHERE Cust_Type = "Best"

Selects all the fields from the Customer Table for each row with a customer label "Best"

What result set is returned from the following query? SELECT Customer_Name, telephone FROM customers WHERE city in ('Boston','New York','Denver');

The Customer_Name and telephone of all customers living in either Boston, New York or Denver

What result set will the following query return? SELECT Item_No, description FROM item WHERE weight > 100 AND weight < 200;

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

What result set will the following query return? SELECT Item_No FROM Order_V WHERE quantity > 10;

The Item_No of all orders that had more than 10 items

What is true about the order in which SQL statements are evaluated?

The SELECT clause is processed before the ORDER BY clause

What result will the following SQL statement produce? SELECT AVG(standard_price) as average FROM Product_V;

The average Standard_Price of all products in Product_V

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

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

What will be returned when the following SQL query is executed? SELECT driver_no, count(*) as num_deliveries FROM deliveries GROUP BY driver_no HAVING count(*) > 2;

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

What will be returned when the following SQL statement is executed? SELECT driver_no,count(*) as num_deliveries FROM deliveries GROUP BY driver_no;

A listing of each driver as well as the number of deliveries that he or she has made

The parts of a coding structure are

Action, Event, Condition

______ is a set of commands used to control a database, including security.

DCL

A ________ view is materialized when referenced.

Dynamic

DDL is typically used during which phases of the development process?

Physical Design

Referential Integrity Constraints are generally established between

Primary and Foreign Keys

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

Referential Integrity

Relational databases are heavily based on the mathematical concept of:

Set Theory

What will result from the following SQL Select statement? SELECT MIN(Product_Description) FROM Product_V;

The first product description alphabetically in Product_V will be shown.

The SQL command ______ adds one or more new columns to a table.

ALTER TABLE

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