Introduction

Oracle clauses are conditional keywords or sentences that allow you to query a database or table based on specific parameters. ‘Select’ fetches the contents of the database, ‘from’ mentions the table name, ‘where’ adds conditions to the query, ‘order by’ sorts the results, ‘group by’ groups the requested material, ‘having’ limits the results of a group by the statement, and so on.

Oracle DISTINCT

Only distinct (different or unique) results are returned using the SELECT DISTINCT command.

A column in a table frequently has multiple duplicate values, and you may only wish to list the difference (distinct) values.

Example

SELECT DISTINCT Country FROM Continents;

Oracle FROM

In a Select statement, the FROM clause is required. The FROM clause defines the tables from which data or columns will be accessed for use in select expressions.

Syntax of FROM clause

FROM Table_Name [Table1,Table2,Table(n)] *

Example

Select * from employee;

Oracle ORDER BY

In Oracle, the keyword or clause ‘ORDER BY’ is used to sort the data being searched in ascending or descending order, with ASC added at the end of the sentence ASC is for ascending order, whereas DESC is for descending order

SELECT columns FROM tables
[where conditions] ORDER BY column[DESC or ASC];

Example

SELECT employee_id, name FROM employee ORDER BY NAME ASC. 

Parameters

The following are some of the most important order by parameters in Oracle:

• columns/expression:We want to extract the column names as well as any other calculations based on expressions (aggregate functions).
• tables:The names of the tables from which we want to get information.
• [where conditions]:t’s an optional clause. If it is specified, the query will only return records that satisfy the criteria specified in the WHERE clause.
• DESC:To sort the list of results in descending order.
• ASC:To sort the list of results in ascending order.

Oracle GROUP BY

The GROUP BY Oracle clause is used to sort a table’s rows into groups. It’s used in the SELECT statement to group rows by column or expression values. The GROUP BY clause organizes the results based on the value of one or more columns.

The Syntax for Group by

SELECT Column_1, Column_2,…, Column-N FROM Table_Name WHERE condition(s)
GROUP BY Column_Name(s) ORDER BY Column(S);

Example

SELECT Deptno, AVG (Sal) FROM Emp GROUP BY Deptno;

Oracle HAVING

In a Select Expression, a HAVING clause limits the results of a GROUP BY. In the same way that a WHERE clause is applied to a select list, the HAVING clause is applied to each group of the grouped table. And then  HAVING clause is used to the entire result as a single group if there is no GROUP BY clause. Any column that does not contain a GROUP BY clause cannot be referenced directly in the SELECT clause. Constants, aggregates, and special registers can all be referred to with this term.

Example

SELECT state, COUNT (*) AS “Number of customers”

FROM employees

WHERE salary > 10000

GROUP BY role

HAVING COUNT (*) >= 2;

Oracle Operators

Individual data elements are manipulated by an operator, who then returns a result. Operands or arguments are the data pieces. Special characters or keywords are used to represent operators. The null operator is represented by the phrase IS NULL, while the multiplication operator is represented by an asterisk (*)Unary and binary operators are the two types of operators. Set operators are also supported by Oracle Database Lite SQL.

Oracle Database Lite SQL Operators Order of Precedence

Oracle Union

It returns all distinct rows selected by either query.

Example

SELECT * FROM

(SELECT ENAME FROM EMPLOYEES WHERE JOB = ‘HR’)

UNION

SELECT ENAME FROM EMP WHERE JOB = ‘ANALYST’);

Oracle Union All

It returns all duplicates as well as distinct rows picked by the query.

Example

SELECT * FROM

(SELECT SAL FROM EMP WHERE JOB = ‘HR’

UNION ALL

SELECT SAL FROM EMP WHERE JOB = ‘ANALYST’);

Oracle Intersect

It returns all unique rows that both queries have picked.

Example

SELECT * FROM orders_list1

INTERSECT

SELECT * FROM orders_list2;

Oracle Minus

The first query returns all distinct rows. However, the second query does not.

Example

SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = ‘PRESIDENT’

MINUS

SELECT SAL FROM EMP WHERE JOB = ‘MANAGER’);