All DDL Commands

1

September 27, 2012 by Er.Hem Raj Thakur

Now in this tutorial we learn about how to create table and alter table or change column name add new column and change its data types… so first of all we learn how to create tables……. for example we create some tables.

  1. I. tbl_department, II. tbl_designation, III. tbl_country, IV. tbl_state, V. tbl_district, VI. tbl_area, VII. tbl_employee.

I. tbl_department

create table tbl_department
(
–//this is for Auto Increment and Add Primary Key
dept_id int not null identity(1,1)primary key,
department varchar(100)
);

__________________________________________________

Note:-here we learn how to change/rename the column name from a table

1. Renaming database table column to new name.

Example uses db_gemgoogali database.

Syntax:- exec sp_Rename ‘dbo.tbl_department.Old Column Name’,’New Column Name’

exec sp_Rename ‘dbo.tbl_department.department’,’dept_name’

then we execute the SP by pressing F5 system display the message in your computer screen i.e.
–Caution: Changing any part of an object name could break scripts and stored procedures.

select * from tbl_department

____________________________________

Note:- Here we learn how to Add New Column in a tables.

Syntax:-

ALTER TABLE <table name> ADD <new column name> <data type> NOT NULL

alter table tbl_department Add location varchar(50)not null

Now your New table defination is like this:-

create table tbl_department
(
–//this is for Auto Increment and Add Primary Key
dept_id int not null identity(1,1)primary key,
department varchar(100),
location varchar(50)not null
);

_______________________________________

2.Change/Rename the database table Name.

We can change the table name too with the same command.

Syntax:- exec sp_Rename ‘Old_table’, ‘New_Table’

exec sp_RENAME 'tbl_employees', 'tbl_employee'
GO

Following message is display on the computer screen.

–Caution: Changing any part of an object name could break scripts and stored procedures.

_______________________________________

II. tbl_designation

create table tbl_designation
(
desi_id int not null identity(1,1)primary key,–this is for Auto Increment
desi_name varchar(100),
dept_id int not null,
foreign key(dept_id) references tbl_department (dept_id)–Add Foreign Key in your table
);

III. tbl_country

create table tbl_country
(
country_id int not null identity(1,1)primary key,
country_name varchar(100),
isd_code varchar(6)
);

IV. tbl_state

create table tbl_state
(
state_id int not null identity(1,1)primary key,
state_name varchar(100),
country_id int not null,
foreign key(country_id) references tbl_country (country_id)
);

V. tbl_district

create table tbl_district
(
district_id int not null identity(1,1)primary key,
district_name varchar(100) ,
country_id int not null,
state_id int not null,
foreign key(country_id) references tbl_country (country_id),
foreign key(state_id) references tbl_state(state_id)
);

VI. tbl_area

create table tbl_area
(
area_id int not null identity(1,1)primary key,
area_name varchar(100),
country_id int not null,
state_id int not null,
district_id int not null,
foreign key(country_id) references tbl_country (country_id),
foreign key(state_id) references tbl_state(state_id),
foreign key(district_id) references tbl_district(district_id)
);

VII. tbl_employee

create table tbl_employee
(
emp_id int not null identity(1,1)primary key,
emp_name varchar(50),
dept_id int not null,
desi_id int not null,
emp_address varchar(200),
country_id int not null,
state_id int not null,
district_id int not null,
area_id int not null,
phone_no varchar(100),
email_address varchar(100) ,
emp_sal decimal(6,2),
foreign key(country_id) references tbl_country (country_id),
foreign key(state_id) references tbl_state(state_id),
foreign key(district_id) references tbl_district(district_id),
foreign key(area_id) references tbl_area(area_id),
foreign key(dept_id) references tbl_department (dept_id),
foreign key(desi_id) references tbl_designation (desi_id)
);

_______________________________________

Note:- Now We learn about how to Drop.Delete Table or Delete the Column in a perticular Table.

Syntax:- Drop table <table_name> //for delete/Drop the Table

for Example:- drop table tbl_emp

Syntax:- Alter table<table_name> drop column <Column_Name>

For Example:- alter table tbl_emp drop column id

See the Data Flow Diagram :-click here

One thought on “All DDL Commands

  1. […] About DLL Commands All DLL Commands Share this:TwitterFacebookLike this:LikeBe the first to like […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: