百分点sql练习

最新推荐文章于 2024-07-25 23:12:39 发布

前事不忘后事之师

最新推荐文章于 2024-07-25 23:12:39 发布

阅读量239

点赞数 1

分类专栏：百分点文章标签： sql

本文链接：https://blog.csdn.net/weixin_45211029/article/details/115028356

版权

百分点专栏收录该内容

1 篇文章 0 订阅

订阅专栏

基础sql，hql练习

1.数据准备
2.练习题目

1.数据准备

DROP TABLE IF EXISTS `t_person_base_info`; 
CREATE TABLE `t_person_base_info` (
  `id_card` varchar(18) comment '身份证号'  DEFAULT NULL,
  `name` varchar(20) comment '名称'  DEFAULT NULL,
  `gender` varchar(2) comment '性别'  DEFAULT NULL,
  `job` varchar(50) comment '岗位'  DEFAULT NULL
) comment '人员基本信息表' ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_person_base_info` VALUES ('330726199609043116', '周子明', '男', '销售');
INSERT INTO `t_person_base_info` VALUES ('330722198311191419', '陈晓', '女', '销售');
INSERT INTO `t_person_base_info` VALUES ('330719199105264310', '王志龙', '男', '研发');
INSERT INTO `t_person_base_info` VALUES ('330727198404140715', '陈狄鑫', '男', '研发');
INSERT INTO `t_person_base_info` VALUES ('330726198106171719', '应勇', '男', '销售');
INSERT INTO `t_person_base_info` VALUES ('330702199308073224', '李霞', '女', '前端');
INSERT INTO `t_person_base_info` VALUES ('330722199003290811', '潘绍', '男', '研发');
INSERT INTO `t_person_base_info` VALUES ('33072219930829648X', '何娜', '女', '前端');

DROP TABLE IF EXISTS `t_shopping_records`;
CREATE TABLE `t_shopping_records` (
  `id_card` varchar(18) comment '身份证号' DEFAULT NULL,
  `goods_name` varchar(50) comment '商品名称' DEFAULT NULL,
  `price` decimal(10,2) comment '单价'  DEFAULT NULL,
  `num` int(11) comment '购买数量'  DEFAULT NULL,
  `order_time` datetime comment '下单时间'  DEFAULT NULL
) comment '购物信息记录' ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_shopping_records` VALUES ('330726199609043116', '面包', '5.00', '3', '2020-12-01 14:33:59');
INSERT INTO `t_shopping_records` VALUES ('330726199609043116', '矿泉水', '3.00', '5', '2020-12-02 14:34:04');
INSERT INTO `t_shopping_records` VALUES ('330726199609043116', '袜子', '8.00', '3', '2020-12-02 14:34:08');
INSERT INTO `t_shopping_records` VALUES ('330702199308073224', '矿泉水', '3.00', '8', '2020-12-03 14:34:12');
INSERT INTO `t_shopping_records` VALUES ('330702199308073224', '乳酸奶', '10.00', '2', '2020-12-05 14:34:16');
INSERT INTO `t_shopping_records` VALUES ('33072219930829648X', '篮球', '180.00', '1', '2020-12-05 14:34:21');
INSERT INTO `t_shopping_records` VALUES ('33072219930829648X', '袜子', '7.00', '6', '2020-12-07 14:34:27');
INSERT INTO `t_shopping_records` VALUES ('330722198311191419', '面包', '4.00', '5', '2020-12-08 14:34:31');
INSERT INTO `t_shopping_records` VALUES ('330722198311191419', '手机', '2000.00', '1', '2020-12-09 14:34:35');
INSERT INTO `t_shopping_records` VALUES ('330719199105264310', '矿泉水', '4.00', '2', '2020-12-01 14:34:40');
INSERT INTO `t_shopping_records` VALUES ('330719199105264310', '篮球', '190.00', '2', '2020-12-03 14:34:45');
INSERT INTO `t_shopping_records` VALUES ('330719199105264310', '面包', '5.00', '7', '2020-12-04 14:34:49');
INSERT INTO `t_shopping_records` VALUES ('330719199105264310', '笔记本', '3000.00', '1', '2020-12-18 14:34:53');
INSERT INTO `t_shopping_records` VALUES ('330727198404140715', '袜子', '15.00', '6', '2020-12-09 14:34:57');
INSERT INTO `t_shopping_records` VALUES ('330727198404140715', '球鞋', '399.00', '1', '2020-12-06 14:35:01');
INSERT INTO `t_shopping_records` VALUES ('330727198404140715', '电池', '20.00', '2', '2020-12-07 14:35:06');
INSERT INTO `t_shopping_records` VALUES ('330722199003290811', '矿泉水', '3.50', '4', '2020-12-09 14:35:10');
INSERT INTO `t_shopping_records` VALUES ('330722199003290811', '手机', '1999.00', '2', '2020-12-04 14:35:13');
INSERT INTO `t_shopping_records` VALUES ('330722199003290811', '面包', '7.90', '2', '2020-12-14 14:35:17');
INSERT INTO `t_shopping_records` VALUES ('330722199003290811', '乳酸奶', '9.90', '1', '2020-12-16 14:35:21');
INSERT INTO `t_shopping_records` VALUES ('33072219930829648X', '笔记本', '2999.00', '1', '2020-12-08 14:35:25');
INSERT INTO `t_shopping_records` VALUES ('33072219930829648X', '面包', '4.90', '5', '2020-12-02 14:35:31');

2.练习题目

练习一

要求描述：
查询表t_person_base_info中job为‘前端’或‘研发’的所有内容

预期结果：

sql：

select * from ly_t_person_base_info 
where job='前端' or job='研发'

hql：

select * from ly_dm.dm_ly_t_person_base_info 
where job='前端' or job='研发'

练习二

要求描述：
查询表t_person_base_info中gender为‘男’ 和 ‘女’各有多少人

预期结果：

sql：

select gender,count(*) from t_person_base_info 
group by gender

hql：

select gender,count(*) from ly_dm.dm_ly_t_person_base_info 
group by gender

练习三

要求描述：
查询表t_person_base_info中job为‘销售’ 的不同性别各有多少人

预期结果：

**sql：** ```sql select job,gender,count(*) from t_person_base_info where job='销售' group by gender ``` **hql：** ```sql select job,gender,count(*) from ly_dm.dm_ly_t_person_base_info where job='销售' group by gender,job ```

练习四

要求描述：
查询表t_person_base_info中年龄大于30岁的所有人

预期结果：

sql：

 SELECT id_card,name,gender, jobfrom t_person_base_info
 where  year(now()) -year(substring(id_card,7,8) )>30;

hql：

 SELECT id_card,name,gender,job from  ly_dm.dm_ly_t_person_base_info
 where datediff(from_unixtime(unix_timestamp(),"yyyy-MM-dd"),to_date(from_unixtime(UNIX_TIMESTAMP(substring(id_card,7,8),'yyyyMMdd'))))>10950;

练习五

要求描述：
查询表t_person_base_info中gender标记错误的人

注意：要了解身份证18位每位数字的含义

预期结果：

sql：

SELECT id_card,name,gender,job,
case id_card when (substring(id_card,17,1)+0)%2=1 then "男" else "女"  end as cc
from t_person_base_info
where ((substring(id_card,17,1)+0)%2=1 and gender="女") or ((substring(id_card,17,1)+0)%2=0 and gender="男");

hql：

SELECT id_card,name,gender,job,
case  when (substring(id_card,17,1)+0)%2=1 then '男' else '女'  end as cc
from ly_dm.dm_ly_t_person_base_info
where ((substring(id_card,17,1)+0)%2=1 and gender='女') or ((substring(id_card,17,1)+0)%2=0 and gender='男');

练习六

要求描述：
查询表t_shopping_records中每个人花费总额

预期结果：

sql：

SELECT id_card,sum(price*num) all_cost
from t_shopping_records
group by id_card;

hql：

SELECT id_card,sum(price*num) all_cost
from ly_dm.dm_ly_t_shopping_records
group by id_card;

练习七

要求描述：
查询表t_shopping_records中销售数量最多的商品

预期结果：

sql：

SELECT goods_name,sum(num)  all_num
from t_shopping_records
group by goods_name
order by all_num desc
LIMIT 1;

hql：

SELECT goods_name,sum(num)  all_num
from ly_dm.dm_ly_t_shopping_records
group by goods_name
order by all_num desc
LIMIT 1;

练习八

要求描述：
查询表t_shopping_records中每天销售总额，按日期排序递增

预期结果：

sql：

SELECT order_time,sum(price*num) all_cost
FROM t_shopping_records
group by substring(order_time,1,10)
order by order_time asc;

hql：

SELECT collect_set(order_time) a,sum(price*num) all_cost
FROM ly_dm.dm_ly_t_shopping_records
group by substring(order_time,1,10)
order by a asc;

练习九

要求描述：
查询表t_shopping_records中goods_name为“矿泉水”的单价走势情况

预期结果：

sql：

SELECT goods_name,substring(order_time,1,10) as day,price
FROM t_shopping_records
where goods_name="矿泉水"
order by order_time;

hql：

SELECT goods_name,substring(order_time,1,10) as day,price
FROM ly_dm.dm_ly_t_shopping_records
where goods_name="矿泉水"
order by day;

练习十

要求描述：
根据表t_person_base_info和表t_shopping_records关联判断，谁没有购物

预期结果：

sql：

select a.id_card,a.name,a.gender,a.job
from t_person_base_info a left join t_shopping_records s
on a.id_card = s.id_card
where s.id_card is null;

hql：

select a.id_card,a.name,a.gender,a.job
from ly_dm.dm_ly_t_person_base_info a left join ly_dm.dm_ly_t_shopping_records s
on a.id_card = s.id_card
where s.id_card is null;

练习十一

要求描述：
查询表t_shopping_records统计每个人商品最多花费情况

预期结果：

sql：

SELECT id_card,pr cost,goods_name
from
( select id_card,goods_name,sum(price*num) pr
from t_shopping_records
group by id_card,goods_name
ORDER BY pr DESC
) t
group by id_card;

hql：

SELECT id_card,pr cost,goods_name
from
( select id_card,goods_name,sum(price*num) pr
from ly_dm.dm_ly_t_shopping_records
group by id_card,goods_name
ORDER BY pr DESC
) t
group by id_card;

练习十二

要求描述：
根据表t_person_base_info和表t_shopping_records关联判断，每个人总消费情况

预期结果：

sql：

SELECT p.id_card,P.name,P.gender,P.job,s.pr all_cost
FROM(select id_card,sum(price*num) pr
from t_shopping_records
group by id_card
) s right join t_person_base_info p
on s.id_card = p.id_card;

hql：

SELECT p.id_card,P.name,P.gender,P.job,s.pr all_cost
FROM(select id_card,sum(price*num) pr
from ly_dm.dm_ly_t_shopping_records 
group by id_card
) s right join ly_dm.dm_ly_t_person_base_info p
on s.id_card = p.id_card;

前事不忘后事之师

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
5
评论
百分点sql练习

基础sql，hql练习1.数据准备2.练习题目练习一练习二练习三练习四练习五练习六练习七练习八练习九练习十练习十一练习十二1.数据准备DROP TABLE IF EXISTS `t_person_base_info`; CREATE TABLE `t_person_base_info` ( `id_card` varchar(18) comment '身份证号' DEFAULT NULL, `name` varchar(20) comment '名称' DEFAULT NULL, `g
复制链接

扫一扫

专栏目录