Chapter 8: SQL Functions and Procedures
Cengage Learning Testing, Powered by Cognero
Page 1
1. In procedural languages, you can accomplish many tasks using a single command.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
228
2. When you need to complete tasks that are beyond the capabilities of SQL, you need to use a procedural language.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
3. PL/SQL, which was developed by Oracle as an extension of SQL, is an example of a procedural language.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
4. You can embed SQL commands in Access programs similarly to the way you can in PL/SQL and T-SQL.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
228
5. You use aggregate functions to perform calculations based on groups of records.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
6. The UPPER function allows you to manipulate numeric data.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
229
7. You cannot use functions in WHERE clauses.
a.
True
b.
False
Chapter 8: SQL Functions and Procedures
Cengage Learning Testing, Powered by Cognero
Page 2
ANSWER:
False
POINTS:
1
REFERENCES:
229
8. A function can have more than one argument.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
9. To truncate (remove) everything to the right of the decimal point, use the TRUNCATE function.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
231
10. If a price is 24.95, the function ROUND(PRICE,0) would result in 24.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
231
11. The ADD_MONTHS function has one argument.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
231
12. Oracle, SQL Server, and Access all use the ADD_MONTHS function to add the number of months to a date.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
231
13. To add a specific number of days to a date, you must use a function.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
232
Cengage Learning Testing, Powered by Cognero
Page 3
14. In Access, use the & symbol to concatenate columns.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
15. In SQL Server, use the & symbol to concatenate columns.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
234
16. A client is a computer that is connected to a network and has access through the server to the database.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
17. When you anticipate running a particular query often, you can improve overall performance by saving the query in a
file called a routine.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
235
18. To create a stored procedure, use the CREATE PROCEDURE command.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
19. PL/SQL commands have a certain format and cannot include any blank lines in the code.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
236
20. In PL/SQL, procedural code appears between the BEGIN and END commands.
a.
True
b.
False
Chapter 8: SQL Functions and Procedures
Cengage Learning Testing, Powered by Cognero
Page 4
ANSWER:
POINTS:
REFERENCES:
21. DBMS_OUTPUT is a package that contains multiple procedures, including PUT_LINE.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
22. To handle processing of errors, include an ABORT clause in a stored procedure.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
238
23. You can use a stored procedure to update data but not to delete data.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
240
24. PL/SQL can process only one record at a time.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
25. A trigger is a pointer to a row in the collection of rows retrieved by an SQL command.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
242
26. SQL Server uses an extended version of SQL called T-SQL or Transact-SQL.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
Cengage Learning Testing, Powered by Cognero
Page 5
27. In T-SQL, all arguments start with the ampersand (&) symbol.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
249
28. In T-SQL, arguments should have the same data type and length as the particular column in a table that they represent.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
29. To call a procedure in SQL Server, use the EXEC command.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
30. In Microsoft Access, you must store an SQL command in a string variable.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
31. Microsoft Access supports triggers.
a.
True
b.
False
ANSWER:
False
POINTS:
1
REFERENCES:
258
32. In SQL Server, the INSERTED table is a temporary system table that contains a copy of the values that the last SQL
command inserted.
a.
True
b.
False
ANSWER:
POINTS:
REFERENCES:
33. In Oracle and SQL Server, the ____ function displays a value in uppercase letters.
a.
CAPITAL
b.
UPPER
Chapter 8: SQL Functions and Procedures
Cengage Learning Testing, Powered by Cognero
Page 6
c.
LARGE
d.
UCASE
ANSWER:
b
POINTS:
1
REFERENCES:
229
34. In Oracle and SQL Server, the ____ function displays a value in lowercase letters.
a.
REGULAR
b.
LOWER
c.
SMALL
d.
LCASE
ANSWER:
b
POINTS:
1
REFERENCES:
229
35. In Microsoft Access, the ____ function displays a value in lowercase letters.
a.
REGULAR
b.
LOWER
c.
SMALL
d.
LCASE
ANSWER:
d
POINTS:
1
REFERENCES:
230
36. In Microsoft Access, the ____ function displays a value in uppercase letters.
a.
CAPITAL
b.
LARGE
c.
UPPER
d.
UCASE
ANSWER:
d
POINTS:
1
REFERENCES:
230
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
ANSWER:
c
POINTS:
1
REFERENCES:
230/231
38. In Oracle and SQL Server, use the ____ function to truncate everything to the right of the decimal point.
a.
TRUNCATE
Chapter 8: SQL Functions and Procedures
Cengage Learning Testing, Powered by Cognero
Page 7
b.
FLOOR
c.
REMOVE
d.
TRIM
ANSWER:
b
POINTS:
1
REFERENCES:
231
39. 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
ANSWER:
c
POINTS:
1
REFERENCES:
231
40. 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
ANSWER:
b
POINTS:
1
REFERENCES:
231
41. In Oracle, you can use the ____ function to obtain today’s date.
a.
TODAYS_DATE
b.
CURRENT_DATE
c.
NOWDATE
d.
SYSDATE
ANSWER:
d
POINTS:
1
REFERENCES:
233
42. In Microsoft Access, you can use the ____ function to obtain today’s date.
a.
TODAYS_DATE
b.
CURRENT_DATE
c.
DATE()
d.
SYSDATE
ANSWER:
c
POINTS:
1
REFERENCES:
233
43. In SQL Server, you can use the ____ function to obtain today’s date.
a.
TODAYS_DATE
Chapter 8: SQL Functions and Procedures
Cengage Learning Testing, Powered by Cognero
Page 8
b.
GETDATE()
c.
CURRENTDATE()
d.
SYSDATE
ANSWER:
b
POINTS:
1
REFERENCES:
233
44. 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
ANSWER:
d
POINTS:
1
REFERENCES:
234
45. To create a stored procedure in Oracle, use the ____ command.
a.
CREATE ROUTINE
b.
CREATE SCRIPT
c.
CREATE PROCEDURE
d.
CREATE EXECUTABLE
ANSWER:
c
POINTS:
1
REFERENCES:
234
46. 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
ANSWER:
b
POINTS:
1
REFERENCES:
236
47. 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
ANSWER:
d
POINTS:
1
REFERENCES:
236
Cengage Learning Testing, Powered by Cognero
Page 9
48. 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
ANSWER:
a
POINTS:
1
REFERENCES:
242
49. 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
ANSWER:
b
POINTS:
1
REFERENCES:
242
50. To call a procedure in SQL Server, use the ____ command.
a.
CALL
b.
RUN
c.
DO
d.
EXEC
ANSWER:
d
POINTS:
1
REFERENCES:
249
51. In Microsoft Visual Basic, use the ____ statement to create a string variable.
a.
Dim
b.
Decl
c.
Var
d.
Declare
ANSWER:
a
POINTS:
1
REFERENCES:
253
52. A(n) ____ is a procedure that is executed automatically in response to an associated database operation.
a.
cursor
b.
trigger
c.
waypoint
d.
fetchpoint
ANSWER:
b
POINTS:
1
REFERENCES:
257
Cengage Learning Testing, Powered by Cognero
Page 10
53. SQL is a powerful ____________________ language in which you communicate tasks to the computer using simple
commands.
ANSWER:
nonprocedural
non-procedural
POINTS:
1
REFERENCES:
227
54. A(n) ____________________ language is one in which you must give the computer the step-by-step process for
accomplishing a task.
ANSWER:
procedural
POINTS:
1
REFERENCES:
228
55. Oracle uses ____________________ as an extension of SQL to accomplish procedural tasks.
ANSWER:
PL/SQL
POINTS:
1
REFERENCES:
228
56. ____________________ is the procedural language that SQL Server uses.
ANSWER:
T-SQL
Transact-SQL
POINTS:
1
REFERENCES:
228
57. In the function UPPER(LAST_NAME), the item in parentheses (LAST_NAME) is called the
____________________ for the function.
ANSWER:
argument
POINTS:
1
REFERENCES:
229
58. The process of combining two or more character columns into a single expression is called ____________________.
ANSWER:
concatenation
POINTS:
1
REFERENCES:
233
59. To concatenate columns in Oracle, you type two ____________________ between the column names.
ANSWER:
|| (vertical lines)
vertical lines (||)
vertical lines
||
POINTS:
1
REFERENCES:
233
60. A(n) ____________________ is a computer that is connected to a network and has access through the server to the
database.
ANSWER:
client
Chapter 8: SQL Functions and Procedures
ANSWER:
DEALLOCATE
POINTS:
1
REFERENCES:
251
70. In SQL Server, the cursor property ____________________ means that the cursor is used for retrieval purposes only.
ANSWER:
READ_ONLY
POINTS:
1
REFERENCES:
251
71. In Microsoft Access, programs are written in ____________________, which does not support embedded SQL
commands directly in the code.
ANSWER:
Visual Basic
POINTS:
1
REFERENCES:
252
72. A(n) ____________________ is a procedure that is executed automatically in response to an associated database
operation, such as an INSERT, UPDATE, or DELETE command.
ANSWER:
trigger
POINTS:
1
REFERENCES:
257
73. What is the difference between a nonprocedural language and a procedural language? Give an example of each.
ANSWER:
A nonprocedural language is one in which you communicate tasks to the computer using simple
commands. A procedural language is one in which you must give the computer a step-by-step process
for accomplishing a task.
SQL is a nonprocedural language and PL/SQL (and T-SQL) are examples of procedural languages.
POINTS:
1
REFERENCES:
228
74. Name two reasons for creating a stored procedure.
ANSWER:
1. improved overall performance because users execute the compiled, optimized code
2. convenience
POINTS:
1
REFERENCES:
235
75. What are the three advantages of cursors?
ANSWER:
1. Coding in the procedure is greatly simplified.
2. In a normal PL/SQL program, the programmer must determine the most efficient way to access the
data. In a program using embedded SQL, a special component of the database management system
called the optimizer determines the best way to access the data.
3. If 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 is the cursor definition. The procedural code is
not affected.
POINTS:
1
REFERENCES:
248