Create and alter tables using T-SQL syntax (simple statements).

To create table:

Create table table_name (

Column_name1 data_type(size)

Column_name2 data_type(size)

)

Simple create table Example :

Create table customers(cno int not null, customer_name varchar(25) )

Create table by adding Primary key:

Create table customers(cno int not null constraint pk_cno Primary key, customer_name varchar(25))

Create table by adding identity property to a column :

Create table customers (cno int not null identity(1,1) , customer_name varcahr(25) )

Adding a column to an Existing table:

Syntax:

ALTER table table_name ADD column_name data_type(size)

Example:

ALTER table customers ADD city varcahr(25)

Changing the data type  of a column :

Syntax:

Alter table table_name Alter column column_name  changed_data_type(size)

Example:

Alter table customers Alter column customer_name nvarchar(25)

Dropping a column from an existing table :

Syntax:

ALTER table table_name DROP COLUMN column_name

Example:

ALTER table customers DROP COLUMN city

Adding primary key to an existing table :

Syntax:

ALTER table table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column_name)

Example:

ALTER table customers ADD CONSTRAINT pk_cno PRIMARY KEY(cno)

Adding Foreign Key to a column :

Syntax:

ALTER table table_name_2 ADD FOREIGN KEY(column_name) REFERENCES table_name_1(column_name)

Example:

ALTER table orders ADD FOREIGN KEY(cust_id) REFERENCES customers(cno)

Consider that there are two table customers(table 1) and orders (table 2) in table 2 (i.e orders) there is cust_id column to which we need to make as foreign key. Which can be done by executing the above query

How to drop a constraint :

Syntax:

ALTER table table_name DROP CONSTRAINT constraint_name

Example:

ALTER table customers DROP CONSTRAINT pk_cno