When is the homework due? ;)

nepalikanchha · May 23, 2015 10:17 AM

what have you done so far and where are you stuck ?

alece · May 23, 2015 12:46 PM

Copy and Paste it on SSMS. Other than no.5 everything should work. Please provide more details for no.5 (see comments) Editor doesn't preserve line breaks here so a SQL fiddle has been made. you will be able to see formatted code here http://sqlfiddle.com/#!3/d58dc... Also for Question 5.. -------Write a procedure to remove duplicates from a table containing million rows using batching --------not sure what do you mean by batching ??? please be more specific...and from what table you want to delete the records?? do you hav any sample data. just want to see how data is structured. Last edited: 23-May-15 05:21 PM Last edited: 23-May-15 05:23 PM

a_little_idea · May 23, 2015 4:51 PM

---Create Tables----- create table customers ( cid int identity(1,1) not null, cname nvarchar(500), cranking int, cage int ) go create table product ( pid int identity(1,1) not null, pname nvarchar(500), ptype int ) go create table purchase ( pid int, cid int, [date] date ) go ------------ ----Generate Dummy Data------ insert into customers values('Customer Name 1',3,30); insert into customers values('Customer Name 2',2,20); insert into customers values('Customer Name 3',1,35); insert into customers values('Customer Name 4',6,28); insert into customers values('Customer Name 5',5,27); insert into customers values('Customer Name 6',4,25); insert into customers values('Customer Name 7',9,23); insert into customers values('Customer Name 8',10,24); insert into customers values('Customer Name 9',3,30); insert into customers values('Customer Name 10',3,27); insert into customers values('Customer Name 11',3,55); insert into customers values('Customer Name 12',3,25); go insert into product values('Product 1',1); insert into product values('Product 2',2); insert into product values('Product 3',3); insert into product values('Product 4',1); insert into product values('Product 5',1); insert into product values('Product 6',1); insert into product values('Product 7',2); insert into product values('Product 8',3); insert into product values('Product 9',1); insert into product values('Product 10',1); insert into product values('Product 11',1); insert into product values('Product 12',2); insert into product values('Product 13',3); insert into product values('Product 14',1); insert into product values('Product 15',1); go insert into purchase values(1,1,getdate()); insert into purchase values(1,2,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,4,getdate()); insert into purchase values(1,5,getdate()); insert into purchase values(2,1,getdate()); insert into purchase values(3,2,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(2,4,getdate()); insert into purchase values(3,5,getdate()); insert into purchase values(2,9,getdate()); insert into purchase values(1,2,getdate()); insert into purchase values(2,3,getdate()); insert into purchase values(2,4,getdate()); insert into purchase values(3,5,getdate()); insert into purchase values(3,1,getdate()); insert into purchase values(3,1,getdate()); insert into purchase values(3,1,getdate()); insert into purchase values(15,1,getdate()); insert into purchase values(15,1,getdate()); insert into purchase values(15,2,getdate()); insert into purchase values(15,3,getdate()); insert into purchase values(15,1,getdate()); insert into purchase values(6,1,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(2,6,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); insert into purchase values(1,3,getdate()); go --------------------------- ----Question 1-- --find all the customer who bought the product with PID=15 select c.cid,cname,p.date,pr.pname from customers c inner join purchase p on c.cid = p.cid inner join product pr on p.pid = pr.pid where pr.pid = 15 go --Question 2---- -- find all the customer who have purchased at least one product. select c.cid,c.cname,c.cranking,c.cage from customers c where exists ( select pid from purchase p where p.cid = c.cid ) go ---Question 3---- --3. find the youngest customer for each ranking level. select cranking,min(cage) as youngest from customers group by cranking go --4.write a stores procedure that resets the ranking of each person by the number of purchase. --[Ranking 0(#purchases<10), Ranking 1( 10<=# purchases<20)] and returns the new rankings. create procedure resetRanking as Begin update c set c.cranking = case when co.totalPurchase between 1 and 10 then 0 when co.totalPurchase between 11 and 20 then 1 when co.totalPurchase between 21 and 30 then 2 end from customers c inner join (select cid, count(') as totalPurchase from purchase group by cid) co on c.cid = co.cid end go exec resetRanking go ----5..Write a procedure to remove duplicates from a table containing million rows using batching ----not sure what do you mean by batching ??? please be more specific...and from what table you want to delete the records?? do you hav any sample data. just want to see how data is structed.

a_little_idea · May 23, 2015 4:53 PM