create table student(
name varchar(20),
rollno int,
branch varchar(10)
);describe student; Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
ROLLNO NUMBER(38)
BRANCH VARCHAR2(10)select * from tab;This command list all the table and our created table will be visible at the bottom of the list.
insert into student values('&name', '&rollno', '&branch');Enter value for name: Anurag
Enter value for rollno: 1
Enter value for branch: CS
old 1: insert into student values('&name', '&rollno', '&branch')
new 1: insert into student values('Anurag', '1', 'CS')Use / for the re-execution of the previous command.
Similarly, I have added some more student data.
select * from student;NAME ROLLNO BRANCH
-------------------- ---------- ----------
Anurag 1 CS
Abhijeet 2 IT
Anshuman 3 CSselect name, rollno from student;NAME ROLLNO
-------------------- ----------
Anurag 1
Abhijeet 2
Anshuman 3select * from student where rollno>1;NAME ROLLNO BRANCH
-------------------- ---------- ----------
Abhijeet 2 IT
Anshuman 3 CSselect * from student where name='Abhijeet';select * from student where branch='CS';select name from student where branch='IT';select * from student order by rollno;NAME ROLLNO BRANCH
-------------------- ---------- ----------
Anurag 1 CS
Abhijeet 2 IT
Anshuman 3 CSselect * from student order by rollno desc;NAME ROLLNO BRANCH
-------------------- ---------- ----------
Anshuman 3 CS
Abhijeet 2 IT
Anurag 1 CSHere is our table.
NAME ROLLNO BRANCH
-------------------- ---------- ----------
Anurag 1 CS
Abhijeet 2 IT
Anshuman 3 CSLet's suppose I want to change the name of roll no 2 to Prince
update student set name='Prince' where rollno=2;Now table data will be updated to
NAME ROLLNO BRANCH
-------------------- ---------- ----------
Anurag 1 CS
Prince 2 IT
Anshuman 3 CSAltering a table means changing the old schema of the table to a new schema.
Here is our old table schema.
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
ROLLNO NUMBER(38)
BRANCH VARCHAR2(10)In this example, I want to add a new column mobile
alter table student add(mobile number(10));Now our table will look like this
NAME ROLLNO BRANCH MOBILE
-------------------- ---------- ---------- ----------
Anurag 1 CS
Prince 2 IT
Anshuman 3 CSThe mobile number of every student is now blank. We can update the mobile number of students using the update command.
alter table student modify(name varchar(50));We have updated our name field from varchar(20) to varchar(50)
alter table student drop column mobile;Now, we have deleted the column mobile and now our schema will look like the old schema.