EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Clauses
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

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 ;

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,584 ratings)

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
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more