Clauses In Oracle

What are Clauses in Oracle:

For execution, a specific part of  a SQL statement when you write an additional instruction or condition is called Clause.

Following are clauses in Oracle:

1. Select Clause:

The select clause is used to retrieve data from one or more tables.

Syntax:

Select column1, column2

From Tablename;

For Example,

Select Rollno, Name, Class, Div

From Student;

In this example only the Rollno, Name, Class and Division will be retrieved from the Student table.

Another Example,

Select * from Tablename;

This query is used to retrieve all the rows from the table.

For Example,

Select * From Student;

After executing this query all the rows from the student table will be retrieved and displayed on screen.

2. From Clause:

From clause is used to select or retrieve the data from a table or tables.

Syntax:

Select * From Tablename;

Here the From clause is used to retrieve data from the table name.

For example,

Select * From Student;

Here entire rows will be selected from the Student table.

3. WHERE CLAUSE:

WHERE clause is used to retrieve data from a table with a specified condition.

Syntax:

Select Column1, column2, column3

From Tablename

Where Condition;

USe select query to retrieve data from different columns, the use From clause to select the table and the use Where clause to write  a specified condition to retrieve data from the different columns based on the condition.

For Example,

Select Name, Class, Div

From Student

Where Rollno=45;

In the above example the Name, Class and Division will be retrieved from the Student table whose Rollno is 45.

Another Example,

Select * from Employee

Where Designation =’Manager’;

Here all the rows of the Employee table will be retrieved whose Designation is Manager.

4. ORDER BY Clause:

The ORDER BY clause is used to sort the data in Ascending or Descending order.

DESC and ASC keywords used to sort the data in Descending and Ascending order. If do not Explicitly define sorting then by default it will retrieve data in Alphabetical order i.e. Ascending order.

Syntax:

Select Column1, column2

From Tablename

ORDER BY Column[ASC/DESC];

For Example,

SQL> select name from student

  2  order by rollno;

NAME

------------

Manisha

Seeta

In above query Name is retrieved from Student table order by Rollno. Here ASC or DESC order is not mentioned, so Database engine will retrieve names by default in Alphabetical i.e. Ascending order.

Another Example,

SQL> select * from student

  2  order by Rollno desc;

ROLLNO NAME         CLASS      DIV

------ ------------ ---------- ----------

BCA02  Seeta                   A

BCA01  Manisha      BCA        A

In the above example all rows from the student table are retrieved by sorting the Rollno order by Descending order.

5. GROUP BY Clause:

The GROUP BY clause is used to group the rows which are based on one or more columns and apply the aggregate function.

Syntax:

Select column1, aggregate_function(column2)

From tablename 

Group By column1;

For Example,

SQL> select e_address, avg(salary)

  2  from employee

  3  group by e_address;

E_ADDRESS                 AVG(SALARY)

------------------------- -----------

Satara                          20000

Karad                           35000

Katraj                          10000

Another Example,

SQL> select designation,avg(salary)

  2  from employee

  3  group by designation;

DESIGNATION     AVG(SALARY)

--------------- -----------

Manager               25000

Another Example,

SQL> select designation, sum(salary) as Total_salary

  2  from employee

  3  Group by designation;

DESIGNATION     TOTAL_SALARY

--------------- ------------

Manager               100000

6. HAVING Clause:

The HAVING clause is used to filter the result of the ORDER BY clause by using aggregate function conditions.

Syntax:

Select column1, aggregate_function(column2)

From Tablename

GROUP BY Column1 HAVING Condition;

For Example,

SQL> select * from student;

ROLLNO NAME         CLASS      DIV       INT_MARKS  EXT_MARKS

------ ------------ ---------- ---------- ---------- ----------

BCA01  Manisha      BCA        A                  26         65

BCA02  Seeta        BBA           A                  28                66

BCA03  Rohan        BA            C                  22         45

BCA04  Rakesh       BA            C                  24        55

BCA05  Rupesh       BCA         A                  23         50

SQL> select name, avg(ext_marks ) from student

  2  group by name

  3  having avg(ext_marks)>50;

NAME         AVG(EXT_MARKS)

------------ --------------

Manisha                 65

Seeta                    66

Rakesh                  55

Here in the above example the students' names are selected from the student table by grouping the average external marks and the having clause is used to retrieve the grouped data as per the given condition i.e. average external marks greater than 50.