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:
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
Integration with SQL: PL/SQL seamlessly integrates with SQL, allowing developers to embed SQL statements within their procedural code.
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.
Security and Privileges: Developers can encapsulate business logic within stored procedures, controlling access and ensuring data security.
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 -
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)
)
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 |
| 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 - *
CREATE TABLE departments_yourname (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(30),
manager_id NUMBER(6),
location_id NUMBER(4)
)
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 - *
SELECT * FROM employees_yourname WHERE salary > 6000;
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 - *
SELECT * FROM employees_yourname WHERE job_id IN ('IT_PROG', 'CLERK');
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 - *
SELECT * FROM employees_yourname WHERE commission_pct IS NOT NULL;
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 - *
SELECT first_name || ' ' || last_name AS "Full Name", salary + NVL(commission_pct, 0) AS "Total Salary" FROM employees_yourname;
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 - *
SELECT department_id, MAX(salary), MIN(salary), SUM(salary) FROM employees_yourname GROUP BY department_id;
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 - *
ALTER TABLE employees_yourname RENAME COLUMN employee_id TO yourname_employee_id;
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 - *
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;
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 - *
SELECT * FROM employees_yourname WHERE salary > (SELECT salary FROM employees_yourname WHERE first_name = 'Bruce');
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 - *
SELECT * FROM employees_yourname WHERE department_id = (SELECT department_id FROM employees_yourname WHERE first_name = 'Bruce');
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 - *
SELECT * FROM employees_yourname WHERE hire_date < (SELECT hire_date FROM employees_yourname WHERE first_name = 'Bruce');
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 -
SELECT * FROM employees_yourname WHERE manager_id = (SELECT employee_id FROM employees_yourname WHERE first_name = 'King');
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 - *
CREATE TABLE EMP_COP AS SELECT * FROM employees_yourname;
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 - *
ALTER TABLE employees_yourname ADD (totsal NUMBER(9,2));
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 - *
SELECT *
FROM locations
INNER JOIN departments_yourname ON locations.location_id = departments_yourname.location_id
WHERE city = 'London';
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:
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 - *
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;
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 - *
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 - *
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 - *
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 –
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.
Top comments (0)