SQL COMMANT BY ANIL KUMAR
create database pawan
sp_renamedb pawan,manoj /* to raname database */
use manoj
create table emp(eid bigint not null, ename nvarchar(30) not null,esal bigint not null) /* to create a table*/
alter table stu alter column result bigint
alter table stu add address nvarchar(50) /* to add a new column in a table*/
/* to create back up*/
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
alter table stu alter column address nvarchar(300) /* to change the size of a column */
alter table stu drop column address /* to remove any column */
insert into stu values(9,'radhe',2342) /* to insert records in table */
insert into stu(sid,sname) values(3,'sunil') /* to insert values to the desired columns */
update stu set sname='pankaj' where sid=2 /* to update any record with where clause */
update stu set sname='rahul',result=655 where sid=1 /* to update two columns of a table */
delete from stu where sid=3 /* to delete any row from table */
select * from stu /* to see all record of a table*/
select count(*) from stu /* to count no. of record in a table */
select max(sid) from stu /* to get the maximum id */
select min(sid) from stu /* to get the minimum value */
select sum(sid) from stu /* to get the sum of all values in a column */
select * from stu where sid=1 /*to see any record on the bases of where */
select *from stu where sid between 1 and 3 /* to see any record between the range */
select avg(result) from stu /* to count the average */
select * from stu order by sid desc /* to obtain the record in desc order by result ya any xolumn */
select * from stu order by result asc /* to obtain the record in ascending order by result ya any xolumn */
select sname,sum(result) from stu group by sname /* use of group by */
select sname from stu /* to see only name from a table */
select distinct sname from stu /* row remove duplicacy */
create table prodid(pid bigint,pname nvarchar(30),cost bigint)
select * from prodid
sp_rename prodid,product /* to rename table */
select * from stu
select * from product
truncate table product /* to delete the data only */
insert into emp values(7,'krishan',54234)
/* to apply inner join */
select a.sname,a.result,b.eid,b.ename,b.esal from stu as a inner join emp as b on
a.sname=b.ename
/* to self join */
create table student(sid bigint,sname nvarchar(30),tid bigint)
insert into student values(5,'amrit',2)
SELECT * from student
/* asdfdasfa */
select a.sid,a.sname as studentname,b.sname as teachername from student as a inner join student as b on
a.tid=b.sid
select * from stu
select * from emp
select a.sid,a.sname,a.result,b.eid,b.ename,b.esal from stu as a cross join emp as b
/* to apply left outer join */
select a.sname,a.result from stu as a right outer join product as b on
a.sid=b.pid
/* to apply right outer join */
select a.sname,a.result from stu as a cross join product as b /* cross join */
sp_helpconstraint reg /* to check any constraintis applied or not */
/* primary constraints */
create table reg(id int not null primary key,sname nvarchar(50))
insert into reg values(1,'pawan')
alter table reg drop constraint PK__reg__4CF5691D
alter table reg add constraint regcons primary key(id)
/* unique key constraints */
sp_helpconstraint reg12
create table reg12(id int unique,sname nvarchar(50))
insert into reg12(sname) values('pawan')
select * from reg12
delete from reg12 where sname='pawan'
sp_helpconstraint reg12
alter table reg12 drop constraint UQ__reg12__51BA1E3A
alter table reg12 add constraint ertrte unique(id)
/* unique and forein key constraints */
create table regs(id int not null primary key,sid int foreign key references reg(id),name nvarchar(50))
select * from regs
select * from reg
insert into reg values(107,'sunil')
insert into regs values(8,107,'manoj')
/* check constraints */
create table reg1(id int not null primary key, cname nvarchar(30), salary int not null check(salary<=1000))
select * from reg1
insert into reg1 values(1,'pawan',264)
/* default constraints */
create table reg6(id int not null primary key,sname nvarchar(40) default 'please enter your name',salary int not null)
insert into reg6 values(87,'asdfas',767)
select * from reg6
sp_helpconstraint reg6
alter table reg6 add constraint abcdsdf default 500 for salary
alter table reg6 drop constraint abcd
/* rules constraints */
create rule newrule as @aa<=1000
sp_bindrule 'newrule','reg6.salary'
insert into reg6 values(2000,'pawan',23)
/* view */
create view pawan
as
select id,sname from reg6
select * from reg6
select * from pawan
/* sub queries */
select max(sid) from kopal where sid not in(select max(sid) from kopal)
/*how to create a view */
use pawan
select * from emp
select * from stu
/* view of a two tables */
create view emprecord as select o.sid,o.sname,
c.esal from stu o, emp c where o.sid=c.eid
select * from emprecord
/* view of a two tables */
create view emprecord1 as select o.sid,o.sname,
c.sal from emp o, emp1 c
select * from emprecord1
/* view of a single table */
create view empre as select o.sid,o.sname from emp o
select * from empre ]
/* UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth
Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values)
UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)
*/
/* union */
select * from emp
select * from emp1
select sid,sname from stu
union
select eid,ename from emp
/* unio all */
select * from emp
select * from emp1
select sid,sname from stu
union all
select eid,ename from emp
select * from stu
select * from emp
select sid,sname from stu
intersect
select eid,ename from emp
update stu set result=54234 where sid=3
/* procedures */
create procedure sunil
(
@cityid bigint,
@cityname nchar(30),
@aa nvarchar(20)
)
as
begin
if @aa='1'
select * from city
else if @aa='2'
select * from city where cityid=@cityid
else if @aa='3'
insert into city values(@cityid,@cityname)
else if @aa='4'
delete from city where cityid=@cityid
end
/* procedure 1 */
create procedure strpro
(
@id int
)
as
begin
select @id=count(*)from city
if(@id>0)
print 'hello'
else
print 'good'
end
/* procedure 2 */
create procedure ravi
(
@cityid bigint
)
as
begin
select * from city where cityid=@cityid
end
/* procedure 3 */
ALTER procedure [dbo].[pawan]
(
@cityid bigint,
@cityname nchar(30)
)
as
begin
insert into city(cityid,cityname) values(@cityid,@cityname)
end
/* procedure 4 */
create procedure instu
(
@Sid bigint,
@sname nvarchar(50),
@phy bigint,
@chem bigint,
@math bigint
)
as
begin
insert into stu values(@sid,@sname,@phy,@chem,@math)
end
/* to select top 3 */
select * from emp order by esal desc
select top 1 esal,ename from emp where esal in(
select top 3 esal from emp order by esal desc)
/* top 2 */
select phy from stu order by phy desc
select top 1 phy,sname,sid from stu where phy not in(select top 3 phy from stu order by phy desc)order by phy desc
select phy from stu where phy<(select top 1 phy from stu order by phy desc) and phy>(select min(phy) from stu where phy in(select top 3 phy from stu order by phy desc))
/* having clause */
select ename,sum(eid) from emp group by ename having ename='amrit'
exec delstu 3
exec selstu null
exec updstu 12,'pawan',34,34,234
exec insstu 13,'safas',234,234,234
create database pawan
sp_renamedb pawan,manoj /* to raname database */
use manoj
create table emp(eid bigint not null, ename nvarchar(30) not null,esal bigint not null) /* to create a table*/
alter table stu alter column result bigint
alter table stu add address nvarchar(50) /* to add a new column in a table*/
/* to create back up*/
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
alter table stu alter column address nvarchar(300) /* to change the size of a column */
alter table stu drop column address /* to remove any column */
insert into stu values(9,'radhe',2342) /* to insert records in table */
insert into stu(sid,sname) values(3,'sunil') /* to insert values to the desired columns */
update stu set sname='pankaj' where sid=2 /* to update any record with where clause */
update stu set sname='rahul',result=655 where sid=1 /* to update two columns of a table */
delete from stu where sid=3 /* to delete any row from table */
select * from stu /* to see all record of a table*/
select count(*) from stu /* to count no. of record in a table */
select max(sid) from stu /* to get the maximum id */
select min(sid) from stu /* to get the minimum value */
select sum(sid) from stu /* to get the sum of all values in a column */
select * from stu where sid=1 /*to see any record on the bases of where */
select *from stu where sid between 1 and 3 /* to see any record between the range */
select avg(result) from stu /* to count the average */
select * from stu order by sid desc /* to obtain the record in desc order by result ya any xolumn */
select * from stu order by result asc /* to obtain the record in ascending order by result ya any xolumn */
select sname,sum(result) from stu group by sname /* use of group by */
select sname from stu /* to see only name from a table */
select distinct sname from stu /* row remove duplicacy */
create table prodid(pid bigint,pname nvarchar(30),cost bigint)
select * from prodid
sp_rename prodid,product /* to rename table */
select * from stu
select * from product
truncate table product /* to delete the data only */
insert into emp values(7,'krishan',54234)
/* to apply inner join */
select a.sname,a.result,b.eid,b.ename,b.esal from stu as a inner join emp as b on
a.sname=b.ename
/* to self join */
create table student(sid bigint,sname nvarchar(30),tid bigint)
insert into student values(5,'amrit',2)
SELECT * from student
/* asdfdasfa */
select a.sid,a.sname as studentname,b.sname as teachername from student as a inner join student as b on
a.tid=b.sid
select * from stu
select * from emp
select a.sid,a.sname,a.result,b.eid,b.ename,b.esal from stu as a cross join emp as b
/* to apply left outer join */
select a.sname,a.result from stu as a right outer join product as b on
a.sid=b.pid
/* to apply right outer join */
select a.sname,a.result from stu as a cross join product as b /* cross join */
sp_helpconstraint reg /* to check any constraintis applied or not */
/* primary constraints */
create table reg(id int not null primary key,sname nvarchar(50))
insert into reg values(1,'pawan')
alter table reg drop constraint PK__reg__4CF5691D
alter table reg add constraint regcons primary key(id)
/* unique key constraints */
sp_helpconstraint reg12
create table reg12(id int unique,sname nvarchar(50))
insert into reg12(sname) values('pawan')
select * from reg12
delete from reg12 where sname='pawan'
sp_helpconstraint reg12
alter table reg12 drop constraint UQ__reg12__51BA1E3A
alter table reg12 add constraint ertrte unique(id)
/* unique and forein key constraints */
create table regs(id int not null primary key,sid int foreign key references reg(id),name nvarchar(50))
select * from regs
select * from reg
insert into reg values(107,'sunil')
insert into regs values(8,107,'manoj')
/* check constraints */
create table reg1(id int not null primary key, cname nvarchar(30), salary int not null check(salary<=1000))
select * from reg1
insert into reg1 values(1,'pawan',264)
/* default constraints */
create table reg6(id int not null primary key,sname nvarchar(40) default 'please enter your name',salary int not null)
insert into reg6 values(87,'asdfas',767)
select * from reg6
sp_helpconstraint reg6
alter table reg6 add constraint abcdsdf default 500 for salary
alter table reg6 drop constraint abcd
/* rules constraints */
create rule newrule as @aa<=1000
sp_bindrule 'newrule','reg6.salary'
insert into reg6 values(2000,'pawan',23)
/* view */
create view pawan
as
select id,sname from reg6
select * from reg6
select * from pawan
/* sub queries */
select max(sid) from kopal where sid not in(select max(sid) from kopal)
/*how to create a view */
use pawan
select * from emp
select * from stu
/* view of a two tables */
create view emprecord as select o.sid,o.sname,
c.esal from stu o, emp c where o.sid=c.eid
select * from emprecord
/* view of a two tables */
create view emprecord1 as select o.sid,o.sname,
c.sal from emp o, emp1 c
select * from emprecord1
/* view of a single table */
create view empre as select o.sid,o.sname from emp o
select * from empre ]
/* UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth
Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values)
UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)
*/
/* union */
select * from emp
select * from emp1
select sid,sname from stu
union
select eid,ename from emp
/* unio all */
select * from emp
select * from emp1
select sid,sname from stu
union all
select eid,ename from emp
select * from stu
select * from emp
select sid,sname from stu
intersect
select eid,ename from emp
update stu set result=54234 where sid=3
/* procedures */
create procedure sunil
(
@cityid bigint,
@cityname nchar(30),
@aa nvarchar(20)
)
as
begin
if @aa='1'
select * from city
else if @aa='2'
select * from city where cityid=@cityid
else if @aa='3'
insert into city values(@cityid,@cityname)
else if @aa='4'
delete from city where cityid=@cityid
end
/* procedure 1 */
create procedure strpro
(
@id int
)
as
begin
select @id=count(*)from city
if(@id>0)
print 'hello'
else
print 'good'
end
/* procedure 2 */
create procedure ravi
(
@cityid bigint
)
as
begin
select * from city where cityid=@cityid
end
/* procedure 3 */
ALTER procedure [dbo].[pawan]
(
@cityid bigint,
@cityname nchar(30)
)
as
begin
insert into city(cityid,cityname) values(@cityid,@cityname)
end
/* procedure 4 */
create procedure instu
(
@Sid bigint,
@sname nvarchar(50),
@phy bigint,
@chem bigint,
@math bigint
)
as
begin
insert into stu values(@sid,@sname,@phy,@chem,@math)
end
/* to select top 3 */
select * from emp order by esal desc
select top 1 esal,ename from emp where esal in(
select top 3 esal from emp order by esal desc)
/* top 2 */
select phy from stu order by phy desc
select top 1 phy,sname,sid from stu where phy not in(select top 3 phy from stu order by phy desc)order by phy desc
select phy from stu where phy<(select top 1 phy from stu order by phy desc) and phy>(select min(phy) from stu where phy in(select top 3 phy from stu order by phy desc))
/* having clause */
select ename,sum(eid) from emp group by ename having ename='amrit'
exec delstu 3
exec selstu null
exec updstu 12,'pawan',34,34,234
exec insstu 13,'safas',234,234,234
No comments:
Post a Comment