250+ TOP MCQs on Functions and Procedures and Answers

Database Multiple Choice Questions on “Functions and Procedures”.

1.

Create function dept count(dept_name varchar(20))
begin
declare d count integer;
select count(*) into d count
from instructor
where instructor.dept_name= dept_name
return d count;
end

Find the error in the the above statement.
a) Return type missing
b) Dept_name is mismatched
c) Reference relation is not mentioned
d) All of the mentioned

Answer: a
Clarification: Return integer should be given after create function for this particular function.

2. For the function created in Question 1, which of the following is a proper select statement ?
a)

SELECT dept name, budget
FROM instructor
WHERE dept COUNT() > 12;

b)

SELECT dept name, budget
FROM instructor
WHERE dept COUNT(dept name) > 12;

c)

SELECT dept name, budget
WHERE dept COUNT(dept name) > 12;

d)

SELECT dept name, budget
FROM instructor
WHERE dept COUNT(budget) > 12;

Answer: b
Clarification: The count of the dept_name must be checked for the displaying from instructor relation.

3. Which of the following is used to input the entry and give the result in a variable in a procedure?
a) Put and get
b) Get and put
c) Out and In
d) In and out

Answer: d
Clarification: Create procedure dept count proc(in dept name varchar(20), out d count integer). Here in and out refers to input and result of procedure.

4.

Create procedure dept_count proc(in dept name varchar(20),
out d count integer)
begin
select count(*) into d count
from instructor
where instructor.dept name= dept count proc.dept name
end

Which of the following is used to call the procedure given above ?
a)

Declare d_count integer;

b)

   Declare d_count integer;
   call dept_count proc(’Physics’, d_count);

c)

   Declare d_count integer;
   call dept_count proc(’Physics’);

d)

   Declare d_count; 
   call dept_count proc(’Physics’, d_count);

Answer: b
Clarification: Here the ‘Physics’ is in variable and d_count is out variable.

5. The format for compound statement is
a) Begin ……. end
b) Begin atomic……. end
c) Begin ……. repeat
d) Both Begin ……. end and Begin atomic……. end

Answer: d
Clarification: A compound statement is of the form begin . . . end, and it may contain multiple SQL statements between the begin and the end.A compound statement of the form begin atomic . . . end ensures that all the statements contained within it are executed as a single transaction.

6.

Repeat
sequence of statements;
__________________
end repeat

Fill in the correct option :
a) While Condition
b) Until variable
c) Until boolean expression
d) Until 0

Answer: c
Clarification: None.

7. Which of the following is the correct format for if statement?
a)

If boolean expression
then statement or compound statement
elseif boolean expression
then statement or compound statement
else statement or compound statement
end if

b)

If boolean expression
then statement or compound statement
elsif boolean expression
then statement or compound statement
else statement or compound statement
end if

c)

If boolean expression
then statement or compound statement
elif boolean expression
then statement or compound statement
else statement or compound statement
end if

d)

If boolean expression
then statement or compound statement
else 
 statement or compound statement
else statement or compound statement
end if

Answer: a
Clarification: The conditional statements supported by SQL include if-then-else statements by using this syntax. elif and elsif are not allowed.

8. A stored procedure in SQL is a___________
a) Block of functions
b) Group of Transact-SQL statements compiled into a single execution plan.
c) Group of distinct SQL statements.
d) None of the mentioned

Answer: b
Clarification: If it an atomic statement then the statements are in single transaction.

9. Temporary stored procedures are stored in _________ database.
a) Master
b) Model
c) User specific
d) Tempdb

Answer: d
Clarification: None.

10. Declare out of classroom seats condition

DECLARE exit handler FOR OUT OF classroom seats
BEGIN
SEQUENCE OF statements
END

The above statements are used for
a) Calling procedures
b) Handling Exception
c) Handling procedures
d) All of the mentioned

Answer: b
Clarification: The SQL procedural language also supports the signaling of exception conditions, and declaring of handlers that can handle the exception, as in this code.