DATA DEFINITION LANGUAGE (DDL) sql command for ME/M.Tech students - Computer Programming

Latest

C C++ Java Python Perl Programs Examples with Output -useful for Schools & College Students

Wednesday, October 12, 2011

DATA DEFINITION LANGUAGE (DDL) sql command for ME/M.Tech students

data definition LANGUAGE (ddl): The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let's take a look at the structure and usage of four basic DDL commands: 
1. CREATE                 2.  ALTER                   3. DROP                     4. RENAME
1. CREATE:
 (a)create table: This is used to create a new relation and the corresponding
Syntax: create table relation_name
(field_1 data_type(Size),field_2 data_type(Size), .. . );

Example:
  SQL>create table Student (sno NUMBER(3),sname char(10),class char(5));

(b)create TABLE..as select....: This is used to create the structure of a new relation from the structure of an existing relation.
Syntax:            create table (relation_name_1, field_1,field_2,.....field_n) AS SELECT field_1,field_2,...........field_n from relation_name_2;
Example: SQL>create table std(rno,sname) as select  sno,sname from student;

2. ALTER:
(a)ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.
Syntax: ALTER TABLE relation_name ADD(new field_1 data_type(size), new field_2 data_type(size),..);
Example :  SQL>ALTER TABLE std ADD(Address CHAR(10));

(b)ALTER table...modify...: This is used to change the width as well as data type of fields of existing relations.
Syntax: alter table relation_name modify (field_1 newdata_type(Size), field_2 newdata_type(Size),....field_newdata_type(Size));
Example:SQL>alter table student modify(sname varchar(10),class varchar(5));
3. drop table: This is used to delete the structure of a relation. It permanently deletes the records in the table.
Syntax:                        drop table relation_name;
Example: SQL>drop table std;

4. Rename: It is used to modify the name of the existing database object.
Syntax:                        RENAME table old_relation_name TO new_relation_name;
Example:         SQL>rename table std to std1;
5. TRUNCATE: This command will remove the data permanently. But structure will not be removed.
Syntax:                        TRUNCATE  TABLE <Table name>
Example          TRUNCATE  TABLE student;

Difference between Truncate & Delete:-
ü  By using truncate command data will be removed permanently & will not get back where as by using delete command data will be removed temporally & get back by using roll back command.
ü  By using delete command data will be removed based on the condition where as by using truncate command there is no condition.
ü  Truncate is a DDL command & delete is a DML command.


No comments:

Post a Comment