Pages

Tuesday, December 27, 2011

Data Definition Language (DDL):

Data Definition Language;

This is the definition level language which includes the following statements.

A) CREATE Statement
B) ALTER Statement
C) DROP Statement


A) CREATE Statement: This Statement is used for creating the database and its objects.

B) ALTER Statement: This Statement is used for modifying the database and its objects.

C) DROP Statement: This Statement is used for deleting the database and its objects.

create a TABLE (Syntax):

CREATE TABLE Tablename (Column1 Datatype, Column2 Datatype,……..)

Example:

CREATE TABLE CUSTOMER (CNO INT, CNAME VARCHAR (20), CITY VARCHAR9 (20));

Example:

CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR(20),SAL MONEY,DEPTNO INT);

The above two queries creates two tables with names CUSTOMER, EMP.


ALTER the Database(Syntax):


In Three ways we can modify the tables

1. By adding the new column to the Existing Table
Syntax:

ALTER TABLE TABLENAME ADD NEWCOLUMN DATATYPE [,……..N]

Example:

ALTER TABLE EMP ADD BONUS MONEY

The above statement adds Bonus column to EMP table

2. By changing the Data type of an Existing column:

Syntax:

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME NEWDATATYPE

Example: ALTER TABLE EMP ALTER COLUMN EMPNO BIGINT

The above statement changes the EMPNO data type from INT to BIGINT.

3. By Dropping the Existing column from Existing Table:

Syntax: ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME [,……..N]

Example: ALTER TABLE EMP DROP COLUMN ENAME, SAL

The above statement deletes two existing columns ENAME, SAL from EMP Table.


DROP Database(syntax):

DROP DATABASE DATABASENAME

Example: DROP DATABASE NRSTT

The above statement deletes NRSTT database and its objects