Q1. List out Codds rule? Explain any four in detail.

ans. Codd's Rules are a set of 13 guidelines proposed by Dr. E.F. Codd in 1970 to
define what constitutes a relational database system. These rules aim to ensure
that database systems are truly relational and maintain data integrity,
consistency, and simplicity in handling data. Below is a list of Codd's 13 rules,
followed by a detailed explanation of four of them.

### Codd's 13 Rules:

1. **The Information Rule**
2. **The Guaranteed Access Rule**
3. **The Systematic Treatment of Null Values**
4. **The Dynamic Online Catalog Rule**
5. **The Comprehensive Data Sublanguage Rule**
6. **The View Updating Rule**
7. **The High-Level Insert, Update, and Delete Rule**
8. **The Physical Data Independence Rule**
9. **The Logical Data Independence Rule**
10. **The Integrity Independence Rule**
11. **The Distribution Independence Rule**
12. **The Non-Subversion Rule**
13. **The Rule of Relational Completeness**

### Explanation of Four Rules in Detail:

#### 1. **The Information Rule**

- **Definition**: All information in a relational database should be represented
in the form of values in tables. Specifically, every piece of data should be stored
as a value in a cell, with rows and columns making up the table structure.
- **Explanation**: This rule emphasizes that the entire database should consist
of data stored in tabular form, with each cell containing a single value. It
prevents databases from using complex formats or structures like files or arrays to
store data. It ensures that users and applications can query and manipulate data in
a consistent, predictable way.

#### 2. **The Guaranteed Access Rule**

- **Definition**: Each data element (value) in a relational database should be
accessible by using a combination of the table name, primary key, and column name.
- **Explanation**: This rule ensures that every piece of data stored in the
database is retrievable via some unique method. For example, if a table contains
customer data, you should be able to uniquely identify a customer’s data by their
ID (primary key) and the attribute (column) you're interested in, such as the
customer's name or address. This rule guarantees that no matter how complex the
database is, the data is always accessible in a standardized and logical way.

#### 3. **The Systematic Treatment of Null Values**

- **Definition**: Null values (representing missing or unknown data) should be
treated in a consistent and systematic manner.
- **Explanation**: Nulls should be allowed in a relational database and should
be distinct from other values like zero or an empty string. This rule ensures that
databases can handle situations where data is not available or is unknown,
preventing ambiguity. For example, a null value for "Phone number" could indicate
that the phone number is not provided, whereas an empty string could indicate that
the phone number is explicitly empty, which are different concepts. This rule
provides consistency in how null values are represented and manipulated.

#### 4. **The Logical Data Independence Rule**

- **Definition**: The logical schema (or logical view) of the database should be
independent of the physical storage. Changes in the physical storage of data should
not affect how users interact with the data.
- **Explanation**: This rule ensures that changes in the underlying storage
(e.g., switching from one type of hard drive to another or optimizing how data is
stored) do not affect the logical structure or the way users interact with the
data. For example, if the database administrator changes the way data is indexed or
partitioned, the application or end-users querying the data should not notice any
difference in how they query or retrieve the data. This level of abstraction is
crucial for database scalability and flexibility.

These four rules, along with the others, help ensure that relational database
systems maintain their integrity, flexibility, and simplicity in managing data.

Q2. Discuss PL/SQL block structure.

ans. PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural

extension of SQL. It allows for more complex programming constructs like loops,
conditionals, and exception handling, while still maintaining SQL's power to
interact with databases. A PL/SQL block is the basic unit of code in PL/SQL. It is
a structured block of code that can include SQL queries, procedural logic, and
exception handling.

### PL/SQL Block Structure

A PL/SQL block has a well-defined structure that consists of **four main
components**:
1. **Declaration Section (optional)**
2. **Execution Section (mandatory)**
3. **Exception Handling Section (optional)**

### 1. **Declaration Section (Optional)**

- This is the section where you declare any variables, constants, cursors, and
types that you will use in the block. It is optional because not all PL/SQL blocks
require declarations.
- **Syntax**:
```plsql
DECLARE
-- Variable or constant declarations
variable_name data_type [DEFAULT value];
-- Cursor or other declarations
```
- **Example**:
```plsql
DECLARE
v_employee_name VARCHAR2(50);
v_employee_id NUMBER;
```
- This section can contain:
- **Variables**: Used to store temporary data for the block.
- **Constants**: Similar to variables but their values cannot be changed once
set.
- **Cursors**: Used to retrieve and manipulate result sets.
- **Types**: User-defined data types.

### 2. **Execution Section (Mandatory)**

- The execution section contains the executable statements. This section is
mandatory for every PL/SQL block, and it’s where the main logic is executed. This
is where SQL queries, control-of-flow statements (loops, conditionals), and calls
to procedures or functions are written.
- **Syntax**:
```plsql
BEGIN
-- Executable statements
SQL statements, PL/SQL logic, etc.;
END;
```
- **Example**:
```plsql
BEGIN
SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id =
101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
```
- In this section:
- **SQL statements** can be SELECT, INSERT, UPDATE, DELETE, etc.
- **PL/SQL control structures** (like IF-THEN-ELSE, FOR loops, etc.) can be
used for program logic.
- **Built-in packages** like `DBMS_OUTPUT` can be used for displaying messages
or logging.

### 3. **Exception Handling Section (Optional)**

- The exception handling section is used to handle any errors or exceptions that
may occur during the execution of the block. If no errors occur, the exception
section is skipped.
- **Syntax**:
```plsql
EXCEPTION
WHEN exception_name1 THEN
-- Handle the first type of exception
WHEN exception_name2 THEN
-- Handle the second type of exception
WHEN OTHERS THEN
-- Catch all other exceptions
```
- **Example**:
```plsql
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found for the given employee ID.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Query returned too many rows.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
```
- Common exceptions:
- **NO_DATA_FOUND**: Raised when a SELECT INTO statement returns no rows.
- **TOO_MANY_ROWS**: Raised when a SELECT INTO statement returns more than one
row.
- **OTHERS**: A generic handler that catches all exceptions not specifically
handled by the earlier `WHEN` clauses.

### 4. **PL/SQL Anonymous Block (Complete Example)**

An anonymous PL/SQL block is one that doesn't have a name and can be executed
immediately. It is typically used for simple tasks or quick operations.
```plsql
 DECLARE
v_employee_name VARCHAR2(50);
BEGIN
SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id =
101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
```

### Summary of PL/SQL Block Structure:

1. **DECLARE** (Optional): Declare variables, constants, cursors, etc.
2. **BEGIN** (Mandatory): Write executable code, such as SQL queries and procedural
logic.
3. **EXCEPTION** (Optional): Handle any runtime exceptions or errors.

### Benefits of PL/SQL Block Structure:

- **Modularity**: The block structure allows for modular coding. You can group
related SQL queries and procedural logic together.
- **Error Handling**: The exception handling section ensures that errors can be
caught and handled gracefully without crashing the program.
- **Efficiency**: PL/SQL allows combining multiple SQL operations and procedural
constructs in a single block, which can be more efficient than executing individual
SQL statements.

By using this structured approach, PL/SQL provides a powerful environment for

handling complex data manipulation and business logic directly within the Oracle
Database.

Q3. What is cursor? Explain types of cursor with example.

ans. A **cursor** in PL/SQL is a database object used to retrieve, manipulate, and

navigate through the result set of a SQL query. It provides a mechanism for
processing individual rows returned by a query. Cursors are essential for dealing
with multi-row queries, especially when working with large amounts of data, and
allow you to perform operations such as updating or deleting individual rows.

### Types of Cursors:

There are **two main types of cursors** in PL/SQL:

1. **Implicit Cursors**
2. **Explicit Cursors**

Let’s look at each type in detail:

---

### 1. **Implicit Cursor**

An **implicit cursor** is automatically created by Oracle when a SQL query is

executed that returns a single result (or no result at all). These cursors are used
for executing SQL statements like `SELECT INTO`, `INSERT`, `UPDATE`, or `DELETE`.
The database automatically handles the cursor's opening, fetching, and closing for
you.

#### Key Points:

- Implicit cursors are created by Oracle automatically for SQL statements that
return a single row or no rows.
- You don't need to explicitly declare or manage them.
- The cursor is automatically opened when the SQL statement is executed, and
automatically closed when the operation is completed.

#### Example:
```plsql
DECLARE
v_employee_name VARCHAR2(50);
BEGIN
-- Implicit cursor is used here
SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = 101;

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);

END;
```
In this example, an implicit cursor is created when the `SELECT INTO` statement is
executed. Oracle handles the cursor opening, fetching the result, and closing
automatically.

#### Common Attributes of Implicit Cursors:

- `SQL%ROWCOUNT`: Returns the number of rows affected by the most recent SQL
statement.
- `SQL%FOUND`: Returns TRUE if the last SQL statement affected at least one row;
otherwise, FALSE.
- `SQL%NOTFOUND`: Returns TRUE if the last SQL statement affected no rows;
otherwise, FALSE.
- `SQL%ISOPEN`: Always FALSE because implicit cursors are automatically closed
after execution.

---

### 2. **Explicit Cursor**

An **explicit cursor** is explicitly defined and controlled by the developer. This

type of cursor is used for queries that return multiple rows, allowing you to fetch
rows one by one. You must declare, open, fetch, and close an explicit cursor
manually. Explicit cursors give you more control over fetching and handling
multiple rows.

#### Key Points:

- Explicit cursors are used when a query returns more than one row.
- They must be declared, opened, fetched from, and closed manually by the
developer.
- Explicit cursors can be used with more complex operations like looping through
result sets.

#### Steps to Work with Explicit Cursors:

1. **Declare the cursor**: Define the cursor with a query.
2. **Open the cursor**: Open the cursor to execute the query.
3. **Fetch rows**: Retrieve individual rows from the result set.
4. **Close the cursor**: Close the cursor once you're done with it.
#### Example:
```plsql
DECLARE
CURSOR emp_cursor IS
SELECT employee_name FROM employees WHERE department_id = 10;

v_employee_name employees.employee_name%TYPE;
BEGIN
-- Open the cursor
OPEN emp_cursor;

-- Fetch rows from the cursor one by one

LOOP
FETCH emp_cursor INTO v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND; -- Exit the loop when no more rows are
available
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END LOOP;

-- Close the cursor

CLOSE emp_cursor;
END;
```

#### Explanation:
- **Cursor Declaration**: The cursor `emp_cursor` is declared with the `SELECT`
query that fetches employee names from the `employees` table where `department_id =
10`.
- **Opening the Cursor**: The `OPEN` statement initializes the cursor and executes
the query.
- **Fetching Rows**: The `FETCH` statement retrieves each row of the result set
into the variable `v_employee_name`. The loop continues until no more rows are
available (i.e., when `emp_cursor%NOTFOUND` is TRUE).
- **Closing the Cursor**: Once the loop is finished, the cursor is explicitly
closed with `CLOSE`.

#### Cursor Attributes (for Explicit Cursors):

- `cursor_name%FOUND`: Returns TRUE if the most recent fetch operation retrieved a
row.
- `cursor_name%NOTFOUND`: Returns TRUE if the most recent fetch operation did not
retrieve a row.
- `cursor_name%ROWCOUNT`: Returns the number of rows fetched so far.
- `cursor_name%ISOPEN`: Returns TRUE if the cursor is currently open, FALSE
otherwise.

---

### **Types of Explicit Cursors:**

1. **Static Explicit Cursors**:

- These cursors are defined with a fixed SQL query (i.e., the query doesn’t
change during runtime).
- Example: The cursor defined in the earlier example, `emp_cursor`, is a static
cursor.

2. **Parameterized Explicit Cursors**:

- These cursors are used when you need to pass parameters to the query. The
parameters can be provided at runtime.
- Example:
 ```plsql
DECLARE
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_name FROM employees WHERE department_id = p_dept_id;

v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor(10); -- Pass parameter for department ID

LOOP
FETCH emp_cursor INTO v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END LOOP;

CLOSE emp_cursor;
END;
```
In this example, the cursor `emp_cursor` accepts a parameter `p_dept_id` and
retrieves employee names based on the department ID passed at runtime.

---

### **Summary:**

- **Implicit Cursor**: Automatically created by Oracle for SQL statements returning

a single row or no rows. You don't need to manage it manually.
- **Explicit Cursor**: Manually defined and controlled by the programmer, used for
queries returning multiple rows. It requires manual steps like `DECLARE`, `OPEN`,
`FETCH`, and `CLOSE`.

Using cursors in PL/SQL allows for efficient handling of multiple rows of data and
provides flexibility in controlling the flow of execution, especially when
processing complex queries or when row-level operations are needed.

Q4. Explain exception handling in Oracle.

ans. ### Exception Handling in Oracle

**Exception Handling** in Oracle (PL/SQL) refers to the mechanism used to handle

errors that occur during the execution of a PL/SQL block, function, or procedure.
In PL/SQL, exceptions are used to capture and respond to runtime errors, which
helps in ensuring that your program can continue to run smoothly or fail gracefully
when an error occurs.

PL/SQL provides a robust exception handling model that allows you to catch and
handle different types of errors in a structured way, providing control over how
errors are reported and managed.

### Types of Exceptions:

1. **Predefined Exceptions**: These are Oracle's built-in exceptions that handle
common database errors, such as no data found, too many rows, or unique constraint
violations.
2. **User-Defined Exceptions**: These are exceptions that you define yourself to
handle custom errors specific to your application.
3. **Implicit Exceptions**: These occur automatically when certain runtime errors
(like division by zero or invalid number) happen during the execution of a SQL
statement.

### Basic Structure of Exception Handling in PL/SQL:

A typical PL/SQL block with exception handling is structured in three main
sections:
1. **Declaration Section** (Optional) – where you define variables, cursors, and
exceptions.
2. **Execution Section** (Mandatory) – where the SQL queries and procedural logic
are written.
3. **Exception Section** (Optional) – where exceptions are handled.

```plsql
DECLARE
-- Variable and exception declarations (optional)
v_employee_name VARCHAR2(50);
my_custom_exception EXCEPTION;
BEGIN
-- Executable statements (mandatory)
SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = 101;

-- Raise a user-defined exception

RAISE my_custom_exception;

EXCEPTION
-- Exception handling section (optional)
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Query returned too many rows.');
WHEN my_custom_exception THEN
DBMS_OUTPUT.PUT_LINE('This is a custom exception.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
```

### Exception Handling Components:

1. **Predefined Exceptions**:
Oracle provides a number of predefined exceptions that handle common errors,
such as:
- `NO_DATA_FOUND`: Raised when a `SELECT INTO` statement returns no rows.
- `TOO_MANY_ROWS`: Raised when a `SELECT INTO` statement returns more than one
row.
- `ZERO_DIVIDE`: Raised when attempting to divide a number by zero.
- `DUP_VAL_ON_INDEX`: Raised when trying to insert a duplicate value into a
unique index.
- `INVALID_NUMBER`: Raised when attempting to convert a non-numeric string to a
number.

**Example**:
```plsql
BEGIN
SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id =
999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found!');
 END;
```
In this example, if no employee is found with `employee_id = 999`, the
`NO_DATA_FOUND` exception will be triggered.

2. **User-Defined Exceptions**:
You can define your own exceptions to handle custom errors that occur within
your application. This is done using the `EXCEPTION` keyword.

**Example**:
```plsql
DECLARE
insufficient_funds EXCEPTION;
balance NUMBER := 1000;
withdrawal_amount NUMBER := 1500;
BEGIN
IF withdrawal_amount > balance THEN
RAISE insufficient_funds;
END IF;
EXCEPTION
WHEN insufficient_funds THEN
DBMS_OUTPUT.PUT_LINE('Insufficient funds for withdrawal.');
END;
```

In this example, we create a user-defined exception called `insufficient_funds`.

If the `withdrawal_amount` exceeds the available `balance`, the exception is raised
and caught in the `EXCEPTION` section, where we display an error message.

3. **The `OTHERS` Exception Handler**:

The `OTHERS` handler is a catch-all exception that will handle any exceptions
not explicitly named in the exception section. It should always be placed at the
end of the `EXCEPTION` block.

**Example**:
```plsql
BEGIN
-- Executing SQL operations that may raise exceptions
SELECT * FROM unknown_table;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
```

In this case, if any exception occurs that is not specifically handled (such as
a non-existent table or other issues), the `OTHERS` handler will catch the error,
and the error message will be displayed.

### Exception Propagation:

PL/SQL provides an important concept called **exception propagation**. When an
exception is raised and not handled in the current block, it propagates (or passes)
to the outer block. If the outer block does not handle it, the exception continues
to propagate until it reaches the top-level block or the application is terminated.

### Example of Exception Propagation:

```plsql
DECLARE
v_balance NUMBER := 500;
 v_withdrawal NUMBER := 1000;

insufficient_funds EXCEPTION;
BEGIN
-- Inner block that raises an exception
BEGIN
IF v_withdrawal > v_balance THEN
RAISE insufficient_funds;
END IF;
EXCEPTION
WHEN insufficient_funds THEN
DBMS_OUTPUT.PUT_LINE('Inner block: Insufficient funds!');
RAISE; -- Propagating the exception to the outer block
END;

EXCEPTION
WHEN insufficient_funds THEN
DBMS_OUTPUT.PUT_LINE('Outer block: Insufficient funds!');
END;
```
In this example:
- The inner block raises the `insufficient_funds` exception.
- The exception is caught in the inner block and is propagated to the outer block
using the `RAISE` statement.
- The outer block then handles the exception.

### Key Exception Handling Statements:

1. **RAISE**: Used to explicitly raise an exception, either predefined or user-
defined.
- `RAISE;` raises the current exception.
- `RAISE exception_name;` raises a specific user-defined exception.
- `RAISE predefined_exception;` raises a predefined exception.
2. **SQLERRM**: Provides the error message associated with the last exception.
3. **SQLCODE**: Returns the error code of the last exception (useful for logging or
debugging).

### Summary:
- **Exception handling** helps gracefully manage runtime errors and allows for the
program to handle different error conditions appropriately.
- **Predefined exceptions** such as `NO_DATA_FOUND`, `TOO_MANY_ROWS`, and
`ZERO_DIVIDE` are automatically raised by the Oracle Database for common issues.
- **User-defined exceptions** allow developers to handle specific errors in their
own applications.
- **The OTHERS exception** serves as a catch-all handler for any exceptions not
specifically mentioned.
- Exception handling enhances the reliability and maintainability of PL/SQL code by
ensuring that errors are captured and handled properly.

Q5. Discuss types of joins.

ans. In SQL, **joins** are used to combine rows from two or more tables based on a
related column between them. Joins are essential for querying data from multiple
tables in a relational database. There are several types of joins, each serving
different purposes depending on how you want to combine the data. Here are the
**types of joins** in SQL:

### 1. **Inner Join**

- **Definition**: The `INNER JOIN` keyword returns rows when there is at least
one match in both tables. If no match is found, the row is excluded from the result
set.
- **Use Case**: It is used when you want to select records that have matching
values in both tables.

**Syntax**:
```sql
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
```

**Example**:
```sql
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
```
- In this example, only employees who belong to a department will be returned in
the result.

---

### 2. **Left Join (or Left Outer Join)**

- **Definition**: The `LEFT JOIN` (or `LEFT OUTER JOIN`) returns all records
from the left table (table1), and the matched records from the right table
(table2). If there is no match, `NULL` values will be returned for columns from the
right table.
- **Use Case**: It is useful when you want to retrieve all records from the left
table, including those that don’t have a match in the right table.

**Syntax**:
```sql
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
```

**Example**:
```sql
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
```
- In this example, all employees will be returned. If an employee does not
belong to a department, the department's name will be `NULL`.

---

### 3. **Right Join (or Right Outer Join)**

- **Definition**: The `RIGHT JOIN` (or `RIGHT OUTER JOIN`) is similar to the
`LEFT JOIN`, but it returns all records from the right table (table2), and the
matched records from the left table (table1). If there is no match, `NULL` values
will be returned for columns from the left table.
- **Use Case**: This join is used when you want to retrieve all records from the
right table, including those that don’t have a match in the left table.
 **Syntax**:
```sql
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
```

**Example**:
```sql
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
```
- In this example, all departments will be returned. If a department has no
employees, the employee's name will be `NULL`.

---

### 4. **Full Join (or Full Outer Join)**

- **Definition**: The `FULL JOIN` (or `FULL OUTER JOIN`) returns all records
when there is a match in either the left table (table1) or the right table
(table2). If there is no match, the missing side will contain `NULL` values.
- **Use Case**: It is used when you want to retrieve all records from both
tables, including unmatched rows from both sides.

**Syntax**:
```sql
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
```

**Example**:
```sql
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;
```
- In this example, all employees and all departments will be returned. If an
employee does not belong to a department, the department's name will be `NULL`, and
if a department has no employees, the employee's name will be `NULL`.

---

### 5. **Cross Join**

- **Definition**: A `CROSS JOIN` returns the Cartesian product of two tables,
i.e., it returns all possible combinations of rows from both tables. It does not
require a condition and can produce a large result set.
- **Use Case**: It is rarely used in practice, but it can be useful when you
need every combination of rows from two tables.

**Syntax**:
```sql
SELECT columns
FROM table1
CROSS JOIN table2;
```
 **Example**:
```sql
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;
```
- In this example, every employee will be paired with every department,
resulting in a Cartesian product (i.e., each row in `employees` will be combined
with each row in `departments`).

---

### 6. **Self Join**

- **Definition**: A `SELF JOIN` is a join where a table is joined with itself.
This is useful when you have a hierarchical or recursive relationship within the
same table.
- **Use Case**: It is typically used for hierarchical data, such as employees
and their managers within the same `employees` table.

**Syntax**:
```sql
SELECT a.column_name, b.column_name
FROM table a, table b
WHERE a.column_name = b.column_name;
```

**Example**:
```sql
SELECT e1.employee_name AS Employee, e2.employee_name AS Manager
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;
```
- In this example, the `employees` table is joined with itself to return each
employee along with their manager.

---

### Summary of Join Types:

| **Join Type** | **Description**
| **Example Use Case**
|
|------------------------|---------------------------------------------------------
----------------------------------------------------|------------------------------
--------------------------------------------------------|
| **Inner Join** | Returns records with matching values in both tables.
| Retrieve employees who belong to a department.
|
| **Left Join (Outer)** | Returns all records from the left table, and matched
records from the right table; `NULL` for unmatched rows. | Retrieve all employees,
even if they do not belong to a department. |
| **Right Join (Outer)** | Returns all records from the right table, and matched
records from the left table; `NULL` for unmatched rows. | Retrieve all departments,
even if they have no employees. |
| **Full Join (Outer)** | Returns all records when there is a match in either
left or right table; `NULL` for unmatched rows. | Retrieve all employees
and departments, even if some do not match. |
| **Cross Join** | Returns the Cartesian product of two tables (every
combination of rows). | Rarely used; may be
used in generating test data with all combinations. |
| **Self Join** | Joins a table with itself.
| Useful for hierarchical data, such as employees and their managers in the same
table. |

Each type of join has its use case depending on what you need to achieve with your
query, and the choice of join determines how records from multiple tables are
combined and filtered.

Q6. Discuss cursor attributes. Explain te use of eac attributes providing

appropriate
examples.

ans. ### Cursor Attributes in PL/SQL

In PL/SQL, **cursors** are used to handle and manipulate the result sets of SQL
queries. After a cursor is opened and a `FETCH` operation retrieves rows, **cursor
attributes** are used to check and interact with the state of the cursor and the
data being processed. These attributes provide useful information about the cursor,
such as the number of rows fetched, whether any rows were found, and the cursor's
open status.

### Key Cursor Attributes

1. **%FOUND**
2. **%NOTFOUND**
3. **%ROWCOUNT**
4. **%ISOPEN**

Each of these attributes is used to get information about the cursor and the result
set it handles.

---

### 1. **%FOUND**
- **Definition**: The `%FOUND` attribute returns a boolean value (`TRUE` or
`FALSE`). It indicates whether the last `FETCH` operation successfully retrieved a
row.
- **Use Case**: It is used to check if a row was successfully fetched from the
result set.

**Syntax**:
```plsql
cursor_name%FOUND
```

- **TRUE**: If the last fetch operation found a row.

- **FALSE**: If the last fetch operation did not find a row or if the cursor is
empty.

**Example**:
```plsql
DECLARE
CURSOR emp_cursor IS
SELECT employee_name FROM employees;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor;
 LOOP
FETCH emp_cursor INTO v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND; -- Exit the loop if no more rows are found
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name);
END LOOP;

CLOSE emp_cursor;
END;
```

- In this example, the cursor `emp_cursor` is used to fetch employee names. The
`emp_cursor%FOUND` is implicitly checked via the `EXIT WHEN emp_cursor%NOTFOUND`
condition in the loop. If a row is fetched, `%FOUND` would be `TRUE`.

---

### 2. **%NOTFOUND**
- **Definition**: The `%NOTFOUND` attribute is the opposite of `%FOUND`. It
returns a boolean value (`TRUE` or `FALSE`) that indicates whether the last `FETCH`
operation did **not** retrieve a row.
- **Use Case**: It is used to determine if the last fetch operation was
unsuccessful or if no rows were found.

**Syntax**:
```plsql
cursor_name%NOTFOUND
```

- **TRUE**: If the last `FETCH` operation failed to retrieve a row (i.e., no

more rows are available).
- **FALSE**: If the last `FETCH` operation retrieved at least one row.

**Example**:
```plsql
DECLARE
CURSOR emp_cursor IS
SELECT employee_name FROM employees;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor;

FETCH emp_cursor INTO v_employee_name;

IF emp_cursor%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No employees found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name);
END IF;

CLOSE emp_cursor;
END;
```

- In this example, the `emp_cursor%NOTFOUND` is checked after the first `FETCH`.

If no row is fetched, it outputs 'No employees found.'

---

### 3. **%ROWCOUNT**
 - **Definition**: The `%ROWCOUNT` attribute returns the number of rows that have
been fetched or affected by the cursor (in the case of `INSERT`, `UPDATE`, `DELETE`
operations).
- **Use Case**: It is often used to track how many rows have been processed by
the cursor or SQL statement. It can be used to implement pagination or report
progress in long-running operations.

**Syntax**:
```plsql
cursor_name%ROWCOUNT
```

- **Value**: It returns an integer value indicating the number of rows fetched

or processed by the cursor.

**Example**:
```plsql
DECLARE
CURSOR emp_cursor IS
SELECT employee_name FROM employees;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name);
DBMS_OUTPUT.PUT_LINE('Rows fetched so far: ' || emp_cursor%ROWCOUNT);
END LOOP;

CLOSE emp_cursor;
END;
```

- In this example, after each fetch, the `emp_cursor%ROWCOUNT` is printed to

indicate how many rows have been fetched so far. This is useful for tracking
progress in the loop.

---

### 4. **%ISOPEN**
- **Definition**: The `%ISOPEN` attribute returns a boolean value (`TRUE` or
`FALSE`). It indicates whether the cursor is currently open or closed.
- **Use Case**: It is used to check the open status of a cursor, particularly
useful for debugging and ensuring that cursors are properly managed (i.e., opened
and closed correctly).

**Syntax**:
```plsql
cursor_name%ISOPEN
```

- **TRUE**: If the cursor is currently open.

- **FALSE**: If the cursor is closed.

**Example**:
```plsql
DECLARE
CURSOR emp_cursor IS
 SELECT employee_name FROM employees;
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is open.');
END IF;

CLOSE emp_cursor;
IF NOT emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is closed.');
END IF;
END;
```

- In this example, the `%ISOPEN` attribute is used to check if the cursor is

open after it has been opened, and to verify that it is closed after the `CLOSE`
statement is executed.

---

### Summary of Cursor Attributes

| **Attribute** | **Description**
| **Return Type** | **Common Use Case**
|
|----------------------|-----------------------------------------------------------
------------------------------------------------|---------------------------|------
-----------------------------------------------------------------------------------
-------------------------|
| **%FOUND** | Returns `TRUE` if the last fetch found a row, otherwise
`FALSE`. | Boolean (`TRUE`/`FALSE`) |
Used to check if a `FETCH` was successful. Example: determining if a row was
returned from a query. |
| **%NOTFOUND** | Returns `TRUE` if the last fetch did not find a row,
otherwise `FALSE`. | Boolean (`TRUE`/`FALSE`)
| Used to check if no rows were found after a fetch. Example: handling the case
when no rows are found. |
| **%ROWCOUNT** | Returns the number of rows fetched or affected by the
cursor. | Integer |
Used to track the number of rows processed or fetched by the cursor. Example:
counting how many rows were fetched.|
| **%ISOPEN** | Returns `TRUE` if the cursor is open, otherwise `FALSE`.
| Boolean (`TRUE`/`FALSE`) | Used to check whether a cursor is currently open.
Example: preventing operations on a closed cursor. |

### Conclusion
Cursor attributes in PL/SQL are powerful tools for controlling and managing the
flow of data when working with cursors. They help you to:
- Check if the cursor returned any rows (`%FOUND` / `%NOTFOUND`).
- Keep track of how many rows have been fetched (`%ROWCOUNT`).
- Ensure that the cursor is open and properly managed (`%ISOPEN`).

By using these attributes effectively, you can write more efficient and error-free
PL/SQL code when working with cursors and result sets.