Creating a Table
SQL> create table employee (id number, name varchar2(10), address varchar2(10), phone varchar2(10));
Output
Table created.
View the structure of the table
SQL> desc employee;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
PHONE VARCHAR2(10)
Adding another column to the employee table
SQL> alter table employee add gender varchar2(10);
Table altered.
SQL> desc employee;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
PHONE VARCHAR2(10)
GENDER VARCHAR2(10)
Dropping the gender column from employee table
SQL> alter table employee drop column gender;
Table altered.
SQL> desc employee;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
PHONE VARCHAR2(10)
Changing the data type for a column in the employee table
SQL> alter table employee modify id varchar2(10);
Table altered.
SQL> desc employee;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
ID VARCHAR2(10)
NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
PHONE VARCHAR2(10)
Renaming a column - change id to employee_id in employee table
SQL> alter table employee rename column id to employee_id;
Table altered.
SQL> desc employee;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID VARCHAR2(10)
NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
PHONE VARCHAR2(10)
Dropping a table
SQL> drop table employee;
Table dropped.
SQL> desc employee;
Output
ERROR:
ORA-04043: object employee does not exist
Inserting a Record into the employee table
SQL> insert into employee values(111,'satish','vellore','87777888');
Output
1 row created.
View the records from a table using Select
SQL> select * From employee;
Output
ID NAME ADDRESS PHONE
---------- ---------- ---------- ----------
111 satish vellore 87777888
Inserting another record
SQL> insert into employee values(112,'ram','chennai','87997888');
Output
1 row created.
Inserting only data for specific columns for an employee
SQL> insert into employee(id,name,phone) values (113,'tom','77777');
1 row created.
SQL> select * from employee;
Output
ID NAME ADDRESS PHONE
---------- ---------- ---------- ----------
111 satish vellore 87777888
112 ram chennai 87997888
113 tom 77777
Updating Data type for a column with data
SQL> update employee set id=null;
3 rows updated.
SQL> desc employee;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
PHONE VARCHAR2(10)
SQL> alter table employee modify id varchar2(10);
Table altered.