Post
Ask Question

Create, Insert, Update, Delete, Select, Truncate, Drop Statement In Oracle PL-SQL

Introduction 

In this article, we will learn how to create a table, insert records, select statements, update a statement, truncate a table and drop table statements in an oracle database.

1. Create Table 

Create Table statement used to create the new table in the oracle database, in the following code, we will create an Employee table in the oracle database. in the following script, we will create an Employee table with having columns EmployeeNo, EmployeeName, EmployeeSal, EmployeeCity, EmployeeDob.

CREATE TABLE Employee
(
    EmployeeNo char(4),
    EmployeeName varchar2(30),
    EmployeeSal number(10,2),
    EmployeeCity varchar2(30),
    EmployeeDob date
);

2. Insert Record 

INSERT statement used to insert values into the table, we can insert optional column by specifying the column name. using the following technique we will insert an optional column into a table. In below script we will insert EmployeeNo, EmployeeName, EmployeeSal, EmployeeCity, EmployeeDob data into the Employee table.

INSERT INTO Employee(EmployeeNo,EmployeeName,EmployeeSal,EmployeeCity,EmployeeDob)
values('1', 'Arvind', 5000, 'Mumbai','23-DEC-1992');

Other Options to insert records, using this technique all the table's columns are required.

INSERT INTO Employee
values('2', 'Santosh', 5000, 'Delhi','23-DEC-1994');

3. Select Statement 

The select statement is used to select the record from the table, either we can use * (all the columns) or specify the column name. In the following example, we will select all the records into the table 

select * from Employee

or (In the below example we will specify the number of columns, the best practice always specify the column name )

select EmployeeNo,EmployeeName,EmployeeSal,EmployeeCity,EmployeeDob from Employee

4. Update Statement 

with the help of the update statement, we can update the records into a table. In the below example we will update the employee name KASHISH having employeeNo 1.

UPDATE Employee
SET EmployeeName='KASHISH'
WHERE EmployeeNo=1

5. Delete Statement

with the help of the delete statement, we can delete the records from the table. in the following script, we will delete the records from the Employee table having EmployeeNo 1.

DELETE
FROM 
EMPLOYEE
WHERE employeeNo=1

6. Truncate Statement

The following syntax for the TRUNCATE TABLE statement in Oracle/PLSQL . In the following example, we will truncate the employee table 

TRUNCATE TABLE EMPLOYEE;

In the Oracle database, truncating the table is a fast way to clear out records from the table if you do not need to worry about rolling back.

7. Drop Table Statement

Oracle DROP TABLE statement is used to remove or delete a table from the Oracle database. The following statement will drop the table named Employee. 

DROP TABLE EMPLOYEE;

Summary

In this article, we have seen how to create a table, insert records into the table, update records in the table and delete records from the table 

I hope this helps. If this helps you then share it with others.
 
Sharing is caring! :) 


Similar Articles