Table of Contents
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.
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.
SELECT DISTINCT Country FROM Continents;
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)] *
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];
SELECT employee_id, name FROM employee ORDER BY NAME ASC.
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);
SELECT Deptno, AVG (Sal) FROM Emp GROUP BY Deptno;
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.
SELECT state, COUNT (*) AS “Number of customers”
WHERE salary > 10000
GROUP BY role
HAVING COUNT (*) >= 2;
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
|Precedence Level||SQL Operators|
|1||+, – arithmetic operators, PRIOR operator|
|2||* / arithmetic operators|
|3|||| character operators, binary + , – arithmetic operations|
|4||All comparison operators|
|5|| NOT logical operator|
|6||AND logical operator|
|7||OR logical operator|
It returns all distinct rows selected by either query.
SELECT * FROM
(SELECT ENAME FROM EMPLOYEES WHERE JOB = ‘HR’)
SELECT ENAME FROM EMP WHERE JOB = ‘ANALYST’);
Oracle Union All
It returns all duplicates as well as distinct rows picked by the query.
SELECT * FROM
(SELECT SAL FROM EMP WHERE JOB = ‘HR’
SELECT SAL FROM EMP WHERE JOB = ‘ANALYST’);
It returns all unique rows that both queries have picked.
SELECT * FROM orders_list1
SELECT * FROM orders_list2;
The first query returns all distinct rows. However, the second query does not.
SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = ‘PRESIDENT’
SELECT SAL FROM EMP WHERE JOB = ‘MANAGER’);