Oracle

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:

SELECT department
       ,SUM(Salary) Total_Salary
FROM employee
GROUP BY department
HAVING 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:

SELECT department
       ,COUNT(employee_id) Number_Of_Employees
FROM employee
GROUP BY department
HAVING 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:

SELECT department
       ,SUM(Salary) Total_Salary
FROM employee
GROUP 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:

SELECT department
       ,MAX(salary) Maximum_Salary
FROM employee
GROUP 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.

LIKE Condition in Oracle SQL -PLSQL

October 24, 2012 by techhoneyadmin

The LIKE condition in Oracle SQL / PLSQL is used in WHERE clause to place wildcard characters while fetching records.
LIKE condition can be used with SELECT, INSERT, UPDATE and DELETE in SQL statements.

LIKE condition have 2 flavors:
1. % – allows us to match string of any length including zero length.
2. _ allows us to match only a single character.

Syntax for the LIKE condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE condition;

INSERT INTO table_name
VALUES(column_name1
,column_name2
,column_name3
.
.
column_nameN)
WHERE column_name LIKE condition;

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

DELETE FROM table_name
WHERE column_name LIKE condition;

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:

Using ‘%’ in LIKE condition
Suppose we want to see the records of employee(s) who work in departments that starts with ‘S’.

The above can be achieved as:

SELECT *
FROM employee
WHERE department LIKE 'S%';

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we have retrieved the records pertaining to employee(s) who work in departments starting with ‘S’ using LIKE condition in WHERE clause.

Note that the % sign after ‘S’ in query acts as a wildcard character for String of any length.

Scenario 2:

Using ‘_’ in LIKE condition
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
114	Emp E	32000	Sales	10

Suppose we want to see the records of employee(s) whose ‘employee_id’ is of 3 characters which starts with ‘1’ and ends with ‘4’

We can achieve the same as:

SELECT *
FROM employee
WHERE employee_id LIKE '1_4';

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

Employee_ID	Employee_Name	Salary	Department	Commission
104	Emp D	30000	Support
114	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to employee whose ‘employee_id’ has 3 characters that starts with ‘1’ and ends with ‘4’ using LIKE condition in WHERE clause.

EXISTS Condition in Oracle SQL – PLSQL

October 24, 2012 by techhoneyadmin

The EXISTS condition in Oracle SQL / PLSQL will return any records whenever the “exists” condition is met.
EXISTS condition can be used with SELECT, INSERT, UPDATE and DELETE SQL statements.

Syntax for the EXISTS condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

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

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

DELETE FROM table_name
WHERE EXISTS (subquery);

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

And suppose we also have a table named “comm” as shown below:

Emp_ID	Commission_Percent
101	10
102	20
103	20

Scenario 1:

Suppose we want to see the records of employee(s) from ‘employee’ table that also have records in ‘comm’ table.

The above can be achieved as:

SELECT *
FROM employee
WHERE EXISTS(SELECT *
             FROM comm
             WHERE employee.employee_id = comm.emp_id
             );

The result of the above query will be:

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

Here we have retrieved the records pertaining to employee(s) from ‘employee’ table that also have records in ‘comm’ table using EXISTS condition along with WHERE clause.

Scenario 2:

EXISTS condition can also be used with NOT operator.
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

And suppose we also have a table named “comm” as shown below:

Emp_ID	Commission_Percent
101	10
102	20
103	20

Suppose we want to see the records of employee(s) from ‘employee’ table that do not have records in ‘comm’ table.
We can achieve the same as:

SELECT *
FROM employee
WHERE NOT EXISTS(SELECT *
                 FROM comm
                 WHERE employee.employee_id = comm.emp_id
                 );

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

Employee_ID	Employee_Name	Salary	Department	Commission
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to employee(s) from ‘employee’ table that do not have records in ‘comm’ table using EXISTS condition with NOT operator in combination with WHERE clause.

BETWEEN Condition in Oracle SQL – PLSQL

October 24, 2012 by techhoneyadmin

The BETWEEN condition in Oracle SQL / PLSQL allows us to filter the records within a range of values including values provided for the range.
BETWEEN condition can be used with SELECT, INSERT, UPDATE and DELETE SQL statements.

Syntax for the BETWEEN condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
column_nameN)
WHERE column_name BETWEEN value1 AND value2;

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE column_name BETWEEN value1 AND value2;

DELETE FROM table_name
WHERE column_name BETWEEN value1 AND value2;

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 employee having Salary >= ‘20000’ and Salary <= '31000'. [sourcecode language="sql"] SELECT * FROM employee WHERE salary BETWEEN 20000 AND 31000; [/sourcecode] 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
104	Emp D	30000	Support

Here we have retrieved the records pertaining to employee having Salary more than ‘20000’ and less than ‘31000’ including the values provided in the range using BETWEEN condition along with WHERE clause.

Scenario 2:

We can use multiple BETWEEN conditions in a query.
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 of employees who are having Salary more than ‘20000’ and less than ‘31000’ and having commission more than ’15’ and less than ’25’ including values provided in range.

We can achieve the same as:

SELECT *
FROM employee
WHERE salary     BETWEEN 20000 AND 31000
AND   commission BETWEEN 15    AND 25;

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

Employee_ID	Employee_Name	Salary	Department	Commission
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20

Here we have successfully retrieved the records pertaining to employees who are having Salary >= ‘20000’ and Salary <= '31000' and having commission more than '15' and less than '25' using BETWEEN condition in combination with WHERE clause.