Essential Queries for DBA Beginners-Oracle

Create Table

Create Table EMP(  ENAME  Varchar2(20),
                                AGE  NUMBER,
                                SEX CHAR(1));  --creating emp table
Create table EMP1 as select * from EMP ; --create table EMP1 as same as EMP table

Insert  values to a table

Insert Into EMP Values ('XXX',25,'M'); --Insert into emp table
Insert Into EMP (ENAME,AGE,SEX) Values('YYY',25,'F'); --Insert into emp table
Insert Into EMP (ENAME,AGE) Values('YYY',25); --Insert only two values to emp table

Select from a table

Select * from EMP;     --Select all columns the employee details.
Select ENAME,AGE,SEX  from EMP;  --Select all mentioned columns the employee details.

Delete from a table

Delete *  from EMP;  -- All the EMPLOYEE  records will be deleted.
Delete * from EMP where AGE > 55;  -- Delete  the employees whose age is greater than 55.

Update table

Update EMP set AGE=34;         --Update the EMP table for set all the employees age to 24.
Update EMP set AGE=24,SEX='M'  --Set All the Employees Age  to 24 and sex to 'M'

Update EMP set AGE=34 Where ENAME='SCOTT';  --Update  the age of  EMP table  where Employee                                                                                          name is SCOTT
Update EMP set AGE=24,SEX='M'  Where ENAME='SCOTT'; --Update  the age and sex  of  EMP                                                                                                table  where Employee name is SCOTT

Alter the Structure of a Table

Add a column to a EMP table 

Alter table EMP add   City Varchar2(10);  -- Add a column city
Alter table EMP add (City varchar2(10),Postal_Code varchar2(4)); --add multiple columns to EMP table

Change the column definition of a table 

Alter table EMP modify  SEX CHAR(2)  not null; --EMP table sex column definition has been changed
Alter table EMP modify  (SEX CHAR(2),ENAME varchar2(40)); --Change definition of multiple columns

Drop column 

Alter table EMP drop column Postal_Code;  --drop column postal_code

Rename column 

Alter table Emp rename column City to town; --rename column  city to town

Rename Table

Alter table EMP rename to EMPLOYEE;  --rename table to EMPLOYEE

Drop table 

Drop table EMP;

Truncate table

TRUNCATE EMP;