Sub Languages

SUB LANGUAGES:
1. DDL (DATA DEFINITION LANGUAGE)

2. DML (DATA MANIPULATION LANGUAGE)
3. DRL (DATA RETRIVAL LANGUAGE)
4. DCL (DATA CONTROL LANGUAGE)
5. TCL (TRANSACT CONTROL LANGUAGE)
DDL Sub language: This sub language is used to work with table header part.
This sub language contains different types of commands,
a. Create,
b. Alter,
c. Truncate,
d. Drop
e. Rename
The shortcut to remember these commands is Dr. CAT.
D means Drop, R means Rename, C means Create, A means Alter and T means Truncate.
CREATE: This command is used to create a header of an object like table.

SQL> create table student(sid number(10),sname varchar(10),loc varchar(10));
Table created.
ALTER: Alter is used to modify the structure of a table

We can add columns, drop columns or change the datatypes and sizes of columns also.
ADDING COLUMNS
Adding single column:
SQL> alter table student add gender varchar(10);

Table altered.
Adding Multiple columns:
SQL> alter table student add (marks number(10),fname varchar(10));

Table altered.
--to see table description
SQL> desc student;

Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER(10)
SNAME VARCHAR2(10)
LOC VARCHAR2(10)
GENDER VARCHAR2(10)
MARKS NUMBER(10)
FNAME VARCHAR2(10)
DROPPING COLUMNS
Dropping Single column
SQL> alter table student drop(gender);

Table altered.
Dropping Multiple columns
SQL> alter table student drop(loc,marks);

Table altered.
--see table description
SQL> desc student;
Name Null? Type

----------------------------------------- -------- ----------------------------
SID NUMBER(10)
SNAME VARCHAR2(10)
FNAME VARCHAR2(10)
CHANGING THE DATATYPES AND SIZES OF COLUMNS
Changing datatype of single column
SQL> alter table student modify(sname char(10));

Table altered.
Changing datatype of multiple columns
SQL> alter table student modify(fname char(50),sname varchar(30));

Table altered.
SQL> desc student;
Name Null? Type

----------------------------------------- -------- ----------------------------
SID NUMBER(10)
SNAME VARCHAR2(30)
FNAME CHAR(50)
RENAME: Rename is used to rename the column names or table names.

Renaming column name
SQL>alter table student rename column sid to student_id;

Table altered.
SQL> desc student;

Name Null? Type
----------------------------------------- -------- ----------------------------
STUDENT_ID NUMBER(10)
SNAME VARCHAR2(30)
FNAME CHAR(50)
Renaming table name
SQL> rename student to student_tab;

Table renamed.
SQL> desc student_tab;
Name Null? Type

----------------------------------------- -------- ----------------------------
STUDENT_ID NUMBER(10)
SNAME VARCHAR2(30)
FNAME CHAR(50)
DML sub language: This sub language is used to work with data part of table.
This sub language contains three commands, INSERT, UPDATE and DELETE
The shortcut of these commands is UID.
U means Update, I means INSERT, D means DELETE.
INSERT:
In different ways we can insert the data into table
1. Using multiple INSERTs for multiple rows

SQL> insert into student_tab values(1,'sai','nani');
1 row created.
SQL> insert into student_tab values(2,'chandu','satya');

1 row created.
SQL> insert into student_tab values(3,'smith','satya');

1 row created.
SQL> insert into student_tab values(4,'hemanth','jones');

1 row created.
SQL> insert into student_tab values(5,'bala','teja');

1 row created.
2. Using single INSERT for multiple rows
SQL> insert into student_tab values(&student_id,'&student_name','&father_name');
Enter value for student_id: 6

Enter value for student_name: srikanth
Enter value for father_name: raju
old 1: insert into student_tab values(&student_id,'&student_name','&father_name')

new 1: insert into student_tab values(6,'srikanth','raju')
1 row created.
SQL> /

Enter value for student_name: swetha
Enter value for father_name: smith

new 1: insert into student_tab values(7,'swetha','smith')
1 row created.
SQL> /

Enter value for student_name: renu
Enter value for father_name: swarup

new 1: insert into student_tab values(8,'renu','swarup')
1 row created.
3. Inserting values for required fields
SQL> insert into student_tab(student_id,sname) values(9,'teja');

1 row created.
SQL> select * from student_tab;
STUDENT_ID SNAME FNAME

--------- ------ ----------
1 sai nani
2 chandu satya
3 smith satya
4 hemanth jones
5 bala teja
6 srikanth raju
7 swetha smith
8 renu swarup
9 teja
9 rows selected.
UPDATE: Update command is used to modify the data in a table.
We can update single value or multiple values
Updating single value in a row
SQL> update student_tab set fname='nani' where student_id=9;

1 row updated.
Updating multiple values in a row
SQL> update student_tab set sname='jones',fname='turner' where student_id=8;

1 row updated.

--------- ------ ----------
1 sai nani
2 chandu satya
3 smith satya
4 hemanth jones
5 bala teja
6 srikanth raju
7 swetha smith
8 jones turner
9 teja nani
9 rows selected.
DELETE: Delete command is used to remove single or multiple or all records

Deleting single record:
SQL> delete from student_tab where student_id=9;

1 row deleted.
Deleting multiple records:
SQL> delete from student_tab where student_id in (4,6,8);

3 rows deleted.

--------- ------ ----------
1 sai nani
2 chandu satya
3 smith satya
5 bala teja
7 swetha smith
NOTE: If we don’t use WHERE condition in delete statement or update statements then it affects all the
records.
DDL:
TRUNCATE: Truncate command will remove the entire data but structure will remain same.
SQL> truncate table student_tab;
Table truncated.
DROP: Drop command will drop the entire table from database and this dropped table will
move to the RECYCLE BIN.
SQL> drop table student_tab;

Table dropped.
NOTE: Now we can get back the table from RECYCLE BIN to our database by using FLASHBACK
Command.
To see the dropped tables in RECYCEBIN:
SQL> select * from recyclebin;
To get back table from RECYCLEBIN TO database:
SQL> flashback table student_tab to before drop;

Flashback complete.
To drop table permanently from database we will use PURGE in the drop statement, so that
dropped table will not go to RECYCLEBIN.
SQL> drop table student_tab purge;

Table dropped.
DRL Sub language: This sub language contains only one SELECT command.
SELECT command is used to retrieve the data from table.
SQL> Select * from student_tab;
NOTE: here * represents all columns
To retrieve required column
SQL> select sname,fname from student_tab;

Sub Languages

Uploaded by

Copyright:

Available Formats

Sub Languages

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Sub Languages

Uploaded by

Copyright:

Available Formats

SUB LANGUAGES:

1. DDL (DATA DEFINITION LANGUAGE)

The shortcut to remember these commands is Dr. CAT.

CREATE: This command is used to create a header of an object like table.

ALTER: Alter is used to modify the structure of a table

Adding single column:

SQL> alter table student add gender varchar(10);

Adding Multiple columns:

SQL> alter table student add (marks number(10),fname varchar(10));

--to see table description

SQL> desc student;

Dropping Single column

SQL> alter table student drop(gender);

Dropping Multiple columns

SQL> alter table student drop(loc,marks);

--see table description

SQL> desc student;

Name Null? Type

CHANGING THE DATATYPES AND SIZES OF COLUMNS

Changing datatype of single column

SQL> alter table student modify(sname char(10));

Changing datatype of multiple columns

SQL> alter table student modify(fname char(50),sname varchar(30));

SQL> desc student;

Name Null? Type

RENAME: Rename is used to rename the column names or table names.

SQL>alter table student rename column sid to student_id;

SQL> desc student;

Renaming table name

SQL> rename student to student_tab;

SQL> desc student_tab;

Name Null? Type

The shortcut of these commands is UID.

U means Update, I means INSERT, D means DELETE.

1. Using multiple INSERTs for multiple rows

SQL> insert into student_tab values(2,'chandu','satya');

SQL> insert into student_tab values(3,'smith','satya');

SQL> insert into student_tab values(4,'hemanth','jones');

SQL> insert into student_tab values(5,'bala','teja');

2. Using single INSERT for multiple rows

SQL> insert into student_tab values(&student_id,'&student_name','&father_name');

Enter value for student_id: 6

old 1: insert into student_tab values(&student_id,'&student_name','&father_name')

Enter value for student_id: 7

old 1: insert into student_tab values(&student_id,'&student_name','&father_name')

Enter value for student_id: 8

old 1: insert into student_tab values(&student_id,'&student_name','&father_name')

SQL> insert into student_tab(student_id,sname) values(9,'teja');

SQL> select * from student_tab;

STUDENT_ID SNAME FNAME

UPDATE: Update command is used to modify the data in a table.

We can update single value or multiple values

Updating single value in a row

SQL> update student_tab set fname='nani' where student_id=9;

Updating multiple values in a row

SQL> update student_tab set sname='jones',fname='turner' where student_id=8;

SQL> select * from student_tab;

STUDENT_ID SNAME FNAME