Part 9

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) A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries B) A listing of all drivers C) A listing of the number of deliveries greater than 2 D) A listing of all drivers who made more than 2 deliveries

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

13) What does the following SQL command do? insert into Customer_T values (001,'John Smith','231 West St','Boston','MA','02115'); A) Adds a new record to the Customer_T B) Creates the Customer_T table C) Deletes the Customer_T table D) Updates the Customer_T table

A) Adds a new record to the Customer_T

________ use the result of the outer query to determine the processing of the inner query. A) Correlated subqueries B) Outer subqueries C) Inner subqueries D) Subqueries

A) Correlated subqueries

________ is a set of commands used to update and query a database. A) DML B) DDL C) DCL D) DPL

A) DML

What does the following SQL statement do? Delete from Customer_T where state = 'HI'; A) Deletes all records from customer_t where the state is equal to HI B) Removes the Customer_T table from the database C) Deletes all records from the Customer_T table D) None of the above

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

What does the following SQL statement do? Select * From Customer Where Cust_Type = "Best" A) Selects all the fields from the Customer table for each row with a customer labeled "Best" B) Selects the "*" field from the Customer table for each row with a customer labeled "Best" C) Selects fields with a "*" in them from the Customer table D) Selects all the fields from the Customer table for each row with a customer labeled "*"

A) Selects all the fields from the Customer table for each row

The UNION clause is used to: A) combine the output from multiple queries into a single result table. B) join two tables together to form one table. C) find all rows that do not match in two tables. D) none of the above.

A) 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. A) correlated B) paired C) natural D) inner

A) correlated

User-defined transactions can improve system performance because: A) transactions are processed as sets, reducing system overhead. B) transactions are mapped to SQL statements. C) speed is improved due to query optimization. D) all of the above.

A) transactions are processed as sets, reducing system overhead.

The outer join syntax does not apply easily to a join condition of more than ________ tables. A) two B) three C) four D) five

A) two

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) A listing of all drivers, sorted by driver number B) A listing of each driver as well as the number of deliveries that he or she has made C) A count of all of the deliveries made by all drivers D) None of the above

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

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) A listing of all drivers who made deliveries to state = 'MA', sorted by driver number B) 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 C) A count of all of the deliveries made to state = 'MA' by all drivers D) None of the above

B) 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 does the following SQL statement do? Update Product_T Set Unit_Price = 775 Where Product_ID = 7 A) Changes the price of a unit called Product_T to 7 B) Changes the unit price of Product 7 to 775 C) Changes the length of the Unit_Price field to 775 D) Updates the Product_T table to have a unit price of 775

B) Changes the unit price of Product 7 to 775

Which of the following is a technique for optimizing the internal performance of the relational data model? A) Avoiding indexes on secondary keys B) Clustering data C) Not reporting statistics to save machine resources D) Using random index organizations

B) Clustering data

DDL is typically used during which phases of the development process? A) Implementation B) Physical design C) Analysis D) All of the above

B) Physical design

What result will the following SQL statement produce? Select Avg(standard_price) as average from Product_V; A) The average of all products in Product_V B) The average Standard_Price of all products in Product_V C) The average price of all products D) None of the above

B) The average Standard_Price of all products in Product_V

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') A) It would work just fine. B) You must specify the fields to insert if you are only inserting some of the fields. C) There is no table keyword. D) None of the above.

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

The SQL command ________ adds one or more new columns to a table. A) create table B) alter table C) create view D) create relationship

B) alter table

In order for two queries to be UNION-compatible, they must: A) both have the same number of lines in their SQL statements. B) both output compatible data types for each column and return the same number of rows. C) both return at least one row. D) all of the above.

B) both output compatible data types for each column and return the same number of rows.

A join operation: A) brings together data from two different fields. B) causes two tables with a common domain to be combined into a single table or view. C) causes two disparate tables to be combined into a single table or view. D) is used to combine indexing operations.

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

A ________ is a temporary table used in the FROM clause of an SQL query. A) correlated subquery B) derived table C) view table D) none of the above

B) derived table

Any create command may be reversed by using a ________ command. A) truncate B) drop C) delete D) unpack

B) drop

A ________ view is materialized when referenced. A) virtual B) dynamic C) materialized D) base

B) dynamic

The first in a series of steps to follow when creating a table is to: A) identify columns that must be unique. B) identify each attribute and its characteristics. C) create an index. D) identify columns that must be null.

B) identify each attribute and its characteristics.

One major advantage of the outer join is that: A) information is easily accessible. B) information is not lost. C) the query is easier to write. D) all of the above.

B) information is not lost.

Explicit commands to manage transactions are needed when: A) a transaction consists of just one SQL command. B) multiple SQL commands must be run as part of a transaction. C) autocommit is set to off. D) none of the above.

B) 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): A) equi-join. B) natural join. C) multivariate join. D) inner join.

B) natural join.

The ________ is the structure that contains descriptions of objects such as tables and views created by users. A) SQL B) schema C) catalog D) master view

B) schema

A type of join where a table is joined to itself is called a(n): A) unary join. B) self-join. C) unnatural join. D) pinned join.

B) self-join.

A type of query that is placed within a WHERE or HAVING clause of another query is called a: A) master query. B) subquery. C) superquery. D) multi-query.

B) subquery.

All of the following are guidelines for better query design EXCEPT: A) understand how indexes are used in query processing. B) use a lot of self-joins. C) write simple queries. D) retrieve on the data that you need.

B) use a lot of self-joins.

In which of the following situations would one have to use an outer join in order to obtain the desired results? A) A report is desired that lists all customers who placed an order. B) A report is desired that lists all customers and the total of their orders. C) 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). D) There is never a situation that requires only an outer join.

C) 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).

What does the following SQL statement do? Alter Table Customer_T Add (Type Varchar (2)); A) Alters the Customer_T table to accept Type 2 Varchars B) Alters the Customer_T table to be a Type 2 Varchar C) Alters the Customer_T table, and adds a field called "Type" D) Alters the Customer_T table by adding a 2-byte field called "Varchar"

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

________ is a set of commands used to control a database, which includes security. A) DML B) DDL C) DCL D) DPL

C) DCL

________ takes a value of true if a subquery returns an intermediate results table which contains one or more rows. A) In B) Having C) Exists D) Extents

C) Exists

Which of the following finds all groups meeting stated conditions? A) Select B) Where C) Having D) Find

C) Having

EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows. A) FALSE B) 1 C) TRUE D) undefined

C) TRUE

Which of the following is true of the order in which SQL statements are evaluated? A) The SELECT clause is always processed first. B) The SELECT clause is always processed last. C) The SELECT clause is processed before the ORDER BY clause. D) The GROUP BY clause is processed before the WHERE clause.

C) The SELECT clause is processed before the ORDER BY clause.

n an SQL statement, which of the following parts states the conditions for row selection? A) Select B) From C) Where D) Group By

C) Where

The command for creating a database is: A) create table. B) create view. C) create schema. D) create authorization.

C) create schema.

The SQL command ________ defines a logical table from one or more tables or views. A) create table B) alter table C) create view D) create relationship

C) create view

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): A) natural join. B) equi-join. C) outer join. D) union join.

C) outer join.

Indexes are created in most RDBMSs to: A) provide a quicker way to store data. B) decrease the amount of disk space utilized. C) provide rapid random and sequential access to base-table data. D) increase the cost of implementation.

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

SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query. A) grouping B) joining C) subquery D) union

C) subquery

Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by: A) using the if-then-else construct. B) using the immediate if statement. C) using the CASE keyword in a statement. D) using a subquery.

C) using the CASE keyword in a statement.

Which of the following is a purpose of the SQL standard? A) To specify syntax and semantics of SQL data definition and manipulation B) To specify minimal and complete standards, which permit different degrees of adoption in products C) To define the data structures and basic operations for SQL databases D) All of the above

D) All of the above

The ________ clause is used to combine the output from multiple queries into a single result table. A) INTERSECT B) DIVIDE C) COLLATE D) UNION

D) UNION

A view may not be updated directly if it contains: A) the DISTINCT keyword. B) derived columns and expressions in the SELECT clause. C) uses the GROUP BY or HAVING clause. D) all of the above.

D) all of the above.

The benefits of a standardized relational language include: A) application longevity. B) reduced training costs. C) cross-system communication. D) all of the above.

D) all of the above.

A join in which the joining condition is based on equality between values in the common columns is called a(n): A) equi-join. B) unilateral join. C) natural join. D) both A and C.

D) both A and C.

The most commonly used form of join operation is the: A) outer join. B) union join. C) equi-join. D) natural join.

D) natural join.

An operation to join a table to itself is called a: A) sufficient-join. B) inner join. C) outer join. D) self-join.

D) self-join.

A catalog is the structure that contains object descriptions created by a user.

False

A subquery in which processing the inner query depends on data from the outer query is called a codependent query.

False

DCL is used to update the database with new records.

False

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

False

Implementation of a standard can never stifle creativity and innovation.

False

SQL has been implemented only in the mainframe and midrange environments.

False

SQL originated from a project called System-S.

False

The following query will execute without errors. select customer.customer_name, salesman.sales_quota from customer where customer.salesman_id = (select salesman_id where lname = 'SMITH');

False

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

True

A major benefit of SQL as a standard is reduced training costs.

True

Applications can be moved from one machine to another when each machine uses SQL.

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.

True

Joining tables or using a subquery may produce the same result.

True

One of the original purposes of the SQL standard was to provide a vehicle for portability of database definition and application modules between conforming DBMSs.

True

SQL is both an American and international standard for database access.

True

Some DBMS can handle graphic data types as well as text and numbers.

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

The following queries produce the same results. select customer_name, customer_city from customer, salesman where customer.salesman_id = salesman.salesman_id and salesman.lname = 'SMITH'; select customer_name, customer_city from customer where customer.salesman_id = (select salesman_id from salesman where lname = 'SMITH');

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.

True