PL/SQL SELECT FROM

PL/SQL is Oracle procedural extension for SQL which allows for more powerful and flexible database manipulation. The SELECT statement is one of the most fundamental and widely used commands in SQL. It allows users to retrieve data from one or more tables in a database. In this article, we will learn about PL/SQL SELECT FROM with the help of examples and their outputs.

PL/SQL SELECT FROM

• The SELECT FROM statement in PL/SQL is used to retrieve data from one or more tables in a database.
  
• The SELECT clause specifies the columns that we want to retrieve, while the FROM clause specifies the tables from which to retrieve the data.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Key terms:

• SELECT: Specifies the columns we want to retrieve.
  
• FROM: Specifies the table from which to retrieve the data.
  
• WHERE (Optional): Filters records based on a specified condition

Examples of PL/SQL SELECT FROM

Employees Table

This query creates an employees table with five columns: employee_id (which is the primary key), first_name, last_name, department_id, and salary. Each column is defined with a data type that specifies the kind of data it can store.

Insert statements insert three records into the employees table. Each INSERT command adds a new employee with specified details such as employee_id, first_name, last_name, department_id, and salary

Query:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    department_id NUMBER,
    salary NUMBER
);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (1, 'John', 'Doe', 101, 50000); 
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (2, 'Jane', 'Smith', 102, 60000); 
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (3, 'Michael', 'Johnson', 101, 55000);

Output:

Explanation:

• This set of SQL queries demonstrates how to create a table in PL/SQL, insert records into the table, and structure the data.
  
• The employees table now holds data about three employees, including their unique IDs, names, associated departments, and salaries.
  
• This setup forms the basis for further queries that can retrieve or manipulate this data.

Department Table

This query creates a departments table with two columns: department_id (the primary key) and department_name. The department_id uniquely identifies each department, while department_name stores the name of the department

These statements insert three records into the departments table, each representing a department with its unique ID and name.

Query:

CREATE TABLE departments (     
       department_id NUMBER PRIMARY KEY,    
       department_name VARCHAR2(50)
 );

INSERT INTO departments (department_id, department_name) VALUES (101, 'HR');
INSERT INTO departments (department_id, department_name) VALUES (102, 'Finance');
INSERT INTO departments (department_id, department_name) VALUES (103, 'IT');

Output:

Explanation:

• The query creates a departments table and inserts three rows representing different departments: HR, Finance, and IT. Each department is uniquely identified by a department_id.
  
• The output shows that the table now holds the department IDs and names as specified by the inserted records. This structure allows for easy referencing of departments in future queries.

Example 1: Select All Fields from One Table

This query retrieves all columns (* wildcard) and records from the employees table. The SELECT * statement is commonly used when you need to view all data stored in a table without filtering any columns or rows.

Query:

SELECT * 
FROM employees;

Output

Explanation:

• The output will display all the records and fields from the employees table.
  
• Each row represents an employee with details such as employee_id, first_name, last_name, department_id, and salary.
  
• This provides a complete view of the data stored in the employees table without any filters.

Example 2: Select Individual Fields from One Table

This query retrieves only the first_name and salary columns from the employees table. By specifying the column names directly, you can narrow down the results to only the data you need, which is useful for focusing on specific aspects of the data.

Query:

SELECT first_name, salary
FROM employees;

Output:

Explanation:

• The output displays only the first_name and salary fields for each employee.
  
• This allows you to see the names of the employees along with their corresponding salaries, without including other columns like employee_id or department_id.
  
• It provides a focused view of the relevant data, making it easier to analyze specific information.

Example 3: Select Fields from Multiple Tables

This query retrieves the first_name of each employee from the employees table and the corresponding department_name from the departments table. It uses an inner join to match records from both tables based on the department_id.

Query:

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Output:

Explanation:

• The output displays the first names of the employees along with the names of the departments they belong to.
  
• This result is achieved by joining the employees and departments tables on the department_id.
  
• It allows you to see how employees are distributed across different departments, providing a comprehensive view of the organization’s structure.

Conclusion

The SELECT FROM statement is a powerful tool in PL/SQL for retrieving data from one or more tables in a database.By mastering the use of the SELECT and FROM clauses, you can efficiently query data, whether you're working with individual fields or joining multiple tables. Understanding these basic operations is essential for anyone working with PL/SQL and Oracle databases.