WorksheetsPL PGSQL
Total questions: 34
Worksheet time: 34mins
Name
Class
Date
1.
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
2.
Temporary stored procedures are stored in _________ database.
a)
Master
b)
Model
c)
User specific
d)
Temp
3.
A __________ is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data.
a)
Procedures
b)
Triggers
c)
Functions
d)
None of the mentioned
4.
Triggers are supported in
a)
Delete
b)
Update
c)
Views
d)
All of the mentioned
5.
What are the after triggers?
a)
Triggers generated after a particular operation
b)
These triggers run after an insert, update or delete on a table
c)
These triggers run after an insert, views, update or delete on a table
d)
All of the mentioned
6.
Which loop construct is used in PL/pgSQL for iterating over a range of values?
a)
WHILE loop
b)
FOR loop
c)
DO loop
d)
LOOP loop
7.
Which of the following is an PostgreSQL-supported trigger?
a)
BEFORE
b)
INSTEAD OF
c)
AFTER
d)
all of above
8.
What of the following are types of triggers?
a)
Define, Create
b)
Drop, Comment
c)
Insert, Update, Delete
d)
All of the mentioned
9.
Triggers ________ enabled or disabled
a)
Can be
b)
Cannot be
c)
Ought to be
d)
Always
10.
What is the symbol for assignment operator in PL/SQL?
a)
==
b)
=>
c)
:=
d)
_=
11.
What is the symbol for the operator used to compare equality between two variables in PL/SQL?
a)
==
b)
=
c)
=>
d)
_=
12.
What is the output of the following PL/SQL block? do $$
DECLARE
a int:= 100;
b int:= 200;
BEGIN
IF a=100 THEN
IF b<>200 THEN
Raise notice 'value is:%' ,b;
END IF;
END IF;
Raise notice 'value is:%' ,a;
END;
$$
a)
100
b)
200
c)
300
d)
400
13.
What will be the output ? CREATE OR REPLACE PROCEDURE squareNum(x INOUT int) AS $$
DECLARE
a int;
BEGIN
x := x * x;
END;
$$ LANGUAGE plpgsql;
DO $$
DECLARE
a int;
BEGIN
a := 5;
CALL squareNum(a);
RAISE NOTICE 'Result: %', a;
END;
$$;
a)
5
b)
10
c)
25
d)
50
14.
What is the difference between a PL/SQL function and a PL/SQL Procedure?
a)
There is no difference
b)
A procedure can return more than variable whereas a function can return only one variable
c)
A function can be called through an SQL SELECT statement but a procedure cannot.
d)
Both B and C
15.
THE _____ clause specifies the table name on which the trigger is to be attached.
a)
For
b)
On
c)
None
d)
In
16.
In PL/pgSQL, which of the following best describes the purpose of a cursor?
a)
To update data in a table
b)
To define a stored procedure
c)
To control the flow of execution in a loop
d)
To retrieve and process a set of rows from a query result
17.
PL/SQL function must contain a –
a)
Follow Statement
b)
GOTO statement
c)
Returns Statement
d)
Return Statement
18.
In order to remove a PL/SQL function, which statement is used?
a)
REMOVE FUNCTION
b)
DELETE FUNCTION
c)
ERASE FUNCTION
d)
DROP FUNCTION
19.
An SQL __________ refers to a program that retrieves and processes one row at a time, based on the results of the SQL statement.
a)
Cursor
b)
Function
c)
Procedure
d)
View
20.
Which of the following PL/SQL cursors is automatically created?
a)
Implicit
b)
Explicit
c)
Exaggerate
d)
Oversplit
21.
Which of the following is/are cursor/s in PL/SQL?
a)
FOUND
b)
ROWCOUNT
c)
ISOPEN
d)
All of the mentioned
22.
Which cursor statement is used to place the content of the current row into variables?
a)
fetch
b)
get
c)
open
d)
close
23.
In PostgreSQL PL/pgSQL, which of the following statements about procedures is true?
a)
Procedures cannot accept parameters.
b)
Procedures always return a value.
c)
Procedures are compiled and executed once when created.
d)
Procedures can have OUT parameters to return multiple values.
24.
Which of the following statements is/are correct?
a)
%TYPE: This declaration is used for the purpose of anchoring by providing the data type of any variable, column, or constant.
b)
%ROWTYPE: This is used for declaring a variable that has the same data type and size as that of a row in the table.
c)
None of them
d)
Both A and B
25.
Which of the following statements is/are incorrect?
a)
When a subquery returns more than one row, an implicit cursor is used.
b)
When a subquery returns more than one row, an explicit cursor is used.
c)
An implicit cursor is used for all DML operations like DECLARE, OPEN, FETCH, CLOSE.
d)
None of these
26.
What will be the output of following PL/SQL block?
DO $$
DECLARE
a INTEGER := 100;
BEGIN
IF a = 50;
RAISE NOTICE 'Value of a is 10';
ELSIF a = 75 THEN
RAISE NOTICE 'Value of a is 20';
ELSE
RAISE NOTICE 'None of the values is matching';
END IF;
RAISE NOTICE 'Exact value of a is: %', a;
END;
$$;
a)
It will print 'values of a is 10’
b)
It will print ‘values of a is 20’
c)
It will print 'values of a is 100’
d)
It has syntax error
27.
In the PL/SQL block below, how many rows will be inserted in the messages table?
DO $$
DECLARE
Start1 INTEGER := 2;
end1 INTEGER := 100;
i INTEGER;
BEGIN
FOR i IN Start1..end1 LOOP
INSERT INTO messages VALUES (i);
END LOOP;
END;
$$;
a)
0
b)
99
c)
1
d)
100
28.
In PL/pgSQL, which of the following describes the proper structure of an IF statement when dealing with multiple conditions?
a)
Using IF...ELSE statements for each condition separately.
b)
Using a single IF statement with multiple THEN clauses.
c)
Using nested IF statements within each other.
d)
Using ELSIF and ELSE clauses to handle multiple conditions in a single IF statement.
29.
In PostgreSQL, what is a "window function"? (Don't consider)
a)
A function that operates on rows and returns a single value.
b)
A function that performs calculations on table columns.
c)
A function that applies calculations across a set of table rows related to the current row.
d)
A function that executes only when called explicitly by the user.
30.
In the SQL Cursor, which attribute is TRUE when a cursor has some remaining rows to fetch, and FALSE when a cursor has no rows left to fetch?
a)
ROWCOUNT
b)
FOUND
c)
NOTFOUND
d)
ISOPEN
31.
The correct syntax to declare PL/SQL variable is –
a)
variable_name initial_value:= datatype
b)
variable_name datatype >= initial_value
c)
variable_name datatype := initial_value
d)
variable_name := initial_value
32.
Which of the following attributes of cursor is used to determine whether a cursor contains tuples after the execution of a FETCH statement.?
a)
FOUND
b)
NOTFOUND
c)
ISCLOSE
d)
ISOPEN
33.
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
34.
The format for compound statement is
a)
Begin ……. end
b)
Begin atomic……. end
c)
Begin ……. repeat
d)
Both Begin ……. end and Begin atomic……. end
100 %
