Deck 6: Procedural Language Sql and Advanced Sql

Question 1

Procedural code is executed on the database client machine.

Answer 1

False

Question 2

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.

Answer 2

True

Question 3

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.

Answer 3

True

Question 4

UNION, INTERSECT, and MINUS work properly only if relations are union-compatible, which means that the names of the relation attributes and their data types must be different.

Question 5

SQL supports the conditional execution of procedures (if...then...else statements) that are typically supported by a programming language.

Question 6

To test a trigger, you should use the EXECUTE TRIGGER command.

Question 7

In Oracle, you can use the SQL*Plus command SHOW ERRORS to help you diagnose errors found in PL/SQL blocks.

Question 8

One of the disadvantages of stored procedures is that they increase network traffic.

Question 9

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

Question 10

Stored procedures help reduce code duplication by means of code isolation and code sharing.

Question 11

Triggers can only be used to update table values.

Question 12

Each statement inside the PL/SQL code must end with a period (".").

Question 13

PL/SQL functions are executed in the same way as functions such as MIN and AVG.

Question 14

Automating business procedures and automatically maintaining data integrity and consistency are trivial in a modern business environment.

Question 15

Just like database triggers, stored procedures are stored in the database.

Question 16

Every PL/SQL block must be given a name.

Question 17

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

Question 18

A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event.

Question 19

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

Question 20

A statement-level trigger is assumed if you omit the FOR EACH ROW keywords.

Question 21

Stored procedures are executed using the EXEC command.

Question 22

Cursors are held in a reserved memory area in the client computer.

Question 23

The statement SELECT * FROM T1, T2 produces a(n) ____ join.

A) cross
B) natural
C) equi-
D) full

Question 24

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

Question 25

The ANSI standard defines ____ type(s) of outer join(s)

A) one
B) two
C) three
D) four

Question 26

A ____ join returns rows with matching values and includes all rows from both tables (T1 and T2) with unmatched values.

A) natural
B) cross
C) full outer
D) left outer

Question 27

If you wish to create an inner join, but the two tables do not have a commonly named attribute, you can use a(n) ____ clause.

A) OF
B) USING
C) HAS
D) JOIN ON

Question 28

When using a(n) ____ join, only rows that meet the given criteria are returned.

A) full
B) inner
C) outer
D) set

Question 29

Cursor-style processing involves retrieving data from the cursor, one row at a time.

Question 30

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

A) natural
B) cross
C) full
D) outer

Question 31

The syntax for a left outer join is ____.

A) SELECT column-list
FROM table1 OUTER JOIN table2 LEFT
WHERE join-condition
B) SELECT column-list
FROM table1 LEFT [OUTER] JOIN table2
ON join-condition
C) SELECT column-list
WHERE LEFT table1 = table 2
D) SELECT column-list
FROM table1 LEFT table2 [JOIN]
WHERE join-condition

Question 32

A(n) ____ join returns not only the rows matching the join condition (that is, rows with matching values in the common columns) but also the rows with unmatched values.

A) outer
B) inner
C) equi-
D) cross

Question 33

"Linked SQL" is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion.

Question 34

An implicit cursor is automatically created in procedural SQL when the SQL statement returns only one value.

Question 35

The following SQL statement uses a(n) ____. SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

A) set operator
B) natural join
C) "old-style" join
D) procedural statement

Question 36

An explicit cursor must return two or more rows.

Question 37

You can declare variables inside a stored procedure.

Question 38

A stored function is another name for a stored procedure.

Question 39

How many rows would be returned from a cross join of tables A and B, if A contains 8 rows and B contains 18?

A) 8
B) 18
C) 26
D) 144

Question 40

Stored procedures must have at least one argument.

Question 41

The ____ data type is considered compatible with VARCHAR(35).

A) DATE
B) INT
C) TINYINT
D) CHAR(15)

Question 42

The Oracle ____ function returns the current date.

A) DATE
B) SYSDATE
C) CURRENT_DATE
D) TO_DATE

Question 43

The syntax for the UNION query is ____.

A) query + query
B) UNION (query, query)
C) UNION: query query
D) query UNION query

Question 44

"Union-compatible" means that the ____.

A) names of the relation attributes can be different, but the data types must be alike
B) names of the relation attributes must be the same, but the data types can be different
C) names of the relation attributes must be the same and their data types must be alike
D) number of attributes must be the same, but the names and data types can be different

Question 45

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.

A) UNION
B) UNION ALL
C) INTERSECT
D) MINUS

Question 46

When using the Oracle TO_DATE function, the code ____ represents a three-letter month name.

A) MON
B) MM3
C) MONTH
D) MM

Question 47

____ is a relational set operator.

A) MINUS
B) PLUS
C) ALL
D) EXISTS

Question 48

In subquery terminology, the first query in the SQL statement is known as the ____ query.

A) outer
B) left
C) inner
D) base

Question 49

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

A) TO_DATE()
B) SYSDATE()
C) DATE()
D) TODAY()

Question 50

The Oracle ____ function compares an attribute or expression with a series of values and returns an associated value or a default value if no match is found.

A) NVL
B) TO_CHAR
C) DECODE
D) CONVERT

Question 51

In a subquery, the ____ query is executed first.

A) left
B) right
C) inner
D) outer

Question 52

A ____ is a query (SELECT statement) inside a query.

A) subquery
B) range query
C) join
D) set query

Question 53

Assume you are using the UNION ALL 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 UNION ALL operator?

A) 7
B) 10
C) 15
D) 17

Question 54

The ____ query combines rows from two queries and excludes duplicates.

A) UNION
B) UNION ALL
C) INTERSECT
D) MINUS

Question 55

The ____ data type is compatible with NUMBER.

A) VARCHAR(15)
B) SMALLINT
C) DATE
D) CHAR(10)

Question 56

Assume you are using the INTERSECT 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 INTERSECT operator?

A) 0
B) 2
C) 7
D) 10

Question 57

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

A) CONVERT()
B) TO_DATE
C) TO_CHAR()
D) TO_STRING()

Question 58

The Oracle string concatenation function is ____.

A) CONCAT
B) +
C) ||
D) &&

Question 59

When using the Oracle TO_NUMBER function to convert a character string into a number, ____ represents a digit.

A) 0
B) 9
C) $
D) #

Question 60

Assume you are using the UNION 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 UNION operator?

A) 7
B) 10
C) 15
D) 17

Question 61

The use of the ____________________ operator allows you to compare a single value with a list of values returned by the first subquery (sqA) using a comparison operator other than EQUALS.

Question 62

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

A) PSM
B) PLS
C) SQL Statement
D) PMR

Question 63

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

A) IN
B) OF
C) AND
D) UNION

Question 64

Oracle recommends ____ for creating audit logs.

A) triggers
B) stored procedures
C) stored functions
D) tables

Question 65

The ____ pseudo-column is used to select the next value from a sequence.

A) CURRVAL
B) NEXTVAL
C) NEXT
D) GET_NEXT

Question 66

A(n) ____________________ join returns all rows with matching values in the matching columns and eliminates duplicate columns.

Question 67

The Oracle equivalent to an MS Access AutoNumber is a(n) ____.

A) auto-number
B) sequence
C) TO_NUMBER function
D) trigger

Question 68

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?

A) 0
B) 2
C) 8
D) 10

Question 69

A(n) ____________________ join performs a relational product (also known as the Cartesian product) of two tables.

Question 70

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

A) IN
B) NOT IN
C) AND
D) UNION

Question 71

The ____________________ join is the traditional join in which only rows that meet a given criteria are selected.

Question 72

When using a subquery, the output of a(n) ____________________ query is used as the input for the outer query.

Question 73

The ____________________ outer join returns not only the rows matching the join condition (that is, rows with matching values in the common column), but also the rows in the left-side table with unmatched values in the right-side table.

Question 74

The ____________________ clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows.

Question 75

The IN subquery uses a(n) ____________________ operator.

Question 76

A stored function uses the ____ statement to return a value.

A) EXIT
B) END
C) RETURN
D) PROCESS

Question 77

A(n) ____________________ join returns not only the matching rows but also the rows with unmatched attribute values for one or both tables to be joined.

Question 78

In Oracle, ____ make(s) it possible to merge SQL and traditional programming constructs, such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops,) and error trapping.

A) triggers
B) indexes
C) embedded SQL
D) procedural SQL

Question 79

An alternate syntax for a join is SELECT column-list FROM table1 JOIN table2 ____________________ (common-column).

Question 80

The PL/SQL block starts with the ____ clause.

A) IS
B) OPEN
C) DECLARE
D) BEGIN