面试遇到的数据库考题

/*根据购买的金额,将用户信息进行排列*/
/*1.福州地区所有用户的销售额*/
/*2.建立存储过程，计算积分*/

 

 

insert into Customer values('001','zhangxq')
insert into Customer values('002','zhuangys')
insert into Customer values('003','zhuangys')

update Customer set names='zhangxq' where uid='002'

insert into Goods values('001','G01','25','100')
insert into Goods values('002','G02','35','130')
insert into Goods values('003','G03','25','100')
use Shopping
alter table Goods
alter column
goodsId varchar(51)

update Goods set uid='002' where goodsId='G02'

alter table Customer
add
address varchar(50)

update Customer set address='fz' where uid='001'
update Customer set address='xm' where uid='002'
update Customer set address='fz' where uid='003'

create table PromotionGood
(
goodsId varchar(51),
IsPromotion int check(Ispromotion=0 or IsPromotion=1)
)

insert into PromotionGood values('G01','0')
insert into PromotionGood values('G02','1')

/*根据购买的金额,将用户信息进行排列*/
/*1.福州地区所有用户的销售额*/
/*2.建立存储过程，计算积分*/

select *from Customer,Goods,PromotionGood

declare @amount bigint
set @amount = select sum(goodsAmount*goodPrice) from Goods where uid='001'
select *from Customer
select distinct c1.names from Customer c1,Customer c2 where c1.names<>c2.names

select *from Customer as c1 where c1.names in(select names from Customer as c2 where c1.names=c2.names)
select *from Customer as c1,Customer as c2  where c1.uid<>c2.uid and c1.uid in (select Goods.uid from  Goods,Customer where Goods.uid=Customer.uid and Customer.address='xm')

select *from goods

select sum(goodsAmount*goodPrice) from goods where goods.uid in( select Goods.uid from Customer,goods where Customer.uid=goods.uid and customer.address='fz')

 

一些比较常用的操作。。。。哎怎么会忘记呢。。。