目录
1、查询课程名称为“数据库”，且分数低于60分的学生姓名和分数
2、对所有性别为“女”的学生，同时课程名为“高等数学”的分数统一加5
3、删除姓名为“张翰”（学号=1）课程名为“数据库”的课程成绩
4、统计2021年11月每天新用户的次日保留率（保留两位小数）
8、请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标
创建student表
create table Student(
Sno int primary key,
Sname varchar(255),
Ssex varchar(10),
Sdept varchar(50)
);
创建Course表
create table Course(
Cno int primary key,
Cname varchar(255),
Tno int
);
创建Score表
create table Score(
Sno int,
Cno int,
Degree int,
primary key(Sno, Cno)
);
创建Teacher表
create table Teacher(
Tno int primary key,
Tname varchar(255),
Tsex varchar(10),
Prof varchar(50)
);
在Student表中插入一条记录
insert into Student (Sno, Sname, Ssex, Sdept) values (1,'张翰','男','17届计算机一班');
查询课程名称为“数据库”，且分数低于60分的学生姓名和分数
select s.Sname, SC.Degree
from Student s
join Score SC
on s.Sno = SC.Sno
join Course c
on SC.Cno = c.Cno
where c.Cname = "数据库" and SC.Degree < 60;
对所有性别为“女”的学生，同时课程名为“高等数学”的分数统一加5
update Score SC
set Degree = Degree + 5
where SC.Sno in (
select S.Sno
from Student s
where s.Ssex = "女")
and SC.Cno in (
select c.Cno
from Course c
where c.Cname = "高等数学");
删除姓名为“张翰”（学号=1）课程名为“数据库”的课程成绩
delete from Score
where Sno =1 and Cno in(
select c.Cno
from Course c
where c.Cname = "数据库");创建用户行为日志表tb_user_log(uid-用户ID,artical_id-文章ID,in_time-进入时间,out_time-离开时间,sign_in-是否签到)
create table tb_user_log(
id int primary key,
uid int,
artical_id int,
in_time datetime,
out_time datetime,
sign_in int
);
insert into tb_user_log values(1, 101, 0, '2021-11-01 10:00:00', '2021-11-01 10:00:42', 1);
insert into tb_user_log values(2, 102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:09', 0);
insert into tb_user_log values(3, 103, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0);
insert into tb_user_log values(4, 101, 9002, '2021-11-02 10:00:09', '2021-11-02 10:00:28', 0);
insert into tb_user_log values(5, 103, 9002, '2021-11-02 10:00:51', '2021-11-02 10:00:59', 0);
insert into tb_user_log values(6, 104, 9001, '2021-11-02 11:00:28', '2021-11-02 11:01:24', 0);
insert into tb_user_log values(7, 101, 9003, '2021-11-03 11:00:55', '2021-11-03 11:01:24', 0);
insert into tb_user_log values(8, 104, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0);
insert into tb_user_log values(9, 105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0);
insert into tb_user_log values(10, 101, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0);
问题：统计2021年11月每天新用户的次日保留率（保留两位小数）
注意：次日保留率为当天新增的用户数中第二天右活跃了的用户数占比。
如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序
with t2 as (
select uid, date(in_time) dt from tb_user_log
union
select uid, date(out_time) dt from tb_user_log
)
select t1.dt dt, round(count(t2.uid) / count(t1.uid), 2) uv_left_rate
from (
select uid, min(date(in_time)) dt
from tb_user_log
group by uid) as t1
left join t2
on t1.uid = t2.uid and t1.dt = date_sub(t2.dt, interval 1 day)
where date_format(t1.dt, '%Y-%m') = '2021-11'
group by dt;OrderItem表包含了所有已订购的产品（有些已被订购多次）
create table OrderItem(
prod_id varchar(255) not null,
order_num varchar(255) not null,
quantity int not null
);
insert into OrderItem values ('BR01', 'a1', '105');
insert into OrderItem values ('BR02', 'a2', '1100');
insert into OrderItem values ('BR02', 'a2', '200');
insert into OrderItem values ('BR03', 'a4', '1121');
insert into OrderItem values ('BR017', 'a5', '10');
insert into OrderItem values ('BR02', 'a2', '19');
insert into OrderItem values ('BR017', 'a7', '5');
问题：编写SQL语句，查找所有订购了数量至少100个的BR01、BR02或BR03的订单。你需要返回OrderItem表的订单号(order_num)、产品ID(prod_id)和数量(quantity)，并按产品ID或数量进行过滤
select order_num, prod_id, quantity
from OrderItem
where quantity >= 100
and prod_id in ('BR01', 'BR02', 'BR03')
order by prod_id;6、编写SQL语句，从Products表中检索所有的产品名称(prod_name)，以及名为quant_sold的计算列，其中包含所售产品的总数(在OrderItems表上使用子查询和SUM(quantity)检索)
Products表中检索所有的产品名称：prod_name、产品ID：prod_id
create table Products(
prod_id varchar(255) not null,
prod_name varchar(255) not null
);
insert into Products values('a0001','egg');
insert into Products values('a0002','sockets');
insert into Products values('a0013','coffee');
insert into Products values('a0003','cola');
OrderItems代表订单商品表，订单产品：prod_id、售出数量：quantity
create table OrderItems(
prod_id varchar(255) not null,
quantity int not null
);
insert into OrderItems values('a0001','105');
insert into OrderItems values('a0002','1100');
insert into OrderItems values('a0002','200');
insert into OrderItems values('a0013','1121');
insert into OrderItems values('a0003','10');
insert into OrderItems values('a0003','19');
insert into OrderItems values('a0003','5');
问题：编写SQL语句，从Products表中检索所有的产品名称(prod_name)，以及名为quant_sold的计算列，其中包含所售产品的总数(在OrderItems表上使用子查询和SUM(quantity)检索)
select t1.prod_name, round(sum(t2.quantity), 3) as quant_sold
from Products t1
join OrderItems t2
on t1.prod_id = t2.prod_id
group by t1.prod_name;Orders表代表订单信息含有订单号order_num和顾客id cust_id
create table Orders(
order_num varchar(255) not null,
cust_id varchar(255) not null
);
insert into Orders values('a1','cust10');
insert into Orders values('a2','cust1');
insert into Orders values('a3','cust2');
insert into Orders values('a4','cust22');
insert into Orders values('a5','cust221');
insert into Orders values('a7','cust2217');
Customers表代表顾客信息含有顾客id cust_id和顾客名称cust_name
create table Customers(
cust_id varchar(255) not null,
cust_name varchar(255) not null
);
insert into Customers values('cust10','andy');
insert into Customers values('cust1','ben');
insert into Customers values('cust2','tony');
insert into Customers values('cust22','tom');
insert into Customers values('cust221','an');
insert into Customers values('cust2217','hex');
insert into Customers values('cust40','ace');
问题：检索每个顾客的名称(Customers表中的cust_name)和所有的订单号(Orders表中的order_num)，列出所有的顾客，即使他们没有下过订单。在最后根据顾客姓名cust_name升序返回
select a.cust_name, b.order_num
from Orders b
right join Customers a
on a.cust_id = b.cust_id
order by a.cust_name;现有用户对展示的商品行为表tb_user_event(uid-用户ID,product_id-商品ID,event_time-行为时间,if_click-是否点击,if_cart-是否加购物车,if_payment-是否付款,if_refund-是否退货退款)
create table tb_user_event(
id int primary key,
uid int not null,
product_id int not null,
event_time datetime,
if_click tinyint,
if_cart tinyint,
if_payment tinyint,
if_refund tinyint
);
insert into tb_user_event values(1,101,8001,'2021-10-01 10:00:00',0 ,0,0,0);
insert into tb_user_event values(2,102,8001,'2021-10-01 10:00:00',1 ,0,0,0);
insert into tb_user_event values(3,103,8001,'2021-10-01 10:00:00',1 ,1,0,0);
insert into tb_user_event values(4,104,8001,'2021-10-02 10:00:00',1 ,1,1,0);
insert into tb_user_event values(5,105,8001,'2021-10-02 10:00:00',1 ,1,1,0);
insert into tb_user_event values(6,101,8002,'2021-10-03 10:00:00',1 ,1,1,0);
insert into tb_user_event values(7,109,8001,'2021-10-04 10:00:00',1 ,1,1,1);
问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标
注意：商品点展比=点击数÷展示数；
加购率=加购数÷点击数；
成单率=付款数÷加购数；退货率=退款数÷付款数，
当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。
select product_id,
round(cnt_click / cnt_prod, 3) ctr,
round(cnt_cart / cnt_click, 3) cart_rate,
round(cnt_pay / cnt_cart, 3) payment_rate,
round(cnt_refund / cnt_pay, 3) refund_rate
from(
select product_id,
count(*) cnt_prod,
sum(if_click) cnt_click,
sum(if_cart) cnt_cart,
sum(if_payment) cnt_pay,
sum(if_refund) cnt_refund
from tb_user_event
where date_format(event_time, '%Y-%m') = '2021-10'
group by product_id
)t_product
having refund_rate <= 0.5
order by product_id;