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;
