Database Storage & Design Chapter 7 2 Answer Aacsb Information Technology Application Knowledge Difficulty Moderate Lo Understand How Create

66) Based on the tables below, which of the following SQL statements would increase the

balance of the Gonzales account by $100 to a total of $450?

GENERAL SALES DATABASE:

SALESREP

SalesRepNo

RepName

HireDate

654

Jones

01/02/2005

734

Smith

02/03/2007

345

Chen

01/25/2018

434

Johnson

11/23/2004

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

A) SELECT Gonzales

FROM CUSTOMER

INSERT VALUES PLUS (100) INTO Balance;

B) SELECT Gonzales

FROM CUSTOMER

INSERT VALUES (450) INTO Balance;

C) INSERT INTO CUSTOMER VALUES (450)

SELECT Balance

WHERE CustName = ‘Gonzales’;

D) UPDATE CUSTOMER

SET Balance = 450

WHERE CustName = ‘Gonzales’;

67) An SQL virtual table is called ________.

A) a CHECK constraint

B) a view

C) embedded SQL

D) a trigger

68) The SQL command used to create a virtual table is ________.

A) CREATE VTABLE

B) CREATE VIEW

C) VTABLE

D) VIEW

69) SQL views are constructed from ________.

A) CREATE statements

B) INSERT statements

C) UPDATE statements

D) SELECT statements

70) According to the SQL-92 standard, statements used to construct views cannot contain

________.

A) the SELECT clause

B) the FROM clause

C) the WHERE clause

D) the ORDER BY clause

71) Which SQL statement is used to retrieve view instances?

A) CREATE

B) DELETE

C) INSERT

D) SELECT

72) SQL views are not used ________.

A) to hide columns

B) to show results of computed columns

C) to hide complicated SQL statements

D) to enforce primary key constraints

73) If the values in an SQL view are changeable through the view itself, which SQL statement is

used to change the values?

A) CREATE

B) DELETE

C) INSERT

D) UPDATE

74) SQL views are always updatable when ________.

A) the view is based on a single table with no computed columns, and all non-null columns are

present in the view

B) the view is based on any number of tables, with or without computed columns, and the

INSTEAD OF trigger is defined for the view

C) the view is based on multiple tables, the update is being done on the most subordinate table,

and the rows of that table can be uniquely identified

D) Both A and B are correct

75) A set of SQL statements stored in an application written in a standard programming language

is called ________.

A) a stored procedure

B) a view

C) embedded SQL

D) a trigger

76) Because SQL statements are set-oriented, whereas programs are element-oriented, the results

of SQL statements used in programs are treated as ________.

A) tables

B) rows

C) files

D) pseudofiles

77) Because SQL statements are table-oriented, whereas programs are element-oriented, the

results of SQL statements used in programs are accessed using ________.

A) standard programming tools

B) custom written programming tools

C) an SQL cursor

D) an SQL trigger

78) A stored program that is attached to a table or view is called ________.

A) a CHECK constraint

B) a view

C) embedded SQL

D) a trigger

79) Which of the following is not an ANSI SQL trigger?

A) BEFORE UPDATE

B) INSTEAD OF UPDATE

C) AFTER INSERT

D) INSTEAD OF CONSTRAINT

80) Which of the following is not an SQL trigger Oracle supports?

A) BEFORE

B) INSTEAD OF

C) AFTER

D) DURING

81) Which of the following is an SQL trigger Microsoft SQL Server supports?

A) BEFORE

B) INSTEAD OF

C) AFTER

D) Both B and C are correct

82) SQL triggers can be used when the DBMS receives a(n) ________ request.

A) INSERT

B) SELECT

C) ALTER

D) CREATE

83) SQL triggers are not used for ________.

A) validity checking

B) providing default values

C) updating views

D) creating tables

84) When a trigger is fired, the DBMS makes the appropriate data available to ________.

A) the SQL interpreter

B) the application code

C) the embedded SQL code

D) the trigger code

85) SQL triggers are created using ________.

A) the SQL CREATE TRIGGER statement

B) the SQL ADD TRIGGER statement

C) the SQL TRIGGER statement

D) the SQL ADD CONSTRAINT TRIGGER statement

86) To set a column value to an initial value that is selected according to some business logic,

you would use ________.

A) the SQL DEFAULT constraint with the CREATE TABLE command

B) an SQL view

C) embedded SQL

D) an SQL trigger

87) 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 ________.

A) a BEFORE trigger

B) an INSTEAD OF trigger

C) an AFTER trigger

D) Depending on the specific logic, either A or B can be used.

88) A stored program that is attached to the database is called ________.

A) a view

B) embedded SQL

C) a trigger

D) a stored procedure

89) Stored procedures have all these advantages except ________.

A) greater security

B) faster development

C) SQL optimized by the DBMS compiler

D) code sharing

90) Because SQL stored procedures allow and encourage code sharing among developers, stored

procedures give database application developers all these advantages except ________.

A) less work

B) standardized processing

C) specialization among developers

D) faster query response times

91) Referential integrity constraints are implemented in SQL using the ________ syntax.

A) PRIMARY KEY

B) FOREIGN KEY

C) DEFAULT

D) UNIQUE

92) If you have a foreign key in a CUSTOMER table that references the primary key in a

SALESREP table, the ON DELETE CASCADE syntax means that ________.

A) when a CUSTOMER is deleted, that CUSTOMER’s SALESREP is also deleted

B) when a CUSTOMER is deleted, all SALESREPS are also deleted

C) when a SALESREP is deleted, all CUSTOMERs of that SALESREP are also deleted

D) when the SALESREP table is dropped, all CUSTOMERs must be deleted

93) Which of the following does not describe a user-defined function?

A) It computes a numeric value

B) It can be called by name from within an SQL statement

C) It may have input parameters passed to it

D) It returns an output value to the calling statement

94) Based on the table below, a function to compute a 10% late penalty on a customer’s balance

would have how many input parameters?

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

A) 0

B) 1

C) 2

D) it doesn’t matter

95) Explain the essential format of the CREATE TABLE statement. Include an example.

96) Explain how relationships are created using SQL. Include an example.

97) Discuss SQL data types.

98) Discuss what is meant by a data type of “Numeric (10,3).” Include at least one example.

99) Distinguish between Char and VarChar data types. Include examples and tradeoffs.

100) The following database will be used in this question:

GENERAL SALES DATABASE:

SALESREP

SalesRepNo

RepName

HireDate

654

Jones

01/02/2005

734

Smith

02/03/2007

345

Chen

01/25/2018

434

Johnson

11/23/2004

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

Explain the use of the SQL statement CREATE TABLE. Do NOT discuss the ALTER statement

in your answer, but DO include an example based on the SALESREP table in the General Sales

database.

101) The following database will be used in this question:

GENERAL SALES DATABASE:

SALESREP

SalesRepNo

RepName

HireDate

654

Jones

01/02/2005

734

Smith

02/03/2007

345

Chen

01/25/2018

434

Johnson

11/23/2004

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

What is an SQL view, and what is it used for? Include an example based on the CUSTOMER

table of the General Sales Database.

102) What is embedded SQL, and what considerations are necessary when using it in an

application?

103) What are SQL triggers and how are they used?

104) What are SQL stored procedures and how are they used?