Table of Contents
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
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 |
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’);
0 Comments