EDUCBA

Home Data Science Data Science Tutorials Oracle Tutorial Oracle Clauses

Oracle Clauses

By Afshan BanuAfshan Banu

Oracle Clauses

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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–

  1. Oracle Warehouse Builder
  2. What Is Oracle Database
  3. Career In Oracle Database Administrator
  4. Guide to Top Oracle Versions
  5. Oracle Operators | Top 7
  6. Complete Guide to ROLLUP in MySQL
0 Shares
Share
Tweet
Share