Exam 8: Advanced SQL

PL/SQL blocks have a section used to declare variables.

What are the four different result types that can be returned from a subquery? For each type,explain what it is and when it is used.

The most useful feature of PL/SQL blocks is that they let you create code that can be named,stored,and executed by the DBMS.

A row-level trigger requires use of the ____________________ keywords.This type of trigger is executed once for each row affected by the triggering statement.

The NEXT command for a cursor is used to retrieve a row from the cursor and place it in the respective PL/SQL variables.

A(n)____ is a block of code (containing standard SQL statements and procedural extensions)that is stored and executed at the DBMS server.

The ____ operator could be used in place of INTERSECT if the RDBMS does not support it.

Using Oracle ____________________,you can write a PL/SQL code block by enclosing the commands inside BEGIN and END clauses.

A(n)____ join will select only the rows with common values in the common attribute(s).

You can declare variables inside a stored procedure.

The ____________________ statement combines rows from two or more queries without including duplicate rows.

The ____ statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second.

One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions.

A(n)____________________ query can be used to produce a relation that retains the duplicate rows.

The ____ function returns the current system date in MS Access.

Describe the characteristics of an Oracle sequence.

In Oracle,the ____ function converts a date to a character string.

To remedy the lack of procedural functionality in SQL,and to provide some standardization within the many vendor offerings,the SQL-99 standard defined the use of persistent stored modules.

A trigger is executed as part of the transaction that triggered it.

Assume you are using the MINUS operator to combine the results from two tables with identical structure,CUSTOMER and CUSTOMER_2.The CUSTOMER table contains 10 rows,while the CUSTOMER_2 table contains 7 rows.Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table.How many records are returned when using the MINUS operator?