Commands

ACROPOLIS INSTITUTE OF TECHNOLOGY &
RESEARCH
Basic Command of DBMS
Ayushi Goyal

COMMANDS
 Create command : This command is used to
create table in database. Created table is also
called Base Table. This table has independent
existence. It is a physical part of data base.
When we create a table then first we determine
the name of table and after that we define its
fields. In field definition we also determine the
data type and its size.
Syntax : Create table <table-name> (column-
name datatype(size),……….);
Ex : create table employee (id number(8,0),
name varchar2(50), dept char(15), salary

 Alter command : This command is used to
modify the structure of table. It can add new field
in a table and it can also modify the field of table.
Syntax : Alter table <table-name> add/modify
(column-name datatype);
Ex: 1.Alter table emp add (address
varchar2(50));
2. Alter table emp modify (name varchar2(25));
 Following task cannot be performed by this
command
1. Change the name of table.
2. Change the name of column.
3. Drop a column.

 Deleting a column from a table : We can also
delete the column of any table. To delete the
column we merge alter and drop command in
a single statement.
Syntax : Alter table <table-name> drop
column <column-name>;
Ex: Alter table emp Drop column address;
 Renaming tables : In Oracle we can change
the name of table object. But it is not possible
by alter command. To change the name of
table we use rename command.
Syntax : Rename <old-table> to <new-table>;
Ex: Rename employee to emp;

 Destroying tables : we can delete the table of
database permanently. For delete the table we use
Drop command .
Syntax : Drop table <table-name>;
Ex. : Drop table employee;
 Truncating tables : In database we can empty any
table completely. To delete all records of table
permanently we can use truncate command.
Syntax : Truncate table <table-name>;
Ex. : Truncate table employee;
 There are some difference between delete and
truncate command.
1. This command drop the table and recreate the table
2. Truncate operations are not transaction safe. An error
occur if an active transaction or table lock exist
3. The no. of deleted rows are not returned.

 Displaying structure of table : To display the
structure of any table, we use describe or desc
command.
Syntax : describe or desc <table-name>;
Ex : desc employee;
 Inserting data into tables : In oracle after create a
table we can insert new data or record in a table.
To enter the data in a table we use insert
command. By this command we can
1. Creates a new row in the database table.
2. Loads the values passed into the columns
specified.
Syntax :insert into <table-name> (column-
name1, column-
name2)values(<expression1>,expression2>);

Ex. : Insert into employee (id,name,dept,salary)
values (1001,’Amit’,’Computer’,23456.35);
Ex:Insert into employee values(&id, &name,
&salary);
 Delete data from table: In oracle we can delete
data from table by using delete command. By
this command we can delete some records of
table and we can also delete all records from
table.
Syntax : 1. delete from <table-name>;
This command will delete all records from table.
Syntax : 2. delete from <table-name> where
<conditions>;
Ex : delete from employee where name=‘amit’;
This command will delete set of records from
table or it can also delete single from a table.

 Updating the contents of table : In oracle we
can update or modify the table by using
update command. This command can update
all rows of table or in can also update some
special records of table.
Syntax : Update <table-name> set <column-
name> = <expression>;
Ex : Update emp set salary = salary +2000;
Syntax : Update <table-name> set <column-
name> = <expression> where <conditions>;
This command will update table conditionally.
Ex : Update emp set salary = salary +2000
where id =1002;

 Display records of table : In any table we can
store records and we can also display these
records in different-2 format. To display or select
records from table we use Select command. We
can display these records by so many types.
1. Display all records of table with all columns.
Syntax : select * from <table-name>;
Ex : select * from employee;
2. Display set of records. (all column & all
records.)
Syntax : select * from employee where
<condition>;
Ex : select * from employee where salary

3. Display some columns and all records
syntax : Select column1, column2, column3
from <table-name>;
Ex : select id, name, dept from employee;
4. Display some columns and some records.
syntax : Select column1, column2, column3
from <table-name> where <conditions>;
Ex : select id, name, dept from employee where
dept=‘computer’;
5. Eliminating duplicate rows when using a select
statement. (distinct)
syntax : Select distinct <column-name> from
<table-name> ;
Ex : select distinct name from employee;
we can also use conditions with this statement.

6. Sorting data in a table (order by).
Syntax : select * from <table-name> order by
<column-name>;
Ex: select * from employee order by salary;
(It will display records in ascending order.)
Ex: select * from employee order by name
desc;
(It will display records in descending order.)
7. Logical operator (and, or, not)
Syntax : select * from <table-name> where
<condition1> an <condition2>;
Ex : select * from employee where
name=‘ankit’ and salary>20000;
Ex : select * from employee where id=1004 or
salary=20000;

8. Between operator
Syntax : select * from <table-name> where
between <expression1> and
<expression2>;
Ex : select * from employee where salary
between 20000 and 40000;
9. Pattern matching (%, _) : These characters
are used to match the character. Here %
represents the all characters. For ex. A%
will represent those strings which will start
from “A”.
Similarly (_) represents a single character.
For ex. Ami_ will represent those strings
whose length will be 4 and after “Ami” there

Ex : Select * from employee where name like
‘S%’ Now this statement will display those
records In which name will start from “S”.
Ex : Select * from employee where name like
‘Ra ----’;
Now this statement will display those records
In which the length of name will be 6 and first
character will be “Ra”.
10. IN : it is used to select the list of values.
Syntax : select * from <table-name>
where<cond.>;
Ex: select * from emp where salary in
(12000,20000);

Ex. : select * from emp1 where name in
(‘amit’,’anil’);
we can also use not keyword with in
operator.
11. Null value : In any column of table we can
check the null value. We can check it with
string or numeric value.
Syntax : select * from <table-name>
where <column-name> is Null;
Ex : select * from emp where name is
Null;

1. Creating a table from another table : In
oracle we can create new table from another
table. This means we can copy the data of
an existing table in a new table. Or we can
say that we can select some data of any
table and it can paste in new table.
Syntax: create table <table> (column-name1,
column-name2,….) as select column-name1,
column-name2,…. From <table>;
Ex.: 1. create table emp1 (reg_no, name) as
select id, name from employee;
2. create table emp1 as select * from
employee where salary>20000;

2. Inserting data into a table from another table
:
Normally by insert command we can store
only 1 record at a time. Buy now we can
select so many record from any table and
then we can insert these records in existing
table.
Syntax : Insert into <table-name> select
column-name1,column-name2,…. From
<table> where <conditions>;
Ex : 1. insert into employee select * from
emp1 where age >21;

 Create table
Employee(id(6), name(15), dept(10),
address(50), salary(10))
Student(roll(5), name(15), dob, total(4),
percentage(5))
1. Add new field “age” in employee table and
“address” in student table.
2. Modify the field size of name from 15 to 20
in both table.
3. Drop dob column from student table.
4. Rename student to stud.
5. Display the structure of both table.

6. Insert 7 records in both table.
7. Enter percentage value through update
command.
8. 10% Increment in salary of all employee .
9. Increment 3000 in salary of those employee
whose department is “computer”
10. display all records of employee and student.
11. display all records of student whose
percentage is greater then 67.
12. Display all records of employee whose salary
is greater then 20000.
13. Display id, name, address and salary of all
employee.

14. display roll, name , address and total of those
students whose percentage between 55 to 75.
15. Display records of those employee whose
name starts from “d” or salary >15000.
16. Display records of those student whose the
length of name is 7.
17. Display records of student in ascending order
according to “percentage”.
18. Display records of employee in descending
order according to salary.
19. Delete records of those students whose
percentage is less then 40.
20. Delete records of those employee whose age
is less then 18.

Commands

Recommended

Recommended

More Related Content

What's hot

What's hot (17)

Viewers also liked

Viewers also liked (20)

Similar to Commands

Similar to Commands (20)

Recently uploaded

Recently uploaded (20)

Commands