MS SQL Commands Part-1

Databases
 1.Demo and 2.Demo1

Tables
1.Stu_Details  Table
-----------------------------------------------------------
These Tables just for example......



2.tblGender  Table
-------------------------------

  

Note:This below tables i used for all the following commands

--select database
use Demo

--List of Database
exec sp_databases;

--create database
create database Demo1

--creating table
create table Stu_Details (
ID int not null primary key,
Name varchar(30) not null,
Dept varchar(20) not null,
Email varchar(30) not null,
Contact numeric(12),
Gender varchar(6)
)


--Adding column
alter table stu_details add Age int

--Altering a table
alter table stu_details  alter column gender varchar(6) not null

--creating table with identity and primary key
create table tblGender (ID int not null identity primary key,Name  varchar(30) not null)

--Selecting tables
select *from tblGender;
select *from Stu_Details;

--Renaming Column name
Exec sp_rename 'stu_details.course','dept'
--or
Exec sp_rename 'stu_details.course','dept','column'

--Inserting Tables
insert into Stu_Details values(1,'prakash','B.Com','p@gmail.com',12365656)
insert into Stu_Details values(2,'chandru','IT','c@gmail.com',779898965)
insert into Stu_Details values(3,'mari','EEE','mm@gmail.com',5488956232)
insert into Stu_Details values(4,'shanthi','CSE','s@gmail.com',8965632323256)
insert into Stu_Details values(5,'mani','MCA','mani@gmail.com',1231545,null)
insert into Stu_Details values(7,'Ramesh','DCE','r@gmail.com',6569745,1)
insert into Stu_Details (ID,Name,Dept,Email) values(8,'Ammu','Aero','ammu@gmail.com')
insert into Stu_Details (ID,Name,Dept,Email,Genderid) values(9,'abi','IT','abi@gmail.com',4)


--Updating table
update Stu_Details set genderid=1 where ID=2
update Stu_Details set genderid=2 where ID=4
update Stu_Details set Email='m@gmail.com', genderid=1 where ID=3
update Stu_Details set genderid=1 where ID=1

--Inserting values into IDENTITY table
insert into tblGender values ('Male')
insert into tblGender values ('Female')
insert into tblGender values ('Unknown')

--Selecting Top * values from the table
select top 2 *from Stu_Details;
select top 1 percent *from Stu_Details;

--Count the no rows in a table
select COUNT(*) from Stu_Details;
select COUNT(*) from tblGender;

--foreign key
alter table stu_details add constraint stu_details_GenderID_FK
foreign key (genderid) references tblgender (ID)

--Default Constraint   (Fill automatically if we created default constraint)
alter table stu_details add constraint stu_details_DFV default 3 for genderid
--Delete default constraint
alter table stu_details drop constraint stu_details_DFV

--Cascading referential integrity
use Demo1
select *from tblgender
select *from Stu_Details
delete tblGender where ID=3

--Using check constraint  (Set limit by condition to column)
alter table stu_details add constraint stu_details_Age_CK check (age>0 and age<100)
--trying to add wrong value
insert into Stu_Details(ID,Name,dept,Email,age) values(10,'parthi','ITI','Parthi@gmail.com',-9999)
------
insert into Stu_Details(ID,Name,dept,Email,age) values(10,'parthi','ITI','Parthi@gmail.com',27)

--Set identity to column
set identity_insert tblgender off
insert into tblGender (Name) values('unknown')


--Reset IDENTITY column
DBCC CHECKIDENT (tblgender,reseed,10)
insert into tblGender (Name) values('unknown')
--It Starts from value 11


--Delete or update multiple columns using 'IN' Keyword
delete tblGender where ID in (5,6)
update Stu_Details set GenderID=2 where ID in (5,8)