/* to select column for perticular months */
select tblcustomer.fname,tblcustomer.lname,tblProduct.pname,tblsale.sprice from tblcustomer inner join tblProduct on tblcustomer.cid=tblProduct.pid inner join tblsale on tblcustomer.cid=tblsale.sid where month(tblsale.date)=11
/* nested queries */
select tblcustomer.fname,tblcustomer.lname,tblcustomer.cid from tblcustomer where not exists (select tblcustomer.fname,tblcustomer.lname,tblcustomer.cid from tblcustomer inner join tblsale on tblcustomer.cid=tblsale.sid)
select tblcustomer.fname,tblcustomer.lname,tblsale.sprice,tblProduct.rprice,abs(tblsale.sid-tblProduct.rprice) as [diff] from tblcustomer inner join tblsale on tblcustomer.cid=tblsale.sid inner join tblproduct on tblcustomer.cid=tblProduct.pid
/* find average */
select tblProduct.category,AVG(tblsale.sprice)as [average]from tblProduct inner join tblsale on tblProduct.pid=tblsale.sid group by tblProduct.category
/*delete duplicate recordes */
/* to see duplicate records */
select col1,col2,count(*) as total from table group by col1,col2 having count(*)>1 order by total desc
/* to delete duplicate record */
with numbered as
(select ROW_NUMBER() over(PARTITION BY col1,col2 order by col1,col2)as dup from table)
delete from numbered where dup>1
/* database attach detach */
/* detach */
use master;
go;
exec sp_detach_db @dbname=N'Buzzedu;
/* detach */
use master;
go
create database Buzzedu
on (filename ='C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Buzzedu.mdf'),
(filename='C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Buzzedu_log.ldf')
for attach;
go
/* create Check Constraints */
alter table job_applied add constraint chk_job_applied check(Stu_id>0)
/* drop Constraints */
alter table job_applied drop constraint chk_job_applied
/* to update row */
update Job_applied set Stu_id=REPLACE(Stu_id,0,1)
/*delete child when delete parent/*
alter table Stu_reply add constraint FK_Stu_thread_Thread_id foreign key(Thread_id) references Stu_thread(Thread_id) on delete cascade
alter table Groups_join add constraint FK_Groups_Group_id foreign key(Group_id) references Groups(Group_id) on delete cascade
alter table Stu_thread add constraint FK_Groups_Group foreign key(Group_id) references Groups(Group_id) on delete cascade
/*unique index */
CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn)
ALTER TABLE dbo.YourChildTable
ADD CONSTRAINT FK_ChildTable_Table
FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn)
/********trigger*************/
Create table tblCustomer(uid int primary key,name varchar(200),balance int);
Create table tblStatement (sid int,userid int references tblCustomer(uid),deposit int);
select * from tblCustomer
select * from tblStatement
CREATE TRIGGER triggerte1 on
tblStatement
after insert
as
begin
declare @uid int,@dp int
select @uid=userid,@dp=deposit from inserted
update tblCustomer set balance=balance+@dp where uid=@uid
end
insert into tblCustomer(uid,name,balance) values (1,'vikas',200)
insert into tblStatement(sid,userid,deposit) values (1,1,700)
Full outer join
select * from Stu_thread full outer join Groups_join on Stu_thread.Group_name=Groups_join.Group_name where Stu_thread.Group_name=@Group_name
read more