What is the difference between SQL and PL/SQL?

SQL (Structured Query Language) and PL/SQL (Procedural Language/Structured Query Language) are both programming languages used in conjunction with relational databases, but they serve different purposes. Here are the key differences between SQL and PL/SQL:

SQL:

• SQL is a language used for managing and manipulating relational databases.
• It focuses on querying and manipulating data within the database.
• SQL is primarily used for tasks such as retrieving data, inserting, updating, and deleting records, creating and modifying database schemas, and performing data operations.
• SQL is a declarative language, where you specify what data you want to retrieve or modify without specifying how to achieve it.
• Examples of SQL statements include SELECT, INSERT, UPDATE, DELETE, CREATE, and ALTER.

PL/SQL:

• PL/SQL is an extension of SQL that provides procedural programming capabilities within the Oracle Database.
• It combines SQL with procedural constructs such as variables, conditions, loops, and exception handling to develop complex database applications.
• PL/SQL is used to write stored procedures, functions, triggers, and packages, which encapsulate logic and business rules that can be executed within the database.
• PL/SQL is a block-structured language, allowing you to define blocks of code that can be executed as a single unit.
• It supports looping, conditional statements, error handling, and other programming constructs for building modular and maintainable database applications.

In summary, SQL is primarily focused on data manipulation and retrieval from a database, while PL/SQL extends SQL by adding procedural programming capabilities for building more complex database applications within the Oracle Database. SQL is used to interact with databases, while PL/SQL is used to write program logic and business rules that can be executed within the database environment.

Let's illustrate the difference between SQL and PL/SQL with an example.

Consider a scenario where you have a database table named "Employees" with columns such as "EmployeeID," "FirstName," "LastName," and "Salary." You want to retrieve the names of all employees with a salary greater than a certain threshold.

Here's how the solution would look using SQL:

SELECT FirstName, LastName
FROM Employees
WHERE Salary > 50000;

In this SQL query, we directly query the "Employees" table to retrieve the first and last names of employees who have a salary greater than 50000. SQL is focused on data retrieval and manipulation, and the above query follows the SQL syntax to specify the columns to retrieve, the table to query, and the condition to filter the data.

Now, let's see how the same solution can be implemented using PL/SQL:

DECLARE
  emp_first_name Employees.FirstName%TYPE;
  emp_last_name Employees.LastName%TYPE;
  emp_salary Employees.Salary%TYPE;
BEGIN
  FOR emp IN (SELECT FirstName, LastName, Salary FROM Employees WHERE Salary > 50000) LOOP
    emp_first_name := emp.FirstName;
    emp_last_name := emp.LastName;
    emp_salary := emp.Salary;
    -- Do something with the employee data (e.g., print or process further)
    DBMS_OUTPUT.PUT_LINE('Name: ' || emp_first_name || ' ' || emp_last_name || ', Salary: ' || emp_salary);
  END LOOP;
END;
/

In this PL/SQL example, we declare variables (emp_first_name, emp_last_name, and emp_salary) to store the employee data. We use a FOR loop to iterate over the result set obtained from the SQL query. Inside the loop, we assign the values from the current row to the variables and perform some actions with the data.

In this case, we simply display the employee's name and salary using the DBMS_OUTPUT.PUT_LINE procedure. PL/SQL allows us to encapsulate procedural logic, handle exceptions, and perform operations on data retrieved through SQL queries.

To summarize, SQL is used for querying and manipulating data directly in the database, while PL/SQL is an extension of SQL that allows the creation of procedural logic within the database environment. SQL focuses on data retrieval and manipulation, while PL/SQL enables the development of complex business logic and procedures for more advanced database programming.