CGS2545 Chapter 8

Terms for Chapter

Dynamic SQL - The process of making an application capable of generating specific SQL code on the fly, as the application is processing.

Embedded SQL - The process of including hard-coded SQL statements in a program written in another language, such as C or Java.

Equi-join - A join in which the joining condition is based on equality between values in the common columns. Common columns appear (redundantly) in the result table.

Function - A stored subroutine that returns one value and has only input parameters.

Join - A relational operation that causes two tables with a common domain to be combined into a single table or view.

Natural join - Same as equi-join except one of the duplicate columns is eliminated in the result table.

Outer join - A join in which rows that do not have matching values in common columns are nevertheless included in the result table.

Persistent Stored Modules (SQL/PSM) - Extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions.

Procedure - A collection of procedural and SQL statements that are assigned a unique name within the schema and stored in the database.

Trigger - A named set of SQL statements that are considered (triggered) when a data modification (INSERT, UPDATE, DELETE) occurs. If a condition stated within the trigger is met, then a prescribed action is taken.

User-defined datatype (UDT) - SQL-99 allows uers to define their own datatype by making it a subclass of a standard type or creating a type that behaves as an object. UDTs may also have defined functions and methods.

Back to the Top

Questions from Companion Website

Multiple Choice

1. A join where common (redundant) columns appear in the result table.
Correct answer : Equi-join
In an equi-join, the redundant columns demonstrate that common attributes have been matched.

2. A join where common columns appear only once in the result table.
Correct answer : Natural join
A natural join is the same as an equi-join except one of the duplicate columns is not shown.

3. A join where nonmatching rows are included in the result table.
Correct answer : Outer join
In an outer join, the nonmatching fields are included and contain null values.

4. A join that contains all columns and all rows from joined tables.
Correct answer : Union Join
SQL-99 allows for a UNION JOIN, which is not implemented in all DBMS products yet.

5. A subquery is enclosed in
Correct answer : parentheses.

6. Using the ____ keyword in a subquery will check to see if any rows fit the conditions, not return values from particular columns.
Correct answer : EXIST
EXIST returns a value of true or false and is included just prior to the beginning of the subquery.

7. The ____ clause is used to combine the output from multiple queries into a single result table.
Correct answer : UNION
The UNION clause is used to combine query results.

8. IF-THEN-ELSE logic can be accomplished by using the ____ keyword(s) in a statement.
Correct answer : CASE
The CASE statement can be used to evaluate an expression that gives a value or a predicate (true, false, donít know).

9. This data dictionary table description includes the owner of each table, table name, column name, data type, and data length.
Correct answer : DBA_TAB_COLUMNS
This table contains 15 attributes that describe the tables in a database.

10. Which is NOT an advantage of SQL-99, Amendment 1?
Correct answer : Less storage space used
Amendment 1 will include additional analytical capabilities, more efficient processing, and more standardized functions.

11. A SQL/PSM statement that sets a condition that results in exiting from a loop is
Correct answer : LEAVE.
LEAVE is one of several SQL-control statements.

12. In SQL, a ____ returns one value and has only input parameters.
Correct answer : function
SQL has a library of built in functions or as well as user defined functions.

13. When the ____ command is used the contents of the log file are applied to the database.
Correct answer : COMMIT
The COMMIT or END TRANSACTION command allows transactions to be processed as sets and helps maintain database integrity.

14. ____ is central to most Internet database applications.
Correct answer : Dynamic SQL
Dynamic SQL is used to generate SQL code as an application is processing.

15. When a ____ occurs, changes to a table are undone.
Correct answer : rollback
This is necessary if a problem occurs in the middle of a transaction.

Back to the Top

True/False Questions

1. The real power of a relational database is in its attributes.
Correct answer : FALSE
The real power is in its relationships between entities.

2. Procedures execute automatically when a predefined condition exists.
Correct answer : FALSE
Triggers execute automatically.

3. Dynamic SQL inserts parameter values as needed.
Correct answer : TRUE
Dynamic SQL is essential to Web applications.

4. A join is a relational operation that causes two common attributes to be combined into a single composite attribute.
Correct answer : FALSE
A join combines two tables.

5. An outer join includes all data of each table joined.
Correct answer : FALSE
A union join includes all data.

6. Join and subquery techniques may often be used to accomplish the same task.
Correct answer : TRUE
However, sometimes only one or the other is effective.

7. In subqueries, you may use IN instead of = .
Correct answer : TRUE
IN works with zero, one, or many values.

8. A correlated subquery uses the result of the inner query to determine the processing of the outer query.
Correct answer : FALSE
In a correlated subquery, processing the inner query depends on the data from the outer query.

9. In order to use the UNION clause on multiple queries, each query must output the same number of rows.
Correct answer : TRUE

10. The CAST command is used to control datatype conversion.
Correct answer : TRUE
This may be necessary when using the UNION clause to combine output from multiple queries.

11. A transaction is a set of closely related update commands that must all be done or none of them done in order for the database to remain valid.
Correct answer : TRUE
Data recovery services are needed if the transaction does not complete all of its commands.

12. The data dictionary holds useful information primarily for the database administrator.
Correct answer : FALSE
Data dictionary information is useful to a database administrator and to database users.

13. SQL-99 allows users to create UDTs.
Correct answer : TRUE
UDTs are User-defined data types.

14. SQL/PSM is a comprehensive database repository.
Correct answer : FALSE
SQL/PSM is used to create and store program modules.

15. Dynamic SQL is the process of including hard-coded SQL statements in a program written in another language.
Correct answer : FALSE
Embedded SQL is the process of including hard-coded SQL statements in a program.

Back to the Top

Essay Questions

1. Describe the proposed amendment to SQL-99, Amendment 1.
Amendment 1 would add a set of analytical functions, OLAP functions, as SQL extensions. It addresses the needs for analytical capabilities within the database engine.

2. What kind of information is stored in the data dictionary?
Tables of database definition information are stored in a data dictionary. Included (but not limited to) are: details on tables, clusters and views, constraint definitions, user information, and system privileges.

3. Why would you use the BEGIN TRANSACTION and END TRANSACTION commands?
The BEGIN TRANSACTION and END TRANSACTION commands define the boundaries of a transaction, commit the work of a transaction as a permanent change to the database and abort a transaction, if necessary.

4. What are the advantages of SQL-invoked routines?
The advantages of SQL-invoked routines include flexibility, efficiency, sharability, and applicability.

5. When should you use a join versus a subquery?
The join technique is useful when data from several relations are to be retrieved and displayed, and the relationships are not necessarily nested.

Links to Other Pages in this Website:

Back to the Top