Where, Group by, Having & Order by

Conditions in select queries:-

When we are required to fetch data according to our requirement, that time we can use many oracle supplied conditions to get the desired output. We can use where clause, group by clause, having clause and order by clause and many more clauses.

What is meant by “where clause”?

We can fetch only desired records by putting where condition in select statement. After specifying table name we can specify where condition.

Syntax for where condition is:-

SELECT COLUMN_LIST FROM TABLE_NAME WHERE CONDITION;

Suppose we have a table called EMPLOYEE and we are required to fetch employees whose employee number is 10:-

SELECT * FROM EMPLOYEE WHERE EMPNO=10;

What is meant by “group by clause”?

When we use aggregate functions in a select query we need to specify group by clause when we use other columns in select query.

Syntax for group by clause is:-

SELECT COLUMN_NAME1, COLUMN_NAME2, MIN/MAX/SUM/COUNT/AVG (COLUMN_NAME) FROM TABLE_NAME WHERE CONDITION GROUP BY COLUMN_NAME1, COLUMN_NAME2;

Suppose, we need to find out employee number with maximum salary we can execute below mentioned query:-

 

What is meant by “having clause”?

Having clause is use after group by clause. HAVING clause restricts the results of a GROUP BY in a Select Expression.

It specifies a search condition for a group or an aggregate function used in select statement .The having clause cannot be used without the GROUP BY clause.

It select rows after grouping and it contain aggregate functions.

Syntax for the having clause is:-

SELECT COLUMN_NAME_LIST, MIN/ MAX/ SUM/ AVG/ COUNT (COLUMN_NAME) FROM TABLE_NAME WHERE CONDITION GROPU BY COLUMN_NAME1, COLUMN_NAME2 HAVING COLUMN_NAME CONDITION;

We can use having condition like:-

What is meant by “order by clause”?

Order by clause will help us to order the output in ascending or descending orders. By default its ascending order.

It is always the last clause in a select query.

Syntax for using order by clause is:-

SELECT COLUMN_LIST FROM TABLE_NAME ORDER BY ASC / DESC;

We can use order by condition like:-

Order by descending:-

 

Order by ascending:-

Order by ascending (by default):-

nd: wG . ; s x�Y ��W f:yes'>

Click OK.

Oracle sql developer:-

Install oracle sql developer or click on sql developer.exe file, it will show you initialization process:-

Click on

Once you click on  you will land onto the below screen. Type the name of the connection as you want, type the username, password, and then click on save password checkbox, type host name or ip_address of the machine, specify the port number and database SID or Service name, after putting all the information click on test  or you can directly click on connect button:-

Once you click on save button, the connection will be saved in the connection tab so you can use it again and again. You can see below the name CONNECTION_TO_SCOTT is present in the connection tab.

If you want to add more connection then just click on  and put all the required information. After you saved it, it will be listed in connection tab.

Conditional Statement