Database Questions and Answers – Functions and Procedures

This set of Database Multiple Choice Questions & Answers (MCQs) focuses 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
View Answer

Answer: a
Explanation: 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)

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

b)

Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!
SELECT dept name, budget
FROM instructor
WHERE dept COUNT(dept name) > 12;

c)

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

d)

advertisement
SELECT dept name, budget
FROM instructor
WHERE dept COUNT(budget) > 12;
View Answer
Answer: b
Explanation: 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
View Answer

Answer: d
Explanation: 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);
View Answer
Answer: b
Explanation: 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
View Answer

Answer: d
Explanation: 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
View Answer

Answer: c
Explanation: 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
View Answer
Answer: a
Explanation: 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
View Answer

Answer: b
Explanation: 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
View Answer

Answer: d
Explanation: 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
View Answer

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

Sanfoundry Global Education & Learning Series – Database Management System.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.