Deck 8: Functions Procedures and Triggers

Question 1

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

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

Answer 1

D

Question 2

How do you add a specific number of days to a date in MySQL?

A) use the DATE_ADD function without an INTERVAL unit
B) use an expression with a plus (+) operator such as "+ 14"
C) use the ADD_DAYS function
D) use the SUM operator

Answer 2

B

Question 3

Applying the function ROUND(PRICE,0) to the price 24.95 results in 24.

Answer 3

False

Question 4

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

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

Question 5

If Mack wants to display the number of days between two dates, such as the current date and an invoice date from a table, which function can he use in his MySQL query to accomplish this?

A) DATE_ADD()
B) SUBTRACT()
C) DATEDIFF()
D) SYSDATE()

Question 6

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

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

Question 7

A procedure that is executed automatically in response to an associated database operation is called a _____.

A) cursor
B) trigger
C) waypoint
D) fetchpoint

Question 8

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

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

Question 9

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?

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

Question 10

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

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

Question 11

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

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

Question 12

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

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

Question 13

Which WHERE clause can you use with a SELECT statement to perform a case-insensitive search for rows containing the first name Marco?

A) WHERE FIRST_NAME = UPPER('MARCO')
B) WHERE FIRST_NAME = LOWER('MARCO')
C) WHERE LOWER(FIRST_NAME) = 'MARCO'
D) WHERE UPPER(FIRST_NAME) = 'MARCO'

Question 14

The DATE_ADD function has one argument.

Question 15

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

Question 16

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

Question 17

You cannot use functions in WHERE clauses.

Question 18

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

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

Question 19

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

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

Question 20

What function can you use to obtain today's date in MySQL?

A) TODAYS_DATE()
B) CURRENT_DATE()
C) DATE()
D) CURDATE()

Question 21

When creating a stored procedure to retrieve a single row with embedded SQL, you must place the procedural code _____.

A) inside a SELECT or WHERE clause
B) within the DELIMITER clause
C) between the BEGIN and END commands
D) after the DECLARE statement

Question 22

Discuss two reasons for creating a stored procedure.

Question 23

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

Question 24

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) DELIMITER
B) END
C) OUT
D) STATEMENT

Question 25

A trigger is executed _____.

A) regularly at given time intervals
B) in response to a command such as INSERT, UPDATE, or DELETE
C) in response to a TRIGGER command
D) at a user's request

Question 26

What are the three major advantages of cursors?

Question 27

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?

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 28

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

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

Question 29

What does the error code 1172 stand for?

A) illegal value
B) not found
C) invalid argument
D) too many rows

Question 30

Which command do you use to write a procedure that should be executed automatically when a certain database operation is performed?

A) SET AFTER
B) SET ON UPDATE
C) CREATE PROCEDURE
D) CREATE TRIGGER

Question 31

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 _____.

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

Question 32

MySQL procedures without error handling _____.

A) display helpful error messages to the user
B) include "declare an exception" clauses
C) return unclear results when used with invalid arguments
D) are common and acceptable

Question 33

What is the difference between a nonprocedural language and a procedural language? Give an example of each.

Question 34

How do you write a procedure to delete a table row?

Question 35

Which statement about triggers is correct?

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.