Updated March 22, 2023
Introduction to Oracle Clauses
Oracle clauses are the conditional keywords or statements that can be included for querying the database or the table based on the requirements. A few of the typically used oracle clauses are ‘Select’ for fetching the contents of the table, ‘from’ for mentioning the table name, ‘where’ for adding the conditions in the query, ‘order by’ for sorting the resulting data, ‘group by’ for grouping the queried content, ‘having’ for limiting the results of group by statement, etc.
The list of the clause which is present in oracle is given below –
- FROM Clause: It is a mandatory part in a SelectThe FROM clause specifies the name of a table from where the data is to be accessible.
- CONSTRAINT Clause: It is an optional clause of a CREATE TABLE statement. A CONSTRAINT clause defines a rule which is to be satisfied while inserting the data.
- WHERE Clause: It is an optional clause of a Select statement or update statement or DELETE statement. The WHERE clause specifies which rows are to be select based on a condition.
- ORDER BY Clause: It is an optional clause of the select statement or a CREATE VIEW statement or an INSERT statement. An ORDER BY clause specifies in which order the result set rows to appear.
- FOR UPDATE Clause: It is an optional clause of a SELECT statement. The cursors are by default read-only. The cursor should be updatable specifies in the FOR UPDATE clause, the FOR UPDATE clause enforces a check SELECT statement during compilation to meets the condition for a cursor to be updatable.
- GROUP BY Clause: It is an optional clause of a Select statement, the GROUP BY clause subsets a result into groups.
- HAVING Clause: It is an optional clause of a select statement. The HAVING clause restricts the group selection which are the results of a GROUP BY clause.
- The result offset and fetch first Clauses: The result offset clause use to skip the N first rows and select the remaining rows as a result set. The fetch first clause use along with the result offset clause to limits the number of rows selected in the result set.
- USING Clause: It is a mandatory clause in the join operation. The USING clause shows which columns equality is to be checked to join the two tables.
- WHERE CURRENT OF Clause: It is an optional clause in a delete or UPDATE statement. The WHERE CURRENT OF clause specifies location which deletes on updatable cursors or to updates.
List of Oracle Clause
Next, let’s understand each clause in details –
1. FROM Clause
FROM clause is a mandatory part in a Select statement. The FROM clause specifies the name of tables from where the data or columns are to be accessible for use in select expressions.
Syntax of FROM clause
FROM Table1 [ , Table2 ] *
Query Examples
Select * from employee ;
Query example with where clause condition
select d.dep_id
from department as d
where dep_id< 10
Query example with an order by clause
select st .tablename, sc .isindex
from sys.systables st, sys.sysconglomerates sc
where st.tableid = sc.tableid
order by tablename, isindex
Query example with a join condition
select *
from flights f, flightavailability fa
where fa.flight_id = f.flight_id
and fa.segment_number = f.segment_number
and f.flight_id < 115
2. CONSTRAINT Clause
CONSTRAINT clause is an optional clause of a CREATE TABLE statement. A CONSTRAINT clause defines a rule which is to be satisfied while inserting the data.
The CONSTRAINTs are different at a different level, as given below –
1. Column – level constraint
Column – level constraints enforce the rule to a single column in the table. The Column – level constraints are given below –
- NOT NULL: It specifies that the column cannot store NULL values.
- PRIMARY KEY: It Specifies that the column values should be unique, which farther can be used to identify a row in the table. The PRIMARY KEY implicitly specifies NOT NULL.
- UNIQUE: It specifies that the column values must be unique.
- FOREIGN KEY: It specifies that the column values must be referenced as the primary key.
- CHECK: It specifies rules for the column values.
2. Table-level constraint
Table-level constraints enforce the rule to one or more columns in the table. The table-level constraints are the same as the column-level constraint but the difference is where the constraint is specified.
Query Examples
Example of column-level primary key constraint named did_pk –
create table department
(
Deptid number constraint did_pk primary key,
Dname char(10),
Location char(80) not null,
) ;
Example for table-level primary key constraint named did_pk
create table department
(
Deptid number constraint did_pk primary key,
Dname char(10),
Location char(80) not null,
primary key (Deptid)
);
3. WHERE Clause
This is is an optional clause of a Select statement or update statement or DELETE statement. The WHERE clause specifies which rows are to be select based on a condition. Only those rows return or delete or update where the condition or expression evaluates to TRUE.
Syntax of Where Clause
WHERE Booleanexpression
Example
Where clause example
select *
from flight
where business_taken_seats is null
or business_taken_seats = 0
Where clause example for join multiple tables
select a.*, last name
from emp_act as a, employee as e
where a.empno = e.empno ;
4. ORDER BY Clause
An ORDER BY clause is an optional clause of the SELECT statement or a CREATE VIEW statement or INSERT statement or a Scalar Subquery or a Table Subquery. The clause specifies the order in which the result set rows to appear.
Syntax of the ORDER BY clause
ORDER BY { columnName | Expression | ColumnPosition }
[ ASC (default) | DESC ] ;
The columnName Refers to the column names by which the result set to order. The ColumnPosition is an integer that specifies the column position in the Select Items in the query of the SELECT statement. The Expression is numeric, datetime and string expressions. The ASC Specifies ascending order. The DES specifies descending order.
Order by example with a correlation name
In the below select statement, the location column has the correlation name country which is used in the order by clause –
Select name, location as country
From employee
Order by country
Order by example with a numeric expression
In the below select statement the order by clause use expression salary + hike –
Select name, salary, hike from emp
Order by salary + hike
Order by example with a function
In the below select statement the order by clause use function to specify the position –
Select a, len from calculation
Order by sin(a)
Order by example specifying with null ordering
You can specify the position of null values using the null ordering specification:
Select * from table1 order by column1 asc nulls last
5. FOR UPDATE Clause
FOR UPDATE clause enforces a check SELECT statement during compilation to meets the condition for a cursor to be updatable.
Syntax of FOR UPDATE clause
FOR
{
FETCH ONLY | READ ONLY | UPDATE [ OF columnName [ , columnName]* ]
}
The columnName specifies in the FROM clause of the query.
FOR UPDATE clause example
select eid, name, salary, deptid from emphist for update
6. GROUP BY Clause
This clause returns a subset of groups result.
Syntax of GROUP BY Clause
GROUP BY
{
columnName [ , columnName ]*
|ROLLUP ( column-Name [ , column-Name ]* )
}
Examples
Find the average salary of an employee grouped by deptid
select avg (salary), deptid
from employee
group by deptid ;
select max (salary), deptid
from employee
group by deptid ;
7. HAVING Clause
The HAVING clause restricts the group selection defines by the GROUP BY clause.
Syntax of HAVING Clause
HAVING Condition
Example of HAVING clause restricts the group selection for avg (salary) –
select avg (salary), deptid
from employee
group by deptid having avg(salary) > 50000;
The below-given query is illegal because the deptid column is not a grouping column –
select avg (salary), deptid
from employee
group by deptid having deptid > 5;
The result offset and fetch first clauses
The result offset and fetch first clauses use to skip the N first rows and fetch first clause use to limits the number of rows selected in the result set.
Syntax of The result offset and fetch first clauses –
OFFSET { integer } {ROW | ROWS}
FETCH { FIRST | NEXT } [ integer ] {ROW | ROWS} ONLY
Examples
Fetch the first row of T
Select * from employee fetch the first row only
8. Using Clause
The using clause uses to join the two tables based on the condition.
Syntax of using clause
Using ( columnname [,columnname ]* )
Examples
In the example, the countries table and the cities table join on the condition as cities.country is equal to countries. country –
Select * from employee join department
using (eid)
9. WHERE CURRENT OF Clause
The WHERE CURRENT OF clause specifies location which deletes on updatable cursors or to updates.
The Syntax of WHERE CURRENT OF clause
WHERE CURRENT OF cursorName
Example
Statement stmt = conn.createStatement();
stmt.setCursorName("employeeres");
ResultSet res = conn.executeQuery(
"SELECT ename, salary FROM employee FOR UPDATE OF salary");
Statement stmt1 = conn.createStatement();
stmt1.executeUpdate("UPDATE employee SET salary = salary +10000 WHERE CURRENT
OF employeeres");
Recommended Articles
This is a guide to Oracle Clauses. Here we discuss the List of Oracles Clause with the examples and syntax with the resp[ective results. Clauses in oracle are keywords which use for the specific purpose or which specify the special meaning. You can also go through our other suggested articles to learn more–
