---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.
.png)
