PLSQL

Question 1

What does PL/SQL stand for?

Answer 1

Procedural SQL

Question 2

What is a major drawback of SQL?

Answer 2

No procedural capability

Question 3

What is one advantage of PL/SQL?

Answer 3

Supports SQL data manipulation along with conditional checking

Question 4

What are the key components of a PL/SQL block?

Answer 4

DECLARE, BEGIN, EXCEPTION, END

Question 5

Fill in the blank: PL/SQL data types are native to _______’s data dictionary.

Answer 5

Oracle

Question 6

What does %TYPE do in PL/SQL?

Answer 6

Declares a variable or constant to have the same data type as that of a previously defined variable or a column in the table or view

Question 7

What is a requirement for naming a variable in PL/SQL?

Answer 7

A variable must begin with a character and can be followed by a maximum of 29 other characters

Question 8

What is DBMS_OUTPUT?

Answer 8

A package that includes a number of procedures and functions that accumulate information in a buffer

Question 9

How do you display user messages in PL/SQL?

Answer 9

Using the PUT_LINE procedure

Question 10

What is the syntax for a simple IF statement in PL/SQL?

Answer 10

if <condition> then <statements>; end if;</statements></condition>

Question 11

What is the syntax for an ELSE IF ladder in PL/SQL?

Answer 11

if <condition1> then <statements>; elsif <condition2> then <statements>; else <statements>; end if;</statements></statements></condition2></statements></condition1>

Question 12

What is a simple loop in PL/SQL?

Answer 12

loop <statements> end loop;</statements>

Question 13

What is the purpose of the EXIT statement in a loop?

Answer 13

To terminate the loop when a certain condition is met

Question 14

What is the syntax for a WHILE loop in PL/SQL?

Answer 14

while <condition> loop <statements> end loop;</statements></condition>

Question 15

What is a stored function in PL/SQL?

Answer 15

Logically grouped PL/SQL statements that perform a specific task and return a single value

Question 16

What are the three parts of a stored function?

Answer 16

Declarative part, executable part, exception handling

Question 17

What is the purpose of the CREATE OR REPLACE FUNCTION statement?

Answer 17

To define a new stored function or replace an existing one

Question 18

What is the main difference between functions and procedures in PL/SQL?

Answer 18

Functions explicitly return a single value, while procedures can return multiple values

Question 19

What is the syntax for creating a stored procedure?

Answer 19

create or replace procedure procedure_name(parameters) is <local> begin <PL/SQL statements>; exception <PL/SQL statements to handle errors> end;</local>

Question 20

What does the term ‘BULK COLLECT’ refer to in PL/SQL?

Answer 20

A method to fetch multiple rows of data into a collection in a single operation

Question 21

True or False: Stored functions can use DDL statements.

Answer 21

False

Question 22

Fill in the blank: To execute a function, use the syntax select _______ from dual;

Answer 22

funname(parameters)

Question 23

What is the purpose of the procedure ‘proc11’?

Answer 23

To calculate the area of a circle given its radius

The formula used is area = radius * radius * pi, where pi is constant.

Question 24

What is the constant value of pi used in ‘proc11’?

Answer 24

3.14

Question 25

What is the minimum balance constant defined in the 'withdraw' procedure?

Answer 25

1000

Question 26

What happens if the new balance after withdrawal is less than the minimum balance?

Answer 26

The procedure outputs 'Cannot withdraw'.

Question 27

What are the three modes of parameter passing in stored procedures?

Answer 27

* IN * OUT * IN OUT

Question 28

What is the default mode of parameter passing?

Answer 28

IN

Question 29

What does the OUT mode of parameter passing do?

Answer 29

Returns value from called program to the calling program.

Question 30

In the OUT mode, what must the calling program pass?

Answer 30

A variable name for the OUT parameter.

Question 31

What is the purpose of the IN OUT mode?

Answer 31

To pass values from the calling program to the called program and return a value.

Question 32

What is the function of 'proc1' in the example?

Answer 32

To retrieve the name of an instructor based on their ID.

Question 33

What SQL command is used to retrieve employee details in the 'get_employee_details' procedure?

Answer 33

SELECT

Question 34

What does the function 'show_description' return if no data is found?

Answer 34

'The Course is not in the database'

Question 35

What are cursors in PL/SQL?

Answer 35

Work areas reserved by Oracle for internal processing of SQL statements.

Question 36

What is an implicit cursor?

Answer 36

A cursor managed automatically by Oracle.

Question 37

What does the attribute SQL%ISOPEN return for implicit cursors?

Answer 37

False

Question 38

What is the first step in using explicit cursors?

Answer 38

Declare a cursor that specifies the SQL statement to be processed.

Question 39

What does the cursor attribute SQL%ROWCOUNT return?

Answer 39

The number of rows affected by an insert, update, delete, or select statement.

Question 40

What is the purpose of the FOR loop in explicit cursors?

Answer 40

To iterate over each record in the cursor.

Question 41

What SQL statement is used to update the budget in the example with implicit cursors?

Answer 41

UPDATE department SET budget = 200000 WHERE building = 'Watson';

Question 42

What is the purpose of the 'Discount' procedure?

Answer 42

To apply a 5% discount to courses with more than 80 enrollments.

Question 43

What SQL clause is used to group records in the 'Discount' procedure?

Answer 43

HAVING

Question 44

What happens when the cursor 'c_group_discount' is iterated over?

Answer 44

The cost of qualifying courses is updated with a 5% discount.

Question 45

Fill in the blank: The 'withdraw' procedure uses _______ to check the existence of an account.

Answer 45

count(*)

Question 46

True or False: In the IN mode, the parameter can be assigned a value within the called program.

Answer 46

False

Question 47

What type of variable must be passed for an OUT parameter?

Answer 47

A variable name

Question 48

What must the calling function not specify for an OUT parameter?

Answer 48

Literal, constant, or expression

Question 49

What is the output when the balance is updated successfully?

Answer 49

'New balance: ' || newbal

Question 50

What command is used to commit changes in the 'withdraw' procedure?

Answer 50

COMMIT

Question 51

What is a trigger in PL/SQL?

Answer 51

A PL/SQL block associated with a table, executed automatically in response to a specific data manipulation event ## Footnote Data manipulation events include insert, update, and delete.

Question 52

What are the components of a trigger?

Answer 52

* Triggering SQL Statement * Trigger Action * Trigger Restriction

Question 53

What is a Before Trigger?

Answer 53

A trigger that is fired before the triggering SQL statement is executed.

Question 54

What is an After Trigger?

Answer 54

A trigger that is fired after the triggering SQL statement is executed.

Question 55

What is the difference between statement level and row level triggers?

Answer 55

* Statement Level: Fired for each DML operation on the table, cannot access individual record values. * Row Level: Triggered for each record inserted, updated, or deleted.

Question 56

What is a Row Level Trigger?

Answer 56

A trigger fired for each and every record which is inserted, updated, or deleted from the table.

Question 57

What does :old.column_name and :new.column_name indicate?

Answer 57

:old indicates the old value of a column, while :new indicates the new value.

Question 58

What is RAISE_APPLICATION_ERROR?

Answer 58

A procedure to issue user-defined error messages from stored subprograms.

Question 59

What is the error number range for RAISE_APPLICATION_ERROR?

Answer 59

-20000 to -20999.

Question 60

What are the disadvantages of triggers?

Answer 60

* Can execute every time a field is updated, leading to system overhead. * Difficult to view and debug compared to tables and views. * Do not receive parameters.

Question 61

What are the advantages of triggers?

Answer 61

* Event logging and storing information on table access. * Auditing. * Implicit execution compared to explicit calls for procedures.

Question 62

How do you disable a trigger in PL/SQL?

Answer 62

ALTER TRIGGER trigger_name DISABLE;

Question 63

How do you enable a trigger in PL/SQL?

Answer 63

ALTER TRIGGER trigger_name ENABLE;

Question 64

What SQL statement is used to drop a trigger?

Answer 64

DROP TRIGGER trigger_name;

Question 65

Fill in the blank: A trigger is executed __________ in response to a specific data manipulation event.

Answer 65

implicitly

Question 66

What is the purpose of the example trigger that raises an error if more than 1000 books from the same publisher have been added?

Answer 66

To enforce a business rule preventing excessive entries from a single publisher.

Question 67

What SQL statement retrieves the count of accidents grouped by location?

Answer 67

SELECT location, COUNT(*) AS num FROM accident GROUP BY location;

Question 68

When should all accidents from a location be inserted into Table1?

Answer 68

If the number of accidents exceeds 20.

Question 69

What does the INSERT_PROCEDURE do?

Answer 69

Inserts all accidents that occurred in a specified location into Table1.

Question 70

What is the purpose of the cursor in the test procedure?

Answer 70

To iterate over each driver and count accidents to determine where to store their information.

Question 71

What is the significance of the sysdate function in triggers?

Answer 71

It records the current date and time of the DML operation.

Question 72

What happens when the condition in a row level trigger is met?

Answer 72

The specified action (like raising an error) will be executed.

Question 73

What SQL command is used to fetch data from a cursor?

Answer 73

FETCH cursor_name INTO variable_name;