ITEC 315 Quiz Review

What result set is returned from the following query? SELECT Customer_Name, telephone FROM customers WHERE city in ('Boston','New York','Denver'); A) The Customer_Name and telephone of all customers B) The Customer_Name of all customers living in Boston, New York or Denver C) The Customer_Name and telephone of all customers living in either Boston, New York or Denver D) The Customer_Name and telephone of all customers living in Boston and New York and Denver

C) 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; A) The Item_No and description for all items weighing less than 100 B) The Item_No and description for all items weighing between 101 and 199 C) The Item_No for all items weighing between 101 and 199 D) The Item_No for all items weighing more than 200

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

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 mode of all prices C) The average Standard_Price of all products in Product_V D) The average price of all products

C) The average Standard_Price of all products in Product_V

A(n) ________ prevents another transaction from reading and therefore updating a record until it is unlocked. A) shared lock B) record controller C) exclusive lock D) authorization rule

C) exclusive lock

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

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

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

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

In order for a transaction to be consistent: A) it must run the same way all the time. B) it must tell the truth. C) it must run using the same amount of memory. D) any database constraints that must be true before the transaction must also be true after the transaction.

D) any database constraints that must be true before the transaction must also be true after the transaction.

When incorrect data have been introduced, the database is best recovered by: A) starting with a new ERD. B) formatting server. C) reloading RDMS software. D) restarting from the most recent checkpoint and processing subsequent transactions

D) restarting from the most recent checkpoint and processing subsequent transactions

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

DCL

INSERT INTO is an example of ________ code A) DML B) TIO C) DDL D) DCL

DML

A(n) ________ is a procedure for acquiring the necessary locks for a transaction where all necessary locks are acquired before any are released A) two-phase lock B) authorization rule C) record controller D) exclusive lock

A) two-phase lock

A catalog is the structure that contains object descriptions created by a user (T/F)

False

A database is maintained and queried using the data mapping language (DML) (T/F)

False

A default value is the value that a field will always assume, regardless of what the user enters for an instance of that field. (T/F)

False

A domain is a way to create a structure that acts like a table. (T/F)

False

A referential integrity constraint specifies that the existence of an attribute in one table depends upon the existence of a foreign key in the same or another table (T/F)

False

A shared lock prevents another transaction from reading a record (T/F)

False

A transaction is consistent if it always runs with the same amount of memory (T/F)

False

A transaction is durable if it can resist failure (T/F)

False

COUNT(*) tallies only those rows that contain a value, while COUNT counts all rows (T/F)

False

Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields. (T/F)

False

During multiple simultaneous transactions data integrity is maintained by logging. (T/F)

False

Horizontal partitioning refers to the process of combining several smaller relations into a larger table. (T/F)

False

In databases, null values are equivalent to zero (T/F)

False

The CREATE SCHEMA DDL command is used to create a table (T/F)

False

The DELETE TABLE DDL command is used to remove a table from the database (T/F)

False

The DROP command deletes rows from a table individually or in groups (T/F)

False

The following query totals sales in state= 'MA' for each salesperson. SELECT salesperson_id, sum(sales) FROM salesperson GROUP BY salesperson_id HAVING state = 'MA'; (T/F)

False

The logical database design always forms the best foundation for grouping attributes in the physical design. (T/F)

False

Using an index for attributes referenced in ORDER BY and GROUP BY clauses has no significant impact upon database performance. (T/F)

False

Versioning is a pessimistic approach to concurrency control (T/F)

False

Which of the following can produce scalar and vector aggregates? Group By Order By Having Sort

GROUP BY

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 Having Group By Sort

ORDER BY

All of the following are well-accepted characteristics of transactions EXCEPT: Durability. Consistency. Persistence. Atomicity.

Persistence.

A single value returned from an SQL query that includes an aggregate function is called a(n): A) summation. B) vector aggregate. C) agate. D) scalar aggregate.

Scalar Aggregate

What does the following SQL statement do? DELETE FROM Customer_T WHERE state = 'HI'; A) Doesn't delete anything because of a syntax error B) Deletes all records from Customer_t where the state is equal to HI C) Deletes all records from the Customer_T table D) Removes the Customer_T table from the database

B) Deletes all records from Customer_t where the state is equal to HI

A transaction is considered isolated when: A) it can only be run on one server. B) any changes in the database are not revealed to the user until the transaction is complete. C) users can see changes to tables before the transaction is complete. D) it runs in a vacuum

B) any changes in the database are not revealed to the user until the transaction is complete.

A factor to consider when choosing a file organization is: A) DML. B) efficient storage. C) DDL. D) inefficient data retrieval.

B) efficient storage.

A file organization where files are not stored in any particular order is considered a: A) hashed file organization. B) heap file organization. C) multi-indexed file organization. D) hash key.

B) heap file organization.

Durability means that: A) transactions can't be erased. B) once a transaction is committed, no subsequent failure of the database can reverse the effect of the transaction. C) transactions never finish on time. D) transactions can withstand failure.

B) once a transaction is committed, no subsequent failure of the database can reverse the effect of the transaction.

A business transaction requires: A) a stored procedure. B) several actions against the database. C) human intervention. D) a trigger.

B) several actions against the database.

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

True

A tablespace is a named set of disk storage elements in which physical files for the database tables may be stored. (T/F)

True

All transactions must have the ACID characteristics. (T/F)

True

An INSERT command does not need to have the fields listed (T/F)

True

Clustering allows for adjacent secondary memory locations to contain rows from several tables. (T/F)

True

Denormalization almost always leads to more storage space for raw data. (T/F)

True

If multiple Boolean operators are used in an SQL statement, NOT is evaluated first, then AND, then OR (T/F)

True

In a sequential file, the records are stored in sequence according to a primary key value. (T/F)

True

Indexes are most useful for columns that frequently appear in WHERE clauses of SQL commands, either to qualify the rows to select or for linking. (T/F)

True

Level of lock granularity is the extent of a database resource include with each lock. (T/F)

True

SQL is both an American and international standard for database access (T/F)

True

Some DBMS can handle graphic data types as well as text and numbers (T/F)

True

The ALTER TABLE command is used to change a table definition

True

The SQL command used to populate tables is the INSERT command (T/F)

True

The following query totals sales for each salesperson. SELECT salesperson_id, sum(sales) FROM salesperson GROUP BY salesperson_id; (T/F)

True

Two-phase locking is a procedure for acquiring locks for a transaction before any are released. (T/F)

True

When a GROUP BY clause is included in an SQL statement, only those columns with a single value for each group can be included. (T/F)

True

When creating tables, it's important to decide which columns will allow null values before the table is created (T/F)

True

________ partitioning distributes the columns of a table into several separate physical records. A) Final B) Vertical C) Crossways D) Horizontal

Vertical

How is the HAVING clause different from the WHERE clause?

While the WHERE clause works on each row in a query result set, the HAVING clause works on the aggregate (or combined) rows in a GROUP BY. WHERE does not allow aggregates while the HAVING does allow aggregates

The benefits of a standardized relational language include: A) increased training costs. B) application turnover. C) confusing the SQL programmers. D) cross-system communication.

cross-system communication

Any create command may be reversed by using a(n) ________ command A) unpack B) drop C) undo D) delete

drop