Here i am going to tell you all the important queries used in Microsoft SQL Server. Just go through this page and you will become master in Microsoft SQL Server because all the queries used in daily programming are briefly explained here.
To create own database
create database demostart
To view the database details
sp_helpdb demostart
To open database
use demostart
To create table in the database
create table emp
(
eno int,
ename varchar(10),
hiredate datetime,
salary int
)
To see the table information
sp_help emp
sp_help:-
It is System defined stored procedure which gives us the information about a table.
To insert data in a table
insert into emp values (100,’king’,’10-may-09′,5000)
insert into emp values(200,’dd’,’20-april-08′,4300)
insert into emp values (102,’ee’,getdate(),6700) — default print date & time
To insert values in a particular column
insert into emp(eno,ename) values (300,’xyx’)
To see all the records
select *from emp
To delete table
drop table emp
To update ename with space where eno=100
update emp set ename=’ ‘ where eno=100
To add additional column (address and phno) in a table
alter table emp add address varchar(30),phno varchar(15)
To see all the records
select *from emp
To modify column size
alter table emp alter column address varchar(45)
To see the table information
sp_help emp
To modify column name(or rename)
sp_rename ‘emp.eno’,’empno’
———– (old name)-(new name)
To rename table
sp_rename ‘emp’,’employee’
To rename of database name
sp_renamedb ‘demostart’,’demoversion’
Drop table column
alter table employee drop column address,phno
To see all the records
select *from employee
To update ename with ‘amir’ where salary is NULL
update employee set ename=’amir’ where salary is NULL
To add value 1000 in salary field (adding temporarly)
select salary+1000, salary from employee where empno=100
To add value 1000 in salary field (adding permanent)
update employee set salary= salary+1000 where empno=100
–operator in sql server–
1)Arithmatic :
+,-,/,*
2)comparison :
=,<,>,<=,>=,<>,like,between,in, is null.
3)logical :
not,and,or
Comparison operator
To see the list those employee who’s empno is equal to 100
select *from employee where empno=100
To see the list those employee who’s empno is not-equal to 100
select *from employee where empno<>100
To insert one more record
insert into employee values(101,’anuj’,’01-jan-10′,2500)
To see all the records
select *from employee
List of those employee whose salary is less than 6000
select *from employee where salary < 6000
in operator to show only 100 and 300 record not show in between
select *from employee where empno in(100,300)
in :we can search for the data based upon the values that are specified in the brackets.
the data will be retrieved if the employee number are 100 or 300)
between operator
The data is retrieved from the table based on a certain range of values starting from
the lowerbound to the upperbound.
select *from employee where empno between 100 and 200
like operator
used for pattern matching.
Example:- names starting with a particular character or ending with a particular character.
select *from employee where ename like ‘%r’ –’r’ should be the last char. of ename
select *from employee where ename like ‘a%’ –’a’ should be the first char. of ename
pattern matching with exact no. of characters
use _ (underscore operator)
one _ sign means exactly 1 character.
select *from employee where ename like ‘a___’
Logical operators:-
To show the list of all employeea whose empno is not equal to 100
select *from employee where not empno=100
To show the list of all employees whose empno is 200 or ename equal to amir
select *from employee where empno=200 or ename=’amir’
To show the list of all employeea whose empno is 300 and ename equal to amir
select *from employee where empno=300 and ename=’amir’
Identity column
These are those column which are used for autogetneration of interger values in a particular sequence.
Ex. we can use identity columns to autogenrate the ID’s in the categoryid column of the category table.
There can only be 1 identity column per table.
create table categories
(
catid int identity(1,1), –first 1 : starting value
catname varchar(20), –2nd 1 : increamenting value
description varchar(20)
)
To insert records
insert into categories values(‘beverages’,’teas and coffees’)
insert into categories values(‘cold drinks’,’fanta pepsi’)
To see all the records
select *from categories
Insert randomly.
set identity_insert categories on — when we identity_insert set is on then we cannot insert dublicate records in a table.
set identity_insert categories off — when we identity_insert set is off then we can insert dublicate records in a table.
To know how many datatypes
select *from systypes
To view the names of the user defined tables in a database
select name from sysobjects where xtype=’u’
Constraints
These are the objects used to validate the data Entry in tables columns.
Type of constraints :-
1) primary key :-
checks for dublicate and null values.
primary key can be defined for single or composite columns.
one primary per table.
2) unique keys :-
checks for dublicate values. one null value is allowed.
3) checks :-
The data must obey the rules of comparison opeators(=,<,>,<=,>=,in,between,like).
4) Rules :-
They are like the checks constraints but have certains differences.
Difference b/w check and Rules
Check constraints are applied with the column definitons.
Rules are created separately and then can be attached with different columns of different tables.
checks constraints check for existing data.
Rules do not check for the existing data.
5) Not Null :-
we have to Enter the values on the columns where not null constraints is defined.
6) Foreign key :-
used for Referential integrity.
if we insert a particular data in a table column, then matching data should be inserted
in the column where the foreign key is defined. The foreign key will check for data by
referencing the column where data is inserted. The Referenced column have primary key or
unique key associated with it.
constraints can be created using the create table or alter table clause.
To define Constraints :-
create table consdemo
(
cno int primary key,
cname varchar(10) unique,
balance int check(balance >5000)
)
insert records
insert consdemo values (100,’abc’,4000) –cannot be accepted coz applied check i.e balance>5000.
insert consdemo values (100,’abc’,6000) — accepted
insert consdemo values (101,’abc’,7000) –not accepted coz cno is primary key and cname applied, unique (i.e dublicate value not allowed)
To view the names of constraints define on a table
sp_helpconstraint consdemo
To drop the Constraints
alter table consdemo drop constraint — not run
insert records
insert consdemo values (102,’xyz’,54) — not run
put check constraint
alter table consdemo add constraint chk check(balance>50) –not run
use of foreign key
create table publishers
(
pubid int primary key,
pubname varchar(20)
)
insert publishers values(1,’book bazar’)
insert publishers values(2,’wiley Eastern’)
select *from publishers
create table author
(
authid int primary key,
pubid int references publishers(pubid),
authorname varchar(20)
)
insert author values(1,2,’Bill Evjen’)
select *from publishers
select *from author
insert author values(2,2,’Bill Evjen’)
insert author values(3,1,’Bill Evjen’)
creating constraints by giving user defined names :–
create table consdd
(
cno int constraint pk primary key,
cname varchar(10) constraint uk unique,
salary int constraint chk1 check(salary>5000)
)
To view the names of constraints define on a table
sp_helpconstraint consdd
To drop the constraint chk in the table of consdd
alter table consdd drop constraint chk1
To view the names of constraints define on a table
sp_helpconstraint consdd
RULES:–
create & Apply RULE–
create rule xyz as @a>5000
— @a : It denotes a variable in sql server known as a.
To Apply the Rule–
sp_bindrule xyz,’consdd.salary’
–Note : we assign a rule i.e(@a>5000), so we cannot insert salary<5000)
select *from consdd
insert consdd values(101,'opk1',6100)-- accepted
insert consdd values(102,'opk2',100) --not accepted(not follow the cond. i.e @a>5000)
sp_helpconstraint consdd
To unbind the rule
sp_unbindrule ‘consdd.salary’
insert consdd values(102,’opk2′,100)– after unbind the rule, then the query will be accepted.
To drop the Rule
drop rule xyz
To view the names of constraints define on a table
sp_helpconstraint consdd
–Another Example of create and applying Rule
create rule adb as @a between ‘a’ and ‘d’
— ‘a’ and ‘d’ is the ist char of cname.
sp_bindrule adb, ‘consdd.cname’
insert consdd values(400,’edf’,3000)– not accepted coz starting char should a and d.
insert consdd values(400,’daf’,3000)– not accepted coz starting char should a and d.
insert consdd values(500,’aef’,3000)– accepted
insert consdd values(300,’adf’,3000)– accepted
insert consdd values(600,’brf’,3000)– accepted
insert consdd values(700,’cef’,3000)– accepted
select *from consdd
To unbind the rule
sp_unbindrule ‘consdd.cname’
insert consdd values(400,’edf’,3000)–accepted (unbind rule)
To drop the Rule
drop rule adb
composite primary key–
— It checks that the particular row that contains a set of values (ex-> 100,abc)cannot be created again.
create table comkey
(
cno int,
cname varchar(10),
address varchar(40),
constraint pkk primary key(cno,cname)
)
To change the table name(i.e rename)
sp_rename ‘comkey’, ‘compkey’
select *from compkey
insert compkey values(100,’abd’,’23/sds’)
insert compkey values(101,’abd’,’45/df’)
select *from compkey
insert compkey values(101,’bd’,’45/df’)
insert compkey values(100,’abd’,’45/df’)
–Note: when we use composite key:- you cannot insert the same record at a time.
–ex. we put the record cno=100 and cname=abd at ist time it will be accepted but
— 2nd time cannot be accepted. but we put cno=100 and cname=xyz(i.e cname is different)
— it will be accepted, or we put cno=200 (i,e cno is diff. value) and cname is same i.e cname=abd
— it will be accepted.
Drop primary key
alter table compkey drop constraint pkk
on delete cascade clause–
— It is used on foreign keys when we are assigning the foreign key on a particular column.
— It automatically deletes the values from the foreign key cloumn if the primary key value is deleted.
–Ex. If 100 is there in primary key column and also there in the foreign key column, then if 100 is
–deleted on primary key column then 100 is automatically deleted from foreign key column.
create table casededemo
(
sno int constraint pkm primary key,
sname varchar(10)
)
create table cascdata
(
sno int constraint fk foreign key references casededemo(sno) on delete cascade,
sales int
)
–note: ‘on delete’ used for automatically deleted.
insert into casededemo values(200,’fgh’)
insert into casededemo values(100,’xyz’)
select *from casededemo
insert into cascdata values(200,6000)
insert into cascdata values(100,4000)
select *from cascdata
delete from casededemo where sno=100 –Note : delete 100 from both table.
select *from cascdata
select *from casededemo
use of order by and group by clause—
–ORDER BY :- It is used to sort data either in ascending or descending order.
select *from consdemo
–insert two records more in consdemo table
insert into consdemo values(104,’xxx’,7000)
insert into consdemo values(102,’sss’,8000)
insert into consdemo values(103,’bbb’,7600)
insert into consdemo values(101,’ccc’,7600)
insert into consdemo values(105,’ddd’,7600)
insert into consdemo values(106,’eee’,7600)
select *from consdemo
To apply ORDER BY in desc order.
select *from consdemo order by cno desc
GROUP BY—
— It is used to display the data from the aggregate function as well as the data from the table.
To count total balance
select balance,count(balance) from consdemo group by balance
–To count total cno with own column name i.e ‘ count of cno’
select cno,count(cno) as “count of cno” from consdemo group by cno
RESTRICTION OF GROUP BY CLAUSES –
–HAVING clause–
select balance,count(balance)as “result” from consdemo group by balance having count(balance)>1
–use the top keyword in sql queries–
–Top keyword is used to display the data either from the begining or from the end or from any
— given position in a table.
–Ex:- If we want to view the first 3 records or the last 3 records or the details of records
— in a given range.
–To print the details of employee who earn the three highest salaries,we can use
–Top keyword.
select *from consdemo
–show top 2 records–
select top 2 *from consdemo
–show last 2 record
select top 2 *from consdemo order by cno desc
–Set operators in sql server–
— These are used to combine the result of 2 or more queries in a single output.
–Ex: If we want to view to the details of a column known as salary from first table and also from the
— 2nd table, we can use the set operators.
– Two union operators:-
1) union :- All the union values are displayed in the result.
2) union All :- The different values (unique or non unique) are displayed in the result.
– To use the set operators:-
–1)Data type must be same for the columns mentioned in the query.
–2)The number of columns mentioned in the query.
create table salesdetails
(
sno int,
salary int
)
insert salesdetails values(300,5000)
select *from salesdetails
– Now create another table which is a copy of the salesdetails table.
create table salesunion
(
sno int,
salary int
)
insert salesunion values(200,1000)
select * into sales_union from salesdetails
select *from salesunion
select *from salesdetails
–To copy records from another table
select *into sales_details from salesdetails
select *from sales_details
–To show salary of salesdetails and sno of salesunion
select salary from salesdetails union select sno from salesunion
–To show salary of salesdetails and cno of consdemo
select salary from salesdetails union select cno from consdemo
–temporary tables–
create table #emp
(
eno int,
ename varchar(10)
)
insert #emp values(100,’abc’)
select *from #emp
sp_help #emp
–SUBQUERIES–
–These are the sql statements which are Embedded inside another sql statements.
–Ex: 1) To find the details of the person who earn the maximum salary or 3rd highest salary
–we have to use the subqueries.
–subqueries always written in parenthesis.
–subqueries evaluate a particular data which can then be used as the input to the main query.
–subqueries evaluate first.
–subqueries can be nested 21 times.
–TYPES OF SUBQUERIES –
–1) Single row subqueries.
–2) Multiple Row subqueries.
–3) subqueries using the exist operator.
–only 1 row is returned by the subquery.
–MULTIPLE ROW SUBQUERIES–
–subqueries–in a single row—
select *from salesdetails where salary=(select max(salary) from salesdetails)
–subqueries–in a multiple row—
select *from salesdetails where salary in(select salary from sales_details)
insert sales_details values(300,3000)
select *from sales_details
—subquery using exits operator
select *from salesdetails where exists(select salary from salesunion where sno=100)
select *from salesdetails where not exists(select salary from salesunion where sno=1600)
—joins—
create table table1
(
sno int,
salary int
)
insert table1 values(100,2000)
insert table1 values(200,4000)
insert table1 values(300,5000)
insert table1 values(400,6000)
insert table1 values(500,7000)
select *from table1
create table table2
(
sno int,
salary int
)
insert table2 values(600,7000)
insert table2 values(100,2000)
insert table2 values(200,4000)
insert table2 values(300,5000)
select *from table2
–CROSS JOIN–
select *from table1 cross join table2
–INNER JOIN—
select s.sno,s.salary,t.sno,t.salary from table1 s inner join table2 t on s.sno=t.sno
–outer join–
select s.sno,s.salary,t.sno,t.salary from table1 s left outer join table1 t on s.sno=t.sno
–right outer join–
select s.sno,s.salary,t.sno,t.salary from table1 s right outer join table2 t on s.sno=t.sno
–full outer join–
select s.sno,s.salary,t.sno,t.salary from table1 s full outer join table2 t on s.sno=t.sno
–self join–
alter table table1 add sname varchar(10)
update table2 set salary=300 where salary=2000
update table1 set sname=’ddy’ where salary=300
select *from table1
select *from table2
–query of self join
select s.sno,t.salary,s.sname from table1 s inner join table2 t on s.sno=t.salary
–views–
select *from salesdetails
alter table salesdetails add sname varchar(10)
insert salesdetails values(456,800,’ffg’)
update salesdetails set sname=’ddy’ where sno=300
create view vm as select *from salesdetails where sno=100
select *from vm
insert vm values(456,800,’ffg’)
–not allowed to add record in salesdetails
alter view vm as select *from salesdetails where sno=100 with check option
insert vm values(456,800,’ffg’)
sp_helptext vm
drop view vm
–PARTITION VIEWS–
create view vm as select sno,salary from salesdetails union all select *from salesunion
select *from vm
–READONLY VIEW–
create view vwd as select count(*) as “count”from salesdetails
select *from vwd
insert vwd values (23) –can’t be insert coz it’s read only
—show all database views
select *from sysobjects where xtype=’v’
–to show view in one table
sp_depends salesdetails
–STORED PROCEDURE–
–1)
create procedure proce
as
select *from emp
proce
–2)
create procedure demo(@a int,@b int)
as
declare
@c int
set @c=@a+@b
print ‘sum=’+ convert(varchar(10), @c)
demo 2,3
drop procedure demo
–3)
create procedure ques1(@a int ,@b int)
as
declare
@c int
set @c=@a+@b
print @c
ques1 2 ,3
sp_helptext demo
–4)
create procedure ques2(@a varchar(10))
as
declare
@b varchar(10)
set @b=@a
print ‘b= ‘+@b
ques2 ‘hello pro’
–5)area of circle
create procedure ques3(@pi float ,@r float )
as
declare
@area float
set @area=@pi*@r*@r
print ‘area of cir=’+convert(varchar(10),@area)
ques3 3.14,2
–6)area of triangle
create procedure ques4(@b int ,@h int)
as
declare
@area int
set @area=(@b*@h)/2
print ‘area of tri=’+convert(varchar(10),@area)
ques4 3,5
drop procedure ques4
sp_helptext ques4
–IF ELSE–
–1)w.a.proc to find the greatest b/w two nos.
create procedure ques5(@a int,@b int)
as
if @a>@b
print convert(varchar(5),@a)+’ a is greater’
else
print convert(varchar(5),@b)+’ b is greater’
ques5 3,5
–2)w.a.proc to find the greatest b/w three nos.
create procedure ques6(@a int ,@b int ,@c int)
as
if @a>@b
if @a>@c
print ‘ a is g’
else
print ‘c is g’
else
if @b>@c
print ‘b is g’
else
print ‘c is g’
ques6 2,4,3
–3)extracting to database
create procedure ques7(@a int)
as
declare
@nm varchar(10)
select @nm= ename from emp where eno=@a
print @nm
ques7 101
select *from emp
drop procedure ques7
–4)extracting to database
create procedure ques8(@a varchar(10))
as
declare
@nm varchar(10)
select @nm= ename from emp where ename=@a
if @nm=@a
print’found’
else
print’not found’
ques8 ‘radhika’
—LOOPING—
–1)while loop
create procedure whloop (@a int)
as
while @a<20
begin
set @a=@a+1
print @a
end
whloop 10
--2)print rev order
create procedure whloop1(@no int)
as
declare
@rev int,@rem int
set @rev=0
while @no>0
begin
set @rem=@no%10
set @rev=@rev*10+@rem
set @no=@no/10
end
print @rev
whloop1 1234 drop procedure whloop1
–3)NESTED PROCEDURE–
create procedure first
as
print ‘first procedure’
create procedure second
as
print ’2nd procedure’
exec first
second
–FREE TEXT SEARCHING
create table ftextsearch
(
fno int primary key,
ename varchar(20),
address varchar(40)
)
select *from ftextsearch
insert ftextsearch values(50,’xx’,’0-2LN’)
select *from ftextsearch where contains (address,’0-2LN’)
–select address from ftextsearch where address=’0-2LN’
sp_help ftextsearch
drop table ftextsearch