Database Concepts, 8e (Kroenke)
Appendix E Advanced SQL
1) Importing Microsoft Excel data into a DMBS varies considerably from DBMS to DBMS.
2) One of the limitations of Microsoft Access 2016 is that is does not include application
development tools.
3) A stored program that is attached to a table or view is called a stored procedure.
4) SQL triggers use the SQL keywords BEFORE, INSTEAD OF, and AFTER.
5) SQL triggers can be used with SQL operations INSERT, UPDATE, and DELETE.
6) SQL triggers can be used when the DBMS receives an INSERT request.
7) The Oracle DBMS supports the SQL BEFORE trigger.
8) The SQL Server DBMS supports the SQL BEFORE trigger.
9) SQL triggers can be used when the DBMS receives an UPDATE request.
10) SQL triggers are used for providing default values, validity checking, updating views, and
performing referential integrity actions.
11) A stored program that is stored within the database and compiled when used is called a
trigger.
12) Stored procedures have the advantage of greater security, decreased network traffic, SQL
optimized by the DBMS compiler, and code sharing.
13) Unlike application code, stored procedures are never distributed to the client computers.
14) 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.
15) 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
E) a stored procedure
16) Which of the following is not an SQL trigger?
A) BEFORE UPDATE
B) INSTEAD OF UPDATE
C) BEFORE INSERT
D) INSTEAD OF CONSTRAINT
E) AFTER DELETE
17) Which of the following is an SQL trigger that Oracle supports?
A) BETWEEN INSERT
B) WHILE INSERT
C) DURING INSERT
D) AFTER INSERT
E) ALONG WITH INSERT
18) Which of the following is an SQL trigger that Microsoft SQL Server supports?
A) BEFORE
B) INSTEAD OF
C) AFTER
D) B and C only
E) A, B, and C
19) SQL triggers can be used when the DBMS receives a(n) ________ request.
A) ADD
B) UPDATE
C) CLEAN
D) FREE
E) NEW
20) SQL triggers are used for all uses except ________.
A) validity checking
B) providing default values
C) enforcing synchronization
D) enforcing data constraints
E) updating views
21) A stored program that is attached to the database is called ________.
A) a CHECK constraint
B) a view
C) embedded SQL
D) a trigger
E) a stored procedure
22) Stored procedures have the advantage of all choices except ________.
A) greater security
B) data integrity through distributed copies
C) SQL optimized by the DBMS compiler
D) code sharing
E) decreased network traffic
23) Because SQL stored procedures allow and encourage code sharing among developers, stored
procedures give database application developers the advantages of all except ________.
A) increased network throughput
B) standardized processing
C) specialization among developers
D) less work
E) greater security
24) What are SQL triggers and how are they used?
25) What are SQL stored procedures and how are they used?
26) The SQL statement DROP VIEW ViewName is used to delete a view from a database.
27) What is not a reason to use the SQL ALTER TABLE statement?
A) Add triggers
B) Add columns
C) Drop columns
D) Add constraints
E) Modify columns
28) SQL views are used for all except ________.
A) to backup preexisting tables
B) to display the results of computations
C) to hide complicated SQL syntax
D) to layer built-in functions
E) to hide columns or rows
29) The LEFT and RIGHT keywords can be used to include unmatched rows into the results.
30) Describe a situation where a SQL MERGE statement is appropriately used in a table.
31) A relational database table meets the definition of a set.
32) An SQL virtual table is called a view.
33) SQL views contain their own data.
34) SQL views can be used to hide rows.
35) SQL views can be used to hide columns.
36) SQL views can be used to display the results of computed columns.
37) SQL views can be used to hide complicated SQL syntax.
38) SQL views can be used to layer built-in SQL functions.
39) An SQL virtual table is called a(n) ________.
A) CHECK constraint
B) view
C) embedded SQL statement
D) trigger
E) stored procedure
40) Microsoft Access ________.
A) supports standard SQL view statements
B) only supports SQL view statements as QBE constructions
C) can store a view equivalent Access query
D) can store a view equivalent Access query but does not provide a means to use it
E) cannot store any database object that is equivalent to an SQL view
41) Microsoft Access queries ________.
A) can use previously stored view equivalent Access queries
B) cannot use previously stored view equivalent Access queries for any purpose
C) cannot use previously stored view equivalent Access queries to hide rows
D) cannot use previously stored view equivalent Access queries to hide columns
E) cannot use previously stored view equivalent Access queries to hide complicated SQL syntax
42) An SQL virtual table is called a(n) ________.
43) SQL views can be used to hide table ________.
44) SQL views can be used to display the results of ________.
45) SQL views can be used to layer ________.
46) Correlated subqueries do exactly the same type of work as join queries.
47) Unlike typical subqueries, correlated subqueries creates “copies” of a table, and evaluate
each row of the outer copy with every row of the inner copy.
48) Outer joins are completely supported in Microsoft Access.
49) Similar to correlated subqueries, queries on recursive relationships use aliases (copies) of the
same table.
50) The SQL UNION operator is supported by all DBMS products.
51) What is the first stage in the logical process flow of a User-Defined function?
A) Receive input from calling SQL statement
B) Preprocess parameter values
C) Process parameter values to create result(s)
D) Arrange results into a formal ResultSet
E) Output to calling SQL statement
52) SQL views are constructed from SELECT statements.
53) SQL statements used to construct views cannot contain the WHERE clause.
54) The SQL command CREATE VIEW is used to create view structures.