Deck 8: SQL Functions and Procedures

Question 1

In procedural languages, you can accomplish many tasks using a single command.

Answer 1

False

Question 2

You can embed SQL commands in Access programs similarly to the way you can in PL/SQL and T-SQL.

Answer 2

False

Question 3

To create a stored procedure, use the CREATE PROCEDURE command.

Answer 3

True

Question 4

When you anticipate running a particular query often, you can improve overall performance by saving the query in a file called a routine.

Question 5

In PL/SQL, procedural code appears between the BEGIN and END commands.

Question 6

PL/SQL, which was developed by Oracle as an extension of SQL, is an example of a procedural language.

Question 7

Oracle, SQL Server, and Access all use the ADD_MONTHS function to add the number of months to a date.

Question 8

In Access, use the & symbol to concatenate columns.

Question 9

To add a specific number of days to a date, you must use a function.

Question 10

A client is a computer that is connected to a network and has access through the server to the database.

Question 11

If a price is 24.95, the function ROUND(PRICE,0) would result in 24.

Question 12

You cannot use functions in WHERE clauses.

Question 13

The UPPER function allows you to manipulate numeric data.

Question 14

PL/SQL commands have a certain format and cannot include any blank lines in the code.

Question 15

The ADD_MONTHS function has one argument.

Question 16

You use aggregate functions to perform calculations based on groups of records.

Question 17

A function can have more than one argument.

Question 18

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

Question 19

To truncate (remove) everything to the right of the decimal point, use the TRUNCATE function.

Question 20

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

Question 21

In Oracle and SQL Server, the ____ function displays a value in lowercase letters.

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

Question 22

You can use a stored procedure to update data but not to delete data.

Question 23

To call a procedure in SQL Server, use the EXEC command.

Question 24

In Microsoft Access, the ____ function displays a value in uppercase letters.

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

Question 25

PL/SQL can process only one record at a time.

Question 26

In SQL Server, the INSERTED table is a temporary system table that contains a copy of the values that the last SQL command inserted.

Question 27

In Microsoft Access, you must store an SQL command in a string variable.

Question 28

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

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

Question 29

In T-SQL, all arguments start with the ampersand (&) symbol.

Question 30

A trigger is a pointer to a row in the collection of rows retrieved by an SQL command.

Question 31

DBMS_OUTPUT is a package that contains multiple procedures, including PUT_LINE.

Question 32

In T-SQL, arguments should have the same data type and length as the particular column in a table that they represent.

Question 33

In Microsoft Access, the ____ function displays a value in lowercase letters.

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

Question 34

In SQL Server and Microsoft Access, to add a specific number of months to a date, use the ____ function.

A) MONTHS
B) DATEADD()
C) ADD_MONTHS
D) CALC_MONTHS

Question 35

In Oracle and SQL Server, the ____ function displays a value in uppercase letters.

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

Question 36

Microsoft Access supports triggers.

Question 37

To handle processing of errors, include an ABORT clause in a stored procedure.

Question 38

In Oracle and SQL Server, use the ____ function to truncate everything to the right of the decimal point.

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

Question 39

In Oracle, SQL Server, and Microsoft Access, use the ____ function to round a numeric value to a desired number of decimal places.

A) INT
B) INTEGER
C) ROUND
D) TRUNCATE

Question 40

SQL Server uses an extended version of SQL called T-SQL or Transact-SQL.

Question 41

When you use a cursor in a procedure, the ____ 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 42

A(n) ____ is a procedure that is executed automatically in response to an associated database operation.

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

Question 43

The process of combining two or more character columns into a single expression is called ____________________.

Question 44

In Microsoft Access, you can use the ____ function to obtain today's date.

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

Question 45

To create a stored procedure in Oracle, use the ____ command.

A) CREATE ROUTINE
B) CREATE SCRIPT
C) CREATE PROCEDURE
D) CREATE EXECUTABLE

Question 46

Oracle uses ____________________ as an extension of SQL to accomplish procedural tasks.

Question 47

To remove extra spaces to the right of a value, for example, the value in a FIRST_NAME column, use the ____ function.

A) TRUNCATE
B) TRIM
C) RIGHT
D) RTRIM

Question 48

In SQL Server, you can use the ____ function to obtain today's date.

A) TODAYS_DATE
B) GETDATE()
C) CURRENTDATE()
D) SYSDATE

Question 49

In the function UPPER(LAST_NAME), the item in parentheses (LAST_NAME) is called the ____________________ for the function.

Question 50

A(n) ____________________ is a computer that is connected to a network and has access through the server to the database.

Question 51

In PL/SQL, you can ensure that a variable has the same data type as a particular column by using the ____ attribute.

A) @VARIABLE
B) %VARIABLE
C) @DATATYPE
D) %TYPE

Question 52

In Microsoft Visual Basic, use the ____ statement to create a string variable.

A) Dim
B) Decl
C) Var
D) Declare

Question 53

To concatenate columns in Oracle, you type two ____________________ between the column names.

Question 54

____________________ is the procedural language that SQL Server uses.

Question 55

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

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

Question 56

To call a procedure in SQL Server, use the ____ command.

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

Question 57

A(n) ____ is a pointer to a row in the collection of rows retrieved by an SQL command.

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

Question 58

SQL is a powerful ____________________ language in which you communicate tasks to the computer using simple commands.

Question 59

A(n) ____________________ language is one in which you must give the computer the step-by-step process for accomplishing a task.

Question 60

In Oracle, you can use the ____ function to obtain today's date.

A) TODAYS_DATE
B) CURRENT_DATE
C) NOWDATE
D) SYSDATE

Question 61

A(n) ____________________ is a procedure that is executed automatically in response to an associated database operation, such as an INSERT, UPDATE, or DELETE command.

Question 62

To handle processing of errors, include a(n) ____________________ clause in stored procedure.

Question 63

In SQL Server, the cursor property ____________________ means that the cursor is used for retrieval purposes only.

Question 64

In Microsoft Access, programs are written in ____________________, which does not support embedded SQL commands directly in the code.

Question 65

Although Access does not support stored procedures, you can achieve some of the same convenience by creating a(n) ____________________ query that prompts the user for the arguments you would otherwise use in a stored procedure.

Question 66

Name two reasons for creating a stored procedure.

Question 67

To create a stored procedure in Oracle, use the ____________________ command.

Question 68

A(n) ____________________ is a pointer to a row in the collection of rows retrieved by an SQL command.

Question 69

A procedure that updates data is called a(n) ____________________ procedure.

Question 70

What are the three advantages of cursors?

Question 71

In SQL Server, you deactivate a cursor by using the ____________________ command.

Question 72

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

Question 73

Each procedure that you create in Oracle should end with a(n) ____________________.

Question 74

To close a cursor and deactivate it, use the ____________________ command.

Question 75

When you use a cursor, the ____________________ command opens the cursor and causes the query to be executed, making the results available to the procedure.