20 Essential Oracle Questions for Beginners
Tags: oracle, sql, beginners, programming
Published: 2024-01-27 12:44:29

Welcome to the world of Oracle PL/SQL! Whether you're a beginner or looking to improve your skills, this article will guide you through 20 essential PL/SQL questions, complete with detailed solutions and helpful screenshots.

## Introduction to PL/SQL

**PL/SQL (Procedural Language/Structured Query Language)** is Oracle Corporation's powerful and robust extension of SQL. While SQL (Structured Query Language) is primarily used for database queries and manipulation, PL/SQL takes it further by providing a procedural programming language, allowing developers to create powerful and flexible applications.

## Key Features of PL/SQL

### 1. Procedural Constructs

PL/SQL introduces procedural constructs like loops, conditionals, and exception handling, transforming SQL into a complete programming language. This allows developers to build complex logic and control structures within the database.

### 2. Block Structure

PL/SQL code is organized into blocks, which can be anonymous or named. Blocks consist of declarations, executable statements, and exception handlers. This modular structure enhances code readability and maintainability.

### 3. Variables and Data Types

PL/SQL supports the declaration of variables, constants, and data types. Variables can be used to store and manipulate data within the program, offering a high degree of flexibility in coding.

### 4. Cursors

Cursors are essential in PL/SQL for processing query results row by row. They allow developers to iterate through result sets and perform operations on each row individually.

### 5. Exception Handling

Exception handling is a critical aspect of PL/SQL, enabling developers to manage errors gracefully. With try-catch-like constructs, developers can anticipate and respond to unexpected situations, enhancing the reliability of applications.

### 6. Stored Procedures and Functions

PL/SQL supports the creation of stored procedures and functions. These stored units of code can be called and executed from various applications, providing a modular approach to code organization.

### 7. Triggers

Triggers in PL/SQL are special types of stored procedures that automatically execute in response to specific events, such as data modifications (insert, update, delete). They are powerful tools for enforcing business rules and maintaining data integrity.

## Writing PL/SQL Code

Here's a simple example of a PL/SQL block:

```plsql
DECLARE
  v_name VARCHAR2(50) := 'PL/SQL';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name || '!');
END;
/
```

In this example, we declare a variable `v_name`, assign a value to it, and then use the `DBMS_OUTPUT.PUT_LINE` procedure to display a message. The code is executed within a PL/SQL block, encapsulating the logic.

## Advantages of Using PL/SQL

1. **Integration with SQL:** PL/SQL seamlessly integrates with SQL, allowing developers to embed SQL statements within their procedural code.

2. **Performance Enhancement:** PL/SQL reduces the number of round-trips between the application and the database, enhancing performance by executing multiple SQL statements in a single block.

3. **Security and Privileges:** Developers can encapsulate business logic within stored procedures, controlling access and ensuring data security.

4. **Code Reusability:** The modular nature of PL/SQL allows developers to create reusable components, reducing redundancy and promoting efficient code maintenance.

---

## 20 Oracle Questions
20 Oracle database-related questions can help you to understand and practice the **PL/SQL** in RDBMS.

### 1. WAQ to create table Employees_yourname with columns.
**Ans -** 
```plsql
CREATE TABLE employees_yourname (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(20),
  last_name VARCHAR2(25),
  email VARCHAR2(25),
  phone_number VARCHAR2(20),
  hire_date DATE,
  job_id VARCHAR2(10),
  salary NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id NUMBER(6),
  department_id NUMBER(4)
)
```

![Employee Table Result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wtwpc01v4ip9konymsxl.png)

In this example, we create a table called `employees_yourname` with 11 columns. The `employee_id` column is the primary key, and the remaining columns are of various data types.

| Column Name | Data Type | Description |Explanation |
| ----------- | --------- | ----------- | ----------- |
| employee_id | NUMBER | Primary key | Unique identifier for each employee |
| first_name | VARCHAR2(20) | Employee's first name | Stores up to 20 characters |
| last_name | VARCHAR2(25) | Employee's last name | Stores up to 25 characters |
| email | VARCHAR2(25) | Employee's email address | Stores up to 25 characters |
| phone_number | VARCHAR2(20) | Employee's phone number | Stores up to 20 characters |
| hire_date | DATE | Employee's hire date | Stores date and time |
| job_id | VARCHAR2(10) | Employee's job ID | Stores up to 10 characters |
| salary | NUMBER(8,2) | Employee's salary | Stores up to 8 digits, with 2 decimal places |
| commission_pct | NUMBER(2,2) | Employee's commission percentage | Stores up to 2 digits, with 2 decimal places |
| manager_id | NUMBER(6) | Employee's manager ID | Stores up to 6 digits |
| department_id | NUMBER(4) | Employee's department ID | Stores up to 4 digits |

### 2. WAQ to create table Departments_yourname with columns.
**Ans - **
```plsql
CREATE TABLE departments_yourname (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(30),
  manager_id NUMBER(6),
  location_id NUMBER(4)
)
```

![Department Table Result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c73mshtfe1q7iya4a0sl.png)



In this example, we create a table called `departments_yourname` with 4 columns. The `department_id` column is the primary key, and the remaining columns are of various data types.

| Column Name | Data Type | Description |Explanation |
| ----------- | --------- | ----------- | ----------- |
| department_id | NUMBER | Primary key | Unique identifier for each department |
| department_name | VARCHAR2(30) | Department name | Stores up to 30 characters |
| manager_id | NUMBER(6) | Manager ID | Stores up to 6 digits |
| location_id | NUMBER(4) | Location ID | Stores up to 4 digits |

### 3. WAQ to list all employees those who are earning salary more than 6000.
**Ans - **
```plsql
SELECT * FROM employees_yourname WHERE salary > 6000;
```

![Employee Earning more then 6000](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jyz64re1klcs75o87yky.png)

In this example, we use the `SELECT` statement to retrieve all employees whose salary is greater than 6000. The `*` wildcard character is used to select all columns from the `employees_yourname` table. The `WHERE` clause is used to filter the results based on the specified condition.

### 4. WAQ to get the list of employees those who are either working as IT Programmer or clerk.
**Ans - **
```plsql
SELECT * FROM employees_yourname WHERE job_id IN ('IT_PROG', 'CLERK');
```

![Employees From IT_PROG and CLERK](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/35hu20fngnd7bwbhaam6.png)

In this example, we use the `SELECT` statement to retrieve all employees whose job ID is either `IT_PROG` or `CLERK`. The `IN` operator is used to specify multiple values in the `WHERE` clause.

### 5. WAQ to find employees list those who are earning commission.
**Ans - **
```plsql
SELECT * FROM employees_yourname WHERE commission_pct IS NOT NULL;
```

![Employee who earning commission](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ya6z5gpn85s7ol22nowj.png)

In this example, we use the `SELECT` statement to retrieve all employees whose commission percentage is not null. The `IS NOT NULL` operator is used to filter the results based on the specified condition.

### 6. WAQ to display employees full name with its total salary as salary + commission.
**Ans - **
```plsql
SELECT first_name || ' ' || last_name AS "Full Name", salary + NVL(commission_pct, 0) AS "Total Salary" FROM employees_yourname;
```

![Employees Name with Total Salary](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1uad9wnnvt0bemv1ysbe.png)


In this example, we use the `SELECT` statement to retrieve the full name and total salary of each employee. The `||` operator is used to concatenate the first name and last name columns. The `NVL` function is used to replace null values with 0. The `AS` keyword is used to assign aliases to the columns. The `FROM` clause specifies the table from which the data is retrieved.

### 7. WAQ to find out the maximum salary, minimum salary, and total salary of each department.
**Ans - **
```plsql
SELECT department_id, MAX(salary), MIN(salary), SUM(salary) FROM employees_yourname GROUP BY department_id;
```

![Salary With Grouping in Employees](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzs76ts8ipzb85xsjobi.png)

In this example, we use the `SELECT` statement to retrieve the maximum, minimum, and total salary of each department. The `MAX`, `MIN`, and `SUM` functions are used to calculate the maximum, minimum, and total salary, respectively. The `GROUP BY` clause is used to group the results by department ID.  

### 8. WAQ to alter the id column of every table and the first three letters of your name and then the id column (e.g. your name “Ravi” and the column name “ravi_employee_id”)
**Ans - **
```plsql
ALTER TABLE employees_yourname RENAME COLUMN employee_id TO yourname_employee_id;
```

![RENAME COLUMN employee table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xrks5qd65v9o2ods4y5w.png)

In this example, we use the `ALTER TABLE` statement to rename the `employee_id` column to `ravi_employee_id` in the `employees_ravi` table. The `RENAME COLUMN` clause is used to rename the specified column. `ALTER TABLE` is used to modify the structure of a table.

### 9. Write a SQL Join to find out the Employee and their department name.
**Ans - **
```plsql
SELECT e.first_name || ' ' || e.last_name AS "Employee Name", d.department_name AS "Department Name"
FROM employees_yourname e
INNER JOIN departments_yourname d ON e.department_id = d.department_id;
```

![Employee and Department Join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mgar0droga5qp41evgck.png)


In this example, we use the `SELECT` statement to retrieve the employee name and department name. The `||` operator is used to concatenate the first name and last name columns. The `AS` keyword is used to assign aliases to the columns. The `FROM` clause specifies the tables from which the data is retrieved. The `INNER JOIN` clause is used to combine rows from both tables based on the specified condition.

### 10. Write a SQL Query to find out the salary who are earning more than Bruce.
**Ans - **
```plsql
SELECT * FROM employees_yourname WHERE salary > (SELECT salary FROM employees_yourname WHERE first_name = 'Bruce');
```

![Earning more than Bruce](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p7a65nxu4gq5eiw6v9zb.png)

In this example, we use the `SELECT` statement to retrieve all employees whose salary is greater than Bruce's salary. The `WHERE` clause is used to filter the results based on the specified condition. The subquery `(SELECT salary FROM employees_yourname WHERE first_name = 'Bruce')` is used to retrieve Bruce's salary.

### 11. Write an SQL Query to find all employees who are working in the same department as Bruce.
**Ans - **
```plsql
SELECT * FROM employees_yourname WHERE department_id = (SELECT department_id FROM employees_yourname WHERE first_name = 'Bruce');
```

![Same Department as Bruce](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rnibzxgncd1asrqvlxu8.png)

In this example, we use the `SELECT` statement to retrieve all employees who are working in the same department as Bruce. The `WHERE` clause is used to filter the results based on the specified condition. The subquery `(SELECT department_id FROM employees_yourname WHERE first_name = 'Bruce')` is used to retrieve Bruce's department ID.

### 12. Write a SQL Query to find the list of employees who have more experience than Bruce.
**Ans - **
```plsql
SELECT * FROM employees_yourname WHERE hire_date < (SELECT hire_date FROM employees_yourname WHERE first_name = 'Bruce');
```

![More Experience than Bruce](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6n3tqk89shzthspu7k6r.png)

In this example, we use the `SELECT` statement to retrieve all employees who have more experience than Bruce. The `WHERE` clause is used to filter the results based on the specified condition. The subquery `(SELECT hire_date FROM employees_yourname WHERE first_name = 'Bruce')` is used to retrieve Bruce's hire date.

### 13. Write a Query to find the list of employees who are working under King.
**Ans -**
```plsql
SELECT * FROM employees_yourname WHERE manager_id = (SELECT employee_id FROM employees_yourname WHERE first_name = 'King');
```

![Employees working under King](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rlb0sajyx486fq16s0qr.png)

In this example, we use the `SELECT` statement to retrieve all employees who are working under King. The `WHERE` clause is used to filter the results based on the specified condition. The subquery `(SELECT employee_id FROM employees_yourname WHERE first_name = 'King')` is used to retrieve King's employee ID.

### 14. Make a copy of your Employees table, called Emp_Cop.
**Ans - **
```plsql
CREATE TABLE EMP_COP AS SELECT * FROM employees_yourname;
```

![Copy of Employee Table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3i934rlkix1rpbhc7vug.png)


In this example, we use the `CREATE TABLE` statement to create a new table called `EMP_COP`. The `AS` keyword is used to copy the data from the `employees_yourname` table into the new table. This is known as a `CREATE TABLE AS` statement.

### 15. Alter your Employees table and add the column TOTSAL with data type NUMBER(9,2).
**Ans - **
```plsql
ALTER TABLE employees_yourname ADD (totsal NUMBER(9,2));
```

![Alter Table Employee](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vb7abx6en6n0o6t92gmb.png)

In this example, we use the `ALTER TABLE` statement to add a new column called `totsal` to the `employees_yourname` table. The `ADD` clause is used to add the specified column. The `NUMBER(9,2)` data type is used to store up to 9 digits, with 2 decimal places.

### 16. Write a Query to find the list of departments which are in the city of London.
**Ans - **
```plsql
SELECT * 
FROM locations
INNER JOIN departments_yourname ON locations.location_id = departments_yourname.location_id
WHERE city = 'London';
```

![London Departments](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1kag4xkv5g2jurkstiwo.png)

In this example, we use the `SELECT` statement to retrieve all departments located in London. The `FROM` clause specifies the tables from which the data is retrieved. The `INNER JOIN` clause is used to combine rows from both tables based on the specified condition. The `WHERE` clause is used to filter the results based on the specified condition. 

There is another way to write this query using a subquery:

```plsql
SELECT *
FROM departments_yourname
WHERE location_id = (SELECT location_id FROM locations WHERE city = 'London');
```

In this example, we use the `SELECT` statement to retrieve all departments located in London. The `WHERE` clause is used to filter the results based on the specified condition. The subquery `(SELECT location_id FROM locations WHERE city = 'London')` is used to retrieve the location ID of London.

### 17. Write a Query to find job title and their min and max salary.
**Ans - **
```plsql
SELECT job_title, MIN(salary), MAX(salary)
FROM employees_yourname
INNER JOIN jobs ON employees_yourname.job_id = jobs.job_id
GROUP BY job_title;
```

![JOB Title and Salary (MIN & MAX)](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/henbf174c20jdzlerj7p.png)

In this example, we use the `SELECT` statement to retrieve the job title, minimum salary, and maximum salary of each job. The `FROM` clause specifies the tables from which the data is retrieved. The `INNER JOIN` clause is used to combine rows from both tables based on the specified condition. The `GROUP BY` clause is used to group the results by job title. `MIN` and `MAX` functions are used to calculate the minimum and maximum salary, respectively.

### 18. Write a Function that returns the gross salary of the employee. (input: employee_id, Salary, HRA, DA).
**Ans - **
```plsql
CREATE OR REPLACE FUNCTION gross_salary(
    p_employee_id IN NUMBER,
    p_salary IN NUMBER,
    p_hra IN NUMBER,
    p_da IN NUMBER
) RETURN NUMBER AS
    v_gross_salary NUMBER;
BEGIN
    v_gross_salary := p_salary + p_hra + p_da;
    RETURN v_gross_salary;
END gross_salary;
/

-- Call the function to calculate the gross salary for employee 1 (John Doe).
DECLARE
    v_employee_id NUMBER := 1;
    v_salary NUMBER := 10000;
    v_hra NUMBER := 2000;
    v_da NUMBER := 3000;
    v_result_salary NUMBER;
BEGIN
    v_result_salary := gross_salary(v_employee_id, v_salary, v_hra, v_da);
    DBMS_OUTPUT.PUT_LINE('Gross Salary for Employee ' || v_employee_id || ': ' || v_result_salary);
END;
/
```

In this example, we create a function called `gross_salary` that calculates the gross salary of an employee. The function takes 4 parameters: `p_employee_id`, `p_salary`, `p_hra`, and `p_da`. The `RETURN` keyword is used to return the result. The `CREATE OR REPLACE FUNCTION` statement is used to create a new function. The `DECLARE` statement is used to declare variables. The `BEGIN` and `END` keywords are used to define the function body. The `DBMS_OUTPUT.PUT_LINE` procedure is used to display the result. After creating the function, we call it to calculate the gross salary for employee 1 (John Doe). The `DECLARE` statement is used to declare variables and constants. We call the function using the `gross_salary` keyword, passing in the required parameters. The `DBMS_OUTPUT.PUT_LINE` procedure is used to display the result.

### 19. Write a SQL procedure called QUERY_EMP to query the EMPLOYEES table, retrieving the salary and job ID for an employee when provided with the employee ID.
**Ans - **
```plsql
CREATE OR REPLACE PROCEDURE query_emp(
    p_employee_id IN NUMBER
) AS
    v_salary NUMBER;
    v_job_id VARCHAR2(10);
BEGIN
    SELECT salary, job_id INTO v_salary, v_job_id
    FROM employees_yourname
    WHERE employee_id = p_employee_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
    DBMS_OUTPUT.PUT_LINE('Job ID: ' || v_job_id);
END query_emp;
/

-- Call the procedure to query the employee with ID 1.
BEGIN
    query_emp(1);
END;
/
```

In this example, we create a procedure called `query_emp` that queries the `employees_yourname` table, retrieving the salary and job ID for an employee when provided with the employee ID. The procedure takes 1 parameter: `p_employee_id`. The `CREATE OR REPLACE PROCEDURE` statement is used to create a new procedure. The `DECLARE` statement is used to declare variables and constants. The `BEGIN` and `END` keywords are used to define the procedure body. The `SELECT INTO` statement is used to retrieve the salary and job ID of the specified employee. The `DBMS_OUTPUT.PUT_LINE` procedure is used to display the result. After creating the procedure, we call it to query the employee with ID 1.  We call the procedure using the `query_emp` keyword, passing in the required parameters. The `DBMS_OUTPUT.PUT_LINE` procedure is used to display the result.

### 20. Create a stored procedure named UPD_EMP_SAL to update the salary amount. This procedure accepts two parameters: the job ID for which salary has to be updated, and the new minimum salary for this job ID. This procedure is executed from the trigger on the JOBS table.
**Ans - **
```plsql
CREATE OR REPLACE PROCEDURE upd_emp_sal(
    p_job_id IN VARCHAR2,
    p_new_min_salary IN NUMBER
) AS
BEGIN
    UPDATE employees_yourname
    SET salary = p_new_min_salary
    WHERE job_id = p_job_id;
END upd_emp_sal;
/

-- Call the procedure to update the salary for job ID 'IT_PROG' to 5000.
BEGIN
    upd_emp_sal('IT_PROG', 5000);
END;
/
```

In this example, we create a procedure called `upd_emp_sal` that updates the salary amount. The procedure takes 2 parameters: `p_job_id` and `p_new_min_salary`. The `CREATE OR REPLACE PROCEDURE` statement is used to create a new procedure. The `DECLARE` statement is used to declare variables and constants. The `BEGIN` and `END` keywords are used to define the procedure body. The `UPDATE` statement is used to update the salary of the specified job ID. After creating the procedure, we call it to update the salary for job ID `IT_PROG` to 5000. We call the procedure using the `upd_emp_sal` keyword, passing in the required parameters.

### 21. Employees should receive an automatic increase in salary if the minimum salary for a job is increased. Implement this requirement through a trigger on the JOBS table.
**Ans –**
```plsql
CREATE OR REPLACE TRIGGER upd_emp_sal_trigger
AFTER UPDATE OF min_salary ON jobs
FOR EACH ROW
BEGIN
    upd_emp_sal(:NEW.job_id, :NEW.min_salary);
END;
/
```

In this example, we create a trigger called `upd_emp_sal_trigger` that updates the salary of employees when the minimum salary for a job is increased. The `CREATE OR REPLACE TRIGGER` statement is used to create a new trigger. The `AFTER UPDATE OF` clause specifies the event that triggers the trigger. The `FOR EACH ROW` clause specifies that the trigger is fired for each row affected by the triggering statement. The `BEGIN` and `END` keywords are used to define the trigger body. The `:NEW` keyword is used to refer to the new value of the column. The `upd_emp_sal` procedure is called to update the salary of the specified job ID.

## Conclusion

PL/SQL is a versatile and powerful language that empowers developers to build robust, efficient, and secure database applications. Understanding its features and capabilities is essential for anyone working with Oracle databases, making it a valuable skill for developers and database administrators alike. As you delve deeper into PL/SQL, you'll discover its ability to handle complex scenarios and streamline database interactions, making it a cornerstone in Oracle's technology stack.