clause

ORDER BY Clause in Oracle SQL – PLSQL

October 25, 2012 by techhoneyadmin

The ORDER BY clause in Oracle SQL / PLSQL is used in SELECT statement and allows us to sort the records fetched by SELECT statement.

Syntax for the ORDER BY clause in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE conditions
ORDER BY column(s) ASC/DESC;

The ORDER BY clause will sort the result in ascending order (if ASC is mentioned) or in descending order (if DESC is mentioned) after the column_name in ORDER BY clause.
If nothing is mentioned after the column_name in ORDER BY clause, by default ascending order (ASC) is taken into consideration.

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Suppose we want to see records from ‘employee’ table sorted by ‘department’

We can achieve the same as:

1SELECT *
2FROM employee
3ORDER BY department;

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
105	Emp E	32000	Sales	10
101	Emp A	10000	Sales	10
104	Emp D	30000	Support

Here we can see that the records have been fetched as per ascending order of the ‘department’.

Scenario 2:

Suppose we want to see the list of employees in reverse order of ‘employee_id’
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

We can achieve the same as:

1SELECT *
2FROM employee
3ORDER BY employee_id  DESC;

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
105	Emp E	32000	Sales	10
104	Emp D	30000	Support
103	Emp C	28000	IT	20
102	Emp B	20000	IT	20
101	Emp A	10000	Sales	10

Here we can see that the records have been fetched as per descending order of ‘employee_id’.

HAVING Clause in Oracle SQL – PLSQL

October 25, 2012 by techhoneyadmin

The HAVING clause in Oracle SQL / PLSQL is used in SELECT statement along with the GROUP BY clause and allows us to filter the records fetched by GROUP BY clause based on one or more than one condition.

Syntax for the HAVING clause in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
,column_name3
.
.
column_nameN
Aggregate_function(value/expression)
FROM table_name
WHERE conditions
GROUP BY column_name1
,column_name2
.
.
column_nameN
HAVING condition1
,condition2
.
.
conditionN;

The aggregate function can be SUM(), MIN(), MAX() or COUNT().

Let’s take an example for understanding:

Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Suppose we want to see how much ‘Salary’ is being given to which department, but the total ‘Salary’ being given to a department should be more than ‘30000’.

We can achieve the same as:

1SELECT department
2       ,SUM(Salary) Total_Salary
3FROM employee
4GROUP BY department
5HAVING SUM(salary) > 30000;

The result of the above query will be:

Department	Total_Salary
IT	48000
Sales	42000

Here we have retrieved the records explaining us that ‘48000’ in total is being given to employee(s) working in the ‘IT’ department and ‘42000’ is being given to employee(s) working in the ‘Sales’ department by using HAVING clause with GROUPY BY clause in SELECT statement.

Also, note that ‘Total_Salary’ after the aggregate function ‘SUM(salary)‘ acts as an alias name for the column in query result.

Scenario 2:

Suppose we want to see the departments where more than ‘1’ employees are working.
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

We can achieve the same as:

1SELECT department
2       ,COUNT(employee_id) Number_Of_Employees
3FROM employee
4GROUP BY department
5HAVING COUNT(employee_id) > 1;

Once we have run the above code following will be the result:

Department	Number_Of_Employees
IT	2
Sales	2

Here we have successfully retrieved the records pertaining to departments where more than ‘1’ employees are working using HAVING clause with GROUP BY clause in SELECT statement.

Also, note that ‘Number_Of_Employees’ after the aggregate function ‘COUNT(employee_id)‘ acts as an alias name for the column in query result.

GROUP BY Clause in Oracle SQL – PLSQL

October 25, 2012 by techhoneyadmin

The GROUP BY clause in Oracle SQL / PLSQL is used in SELECT statement and allows us to filter the records by grouping them as per one or more columns.

Syntax for the GROUP BY clause in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
,column_name3
.
.
,column_nameN
Aggregate_function(value/expression)
FROM table_name
WHERE conditions
GROUP BY column_name1
,column_name2
.
.
column_nameN;

The aggregate function can be SUM(), MIN(), MAX() or COUNT().

Let’s take an example for understanding:

Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Suppose we want to see how much ‘Salary’ is being given to which department.

We can achieve the same as:

1SELECT department
2       ,SUM(Salary) Total_Salary
3FROM employee
4GROUP BY department;

The result of the above query will be:

Department	Total_Salary
Support	30000
IT	48000
Sales	42000

Here we have retrieved the records explaining us that ‘30000’ in total is being given to employee(s) working in the ‘Sales’ department, ‘48000’ in total is being given to employee(s) working in the ‘IT’ department and ‘42000’ is being given to employee(s) working in the ‘Sales’ department using GROUP BY clause in SELECT statement.

Also, note that ‘Total_Salary’ after the aggregate function ‘SUM(salary) ‘ acts as an alias name for the column in query result.

Scenario 2:

Suppose we want to see the maximum ‘Salary’ being given in every department
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

We can achieve the same as:

1SELECT department
2       ,MAX(salary) Maximum_Salary
3FROM employee
4GROUP BY department;

Once we have run the above code following will be the result:

Department	Total_Salary
Support	30000
IT	28000
Sales	32000

Here we have successfully retrieved the records pertaining to maximum salary being given in each department using GROUP BY clause in SELECT statement.

WHERE Clause in Oracle SQL – PLSQL

October 23, 2012 by techhoneyadmin

The WHERE clause in SQL / PLSQL allows us to filter the records from the result of a query; WHERE clause can be used with a SELECT, INSERT, UPDATE and DELETE statements.

Syntax for the WHERE clause in SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE conditions;

INSERT INTO table_name
VALUES(column_name1
,column_name2
,column_name3
.
.
,column_nameN)
WHERE conditions;

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
,column_name3 = value/expression
.
.
)
WHERE conditions;

DELETE FROM table_name
WHERE conditions;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Suppose we want to see the records of only ‘Sales’ department then we can achieve the same using WHERE clause as follows:

1SELECT *
2FROM employee
3WHERE department = 'Sales';

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
105	Emp E	32000	Sales	10

Here we have retrieved the records pertaining to ‘Sales’ department only using WHERE clause.

Scenario 2:

WHERE clause can also be used with combination of columns
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Suppose we want to view all the records from the departments ‘Sales’ and ‘IT’.

We can achieve the same as:

1SELECT *
2FROM employee
3WHERE department = 'Sales'
4OR    department ='IT';

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
105	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to ‘Sales’ and ‘IT’ department using WHERE clause.

DISTINCT Clause in Oracle SQL – PLSQL

October 23, 2012 by techhoneyadmin

The DISTINCT clause in SQL / PLSQL allows us to remove duplicate records from the result of a query. It can be used only with a SELECT statement.

Syntax for the DISTINCT clause in PLSQL is:

SELECT DISTINCT column_name(s)
FROM table_name
WHERE conditions;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Here we can see that ‘Sales’ and ‘IT’ departments occurs twice in the department column of ‘employee’ table.
If we wish to view only unique departments from the employee table we can achieve the same by using the DISTINCT clause as:

`1`	`SELECT` `DISTINCT` `department`

`2`	`FROM` `employee;`

The result of the above query will be:

Department

Sales

Support

Here we have retrieved only unique department names from the ‘employee’ table and eliminated the duplicates using DISTINCT clause.

Scenario 2:

DISTINCT clause can also be used with combination of columns.
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we can see that we have 2 occurrences of ‘Sales’ department and the corresponding commission for them is 10.
If we want to get all the unique departments along with their commissions we can achieve the same as:

1SELECT DISTINCT department
2                ,commission
3FROM employee;

Once we have run the above code following will be the result:

Department	Commission
Sales	10
IT	20
Support

Here we have successfully retrieved the unique combination of columns using the DISTINCT clause.