Ex.
No : 1(a) SQL COMMANDS
AIM:
To implement Data Definition Language (DDL) and Data Manipulation Language(DML)
commands in ORACLE.
DESCRIPTION:
DDL Commands:( Data Definition Language)
DDL commands are used to create an object, alter the structure of an object and also drop the
object created.
CREATE Command:
This command is used to create a table or an object.
Syntax: create table <tablename>(<column name1> datatype, <column name2 > datatype,....);
ALTER Command:
This command is used to add a field or modify the definition of field or column.
Syntax:
alter table <tablename> add (<column name1> datatype, <column name2> datatype, .....);
alter table <tablename> modify (<column name1> datatype, <column name2> datatype, ...);
TRUNCATE Command:
This command is used to delete all the rows of a table but not the structure of the table.
Syntax: truncate table <tablename>;
DROP Command:
This command is used to delete the entire table along with the structure.
Syntax: drop table <tablename>;
Note:
i) DESC Command:
This command is used to describe the table structure.( field or column name, datatype, null?)
Syntax: desc <table name>
ii) Data types:
char, varchar(size),varchar2(size),date,number,number(size)
date –default format – dd/mon/yy (Eg. 27-jan-78)
iii) ORACLE implicitly commits the current transaction before and after every Data Definition
Language statement.
DML Commands:( Data Manipulation Language)
DML commands are used to insert, view, update and delete the values of an object.
INSERT Command:
This command is used to insert a set of data values into the tables as defined in the create
table command.
Syntax:
insert into <tablename>values (value1,value2,.....,valuen);
insert into <table name> values(&columnname1,&columnname2,.....,&columnname n);
Note: varchar type field or column must be enclosed within single quotes.Eg.’&column’
SELECT Command:
This command is used to view particular data records or columns.
Syntax:
select <column name1,....>from <tablename>;
select * from <tablename>; - to view all records.
select distinct <columnname> from <tablename>;
select * from <tablename> orderby <columnname>; - default –ascending order.
select * from <tablename> orderby <columnname> desc;
- Records are sorted in descending order w.r.t column name
select * from <tablename> where <condition>;
UPDATE Command:
This command is used to update and change the data values of the table.
Syntax:
update <tablename> set <column>=value where <condition>;
DELETE Command:
This command is used to delete a particular record or all records of the table.
Syntax:
delete from <tablename> where <condition>;
delete * from <tablename>; -- to delete all the records or rows of a table.
-- similar to truncate command.
IMPLEMENTATION OF SQL COMMANDS(DDL &DML):
SQL> create table Tel(name varchar2(10),telno number(7));
Table created.
SQL> desc tel;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
SQL> alter table tel add(city varchar2(10));
Table altered.
SQL> desc tel;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
CITY VARCHAR2(10)
SQL> alter table tel modify(city varchar2(15));
Table altered.
SQL> desc tel;
Name Null? Type
----------------------------------------- -------- ---------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
CITY VARCHAR2(15)
SQL> insert into tel values('Aarthi',2502157,'Chennai');
1 row created.
SQL> insert into tel values('&name',&telno,'&city');
Enter value for name: Banu
Enter value for telno: 2345678
Enter value for city: Mumbai
old 1: insert into tel values('&name',&telno,'&city')
new 1: insert into tel values('Banu',2345678,'Mumbai')
1 row created.
SQL> insert into tel values('&name',&telno,'&city');
Enter value for name: Banu
Enter value for telno: 2345678
Enter value for city: Mumbai
old 1: insert into tel values('&name',&telno,'&city')
new 1: insert into tel values('Banu',2345678,'Mumbai')
1 row created.
SQL> /
Enter value for name: Chitra
Enter value for telno: 2502387
Enter value for city: Tvl
old 1: insert into tel values('&name',&telno,'&city')
new 1: insert into tel values('Chitra',2502387,'Tvl')
1 row created.
SQL> /
Enter value for name: Sankar
Enter value for telno: 2330289
Enter value for city: Tvl
old 1: insert into tel values('&name',&telno,'&city')
new 1: insert into tel values('Sankar',2330289,'Tvl')
1 row created.
SQL> select * from tel;
NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Chitra 2502387 Tvl
Sankar 2330289 Tvl
SQL> select distinct city from tel;
CITY
---------------
Chennai
Mumbai
Tvl
SQL> select * from tel order by telno;
NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl
Banu 2345678 Mumbai
Aarthi 2502157 Chennai
Chitra 2502387 Tvl
SQL> select * from tel order by name desc;
NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl
Chitra 2502387 Tvl
Banu 2345678 Mumbai
Aarthi 2502157 Chennai
SQL> select * from tel where name='Sankar';
NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl
SQL> select * from tel where name='&name';
Enter value for name: Chitra
old 1: select * from tel where name='&name'
new 1: select * from tel where name='Chitra'
NAME TELNO CITY
---------- ---------- ---------------
Chitra 2502387 Tvl
SQL> /
Enter value for name: Banu
old 1: select * from tel where name='&name'
new 1: select * from tel where name='Banu'
NAME TELNO CITY
---------- ---------- ---------------
Banu 2345678 Mumbai
SQL> update tel set city='Tirunelveli' where name='Chitra';
1 row updated.
SQL> select * from tel;
NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Chitra 2502387 Tirunelveli
Sankar 2330289 Tvl
SQL> update tel set city='Tirunelveli' where name='&name';
Enter value for name: Sankar
old 1: update tel set city='Tirunelveli' where name='&name'
new 1: update tel set city='Tirunelveli' where name='Sankar'
1 row updated.
SQL> select * from tel;
NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Chitra 2502387 Tirunelveli
Sankar 2330289 Tirunelveli
SQL> delete from tel where name='Chitra';
1 row deleted.
SQL> select * from tel;
NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Sankar 2330289 Tirunelveli
SQL> truncate table tel;
Table truncated.
SQL> select * from tel;
no rows selected
SQL> desc tel;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
CITY VARCHAR2(15)
SQL> drop table tel;
Table dropped.
SQL> desc tel;
ERROR:
ORA-04043: object tel does not exist
RESULT:
Thus the Data Definition Language and Data Manipulation Language commands are
implemented in ORACLE with database tables.
