Question 1

What are the three major advantages of cursors?

Accepted Answer

The coding in the procedure is greatly simplified. Normally in a program, the programmer must determine the most efficient way to access the data. In a program or procedure using embedded SQL, the optimizer determines the best way to access the data. The programmer isn't concerned with the best way to retrieve the data. In addition, when an underlying structure changes (for example, an additional index is created), the optimizer determines the best way to execute the query with the new structure. The program or procedure does not have to change at all. When the database structure changes in such a way that the necessary information is still obtainable using a different query, the only change required in the program or procedure is the cursor definition. The procedural code is not affected.

Question 2

Displaying a full name in query results when the first and last names are stored in separate columns in the database table is a typical use case for _____.

Accepted Answer

A)  calculations 
B)  concatenation 
C)  the UPPER function 
D)  the ALTER TABLE command 
A)  calculations 
B)  concatenation 
C)  the UPPER function 
D)  the ALTER TABLE command B

Question 3

In SQL Server, you use the & symbol to concatenate columns.

Accepted Answer

A) True 
 B)False  False

Question 4

You cannot use functions in WHERE clauses.

Accepted Answer

A) True 
 B)False

Question 5

Which function displays a value in uppercase letters in Oracle and SQL Server?

Accepted Answer

A)  CAPITAL 
B)  UPPER 
C)  LARGE 
D)  UCASE 
A)  CAPITAL 
B)  UPPER 
C)  LARGE 
D)  UCASE

Question 6

In Oracle, to add a specific number of months to a date, use the _____.

Accepted Answer

A)  MONTHS function 
B)  ADD_DATE function 
C)  ADD_MONTHS function 
D)  CALC_MONTHS function 
A)  MONTHS function 
B)  ADD_DATE function 
C)  ADD_MONTHS function 
D)  CALC_MONTHS function

Question 7

Which function can you use in Oracle, MySQL, and SQL Server to truncate everything to the right of the decimal point?

Accepted Answer

A)  TRUNCATE 
B)  FLOOR 
C)  ROUND 
D)  TRIM 
A)  TRUNCATE 
B)  FLOOR 
C)  ROUND 
D)  TRIM

Question 8

In MySQL, Oracle, and SQL server, you can display a value in lowercase letters using the _____.

Accepted Answer

A)  REGULAR function 
B)  LCASE function 
C)  SMALL function 
D)  LOWER function 
A)  REGULAR function 
B)  LCASE function 
C)  SMALL function 
D)  LOWER function

Question 9

Which of the following statements can you add to a stored procedure to provide meaningful feedback to a user who attempts to execute the procedure with an argument that is invalid because it is missing from the table column being queried?

Accepted Answer

A)  DECLARE EXIT HANDLER FOR NOT FOUND 
B)  DECLARE EXIT HANDLER FOR INVALID ARGUMENT 
C)  ERROR MESSAGE FOR NOT FOUND 
D)  ERROR MESSAGE FOR INVALID ARGUMENT 
A)  DECLARE EXIT HANDLER FOR NOT FOUND 
B)  DECLARE EXIT HANDLER FOR INVALID ARGUMENT 
C)  ERROR MESSAGE FOR NOT FOUND 
D)  ERROR MESSAGE FOR INVALID ARGUMENT

Question 10

To remove extra spaces to the right of a value after concatenating two or more columns, use the _____.

Accepted Answer

A)  TRUNCATE function 
B)  TRIM function 
C)  FLOOR function 
D)  RTRIM function 
A)  TRUNCATE function 
B)  TRIM function 
C)  FLOOR function 
D)  RTRIM function

Question 11

When you need to complete tasks that are beyond the capabilities of SQL, you need to use a procedural language.

Accepted Answer

A) True 
 B)False

Question 12

When you execute a stored procedure to retrieve a single row with embedded SQL, you need MySQL to treat several statements ending in a semicolon as if they were a single statement. What keyword do you use to tell MySQL to use a different character to signal the end of a statement in this situation?

Accepted Answer

A)  DELIMITER 
B)  END 
C)  OUT 
D)  STATEMENT 
A)  DELIMITER 
B)  END 
C)  OUT 
D)  STATEMENT

Question 13

A stored procedure that includes an UPDATE command between the BEGIN and END commands is known as a(n) _____.

Accepted Answer

A)  cursor procedure 
B)  retrieval procedure 
C)  display procedure 
D)  update procedure 
A)  cursor procedure 
B)  retrieval procedure 
C)  display procedure 
D)  update procedure

Question 14

When you use a cursor in a procedure, what command advances the cursor to the next row in the set of rows retrieved by the query and places the contents of the row in the indicated variables?

Accepted Answer

A)  RETRIEVE 
B)  FETCH 
C)  GET 
D)  ADVANCE 
A)  RETRIEVE 
B)  FETCH 
C)  GET 
D)  ADVANCE

Question 15

In SQL Server, you can obtain today's date using the _____.

Accepted Answer

A)  DATEDIFF() function 
B)  GETDATE() function 
C)  CURRENTDATE() function 
D)  SYSDATE function 
A)  DATEDIFF() function 
B)  GETDATE() function 
C)  CURRENTDATE() function 
D)  SYSDATE function

Question 16

Which command is used to call a procedure in SQL Server?

Accepted Answer

A)  CALL 
B)  RUN 
C)  DO 
D)  EXEC 
A)  CALL 
B)  RUN 
C)  DO 
D)  EXEC

Question 17

Variable names in PL/SQL must start with a letter and can contain letters, dollar signs, underscores, and number signs, but cannot exceed _____.

Accepted Answer

A)  15 characters 
B)  30 characters 
C)  45 characters 
D)  60 characters 
A)  15 characters 
B)  30 characters 
C)  45 characters 
D)  60 characters

Question 18

Identify and explain the error handling strategy used in the following procedure.

Accepted Answer

This MySQL procedure that takes a custom

Question 19

In Oracle, SQL Server, and MySQL, you can round a numeric value to a desired number of decimal places using the _____.

Accepted Answer

A)  INT function 
B)  FLOOR function 
C)  ROUND function 
D)  TRUNCATE function 
A)  INT function 
B)  FLOOR function 
C)  ROUND function 
D)  TRUNCATE function

Question 20

Which statement about triggers is correct?

Accepted Answer

A)  In a trigger, the action that should cause the procedure to execute appears between BEGIN and END commands. 
B)  In SQL Server, you create triggers using T-SQL. 
C)  In a trigger, the actions the database should perform automatically appear in the AFTER clause. 
D)  In Oracle, you cannot create triggers. 
A)  In a trigger, the action that should cause the procedure to execute appears between BEGIN and END commands. 
B)  In SQL Server, you create triggers using T-SQL. 
C)  In a trigger, the actions the database should perform automatically appear in the AFTER clause. 
D)  In Oracle, you cannot create triggers.

What are the three major advantages of cursors?

Displaying a full name in query results when the first and last names are stored in separate columns in the database table is a typical use case for _____.

In SQL Server, you use the & symbol to concatenate columns.

You cannot use functions in WHERE clauses.

Which function displays a value in uppercase letters in Oracle and SQL Server?

In Oracle, to add a specific number of months to a date, use the _____.

Which function can you use in Oracle, MySQL, and SQL Server to truncate everything to the right of the decimal point?

In MySQL, Oracle, and SQL server, you can display a value in lowercase letters using the _____.

Which of the following statements can you add to a stored procedure to provide meaningful feedback to a user who attempts to execute the procedure with an argument that is invalid because it is missing from the table column being queried?

To remove extra spaces to the right of a value after concatenating two or more columns, use the _____.

When you need to complete tasks that are beyond the capabilities of SQL, you need to use a procedural language.

When you execute a stored procedure to retrieve a single row with embedded SQL, you need MySQL to treat several statements ending in a semicolon as if they were a single statement. What keyword do you use to tell MySQL to use a different character to signal the end of a statement in this situation?

A stored procedure that includes an UPDATE command between the BEGIN and END commands is known as a(n) _____.

When you use a cursor in a procedure, what command advances the cursor to the next row in the set of rows retrieved by the query and places the contents of the row in the indicated variables?

In SQL Server, you can obtain today's date using the _____.

Which command is used to call a procedure in SQL Server?

Variable names in PL/SQL must start with a letter and can contain letters, dollar signs, underscores, and number signs, but cannot exceed _____.

Identify and explain the error handling strategy used in the following procedure.

In Oracle, SQL Server, and MySQL, you can round a numeric value to a desired number of decimal places using the _____.

Which statement about triggers is correct?

Introduction to Kimtay Pet Supplies and Staywell Student Accommodation Databases

Database Design Fundamentals

Creating Tables

Single Table Queries

Multiple Table Queries

Updating Data

Database Administration

Filters

Exam 8: Functions Procedures and Triggers

What are the three major advantages of cursors?

Displaying a full name in query results when the first and last names are stored in separate columns in the database table is a typical use case for _____.

In SQL Server, you use the & symbol to concatenate columns.

You cannot use functions in WHERE clauses.

Which function displays a value in uppercase letters in Oracle and SQL Server?

In Oracle, to add a specific number of months to a date, use the _____.

Which function can you use in Oracle, MySQL, and SQL Server to truncate everything to the right of the decimal point?

In MySQL, Oracle, and SQL server, you can display a value in lowercase letters using the _____.

Which of the following statements can you add to a stored procedure to provide meaningful feedback to a user who attempts to execute the procedure with an argument that is invalid because it is missing from the table column being queried?

To remove extra spaces to the right of a value after concatenating two or more columns, use the _____.

When you need to complete tasks that are beyond the capabilities of SQL, you need to use a procedural language.

When you execute a stored procedure to retrieve a single row with embedded SQL, you need MySQL to treat several statements ending in a semicolon as if they were a single statement. What keyword do you use to tell MySQL to use a different character to signal the end of a statement in this situation?

A stored procedure that includes an UPDATE command between the BEGIN and END commands is known as a(n) _____.

When you use a cursor in a procedure, what command advances the cursor to the next row in the set of rows retrieved by the query and places the contents of the row in the indicated variables?

In SQL Server, you can obtain today's date using the _____.

Which command is used to call a procedure in SQL Server?

Variable names in PL/SQL must start with a letter and can contain letters, dollar signs, underscores, and number signs, but cannot exceed _____.

Identify and explain the error handling strategy used in the following procedure.

In Oracle, SQL Server, and MySQL, you can round a numeric value to a desired number of decimal places using the _____.

Which statement about triggers is correct?

Introduction to Kimtay Pet Supplies and Staywell Student Accommodation Databases

Database Design Fundamentals

Creating Tables

Single Table Queries

Multiple Table Queries

Updating Data

Database Administration

Filters