SY.Bsc.Cs Sem-3 Based on Mumbai Unversity
Advanced Database Concepts (Uint-1,2,3) Answer Question Bank Answer:- Chat Gpt answer , important answer pdf.
IMP ANSWER Pdf
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
Short answer:-
### Unit 1 - PL/SQL (3 Marks)
**1. Explain Data Types in PL/SQL:**
Data types in PL/SQL specify the kind of values a variable can store. Common data types include:
- **Scalar types:** Simple data types like `NUMBER`, `CHAR`, `VARCHAR2`, `BOOLEAN`, `DATE`, etc.
- **Composite types:** These include `RECORD`, `TABLE`, and `VARRAY`, which allow storage of multiple values.
- **LOB types:** Large object data types like `BLOB`, `CLOB`, `BFILE` for handling large data like images or text.
**2. Briefly explain Unconstrained Loop:**
An unconstrained loop is a loop without a predefined limit. The loop runs indefinitely unless explicitly exited using control statements such as `EXIT`. Example:
```sql
LOOP
-- some code
EXIT WHEN condition;
END LOOP;
```
**3. Demonstrate the usage of GOTO Statement:**
`GOTO` allows control to jump to a specific labeled part of the program. Example:
```sql
DECLARE
counter NUMBER := 1;
BEGIN
<<start>>
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
counter := counter + 1;
IF counter <= 5 THEN
GOTO start;
END IF;
END;
```
**4. Describe PL/SQL Operators:**
PL/SQL operators are symbols that perform operations on variables and values. Categories include:
- **Arithmetic operators:** `+`, `-`, `*`, `/`, `%`
- **Relational operators:** `=`, `<`, `>`, `<=`, `>=`, `<>`
- **Logical operators:** `AND`, `OR`, `NOT`
- **Concatenation operator:** `||`
**5. Write a PL/SQL program to demonstrate function:**
```sql
CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER)
RETURN NUMBER IS
BEGIN
RETURN a + b;
END;
```
This function adds two numbers and returns the result.
**6. Explain Constants in PL/SQL with examples:**
Constants are variables that are assigned a value once and cannot be changed. They are declared using the `CONSTANT` keyword. Example:
```sql
DECLARE
pi CONSTANT NUMBER := 3.14;
BEGIN
DBMS_OUTPUT.PUT_LINE('Value of PI: ' || pi);
END;
```
---
### Unit 2 - PL/SQL (3 Marks)
**1. Explain SavePoint Command with examples:**
`SAVEPOINT` marks a point in a transaction to which you can later roll back. Example:
```sql
SAVEPOINT point1;
-- some DML operations
ROLLBACK TO point1; -- rolls back to the specified savepoint
```
**2. Briefly describe the usage of %ROWTYPE:**
`%ROWTYPE` provides a record type representing a row in a table or a result set. It allows you to work with the whole row of a table. Example:
```sql
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM employees WHERE employee_id = 101;
END;
```
**3. List and describe steps involved in making an Explicit Cursor:**
- **Declare the cursor:** Define the SQL query.
- **Open the cursor:** Execute the SQL query.
- **Fetch the data:** Retrieve rows one by one.
- **Close the cursor:** Release the cursor when done.
**4. Write a short note on Exception Propagation:**
Exception propagation occurs when an exception is raised in a subprogram and passed to the calling block. If no exception handler is found, it continues propagating up the chain.
**5. What is a Record? List its features and syntax:**
A record is a composite data type that groups related fields. Features include the ability to hold different data types and reference table rows. Syntax:
```sql
TYPE employee_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER
);
```
**6. Differentiate between Varray and Index-By-Table:**
- **Varray:** Stores a fixed number of elements in sequential order. The size is predefined.
- **Index-By-Table (Associative Array):** Stores elements without size constraints, and each element has a unique key/index.
---
### Unit 3 - PL/SQL (3 Marks)
**1. Write a PL/SQL program to demonstrate Triggers:**
```sql
CREATE OR REPLACE TRIGGER emp_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting employee ID: ' || :NEW.employee_id);
END;
```
**2. Explain in brief Dirty Read:**
A dirty read occurs when a transaction reads uncommitted changes made by another transaction, leading to inconsistent data.
**3. How to solve the Lost Update Problem:**
The lost update problem can be solved using locking mechanisms (e.g., optimistic or pessimistic locking) to prevent two transactions from overwriting each other’s changes simultaneously.
**4. Write a short note on Packages:**
Packages group related PL/SQL objects (procedures, functions, variables) into a single unit. They have two parts: specification (declares public objects) and body (contains implementation).
**5. Explain View Serializability:**
View serializability ensures that the outcome of executing transactions concurrently is equivalent to some serial execution of those transactions.
**6. Briefly explain Deadlock Recovery:**
Deadlock recovery involves identifying the deadlock (when two or more transactions block each other) and resolving it by rolling back one or more transactions to break the deadlock.
Comments
Post a Comment