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
);
//寫(xiě)出最少有三門科目大于90分的學(xué)生的所有成績(jī)
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）
分別表示：訂單號(hào)，業(yè)務(wù)員，客戶名稱，合同日期
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
);
產(chǎn)品表
PRODUCT(product_id, product_name,number)
分別表示：產(chǎn)品編號(hào)，產(chǎn)品名稱,產(chǎn)品數(shù)量
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）
分別表示：訂單號(hào)，產(chǎn)品編號(hào)，銷售數(shù)量，銷售金額
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).統(tǒng)計(jì)合同日期為2016年10月的各業(yè)務(wù)員的銷售數(shù)量和銷售金額
sale
product_sale
銷售員? 銷售數(shù)量? 銷售金額
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)統(tǒng)計(jì)合同日期為2016年10月的各產(chǎn)品的銷售數(shù)量和銷售金額
產(chǎn)品名稱 銷售數(shù)量 銷售金額 合同日期
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)計(jì)算業(yè)務(wù)員‘張三’銷售的產(chǎn)品為”維生素b”的平均價(jià)格
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');
//格式化數(shù)字
format(要格式化的數(shù)字,保留的小數(shù)位數(shù));
(4)顯示所有產(chǎn)品的庫(kù)存，要求顯示產(chǎn)品名稱和庫(kù)存數(shù)量（用一條SQL）
庫(kù)存：產(chǎn)品總數(shù)量-賣掉的數(shù)量
產(chǎn)品名稱? 庫(kù)存數(shù)量
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).顯示所有產(chǎn)品庫(kù)存，要求顯示產(chǎn)品名稱，庫(kù)存數(shù)量，庫(kù)存狀態(tài)（用一條SQL語(yǔ)句）
當(dāng)庫(kù)存數(shù)>=10000時(shí)，庫(kù)存狀態(tài)顯示庫(kù)存積壓；
當(dāng)庫(kù)存數(shù)<=1000時(shí)，庫(kù)存狀態(tài)顯示庫(kù)存不足；
其他情況,庫(kù)存狀態(tài)顯示庫(kù)存正常。
產(chǎn)品名稱? 庫(kù)存數(shù)量 庫(kù)存狀態(tài)
//case when
select p.product_name,p.number-ps.sale_count as storage,
case
when p.number-ps.sale_count >= 10000 then '庫(kù)存積壓'
when p.number-ps.sale_count <=1000 then
'庫(kù)存不足'
else '庫(kù)存正常'
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
請(qǐng)寫(xiě)出相應(yīng)的sql語(yǔ)句：
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.
請(qǐng)寫(xiě)一個(gè)sql語(yǔ)句，把name字段更新成name+id
也就是變成網(wǎng)友1、網(wǎng)友2.........以此類推
update student set name = concat(name,id);
