Deck 8: Advanced Sql

Question 1

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

Answer 1

True

Question 2

A sequence is not associated with a table and can be dropped from a database with a DROP SEQUENCE command.

Answer 2

True

Question 3

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

Answer 3

False

Question 4

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

Question 5

An inequality condition is also known as a natural join and an equality condition is also called a theta join.

Question 6

A statement-level trigger is assumed if a designer omits the FOR EACH ROW keywords.

Question 7

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

Question 8

Triggers can only be used to update table values.

Question 9

A persistent stored module is stored and executed on the database client machine.

Question 10

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.

Question 11

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

Question 12

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

Question 13

Every PL/SQL block must be given a name.

Question 14

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

Question 15

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

Question 16

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

Question 17

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

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

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

Question 20

SQL supports the conditional execution of procedures (IF-THEN-ELSE statements) that are typically supported by a programming language.

Question 21

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 22

Stored procedures must have at least one argument.

Question 23

"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 24

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 25

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 26

MySQL allows multiple triggering conditions per trigger.

Question 27

Stored procedures are executed using the EXEC command.

Question 28

A row-level trigger is assumed if we omit the FOR EACH ROW keywords and a statement-level trigger required the use of the FOR EACH ROW keyword.

Question 29

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

Question 30

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

Question 31

A relational join operation merges rows from two tables.

Question 32

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 33

A trigger definition consists of the triggering timing, the triggering event, the triggering level, and the triggering action.

Question 34

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

Question 35

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

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

Question 36

Variables can be declared inside a stored procedure.

Question 37

A stored function is another name for a stored procedure.

Question 38

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

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

Question 39

An explicit cursor must return two or more rows.

Question 40

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

Question 41

In the context of SELECT subquery types, a is returned when an UPDATE subquery is used.

A) NULL
B) single value
C) list of values
D) virtual table

Question 42

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

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

Question 43

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

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

Question 44

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

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

Question 45

If a designer wishes to create an inner join, but the two tables do not have a commonly named attribute, he can use a(n) _____ clause.

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

Question 46

"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 47

is a string function that returns the number of characters in a string value.

A) LENGTH
B) SUBSTRING
C) CONCAT
D) UCASE

Question 48

is a relational set operator.

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

Question 49

The Oracle string concatenation function is .

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

Question 50

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 51

The function returns the current system date in MS Access.

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

Question 52

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

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

Question 53

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 54

Which of the following is a feature of a correlated subquery?
A) The inner subquery executes first.
B) The outer subquery initiates the process of execution in a subquery.

A) The inner subquery initiates the process of
D) The outer subquery executes independent execution in a subquery. of the inner subquery.

Question 55

In a subquery, the query is executed first.

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

Question 56

A is a query inside a query.

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

Question 57

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 58

All join conditions are connected through a(n) logical operator.

A) NOR
B) OR
C) AND
D) NOT

Question 59

The Oracle function returns the current date.

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

Question 60

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 61

The syntax for the UNION query is .

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

Question 62

Oracle recommends for creating audit logs.

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

Question 63

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 64

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) cursor-style processing
B) stored procedures
C) embedded SQL
D) Procedural Language SQL

Question 65

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

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

Question 66

Assume a designer is 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 Jenna and Howard 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 67

Which of the following is a feature of oracle sequences?
A) Oracle sequences are tied to columns and tables.
B) Oracle sequences generate a character string that can be assigned to tables.

A) An oracle sequence uses the identity column
D) An oracle sequence can be created and deleted property to automatically number rows. anytime.

Question 68

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

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

Question 69

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 70

is a cursor attribute that returns True if the last FETCH returned a row, and FALSE if not.

A) %ROWCOUNT
B) %NOTFOUND
C) %FOUND
D) %ISOPEN

Question 71

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 72

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

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

Question 73

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 74

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 75

The PL/SQL block starts with the section.

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

Question 76

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

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

Question 77

The statement combines rows from two queries and excludes duplicates.

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

Question 78

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 79

The data type is compatible with NUMBER.

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

Question 80

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

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