statement

SEQUENCE in Oracle SQL – PLSQL

October 29, 2012 by techhoneyadmin

In Oracle SQL /PLSQL whenever we want to create an auto-number field we use SEQUENCE. A SEQUENCE is used to generate number sequences in Oracle SQL.
Sequences are very useful whenever we want to create a unique number that can act as a primary key for a table.

Syntax for creation of a SEQUENCE in Oracle SQL / PLSQL is:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

Let’s understand each line of the above SQL CREATE SEQUENCE command:

MINVALUE: is the minimum value that the sequence can have.
MAXVALUE: is the maximum value up to which the sequence will go. The maximum value allowed is 999999999999999999999999999. If we omit the MAXVALUE portion from the CREATE SEQUENCE command then the MAXVALUE is defaulted to 999999999999999999999999999
START WITH: is the value where the SEQUENCE starts from. The start value must be greater than or equal to MINVALUE.
INCREMENT BY: is the value by which the current value of the sequence is incremented to get the next value of the sequence.
CACHE: Represents the number of values that the sequence will have in cache for better performance.

Let’s take an example for understanding

Suppose have a table named ‘employee’ in the database as shown below, but we do not have any data in the table.

Employee_Id

Employee_Name

Salary

Department

Commission

Let’s create a SEQUENCE and the use it to enter data in ‘employee’ table.

Step 1: Creating a SEQUENCE:

CREATE SEQUENCE employee_seq
MINVALUE 100
MAXVALUE 999999999999999999999999999
START WITH 101
INCREMENT BY 1
CACHE 30;

Here with the help of the above SQL CREATE SEQUENCE command we have created a sequence named ‘employee_seq’ that starts with 101 and can go up to 999999999999999999999999999, it will increment with 1 value at a time and caching up to 30 values for performance.

Step 2: Inserting the data in the ‘employee’ table using the ‘employee_seq’ sequence in ‘employee_id’ field.

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp A',10000,'Sales',10);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp B',20000,'IT',20);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp C',28000,'IT',20);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp D',30000,'Support',NULL);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp E',32000,'Sales',10);

The above SQL INSERT statement will insert 5 rows in the ‘employee’ table.
Note the in all the above SQL INSERT statements we have used employee_seq.netxval to get the next value for the employee_id field.

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

Here we have successfully inserted data in ‘employee’ table using sequence ‘employee_seq’ for the ‘employee_id’ field.

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.