数据库第六天
1.
create table guest(
profileid int(11) not null auto_increment primary key,
gstname varchar(20) not null
);
create table hotel(
mon varchar(10) not null,
profileid int(11) not null,
nights int(11) not null,
foreign key(profileid) references guest(profileid)
);
select g.gstname,h.mon,sum(h.nights) from guest g
left join hotel h
on g.profileid = h.profileid
group by g.gstname,h.mon
order by g.profileid,h.mon;
2.
create table score(
id int(11) not null auto_increment primary key,
name varchar(20) not null,
course varchar(20) not null,
score int(11) not null
);
//写出最少有三门科目大于90分的学生的所有成绩
select name from score where score > 90
group by name having count(course)>=3;
select * from score where name in (select name from score where score > 90
group by name having count(course)>=3);
3.
销售主表
SALE（order_id，salesman，customer，contract_date）
分别表示：订单号，业务员，客户名称，合同日期
create table sale(
order_id int(11) not null auto_increment primary key,
salesman varchar(20) not null,
customer varchar(20) not null,
contract_date date
);
产品表
PRODUCT(product_id, product_name,number)
分别表示：产品编号，产品名称,产品数量
create table product(
product_id int(11) not null auto_increment primary key,
product_name varchar(50) not null,
number int(11) not null
);
销售子表
PRODUCT_SALE（order_id，product_id，sale_count，sale_amount）
分别表示：订单号，产品编号，销售数量，销售金额
create table product_sale(
order_id int(11) not null,
product_id int(11) not null,
sale_count int(11) not null,
sale_amount int(11) not null,
primary key(order_id,product_id),
foreign key(order_id) references sale(order_id),
foreign key(product_id) references product(product_id)
);
(1).统计合同日期为2016年10月的各业务员的销售数量和销售金额
sale
product_sale
销售员 销售数量 销售金额
select s.salesman,ps.sale_count,ps.sale_amount,s.contract_date from sale s left join product_sale ps
on s.order_id = ps.order_id
where s.contract_date between '2016-10-1' and '2016-10-31';
(2)统计合同日期为2016年10月的各产品的销售数量和销售金额
产品名称 销售数量 销售金额 合同日期
select p.product_name,ps.sale_count,ps.sale_amount,s.contract_date from product p
left join product_sale ps
on p.product_id=ps.product_id
left join sale s
on s.order_id = ps.order_id
where s.contract_date between '2016-10-1' and '2016-10-31';
(3)计算业务员‘张三’销售的产品为”维生素b”的平均价格
select sale_amount/sale_count as avg_price from product_sale where order_id in (select order_id from sale where salesman='张三') and product_id =(select product_id from product where product_name='维生素b');
//格式化数字
format(要格式化的数字,保留的小数位数);
(4)显示所有产品的库存，要求显示产品名称和库存数量（用一条SQL）
库存：产品总数量-卖掉的数量
产品名称 库存数量
product
product_sale
select p.product_name,p.number-ps.sale_count as storage from product p
left join product_sale ps
on p.product_id = ps.product_id;
(5).显示所有产品库存，要求显示产品名称，库存数量，库存状态（用一条SQL语句）
当库存数>=10000时，库存状态显示库存积压；
当库存数<=1000时，库存状态显示库存不足；
其他情况,库存状态显示库存正常。
产品名称 库存数量 库存状态
//case when
select p.product_name,p.number-ps.sale_count as storage,
case
when p.number-ps.sale_count >= 10000 then '库存积压'
when p.number-ps.sale_count <=1000 then
'库存不足'
else '库存正常'
end
as state
from product p
left join product_sale ps
on p.product_id = ps.product_id;
4.
create table groups(
group_id int(11) not null auto_increment primary key,
group_name varchar(20) not null
);
create table thread(
thread_id int(11) not null primary key auto_increment,
group_id int(11) not null,
username varchar(20) not null,
foreign key(group_id) references groups(group_id)
);
group_id group_name count(group_id)
1 movie 2
2 music 1
请写出相应的sql语句：
select g.group_id,g.group_name,count(t.group_id) from groups g left join
thread t on g.group_id=t.group_id
group by g.group_id;
5.
请写一个sql语句，把name字段更新成name+id
也就是变成网友1、网友2.........以此类推
update student set name = concat(name,id);
