Deck 8: SQL Functions and Procedures

Question 1

The ADD_MONTHS function has one argument.

Answer 1

False

Question 2

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

Answer 2

True

Question 3

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

Answer 3

True

Question 4

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

Question 5

The UPPER function allows you to manipulate numeric data.

Question 6

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

Question 7

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

Question 8

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

Question 9

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

Question 10

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

Question 11

In Access,use the & symbol to concatenate columns.

Question 12

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

Question 13

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

Question 14

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

Question 15

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

Question 16

You cannot use functions in WHERE clauses.

Question 17

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

Question 18

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

Question 19

A function can have more than one argument.

Question 20

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

Question 21

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 22

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

Question 23

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

Question 24

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

Question 25

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

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 Oracle and SQL Server,the ____ function displays a value in lowercase letters.

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

Question 28

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

Question 29

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

Question 30

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 31

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

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

Question 32

Microsoft Access supports triggers.

Question 33

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

Question 34

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

Question 35

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

Question 36

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

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

Question 37

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 38

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 39

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

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

Question 40

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

Question 41

____________________ is the procedural language that SQL Server uses.

Question 42

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

Question 43

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

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

Question 44

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

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

Question 45

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

Question 46

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 47

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

Question 48

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

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

Question 49

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 50

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 51

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

Question 52

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

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

Question 53

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

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

Question 54

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 55

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

Question 56

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 57

A ____ 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

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

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

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

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

Question 61

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

Question 62

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 63

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

Question 64

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

Question 65

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

Question 66

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

Question 67

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 68

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

Question 69

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

Question 70

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

Question 71

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

Question 72

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

Question 73

What are the three advantages of cursors?

Question 74

Name two reasons for creating a stored procedure.

Question 75

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