MCQ- Unit wise

RDBMS Questions:

Unit -5

**Relationship between SQL & PL/SQL**:

   - Question: What is the relationship between SQL and PL/SQL?

     - A) SQL is a subset of PL/SQL.

     - B) PL/SQL is an extension of SQL.

     - C) SQL and PL/SQL are completely independent languages.

     - D) PL/SQL cannot be used without SQL.

2. **Advantages of PL/SQL**:

   - Question: Which of the following is an advantage of using PL/SQL?

     - A) Reduced code complexity

     - B) Faster execution compared to SQL

     - C) Limited support for procedural constructs

     - D) Inability to integrate with other programming languages

3. **Arithmetic & Expressions in PL/SQL**:

   - Question: Which operator is used for exponentiation in PL/SQL?

     - A) ^

     - B) **

     - C) %

     - D) &

4. **Loops and Conditional Statements in PL/SQL**:

   - Question: Which loop statement in PL/SQL is used for iterating over a range of values?

     - A) FOR loop

     - B) WHILE loop

     - C) LOOP statement

     - D) REPEAT loop

5. **Exceptions Handling**:

   - Question: What is the purpose of the EXCEPTION block in PL/SQL?

     - A) To handle syntax errors

     - B) To catch and handle runtime errors

     - C) To define custom data types

     - D) To execute code unconditionally

6. **Cursor Management**:

   - Question: What is the primary purpose of a cursor in PL/SQL?

     - A) To define a variable

     - B) To manage connections to the database

     - C) To process result sets returned by SELECT queries

     - D) To execute DDL statements

7. **Triggers**:

   - Question: In PL/SQL, triggers are automatically executed in response to which events?

     - A) SELECT statements

     - B) UPDATE, INSERT, and DELETE operations

     - C) COMMIT and ROLLBACK statements

     - D) DDL statements

8. **Functions & Procedures**:

   - Question: What is the key difference between a function and a procedure in PL/SQL?

     - A) Functions can return multiple values, whereas procedures cannot.

     - B) Functions cannot accept parameters, whereas procedures can.

     - C) Functions can be called from SQL queries, whereas procedures cannot.

     - D) Functions return a value, whereas procedures do not necessarily return a value.

**SQL & PL/SQL Basics** 

6. Which of the following statements is true about PL/SQL variables?

   - A) They cannot hold numeric values.

   - B) They are only used for storing strings.

   - C) They are defined using the VAR keyword.

   - D) They must be declared before use.

7. What is the primary purpose of a stored procedure in PL/SQL?

   - A) To define database structures

   - B) To manage user sessions

   - C) To encapsulate a sequence of SQL statements

   - D) To execute DDL statements

8. Which keyword is used to declare a variable in PL/SQL?

   - A) VAR

   - B) DECLARE

   - C) VARIABLE

   - D) LET

9. What is the output of the following PL/SQL code snippet?

   ```

   DECLARE

       num1 INTEGER := 10;

       num2 INTEGER := 5;

       result INTEGER;

   BEGIN

       result := num1 + num2;

       DBMS_OUTPUT.PUT_LINE('Result: ' || result);

   END;

   ```

   - A) Result: 15

   - B) Result: 105

   - C) Result: 5

   - D) Error: variable not initialized

10. Which of the following is NOT a valid PL/SQL block structure?

    - A) DECLARE - BEGIN - EXCEPTION - END

    - B) DECLARE - BEGIN - END

    - C) DECLARE - EXCEPTION - BEGIN - END

    - D) BEGIN - EXCEPTION - END

**Advanced PL/SQL Concepts** (Continued)

6. What is the purpose of the COMMIT statement in PL/SQL?

   - A) To undo changes made by DML statements

   - B) To save changes made by DML statements permanently

   - C) To execute DDL statements

   - D) To roll back transactions

7. Which PL/SQL construct is used to dynamically execute SQL statements?

   - A) CURSOR

   - B) FUNCTION

   - C) EXECUTE IMMEDIATE

   - D) TRIGGER

8. What is the purpose of the RETURNING clause in an INSERT statement?

   - A) To specify the columns to be inserted

   - B) To return the number of rows affected by the insert

   - C) To return values generated by sequences or default expressions

   - D) To rollback changes made by the insert

9. Which of the following is NOT a valid PL/SQL trigger timing point?

   - A) BEFORE

   - B) AFTER

   - C) INSTEAD OF

   - D) BETWEEN

10. In PL/SQL, which construct is used to temporarily store and manipulate subsets of data?

    - A) CURSOR

    - B) TRIGGER

    - C) VIEW

    - D) COLLECTION

** PL/SQL Programming Constructs** (Continued)

6. Which of the following is NOT a valid PL/SQL loop construct?

   - A) FOR loop

   - B) WHILE loop

   - C) REPEAT loop

   - D) LOOP statement

7. In PL/SQL, how is a record declared?

   - A) Using the RECORD keyword

   - B) Using the DECLARE keyword

   - C) Using the ROWTYPE attribute

   - D) Using the CURSOR keyword

8. What is the purpose of the CASE statement in PL/SQL?

   - A) To handle exceptions

   - B) To declare variables

   - C) To control the flow of execution based on multiple conditions

   - D) To define triggers

9. Which of the following is NOT a valid PL/SQL exception handler?

   - A) WHEN OTHERS THEN

   - B) WHEN ZERO_DIVIDE THEN

   - C) WHEN NO_DATA_FOUND THEN

   - D) WHEN VALUE_ERROR THEN

10. In PL/SQL, what is the maximum number of nested blocks allowed?

    - A) 5

    - B) 10

    - C) 255

    - D) Unlimited

**Advanced PL/SQL Topics** (Continued)

6. What is the primary purpose of using autonomous transactions in PL/SQL?

   - A) To execute DDL statements

   - B) To define triggers

   - C) To manage user sessions

   - D) To maintain data consistency within a transaction

7. Which pragma is used to associate an exception code with a user-defined exception name?

   - A) EXCEPTION_INIT

   - B) PRAGMA_EXCEPTION

   - C) DECLARE_EXCEPTION

   - D) EXCEPTION_HANDLE

8. What is the purpose of the DBMS_OUTPUT.PUT_LINE procedure in PL/SQL?

   - A) To insert a new line into a table

   - B) To display output in the console

   - C) To execute SQL statements

   - D) To define triggers

9. In PL/SQL, what is the primary purpose of using bulk binds?

   - A) To improve performance by reducing context switches

   - B) To execute DML statements

   - C) To handle exceptions

   - D) To define triggers

10. Which of the following is a valid use case for using the PRAGMA RESTRICT_REFERENCES pragma in PL/SQL?

    - A) To declare a variable

    - B) To define a cursor

    - C) To enforce restrictions on a function's side effects

    - D) To create a trigger

Answers:

**SQL & PL/SQL Basics**

1. B) Structured Query Language

2. B) A extension of SQL with procedural features

3. B) To store data temporarily

4. A) Better performance

5. C) It is used for defining database structures

6. D) They must be declared before use.

7. C) To encapsulate a sequence of SQL statements

8. B) To manage user sessions

9. A) Result: 15

10. D) BEGIN - EXCEPTION - END

**Advanced PL/SQL Concepts**

1. B) To save changes made by DML statements permanently

2. C) EXECUTE IMMEDIATE

3. C) To return values generated by sequences or default expressions

4. D) BETWEEN

5. A) CURSOR

6. D) TRIGGER

7. C) INSTEAD OF

8. A) FOR loop

9. C) Using the ROWTYPE attribute

10. C) To control the flow of execution based on multiple conditions

**PL/SQL Programming Constructs**

1. D) LOOP statement

2. C) Using the ROWTYPE attribute

3. C) To control the flow of execution based on multiple conditions

4. D) WHEN VALUE_ERROR THEN

5. C) 255

6. A) To execute DDL statements

7. A) EXCEPTION_INIT

8. B) To display output in the console

9. A) To improve performance by reducing context switches

10. C) To enforce restrictions on a function's side effects

**Advanced PL/SQL Topics**

1. D) To maintain data consistency within a transaction

2. A) EXCEPTION_INIT

3. B) To display output in the console

4. A) To improve performance by reducing context switches

5. C) To enforce restrictions on a function's side effects

PL/SQL

 PL/SQL is a block structured language that can have multiple blocks in it.

PL/SQL language such as conditional statements, loops, arrays, string, exceptions, collections, records, triggers, functions, procedures, cursors etc

SQL stands for Structured Query Language i.e. used to perform operations on the records stored in database such as inserting records, updating records, deleting records, creating, modifying and dropping tables, views etc.

What is PL/SQL

PL/SQL is a block structured language. The programs of PL/SQL are logical blocks that can contain any number of nested sub-blocks. Pl/SQL stands for "Procedural Language extension of SQL" that is used in Oracle. PL/SQL is integrated with Oracle database (since version 7). The functionalities of PL/SQL usually extended after each release of Oracle database. Although PL/SQL is closely integrated with SQL language, yet it adds some programming constraints that are not available in SQL.

PL/SQL Functionalities

PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers. It can support Array and handle exceptions (runtime errors). After the implementation of version 8 of Oracle database have included features associated with object orientation. You can create PL/SQL units like procedures, functions, packages, types and triggers, etc. which are stored in the database for reuse by applications.

With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process the data.

The PL/SQL is known for its combination of data manipulating power of SQL with data processing power of procedural languages. It inherits the robustness, security, and portability of the Oracle Database.

PL/SQL is not case sensitive so you are free to use lower case letters or upper case letters except within string and character literals. A line of PL/SQL text contains groups of characters known as lexical units. It can be classified as follows:

Delimeters

Identifiers

Literals

Comments

Syllabus: Topics

1. **Relationship between SQL & PL/SQL**: 

   - SQL (Structured Query Language) is a language used to manage and manipulate relational databases, performing tasks such as querying, updating, and deleting data.

   - PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL that includes procedural features, such as loops, conditional statements, and exception handling. PL/SQL allows for the creation of stored procedures, functions, triggers, and more, enhancing the capabilities of SQL by enabling more complex logic and processing within the database.

2. **Advantages of PL/SQL**:

   - Provides procedural constructs like loops, conditional statements, and exception handling for more complex logic.

   - Enhances performance by reducing the need for multiple round-trips between the database and application, as logic can be executed within the database itself.

   - Improves code reusability and maintainability through the use of stored procedures and functions.

   - Increases security by encapsulating sensitive logic within the database and controlling access through permissions.

   - Facilitates easier integration with other programming languages and systems.

3. **Arithmetic & Expressions in PL/SQL**:

   - PL/SQL supports arithmetic operations such as addition, subtraction, multiplication, and division using standard operators (+, -, *, /).

   - Expressions can be composed using variables, literals, functions, and operators to perform calculations and manipulate data.

4. **Loops and Conditional Statements in PL/SQL**:

   - PL/SQL provides several types of loops, including FOR loops, WHILE loops, and LOOP statements, allowing for iterative processing of data.

   - Conditional statements like IF-THEN-ELSE and CASE statements enable branching logic based on conditions, allowing different paths of execution depending on the evaluation of expressions.

5. **Exceptions Handling**:

   - PL/SQL allows for the handling of errors and exceptions using the EXCEPTION block, which can catch specific exceptions or handle general errors.

   - Exception handling mechanisms include raising exceptions, handling predefined exceptions, and defining custom exceptions to handle specific error conditions gracefully.

6. **Cursor Management**:

   - Cursors in PL/SQL are used to process result sets returned by SELECT queries.

   - Cursors can be implicitly or explicitly declared and manipulated to fetch rows, iterate over result sets, and perform operations on retrieved data.

7. **Triggers**:

   - Triggers in PL/SQL are special types of stored procedures that are automatically executed in response to certain events, such as INSERT, UPDATE, or DELETE operations on a table.

   - Triggers can be used to enforce data integrity, implement business rules, or audit changes to the database.

8. **Functions & Procedures**:

   - Functions and procedures in PL/SQL are reusable blocks of code that encapsulate logic to perform specific tasks.

   - Functions return a single value and can be used in SQL queries or other PL/SQL code.

   - Procedures do not return a value directly but can modify data, perform operations, or call other procedures/functions.

   - Both functions and procedures can have parameters to accept input values and can be stored in the database for reuse.

To create a simple PL/SQL program that demonstrates various concepts such as arithmetic operations, loops, conditional statements, exception handling, cursor management, triggers, functions, and procedures. Here's a program that calculates the factorial of a number:

```sql

-- Create a function to calculate factorial

CREATE OR REPLACE FUNCTION factorial(n IN NUMBER) RETURN NUMBER IS

    result NUMBER := 1;

BEGIN

    -- Check if n is negative, raise an exception if it is

    IF n < 0 THEN

        RAISE_APPLICATION_ERROR(-20001, 'Factorial of negative number is undefined');

    END IF;

    

    -- Iterate from 1 to n and calculate factorial

    FOR i IN 1..n LOOP

        result := result * i;

    END LOOP;

    

    -- Return the factorial

    RETURN result;

END factorial;

/

-- Test the factorial function

DECLARE

    num INTEGER := 5;

    fact_result INTEGER;

BEGIN

    -- Calculate factorial of num using the factorial function

    fact_result := factorial(num);

    

    -- Display the result

    DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is ' || fact_result);

EXCEPTION

    -- Catch any exceptions raised by the factorial function

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

END;

/

```

Explanation:

1. **Function Definition (factorial):**

   - `CREATE OR REPLACE FUNCTION factorial(n IN NUMBER) RETURN NUMBER IS`: Defines a function named `factorial` that takes an input parameter `n` of type `NUMBER` and returns a `NUMBER`.

2. **Exception Handling:**

   - `IF n < 0 THEN ...`: Checks if the input number `n` is negative. If it is, raises a custom exception using `RAISE_APPLICATION_ERROR`.

3. **Loop (FOR loop):**

   - `FOR i IN 1..n LOOP ...`: Iterates from 1 to `n` and calculates the factorial by multiplying `result` with each value of `i`.

4. **Return Statement:**

   - `RETURN result;`: Returns the calculated factorial value.

5. **Test Block (DECLARE - BEGIN - END):**

   - `DECLARE ... BEGIN ... END;`: Declares variables, performs calculations, and displays the result.

   - `fact_result := factorial(num);`: Calls the `factorial` function with a test number (`num`) and assigns the result to `fact_result`.

   - `DBMS_OUTPUT.PUT_LINE(...)`: Displays the factorial result using `DBMS_OUTPUT.PUT_LINE`.

   

6. **Exception Handling in Test Block:**

   - `WHEN OTHERS THEN ...`: Catches any exceptions that occur during the execution of the test block and displays the error message using `SQLERRM`.

This program demonstrates the use of functions, loops, conditional statements, exception handling, and outputting results in PL/SQL.