Data Definition Language

Earlier we have discussed about Data Types in our previous post SQL DATA TYPES,Now lets checkout Languages in SQL.

Lets discuss about SQL languages:
In SQL mainly we have 5 types of Languages.
1) DDL – Data Definition Language
2) DML – Data Manipulation Language
3) DCL – Data Control Language
4) TCL – Transaction Control language
5) DQL/DRL – Data Query/Retrieval language

In DDL, we have below commands.

1.1 Data Definition Language

1) CREATE :

Create Command is used to create table .
Syntax:

create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);

Eg 1:

SQL> create table student (no number (2), name varchar2 (10), marks number (3));

Eg 2:

SQL> CREATE TABLE EMP (Empno INTEGER, Ename VARCHAR2(20),SAL NUMBER,HIRE_DATE DATE);

here we are creating table name called “EMP” with columns empno,ename,sal and hire_date.

2) ALTER:

alter command is similar to alteration of dress after purchasing ,so we go for alteration for dress if it doesn’t fit same way in database also after creating table we can do alteration using ALTER command.
In ALTER so we have different types commands.

a) ADD :- If we want to add additional columns after creating table that time we would use ADD command.
eg : ALTER TABLE EMP ADD (MANAGER INTEGER);
here we are adding manager column using ADD Command,we can add multiple columns at a time.

b) MODIFY :- if we want to modify columns then we go for MODIFY command.
eg: ALTER TABLE EMP MODIFY (ENAME VARCHAR2(100));
here i’m increasing size for ENAME Column using MODIFY command.

c) DROP : If we want drop any column then we go for DROP Command.
Eg: ALTER TABLE EMP DROP COLUMN manager
here we are dropping manager column from EMP table.

d) RENAME: will be used to rename column.

Eg: ALTER TABLE EMP rename column ename to Empname

above command will rename the column name i.e. ename to empname.

3) DROP:
we can use drop command to drop table permanently from database.

Eg: DROP TABLE EMP;
Above command will drop emp table from database.

Note: you might get confuse between drop command in ALTER And Main drop command.
remember that drop command in alter is used to drop columns but main DROP is used to drop any object from database.
In Oracle database we call table ,view ,synonym etc as objects.you will come to know about these objects in coming posts.

Similarly, we can rename table also as below.
4) TRUNCATE: This command is used to delete data from table permanently.

5) RENAME : RENAME command is Used to rename table.

Eg: ALTER TABLE EMP rename to EMPLOYEE;
above command will rename of emp table to employee.

Note: This “RENAME” command and “RENAME” command in “ALTER” are different .Main RENAME command is used to rename table and rename command in alter is used to rename column name in table.