Database Final
What result will the following SQL statement produce? SELECT Avg(Standard_Price) as average from Product_V; The average price of all products The mode of all prices The average of all products in Product_V The average Standard_Price of all products in Product_V
The average Standard_Price of all products in Product_V
________ is a set of commands used to control a database, including security. DDL DML DCL DPL
DCL
INSERT INTO is an example of ________ code. DDL DCL DML TIO
DML
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 Deletes all records from the Customer_T table Removes the Customer_T table from the database Doesn't delete anything because of a syntax error
Deletes all records from Customer_t where the state is equal to HI
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 A listing of the number of deliveries greater than 2 A listing of all drivers who made more than 2 deliveries A listing of all drivers
A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries
A catalog is the structure that contains object descriptions created by a user. (True/False)
False
A database is maintained and queried using the data mapping language (DML). (True/False)
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. (True/False)
False
A domain is a way to create a structure that acts like a table. (True/False)
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. (True/False)
False
A shared lock prevents another transaction from reading a record. (True/False)
False
A transaction is consistent if it always runs with the same amount of memory. (True/False)
False
A transaction is durable if it can resist failure. (True/False)
False
COUNT( *) tallies only those rows that contain a value, while COUNT counts all rows. (True/False)
False
Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields. (True/False)
False
During multiple simultaneous transactions data integrity is maintained by logging. (True/False)
False
Horizontal partitioning refers to the process of combining several smaller relations into a larger table. (True/False)
False
In databases, null values are equivalent to zero. (True/False)
False
The CREATE SCHEMA DDL command is used to create a table. (True/False)
False
The DELETE TABLE DDL command is used to remove a table from the database. (True/False)
False
The DROP command deletes rows from a table individually or in groups. (True/False)
False
The following query totals sales in state= 'MA' for each salesperson.SELECT salesperson_id, sum(sales)FROM salesperson GROUP BY salesperson_idHAVING state = 'MA'; (True/False)
False
The logical database design always forms the best foundation for grouping attributes in the physical design. (True/False)
False
Using an index for attributes referenced in ORDER BY and GROUP BY clauses has no significant impact upon database performance. (True/False)
False
Versioning is a pessimistic approach to concurrency control. (True/False)
False
Which of the following can produce scalar and vector aggregates? ORDER BY SORT HAVING GROUP BY
GROUP BY
How is the HAVING clause different from the WHERE clause?
HAVING is always used following GROUP BY, and is used to limit groups, while WHERE is used to limit rows
The last part of an SQL query to be read is the ________ statement. SELECT ORDER BY FROM WHERE
ORDER BY
To get all the customers from Hawaii sorted together, which of the following would be used? GROUP BY ORDER BY HAVING SORT
ORDER BY
All of the following are well-accepted characteristics of transactions EXCEPT: Persistence. Consistency. Atomicity. Durability.
Persistence.
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 labeled "*" Selects fields with a "*" in them from the Customer table Selects all the fields from the Customer table for each row with a customer labeled "Best" Selects the "*" field from the Customer table for each row with a customer labeled "Best"
Selects all the fields from the Customer table for each row with a customer labeled "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 of all customers living in Boston, New York or Denver The Customer_Name and telephone of all customers living in either Boston, New York or Denver The Customer_Name and telephone of all customers living in Boston and New York and Denver The Customer_Name and telephone of all customers
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 The Item_No and description for all items weighing less than 100 The Item_No for all items weighing between 101 and 199 The Item_No for all items weighing more than 200
The Item_No and description for all items weighing between 101 and 199
A database table is defined using the data definition language (DDL). (True/False)
True
A tablespace is a named set of disk storage elements in which physical files for the database tables may be stored. (True/False)
True
All transactions must have the ACID characteristics. (True/False)
True
An INSERT command does not need to have the fields listed. (True/False)
True
Clustering allows for adjacent secondary memory locations to contain rows from several tables. (True/False)
True
Denormalization almost always leads to more storage space for raw data. (True/False)
True
If multiple Boolean operators are used in an SQL statement, NOT is evaluated first, then AND, then OR. (True/False)
True
In a sequential file, the records are stored in sequence according to a primary key value. (True/False)
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. (True/False)
True
Level of lock granularity is the extent of a database resource include with each lock. (True/False)
True
SQL is both an American and international standard for database access. (True/False)
True
Some DBMS can handle graphic data types as well as text and numbers. (True/False)
True
The ALTER TABLE command is used to change a table definition. (True/False)
True
The SQL command used to populate tables is the INSERT command. (True/False)
True
The following query totals sales for each salesperson. SELECT salesperson_id, sum(sales) FROM salesperson GROUP BY salesperson_id; (True/False)
True
Two-phase locking is a procedure for acquiring locks for a transaction before any are released. (True/False)
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. (True/False)
True
When creating tables, it's important to decide which columns will allow null values before the table is created. (True/False)
True
________ partitioning distributes the columns of a table into several separate physical records. Horizontal Crossways Final Vertical
Vertical
A transaction is considered isolated when: it can only be run on one server. users can see changes to tables before the transaction is complete. any changes in the database are not revealed to the user until the transaction is complete. it runs in a vacuum.
any changes in the database are not revealed to the user until the transaction is complete.
In order for a transaction to be consistent: it must run using the same amount of memory. any database constraints that must be true before the transaction must also be true after the transaction. it must run the same way all the time. it must tell the truth.
any database constraints that must be true before the transaction must also be true after the transaction.
The benefits of a standardized relational language include: increased training costs. confusing the SQL programmers. cross-system communication. application turnover.
cross-system communication.
Any create command may be reversed by using a(n) ________ command. delete unpack undo drop
drop
A factor to consider when choosing a file organization is: efficient storage. DML. inefficient data retrieval. DDL.
efficient storage.
A(n) ________ prevents another transaction from reading and therefore updating a record until it is unlocked. authorization rule record controller exclusive lock shared lock
exclusive lock
A file organization where files are not stored in any particular order is considered a: heap file organization. hash key. multi-indexed file organization. hashed file organization.
heap file organization.
Durability means that: transactions can withstand failure. transactions can't be erased. transactions never finish on time. once a transaction is committed, no subsequent failure of the database can reverse the effect of the transaction.
once a transaction is committed, no subsequent failure of the database can reverse the effect of the transaction.
When incorrect data have been introduced, the database is best recovered by: reloading RDMS software. starting with a new ERD. formatting server. restarting from the most recent checkpoint and processing subsequent transactions.
restarting from the most recent checkpoint and processing subsequent transactions.
A single value returned from an SQL query that includes an aggregate function is called a(n): scalar aggregate. summation. agate. vector aggregate.
scalar aggregate.
A business transaction requires: several actions against the database. human intervention. a stored procedure. a trigger.
several actions against the database.
A(n) ________ is a procedure for acquiring the necessary locks for a transaction where all necessary locks are acquired before any are released. exclusive lock two-phase lock authorization rule record controller
two-phase lock