SQL queries

 /* 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