CS6302 DBMS-FUNCTIONS AND PROCEDURES

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
1. Return type missing
2. Dept_name is mismatched
3. Reference relation is not mentioned
4. All of the mentioned
For the function created in Question 1 ,which of the following is a proper select statement ?
1. SELECT dept name, budget
  FROM instructor
  WHERE dept COUNT() > 12;
2. SELECT dept name, budget
  FROM instructor
  WHERE dept COUNT(dept name) > 12;
3. SELECT dept name, budget
  WHERE dept COUNT(dept name) > 12;
4. SELECT dept name, budget
  FROM instructor
  WHERE dept COUNT(budget) > 12;
Which of he following is used to input the entry and give the result in a variable in a procedure ?
1. Put and get
2. Get and put
3. Out and In
4. In and out
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
1. Declare d_count integer;
2. Declare d_count integer;
  call dept_count proc(’Physics’, d_count);
3. Declare d_count integer;
  call dept_count proc(’Physics’);
4. Declare d_count;
  call dept_count proc(’Physics’, d_count);
The format for compound statement is
1. Begin ……. end
2. Begin atomic……. end
3. Begin ……. repeat
4. Both Begin ……. end and Begin atomic……. end
Repeat
sequence of statements;
__________________
end repeat
1. While Condition
2. Until variable
3. Until boolean expression
4. Until 0
Which of the following is the correct format for if statement ?
1. If boolean expression
  then statement or compound statement
  elseif boolean expression
  then statement or compound statement
  else statement or compound statement
  end if
2. If boolean expression
  then statement or compound statement
  elsif boolean expression
  then statement or compound statement
  else statement or compound statement
  end if
3. If boolean expression
  then statement or compound statement
  elif boolean expression
  then statement or compound statement
  else statement or compound statement
  end if
4. If boolean expression
  then statement or compound statement
  else
  statement or compound statement
  else statement or compound statement
  end if
A stored procedure in SQL is a___________
1. Block of functions
2. Group of Transact-SQL statements compiled into a single execution plan.
3. Group of distinct SQL statements.
4. None of the mentioned
Temporary stored procedures are stored in _________ database.
1. Master
2. Model
3. User specific
4. Tempdb
DECLARE exit handler FOR OUT OF classroom seats
BEGIN
SEQUENCE OF statements
END
1. Calling procedures
2. Handling Exception
3. Handling procedures
4. All of the mentioned

CS6302 DBMS-FUNCTIONS AND PROCEDURES

Quiz