Database Processing, 15e(Kroenke)
Chapter 7: SQL for Database Construction and Application Processing
1) The CREATE TABLE statement is used to name a new table and describe the table’s columns.
2) The CONSTRAINT keyword is used to define one of several types of constraints.
3) The PRIMARY KEY keyword is used to designate the column(s) that are the primary key for
the table.
4) The CONSTRAINT keyword is used to limit column values to specific values.
5) The CONSTRAINT keyword can be used in conjunction with the SQL keywords PRIMARY
KEY and FOREIGN KEY.
6) One advantage of using the CONSTRAINT command to define a primary key is that the
database designer controls the name of the constraint.
7) The UNIQUE keyword is used to define alternate keys.
8) If the table PRODUCT has a column PRICE, and PRICE has the data type Numeric (8,2), the
value 98765 stored in that field will be displayed by the DBMS as 98765.00.
9) If the table ITEM has a column WEIGHT, and WEIGHT has the data type Numeric (4,2), the
value 4321 will be displayed as 43.21.
10) The CHECK keyword is used to limit column values to specific values.
11) The MODIFY keyword is used to change the structure, properties or constraints of a table.
12) Data values to be added to a table are specified by using the VALUES clause.
13) The DELETE keyword is used to delete a table’s structure.
14) When the correct SQL command is used to delete a table’s structure, the command can only
be used with a table that has already had its data removed.
15) One or more rows can be added to a table by using the INSERT statement.
16) Unless it is being used to copy data from one table to another, the INSERT statement can be
used to insert only a single row into a table.
17) Rows in a table can be changed by using the UPDATE statement.
18) The SET keyword is used to specify a new value when changing a column value.
19) The MODIFY keyword is used to change a column value.
20) Rows can be removed from a table by using the DELETE statement.
21) An SQL virtual table is called a view.
22) The SQL command CREATE USER VIEW is used to create a virtual table.
23) SQL views are constructed from SELECT statements.
24) According to the SQL-92 standard, statements used to construct views cannot contain the
WHERE clause.
25) The SELECT command is used to retrieve view instances.
26) The values in an SQL view are not always changeable through the view itself.
27) SQL views can be used to hide columns.
28) SQL views can be used to provide a level of insulation between data processed by
applications and the data stored in the database tables.
29) If the values in an SQL view are changeable through the view itself, the UPDATE command
is used to change the values.
30) The values in an SQL view are always changeable through the view itself.
31) SQL views are updatable when the view is based on a single table with no computed
columns, and all non-null columns are present in the view.
32) Because SQL statements are table-oriented, whereas programs are variable-oriented, the
results of SQL statements used in programs are treated as pseudofiles.
33) A set of SQL statements stored in an application written in a standard programming language
is called embedded SQL.
34) Because SQL statements are table-oriented, whereas programs are variable-oriented, the
results of SQL statements used in programs are accessed using an SQL cursor.
35) A stored program that is attached to a table or view is called a stored procedure.
36) SQL triggers use the ANSI SQL keywords BEFORE, INSTEAD OF, and AFTER.
37) SQL triggers can be used with SQL operations INSERT, UPDATE, and DELETE.
38) SQL triggers can be used when the DBMS receives an INSERT request.
39) SQL triggers are used for providing default values, validity checking, updating views, and
performing referential integrity actions.
40) The Oracle DBMS supports the SQL BEFORE trigger.
41) The SQL Server DBMS supports the SQL BEFORE trigger.
8
42) SQL triggers can be used when the DBMS receives an update request.
43) To set a column value to an initial value that is selected according to some complicated
business logic, you would use the DEFAULT constraint with the CREATE TABLE command.
44) SQL triggers are created using the ADD TRIGGER statement.
45) If the values in an SQL view are not changeable through the view itself, you may still be
able to update the view by using unique application logic. In this case, the specific logic is placed
in an INSTEAD OF trigger.
46) If a trigger is being written to enforce referential integrity actions, you cannot use an
INSTEAD OF trigger.
47) When a trigger is fired, the DBMS makes the appropriate data available to the trigger code.
48) A stored program that is stored within the database and compiled when used is called a
trigger.
49) Stored procedures have the advantage of greater security, decreased network traffic, SQL
optimized by the DBMS compiler, and code sharing.
50) Unlike application code, stored procedures are never distributed to the client computers.
51) Because SQL stored procedures allow and encourage code sharing among developers, stored
procedures give database application developers the advantages of less work, standardized
processing, and specialization among developers.
52) Which SQL keyword is used to name a new table and describe the table’s columns?
A) SET
B) CREATE
C) SELECT
D) ALTER
53) If the table PRODUCT has a column PRICE that has the data type Numeric (8,2), the value
12345 will be displayed by the DBMS as ________.
A) 123.45
B) 12345
C) 12345.00
D) 123450.00
54) Which SQL keyword is used to impose restrictions on a table, data or relationship?
A) SET
B) CREATE
C) SELECT
D) CONSTRAINT
55) One advantage of using the CONSTRAINT phrase to define a primary key is that the
database designer controls the ________.
A) name of the table
B) name of the foreign key field
C) name of the constraint
D) name of the primary key field
56) Which of the following illustrates the authors’ preferred style of defining a primary key?
A) CREATE TABLE CUSTOMER (
CustomerID Integer Primary Key
LastName Char(35) Not Null
First Name Char(25) Null
);
B) CREATE TABLE CUSTOMER (
CustomerID Integer Not Null
LastName Char(35) Not Null
First Name Char(25) Null
CONSTRAINT CustomerPK PRIMARY KEY (CustomerID)
);
C) CREATE TABLE CUSTOMER (
CustomerID Integer Not Null
LastName Char(35) Not Null
First Name Char(25) Null
);
ALTER TABLE CUSTOMER
ADD CONSTRAINT CustomerPK PRIMARY KEY (CustomerID);
D) Both B and C are correct
57) Given the SQL statement:
CREATE TABLE SALESREP (
SalesRepNo int NOT NULL,
RepName char(35) NOT NULL,
HireDate date NOT NULL,
CONSTRAINT SalesRepPK PRIMARY KEY (SalesRepNo),
CONSTRAINT SalesRepAK1 UNIQUE (RepName)
);
We know that ________.
A) RepName is the primary key
B) RepName is a foreign key
C) RepName is a candidate key
D) RepName is a surrogate key
58) The SQL keyword used to limit column values to specific values is ________.
A) CONSTRAINT
B) CHECK
C) NOT NULL
D) UNIQUE
59) Which SQL keyword is used to change the structure, properties or constraints of a table?
A) SET
B) CREATE
C) SELECT
D) ALTER
60) Which SQL keyword is used to delete a table’s structure?
A) DELETE
B) DROP
C) DISPOSE
D) ALTER
61) When the correct SQL command is used to delete a table’s structure, what happens to the
data in the table?
A) If the deleted table was a parent table, the data is added to the appropriate rows of the child
table.
B) If the deleted table was a child table, the data is added to the appropriate rows of the parent
table.
C) The data in the table is also deleted.
D) Nothing because there was no data in the table since only an empty table can be deleted.
62) Which SQL keyword is used to add one or more rows of data to a table?
A) DELETE
B) INSERT
C) SELECT
D) UPDATE
63) Which SQL keyword is used to change one or more rows in a table?
A) MODIFY
B) INSERT
C) SELECT
D) UPDATE
64) Which SQL keyword is used to change the values of an entire column?
A) CHANGE
B) INSERT
C) SELECT
D) SET
65) Which keyword is used to remove one or more rows from a table?
A) DELETE
B) INSERT
C) ERASE
D) SET