Database Management Test 2

Front 1

If you anticipate running a particular query often, you can improve overall performance by saving the query in a special file called a ___.

Back 1

stored procedure

Front 2

When a subquery is used, ___ is evaluated first.

Back 2

the subquery query

Front 3

When used after the word SELECT, the _ symbol indicates that you want to include all fields in the query results in the order in which you described them to the DBMS when you created the table.

Back 3

*

Front 4

In Access SQL, the ___ is used as a wildcard to represent any individual character.

Back 4

question mark

Front 5

You can save the results of a query as a table by including the ___ clause in the query.

Back 5

INTO

Front 6

In order to make changes to existing data in a table, you would use the ___ command.

Back 6

UPDATE

Front 7

In SQL, you use the CHECK clause to enforce ___ integrity.

Back 7

legal-values

Front 8

Essentially, setting the value in a given field to ___ is similar to not enter a value in it at all.

Back 8

null

Front 9

To use a stored procedure in MySQL, a user enters the word ___ followed by the procedure name.

Back 9

CALL

Front 10

The basic form of an SQL retrieval command is ___.

Back 10

SELECT-FROM-WHERE

Front 11

The CHECK clause can be included in the ___ command.

Back 11

CREATE TABLE

Front 12

Part (PartNum, Description, OnHand, Class, Warehouse, Price)

Based on the code above, list the descriptions of all parts that are located in Warehouse 3 for which there are more than 20 units on hand or both.

Back 12

SELECT Description

FROM Part

WHERE Warehouse= ‘3’

OR OnHand> 20

;

Front 13

The ___ command will create an application program’s or individual user’s picture of the database.

Back 13

CREATE VIEW

Front 14

To create an index for the Customer table named RepBal with the keys RepNum and Balance and with the balances listed in descending order, the command is

Back 14

CREATE INDEX RepBal ON Customer (RepNum, Balance DESC)

;

Front 15

A trigger is stored and compiled on the ___.

Back 15

server

Front 16

Many versions of SQL require you to end a command with a ___.

Back 16

semicolon

Front 17

The SQL command for deleting the Warehouse field from the Part table is:

Back 17

ALTER TABLE Part DROP COLUMN Warehouse;

Front 18

A ___ is an action that occurs automatically in response to an associated database operation such as an INSERT, UPDATE, or DELETE command.

Back 18

trigger

Front 19

To add new data to a table use the ___ command.

Back 19

INSERT

Front 20

Customer (CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)

Based on the code above, for each sales rep, list the number, the number of customers assigned to the rep, and the average balance of the rep’s customers. Group the records by rep number and order of the records by rep number.

Back 20

SELECT RepNum, COUNT(*), AVG(Balance)

FROM Customer

GROUP BY RepNum

ORDER BY RepNum

;