santosh-asp tutorial

Insert,Select, Update and Delete Statements in Orcale

First of all we create table and give named as Employee as shwon in below

 create table Employee
(
Name varchar(100) NOT NULL,
Empcode varchar(30) primary key NOT NULL,
Gender varchar(20) NOT NULL,
Salary numeric(10,5) NOT NULL,
Dempartment varchar(50) NOT NULL,
CONSTRAINT Employee_pk PRIMARY KEY (Empcode)  
)

Syntax: (Inserting a single record using the Values keyword)::-

  
INSERT INTO table  
(column1, column2, ... column_n )  
VALUES  
(expression1, expression2, ... expression_n );

Syntax: (Inserting multiple records using a SELECT statement):

  
INSERT INTO table  
(column1, column2, ... column_n )  
SELECT expression1, expression2, ... expression_n  
FROM source_table  
WHERE conditions;

Example:-We insert some records accourding to first syntax as shown in below:

 Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Santosh Kumar Singh','Emp1001','Male',12000.30,'IT')
Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Reena Sahu','Emp1002','Female',13000,'CSE')
Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Anu Singh','Emp1003','Female',15000,'CSE')
Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Shubha','Emp1004','Female',11000,'MCA')
Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Gagan Agrawal','Emp1005','Female',17000,'ECE')
Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Suraj','Emp1006','Male',10000,'CSE')

Select Statement in Oracle:- This statement is used for display one or multiuple records according to our requirments.

Syntax:-

 SELECT expressions/column(s)_name  
FROM tables_name  
WHERE conditions;

Select for All records:-

  
SELECT * FROM tables_name

Example:- We already created a table named is Employee. So, we as example as shown in below

  
Select * Employee

Out Put:-

Select Example: select specific fields

  

Select Name,Gender,Salary from Employee

Out Put:-

Select Example: select specific fields with where condition

  

Select Name,Gender,Salary from Employee Where Empcode='Emp1001'

Out Put:-

Oracle Insert Example: By SELECT statement This method is used for more complicated cases of insertion. In this method insertion is done by SELECT statement. This method is used to insert multiple elements.
In this method, we insert values to the "Employee" table from "EmployeeDetail" table. Both tables are already created with their respective columns.

  

Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) Select Name,Empcode,Gender,Salary,Dempartment from EmployeeDetail

Oracle INSERT ALL statement:- The Oracle INSERT ALL statement is used to insert multiple rows with a single INSERT statement. You can insert the rows into one table or multiple tables by using only one SQL command.
Syntax:-

  

INSERT ALL  
  INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
  INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
  INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
SELECT * FROM dual;

Oracle INSERT ALL Example:-

  

Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Anu Singh','Emp1003','Female',15000,'CSE')
Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Shubha','Emp1004','Female',11000,'MCA')
Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Gagan Agrawal','Emp1005','Female',17000,'ECE')
Insert into Employee (Name,Empcode,Gender,Salary,Dempartment) values('Suraj','Emp1006','Male',10000,'CSE')
SELECT * FROM dual;

Oracle UPDATE Statement:- In Oracle, UPDATE statement is used to update the existing records in a table. You can update a table in 2 ways.
Traditional Update table method:-
Syntax:-

  


UPDATE table  
SET column1 = expression1,  
    column2 = expression2,  
    ...  
    column_n = expression_n  
WHERE conditions;

Update Table by selecting rocords from another table:- Syntax:-

  

UPDATE table1  
SET column1 = (SELECT expression1  
               FROM table2  
               WHERE conditions)  
WHERE conditions;

Oracle Update Example: (Update single column) We want to update Salary column Salary 12000.30000 to 22000 of Santosh Kumar Singh in Employee table as shown in below:

  

Update Employee Set Salary=22000 where Empcode='Emp1001'

In above table Employee we can see Salary of 'Santosh Kumar Singh' has been updated.
Oracle Update Example: (By selecting records from another table):-

  


UPDATE Employee  
SET Name = (SELECT Emp_Name  
                 FROM EmployeeDetil  
                 WHERE Employee.Empcode = EmployeeDetil.Empcode)  
WHERE Salary < 15000;

Oracle DELETE Statement:- In Oracle, DELETE statement is used to remove or delete a single record or multiple records from a table.

Syntax:-

  

DELETE FROM table_name  WHERE conditions;

Example:- We want to Delete Santosh Kumar Singh record in Employee table Where Empcode='Emp1001' as shown in below:

  

Delete from Employee Where Empcode='Emp1001'