Oracle SELECT Statement

➔ The Oracle SELECT statement is mainly used to retrieve data from one or more tables, views, or materialized views.

➔ This is a basic command for querying data in any database, including Oracle databases.

➔ The SELECT statement can be used to retrieve specific rows and columns from a database table, with or without a filter clause.

➔ When executing a SELECT statement, Oracle actually evaluates the FROM clause before the SELECT clause.

Basic Syntax

SELECT column1, column2 FROM table_name;

The SELECT statement is very complex and contains many clauses.

➔ SELECT: Determines which columns or expressions will be retrieved.

➔ FROM: Specify the table containing the data.

➔ WHERE: (Optional) Filters or conditions can be applied to the query.

➔ GROUP BY: (Optional) This clause groups the result set into subgroups based on matching values ​​in one or more columns.

➔ HAVING: (Optional) This clause is used to apply conditions or filters to different groups of data.

➔ ORDER BY: (Optional) This clause is used to sort records in ascending or descending order.

Column aliases.

A column alias is a temporary column name that is assigned to a column during the execution of a query. Multiple words are enclosed in double quotes and the AS keyword is optional.

SELECT first_name "EMP Name", salary AS INCOME
FROM hr.employees WHERE salary > 14000;

Common Example of Oracle SELECT statement:

This query SELECT all columns and available rows from the table.

SELECT * FROM hr.employees; 

This query retrieves first_name and salary column for employees whose salary is over 15000.

SELECT first_name, salary FROM hr.employees WHERE salary > 15000;

Retrieving Top N data:

This query retrieves top 5 data of first_name and salary column for employees whose salary is over 12000.

SELECT first_name, salary FROM hr.employees WHERE salary > 12000
FETCH FIRST 5 ROWS ONLY; 

Querying data from two tables using INNER JOIN

➔ Oracle INNER JOIN is used to combine rows from two or more tables and return only those records that meet the join conditions in both tables.

➔ If a row in one table does not have a corresponding match in the other, it will be omitted from the resulting data.

➔ INNER JOIN is used to find rows where the same value exists in both tables.

SELECT 
    empt.first_name, 
    empt.last_name, 
    dept.department_name
FROM 
    hr.employees empt INNER JOIN hr.departments dept 
    ON empt.department_id = dept.department_id;