数据库系统头歌实验五 SQL复杂业务查询

最新推荐文章于 2023-12-12 18:25:21 发布

stearm210

最新推荐文章于 2023-12-12 18:25:21 发布

阅读量2.9k

点赞数 5

分类专栏：数据库实验文章标签： sql 数据库

本文链接：https://blog.csdn.net/m0_53227534/article/details/128420830

版权

数据库实验专栏收录该内容

7 篇文章 7 订阅

订阅专栏

第1关：

描述如下有一张商品表（goods），字段依次为：商品id、商品名、商品质量 +------+------+--------+ | id | name | weight | +------+------+--------+ | 1 | A1 | 100 | | 2 | A2 | 20 | | 3 | B3 | 29 | | 4 | T1 | 60 | | 5 | G2 | 33 | | 6 | C0 | 55 | +------+------+--------+

还有一张交易表（trans），字段依次为：交易id、商品id、这个商品购买个数 +------+----------+-------+ |tran_id| id | count | +------+----------+-------+ | 1 | 3 | 10 | | 2 | 1 | 44 | | 3 | 6 | 9 | | 4 | 1 | 2 | | 5 | 2 | 65 | | 6 | 5 | 23 | | 7 | 3 | 20 | | 8 | 2 | 16 | | 9 | 4 | 5 | | 10 | 1 | 3 | +------+----------+-------+

查找购买个数超过20,重量小于50的商品，按照商品id升序排序,如: +------+-------+ | id | total | +------+-------+ | 2 | 81 | | 3 | 30 | | 5 | 23 | +------+-------+

示例1：

输入： CREATE TABLE goods ( id int(11) NOT NULL, name varchar(10) DEFAULT NULL, weight int(11) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE trans ( tran_id int(11) NOT NULL, id int(11) NOT NULL, count int(11) NOT NULL, PRIMARY KEY (tran_id) ); insert into goods values(1,'A1',100); insert into goods values(2,'A2',20); insert into goods values(3,'B3',29); insert into goods values(4,'T1',60); insert into goods values(5,'G2',33); insert into goods values(6,'C0',55); insert into trans values(1,3,10); insert into trans values(2,1,44); insert into trans values(3,6,9); insert into trans values(4,1,2); insert into trans values(5,2,65); insert into trans values(6,5,23); insert into trans values(7,3,20); insert into trans values(8,2,16); insert into trans values(9,4,5); insert into trans values(10,1,3);

输出： id total 2 81 3 30 5 23

USE mygoods;
########## Begin ##########
select goods.id,sum(count) as total
from goods,trans 
where weight<50 and goods.id=trans.id
group by goods.id having total>20
order by goods.id;
########## End ##########

第2关：

假设音乐数据库里面现在有几张如下简化的数据表: 关注follow表,第一列是关注人的id,第二列是被关注人的id,这2列的id组成主键 +---------+-------------+ | user_id | follower_id | +---------+-------------+ | 1 | 2 | | 1 | 4 | | 2 | 3 | +---------+-------------+

这张表的第一行代表着用户id为1的关注着id为2的用户这张表的第二行代表着用户id为1的关注着id为4的用户这张表的第三行代表着用户id为2的关注着id为3的用户

个人的喜欢的音乐music_likes表,第一列是用户id,第二列是喜欢的音乐id,这2列的id组成主键 +---------+----------+ | user_id | music_id | +---------+----------+ | 1 | 17 | | 2 | 18 | | 2 | 19 | | 3 | 20 | | 4 | 17 | +---------+----------+

这张表的第一行代表着用户id为1的喜欢music_id为17的音乐 .... 这张表的第五行代表着用户id为4的喜欢music_id为17的音乐

音乐music表，第一列是音乐id，第二列是音乐name,id是主键 +----+------------+ | id | music_name | +----+------------+ | 17 | yueyawang | | 18 | kong | | 19 | MOM | | 20 | Sold Out | +----+------------+

请你编写一个MYSQL，查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐，并且按music的music_name升序排列。你返回的结果中不应当包含重复项上面的查询结果如下: +------------+ | music_name | +------------+ | kong | | MOM | +------------+

示例1

输入： CREATE TABLE follow ( user_id int(4) NOT NULL, follower_id int(4) NOT NULL, PRIMARY KEY (user_id,follower_id));

CREATE TABLE music_likes ( user_id int(4) NOT NULL, music_id int(4) NOT NULL, PRIMARY KEY (user_id,music_id));

CREATE TABLE music ( id int(4) NOT NULL, music_name varchar(32) NOT NULL, PRIMARY KEY (id));

INSERT INTO follow VALUES(1,2); INSERT INTO follow VALUES(1,4); INSERT INTO follow VALUES(2,3);

INSERT INTO music_likes VALUES(1,17); INSERT INTO music_likes VALUES(2,18); INSERT INTO music_likes VALUES(2,19); INSERT INTO music_likes VALUES(3,20); INSERT INTO music_likes VALUES(4,17);

INSERT INTO music VALUES(17,'yueyawang'); INSERT INTO music VALUES(18,'kong'); INSERT INTO music VALUES(19,'MOM'); INSERT INTO music VALUES(20,'Sold Out');

输出： music_name kong MOM

USE mymusic;
########## Begin ##########
select distinct music_name
from follow f1 
right join music_likes ml1 on f1.follower_id=ml1.user_id
right join music m1 on m1.id=ml1.music_id
where f1.user_id=1 and music_name not in (
    select music_name
    from music_likes ml2
    right join music m2 on ml2.music_id=m1.id
    where ml2.user_id=1
)
order by music_name;

########## End ##########

第3关：

这张表的第一行代表着用户id为1的喜欢music_id为17的音乐 .... 这张表的第五行代表着用户id为4的喜欢music_id为17的音乐

请你编写一个MYSQL，查询向follow表中user_id用户，推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐，并且先按follow的user_id升序排列，再按music的music_name升序排列。你返回的结果中不应当包含重复项上面的查询结果如下: +------------+ | music_name | +------------+ | kong | | MOM | +------------+

示例1

输入： CREATE TABLE follow ( user_id int(4) NOT NULL, follower_id int(4) NOT NULL, PRIMARY KEY (user_id,follower_id));

CREATE TABLE music_likes ( user_id int(4) NOT NULL, music_id int(4) NOT NULL, PRIMARY KEY (user_id,music_id));

CREATE TABLE music ( id int(4) NOT NULL, music_name varchar(32) NOT NULL, PRIMARY KEY (id));

INSERT INTO follow VALUES(1,2); INSERT INTO follow VALUES(1,4); INSERT INTO follow VALUES(2,3);

INSERT INTO music_likes VALUES(1,17); INSERT INTO music_likes VALUES(2,18); INSERT INTO music_likes VALUES(2,19); INSERT INTO music_likes VALUES(3,20); INSERT INTO music_likes VALUES(4,17);

INSERT INTO music VALUES(17,'yueyawang'); INSERT INTO music VALUES(18,'kong'); INSERT INTO music VALUES(19,'MOM'); INSERT INTO music VALUES(20,'Sold Out');

输出： user_id music_name 1 kong 1 MOM 2 Sold Out

USE mymusic;
########## Begin ##########
select distinct follow.user_id,music_name
from follow,music_likes,music
where music.id=music_likes.music_id and follow.follower_id=music_likes.user_id and music_id not in(
    select music_id
    from music_likes ml1
    where follow.user_id=ml1.user_id
)
order by follow.user_id,music.music_name;
########## End ##########

第4关：

现有3张业务表，详见如下:

需要输出结果如下，没有支付的日期不需要显示，请写出对应的MYSQL

示例1

输入： CREATE TABLE new_user( user_id int(11) NOT NULL, is_new int(11) NOT NULL, PRIMARY KEY (user_id) ); CREATE TABLE user_pay( user_id int(11) NOT NULL, pay_money int(11) NOT NULL, dt char(20) NOT NULL ); CREATE TABLE login_record( user_id int(11) NOT NULL, login_time char(20) NOT NULL, dt char(20) NOT NULL ); insert into new_user values(1,0); insert into new_user values(2,1); insert into new_user values(3,1); insert into new_user values(4,0); insert into new_user values(5,0); insert into new_user values(6,1); insert into new_user values(7,0); insert into new_user values(8,1); insert into user_pay values(1,30,'2021-11-10'); insert into user_pay values(1,100,'2021-11-10'); insert into user_pay values(2,500,'2021-11-11'); insert into user_pay values(2,200,'2021-11-12'); insert into user_pay values(3,1000,'2021-11-10'); insert into user_pay values(4,800,'2021-11-12'); insert into user_pay values(6,1200,'2021-11-10'); insert into user_pay values(6,700,'2021-11-14'); insert into login_record values(1,'8:00','2021-11-10'); insert into login_record values(1,'12:00','2021-11-10'); insert into login_record values(1,'13:00','2021-11-10'); insert into login_record values(2,'16:00','2021-11-10'); insert into login_record values(2,'12:35','2021-11-11'); insert into login_record values(2,'18:34','2021-11-12'); insert into login_record values(3,'20:00','2021-11-10'); insert into login_record values(4,'21:00','2021-11-12'); insert into login_record values(5,'21:00','2021-11-13'); insert into login_record values(6,'22:30','2021-11-10'); insert into login_record values(6,'13:30','2021-11-14');

输出： dt dau dau_new total_pay total_pay_new 2021-11-10 4 3 2330 2200 2021-11-11 1 1 500 500 2021-11-12 2 1 1000 200 2021-11-14 1 1 700 700

USE myusers;
########## Begin ##########
create view top(dt,total_pay) as
select dt,sum(pay_money)
from user_pay
group by dt;

create view login(dt,dau) as
select dt,count(distinct user_id)
from login_record
group by dt;

create view np(dt,total_pay_new) as
select dt,sum(pay_money)
from user_pay,new_user
where new_user.is_new=1 and user_pay.user_id=new_user.user_id
group by dt;

create view nl(dt,dau_new) as
select dt,count(distinct login_record.user_id)
from login_record,new_user
where new_user.is_new=1 and login_record.user_id=new_user.user_id
group by dt;



select login.dt,dau,dau_new,total_pay,total_pay_new
from login,nl,top,np
where login.dt=nl.dt and nl.dt=top.dt and top.dt=np.dt
order by dt;

########## End ##########

第5关：本关任务：查询某网站每个日期新用户的次日留存率。

某网站每天有很多人登录，请你统计一下该网站每个日期新用户的次日留存率。有一个登录(login)记录表，简况如下:

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了该网站，因为是第1次登录，所以是新用户。

第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了该网站，因为是第2次登录，所以是老用户。

最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了该网站，因为是第2次登录，所以是老用户。

请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明: 2020-10-12登录了3个(user_id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667; 2020-10-13没有新用户登录，输出0.000; 2020-10-14登录了1个(user_id为4)新用户，2020-10-15，user_id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000; 2020-10-15没有新用户登录，输出0.000;

注意: 1、本题的新用户不是真正的新用户定义，是本题给出的新用户定义； 2、MYSQL里计算日期t2与日期t1差的函数为:datediff(t2,t1); 3、MYSQL对某字段total四舍五入保留3位的函数为:round(total,3); 4、MYSQL对把某字段total为空的值置为0的函数为：ifnull(total,0)。

示例1

输入： drop table if exists login; CREATE TABLE login ( id int(4) NOT NULL, user_id int(4) NOT NULL, client_id int(4) NOT NULL, date date NOT NULL, PRIMARY KEY (id));

INSERT INTO login VALUES (1,2,1,'2020-10-12'), (2,3,2,'2020-10-12'), (3,1,2,'2020-10-12'), (4,2,2,'2020-10-13'), (5,1,2,'2020-10-13'), (6,3,1,'2020-10-14'), (7,4,1,'2020-10-14'), (8,4,1,'2020-10-15');

输出： date p 2020-10-12 0.667 2020-10-13 0.000 2020-10-14 1.000 2020-10-15 0.000

USE mydata;
#请在此处添加实现代码
########## Begin ##########
select x.date,
#三位小数
round(count(y.user_id)/count(x.user_id),3) as p
from(
    select user_id,min(date) as date
    from login
    group by user_id
)x
#新建一个表
left join login y on x.user_id=y.user_id and 
#函数计算两者日期
y.date=date_add(x.date,interval+1 day)
group by x.date
union
#不在小日期
select date,0 as p
from login
where date not in(
    select min(date)
    from login
    group by user_id
)
order by date;

########## End ##########

第6关：有的同学会购买网上训练课程来学习，某教育网站对购买记录会产生订单存到数据库，有一个订单信息表(order_info)，表内容如下：

第1行表示user_id为557336的用户在2021-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。第2行为购买成功。

查询满足以下条件的用户：在2021-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足该条件的第一次购买成功的日期first_buy_date，以及满足前面条件的第二次购买成功的日期second_buy_date，以及购买成功的次数cnt，并且输出结果按照user_id升序排序。

函数提示 1、row_number() over(partition by某字段 order by 某字段)：窗口函数按某字段分组，生成从1开始的顺序编号。 2、count(*) over(partition by 某字段)：求分组后的总数。

示例1

输入： drop table if exists order_info; CREATE TABLE order_info( id int(4) NOT NULL, user_id int(4) NOT NULL, product_name char(20) NOT NULL, status char(20) NOT NULL, client_id int(4) NOT NULL, date date NOT NULL, PRIMARY KEY (id));

INSERT INTO order_info VALUES (1,557336,'C++','no_completed',1,'2021-10-10'), (2,230173543,'Python','completed',2,'2021-10-12'), (3,230173543,'Python','completed',2,'2021-10-15'), (4,557336,'Java','completed',1,'2021-10-15'), (5,230173543,'Java','no_completed',2,'2021-10-16'), (6,230173543,'Java','completed',2,'2021-10-20'), (7,230173543,'C++','completed',2,'2021-10-21'), (8,557336,'Java','completed',1,'2021-10-21'), (9,557336,'离散数学','completed',1,'2021-10-22'), (10,230173543,'离散数学','completed',2,'2021-10-22'), (11,663466,'离散数学','completed',3,'2021-10-22'), (12,663466,'C++','completed',3,'2021-10-22'), (13,663466,'Python','no_completed',3,'2021-10-23'), (14,663466,'Python','completed',3,'2021-10-24'), (15,663466,'C++','completed',3,'2021-10-25'), (16,8912311,'Java','completed',1,'2021-10-25'), (17,3345600,'高等数学','completed',1,'2021-10-27'), (18,3345600,'数学分析','completed',1,'2021-10-28'), (19,3345600,'数据库系统','completed',1,'2021-10-28');

输出： user_id first_buy_date second_buy_date cnt 663466 2021-10-22 2021-10-24 3 230173543 2021-10-20 2021-10-21 2

USE mydata;
#请在此处添加实现代码
########## Begin ##########
select user_id,min(case when num=1 then date end) as first_buy_date,max(case when num=2 then date end) as second_buy_date,count(1) as cnt

from (
    select*,row_number() over(partition by user_id order by date) as num
    from order_info
    where status='completed' and date>'2021-10-15' and product_name in ('C++','Java','Python'))v
group by user_id having count(1)>=2;

########## End ##########

第7关：查询各工程号最大的按零件号合计供应量以及该零件号，并先按工程号升序，再按零件号升序排序。

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商供应某种零件给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
create view calculate(JNO,PNO,maxsum) as 
select JNO,PNO,sum(QTY)
from SPJ
group by JNO,PNO
order by JNO,PNO;
select JNO,PNO,maxsum
from calculate as x
where x.maxsum>=all(
    select maxsum 
    from calculate as y
    where x.JNO=y.JNO
);
########## End ##########

stearm210

关注

5
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库系统头歌实验五 SQL复杂业务查询

假设音乐数据库里面现在有几张如下简化的数据表: 关注follow表,第一列是关注人的id,第二列是被关注人的id,这2列的id组成主键 +---------+-------------+ | user_id | follower_id | +---------+-------------+ | 1 | 2 | | 1 | 4 | | 2 | 3 | +---------+-------------+4、MYSQL对把某字段total为空的值置为0的函数为：ifnull(total,0)。
复制链接

扫一扫