Oracle

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:

SELECT DISTINCT department
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:

SELECT DISTINCT department
                ,commission
FROM 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.

DELETE Statement in Oracle SQL – PLSQL

October 23, 2012 by techhoneyadmin

The DELETE statement in Oracle SQL / PLSQL allows us to delete record(s) in a table.

Syntax for the DELETE statement in Oracle SQL / PLSQL is:

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 delete the employee having employee_id = 105 from the ‘employee’ table.
The same can be achieved using the DELETE statement as follows

DELETE FROM employee
WHERE employee_id = 105;

The above statement will delete record from the table where employee_id = 105

Now if we query the ‘employee’ table as;

SELECT *
FROM employee;

We will get the following 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
104	Emp D	30000	Support

Here we can see that a record with employee_id = 105 has been successfully deleted from the ‘employee’ table.

Scenario 2:

Deleting more than one record at a time in an already existing table:
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 employees in the ‘Sales’ department and we want to delete them.
We can achieve the same as:

DELETE FROM employee
WHERE department = 'Sales';

Once we have run the above code we can query the ‘employee’ table and the following will be the result:

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 successfully deleted the desired records from ‘employee’ table in one shot.

UPDATE Statement in Oracle SQL – PLSQL

October 23, 2012 by techhoneyadmin

The UPDATE statement in Oracle SQL / PLSQL allows us to update record(s)in a table.

Syntax for the UPDATE statement in Oracle SQL / PLSQL is:

UPDATE table_name
SET column_name = expression
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 employee_id = 104 does not have any ‘commission’ associated with him, and we want the value of ‘commission’ to be 5.
The same can be achieved using the UPDATE statement as follows:

UPDATE employee
SET    commission  = 5
WHERE  employee_id = 104;

The above statement will update the record of the table where employee_id = 104 and put a value in the ‘commission’ column as 5.
Now if we query the ‘employee’ table as;

SELECT *
FROM employee;

We will get the following 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
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10

Here we can see that a record with employee_id = 104 has been successfully updated in to the ‘employee’ table.

Scenario 2:

Updating more than one column at a time in an already existing table:
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 employee_id =104 does not have any ‘commission’ associated with him, and we want the value of ‘commission’ to be 5 and the department to be ‘IT’.

We can achieve the same as:

UPDATE employee
SET    commission  = 5
       ,department = 'IT'
WHERE  employee_id = 104;

Once we have run the above code we can query the ‘employee’ table and the 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
104	Emp D	30000	IT	5
105	Emp E	32000	Sales	10

Here we have successfully updated ‘employee’ table in one shot.

Scenario 3:

Updating records of a table from records of another table:
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
102	Emp B	20000	IT
103	Emp C	28000	IT
104	Emp D	30000	Support
105	Emp E	32000	Sales

Here we can see that ‘commission’column does not have any value for any employee.
Also, we have a table named ‘comm’ which looks as shown below:

Emp_ID	Commission_Percent
101	10
102	20
103	20
104	15
105	10

We want the ‘employee’ table to be updated with the commission percentage data from the ‘comm’ table.

We can achieve the same as:

UPDATE employee
SET employee.commission  = (SELECT comm.commiossion_percent
                            FROM comm
                            WHERE employee.employee_id = comm.emp_id);

Once we have run the above code we can query the ‘employee’ table and 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
104	Emp D	30000	Support	15
105	Emp E	32000	Sales	10

Here we have successfully updated ‘employee’ table from the ‘comm’ table.

INSERT INTO Statement in Oracle SQL – PLSQL

October 23, 2012 by techhoneyadmin

The INSERT statement in Oracle SQL / PLSQL allows us to insert record(s)/row(s) in a table.

Syntax for the INSERT statement in Oracle SQL / PLSQL is:

INSERT INTO table_name
(column_name1
,column_name2
,column_name3
.
.
column_nameN)
VALUES
(value1
,value2
,value3
.
.
valueN);

Let’s take an example for understanding:
Suppose we 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

Scenario 1:

If we want to add one more row to the employee table we use the INSERT INTO statement as follows:

INSERT INTO employee
(employee_id
,employee_name
,salary
,department
,commission)
VALUES
(105
,'EMP E'
,32000
,'Sales'
,10);

The above statement will insert a new record in the table ‘employee’.

Now if we query the ‘employee’ table as;

SELECT *
FROM employee;

We will get the following 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
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Now we can see that a record with ‘employee_id’ = 105 has been successfully inserted in to the ‘employee’ table.

Scenario 2:

If we want to add row(s) from an already existing table:
Let’s assume that we have created a new table namely ‘employee_data’ using the code shown below:

CREATE TABLE employee_data
(emp_id   NUMBER(20)
,emp_name VARCHAR2(300)
);

If we query the ’employee_data’ table as:

SELECT *
FROM employee_data;

We will get the following result:

Emp_Id

Emp_Name

The table will not be having any records.
Suppose we want the ‘employee_data’ table to store the ‘emp_id’ and ‘emp_name’ of all the employees whose salary is more than 21000.
We can achieve the same as:

INSERT INTO employee_data
(emp_id
,emp_name)
(SELECT employee_id
        ,employee_name
 FROM employee
 WHERE salary >= 21000);

Once we have run the above code we will query the ‘employee_data’ table and following will be the result:

Emp_ID	Emp_Name
103	Emp C
104	Emp D
105	Emp E

Here we have successfully inserted 3 records from ‘employee’ table into ‘employee_data’ table in one shot.

DROP TABLE Statement in Oracle SQL – PLSQL

October 22, 2012 by techhoneyadmin

The DROP TABLE statement in PLSQL allows us to delete or drop a table from the database.

It is a Data Definition Language (DDL) statement.

Syntax for the DROP TABLE statement in PLSQL is:

DROP TABLE table_name;

Let’s take an example for understanding:

Suppose have a table named “Employee” as shown below in database.

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

If we want to delete the ’employee’ table from the database we use the DROP TABLE statement as follows:

DROP TABLE employee;

The above statement will delete the table ’employee’ from the database.