mysql 练习

一、最爱考的sql笔试题

大佬们___先活动活动颈椎

1.1 创建表及数据

/*
 Navicat MySQL Data Transfer

 Source Server         : 三丰153
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : 43.226.148.253:3306
 Source Schema         : seata-test

 Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001

 Date: 28/03/2021 10:18:44
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自动编号',
  `sno` bigint(20) NOT NULL COMMENT '学号',
  `name` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学生姓名',
  `sex` int(2) NULL DEFAULT NULL COMMENT '1-男，2-女',
  `cno` bigint(20) NULL DEFAULT NULL COMMENT '课程号',
  `score` float(64, 0) NULL DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 63 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (36, 4, 'zhangsan4', 1, 2, 99);
INSERT INTO `student` VALUES (38, 5, 'zhangsan5', 1, 6, 47);
INSERT INTO `student` VALUES (40, 6, 'zhangsan6', 1, 7, 37);
INSERT INTO `student` VALUES (42, 5, 'zhangsan5', 1, 7, 98);
INSERT INTO `student` VALUES (43, 6, 'zhangsan6', 1, 1, 63);
INSERT INTO `student` VALUES (44, 1, 'zhangsan1', 1, 6, 81);
INSERT INTO `student` VALUES (45, 5, 'zhangsan5', 1, 1, 80);
INSERT INTO `student` VALUES (46, 3, 'zhangsan3', 1, 2, 99);
INSERT INTO `student` VALUES (47, 4, 'zhangsan4', 1, 1, 73);
INSERT INTO `student` VALUES (48, 4, 'zhangsan4', 1, 7, 53);
INSERT INTO `student` VALUES (49, 2, 'zhangsan2', 1, 1, 47);
INSERT INTO `student` VALUES (50, 4, 'zhangsan4', 1, 6, 85);
INSERT INTO `student` VALUES (51, 3, 'zhangsan3', 1, 1, 64);
INSERT INTO `student` VALUES (52, 5, 'zhangsan5', 1, 2, 53);
INSERT INTO `student` VALUES (53, 6, 'zhangsan6', 1, 2, 44);
INSERT INTO `student` VALUES (54, 6, 'zhangsan6', 1, 6, 77);
INSERT INTO `student` VALUES (55, 2, 'zhangsan2', 1, 7, 58);
INSERT INTO `student` VALUES (56, 3, 'zhangsan3', 1, 7, 97);
INSERT INTO `student` VALUES (57, 2, 'zhangsan2', 1, 6, 68);
INSERT INTO `student` VALUES (58, 2, 'zhangsan2', 1, 2, 59);
INSERT INTO `student` VALUES (59, 1, 'zhangsan1', 1, 1, 88);
INSERT INTO `student` VALUES (60, 1, 'zhangsan1', 1, 2, 84);
INSERT INTO `student` VALUES (61, 3, 'zhangsan3', 1, 6, 66);
INSERT INTO `student` VALUES (62, 1, 'zhangsan1', 1, 7, 89);

SET FOREIGN_KEY_CHECKS = 1;

1.2 笔试题

1.2.1 查询每门课程的最高分、最低分、平均分

select max(score),min(score),avg(score)  from student group by cno

1.2.2 查询每门课的最高分学生的信息

 select cno,sno,name,sex,score from student t,
 (select cno,max(score) score from student group by cno) s
 where t.cno = s.cno and t.score = s.score;

1.2.3 查询每每门课的前三名

 select  t1.*
 from student t1 where 
 (select count(1) from student t2 where t1.cno = t2.cno)   <= 3
 order by cno,score desc;  

1.2.4 删除自动编号不同其他信息相同的学生

 delete from
 student 
 where id not in
 ( 
  select min(temp.id) as id from 
  (select * from student) as temp
  group by 
    temp.cno,
    temp.sno,
    temp.name,
    temp.score
 )

二、课程题

2.1 查询每门课程成绩都大于80分学生的学号

表 student

name	score	course
A	85	语文
A	75	数学
A	82	英语
B	75	语文
B	89	数学
B	79	英语
天使美眉	90	语文
天使美眉	100	数学
天使美眉	100	英语

#SQL1:
select name from student
group by name  having min(score) > 80;
#SQL2:
select name from student
group by name having count(score) = sum(case when score > 0 then 1 else 0 end) 
#SQL3
select name from student 
where name not in 
(select distinct(name) from student where socre <= 80);

2.2 查询课程001的成绩大于课程002成绩的学号

• student表：sno(学号)，sname(姓名)，sex(性别)，dept(系)
• course课程表：cno(课程号)，课程名(cname)
• sc选课表：sno，cno，grade(成绩)

 #SQL1
 select sno from 
 (select sc.* from sc,course where sc.cno = course.cno and cname = '001' ) one
 inner join 
 (select sc.* from sc,course where sc.cno = course.cno and cname = '002' ) two
 on one.sno = two.sno where one.grade > two.grade;

三、推测题

3.1 已知表中推测sql

time	grade
2005-05-09	胜
2005-05-09	胜
2005-05-09	负
2005-05-09	负
2005-05-10	胜
2005-05-10	负
2005-05-10	负

如果要生成下列结果, 该如何写sql语句

time	胜	负
2005-05-09	2	2
2005-05-10	1	2

#SQL1
select time,sum(case grade = '胜' when 1 else 0 end) 胜,
sum(case grade = '负' when 1 else 0 end) 负 from tab
group by time;
#SQL2
select t1.time,t1.sucess as 胜,t2.fail as 负
(select time,count(1) sucess from tab where grade = '胜' group by time ) t1,
(select time,count(1) fail from tab where grade = '负' group by time) t2
where t1.time and t2.time;
#SQL3
select t1.time,t1.sucess as 胜,t2.fail as 负
(select time,count(1) sucess from tab where grade = '胜' group by time ) t1 inner join
(select time,count(1) fail from tab where grade = '负' group by time) t2
on t1.time and t2.time;

3.2 表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列

select  case(when A > B then A else B end),case(when B > C then B else C end)  from tab ;

3.3 请取出当天的记录

• 一个日期判断的sql语句请取出表中日期(SendTime字段)为当天的所有记录 (SendTime字段为datetime型，包含日期与时间)

select  *  from tab where to_char(SendTime,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd');

3.4 按显示格式写出sql语句

• 有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路)：
• 大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
  显示格式：

语文	数学	英语
及格	优秀	不及格

select 
case(when 语文 >= 80 then '优秀' when 语文 >= 60 then '及格' else '不及格' end) 语文,
case(when 数学 >= 80 then '优秀' when 数学 >= 60 then '及格' else '不及格' end) 数学,
case(when 英语 >= 80 then '优秀' when 英语 >= 60 then '及格' else '不及格' end) 英语
from tab

3.5 从tab1,tab2中取出如tab3所列格式数据

tab1:

mon	dep	yj
一月份	01	10
一月份	02	10
一月份	03	5
二月份	02	8
二月份	04	9
三月份	03	8

tab2:

dep	depname
01	国内业务一部
02	国内业务二部
03	国内业务三部
04	国际业务部

tab3 (result):

dep	一月份	二月份	三月份
01	10	null	null
02	10	8	null
03	5	null	8
04	null	9	null

select dep 
(select yj  from tab1  where mon = '一月份' and tab1.dep = t.dep ) 一月份,
(select yj  from tab1  where mon = '二月份' and tab1.dep = t.dep) 二月份,
(select yj  from tab1  where mon = '三月份' and tab1.dep = t.dep) 三月份
 from  tab2 t ; 

求总销售额

select 
sum(case when mon = '一月份' then yj else 0 end)  一月份 ,
sum(case when mon = '二月份' then yj else 0 end)  二月份 ,
sum(case when mon = '三月份' then yj else 0 end)  三月份 
from  tab1 , tab2 where tab1.dep = tab2.dep;

3.6 使用一条SQL求出四个球队所有比赛组合

• SQL题目：
  一个表team，里面只有一个字段name, 一共有4条纪录，分别是a,b,c,d, 对应四个球队，现在四个球队进行比赛，
  用一条sql语句显示所有可能的比赛组合。
• 创建表和插入数据

create table team(name varchar2(30));
 
insert into team values('a');
insert into team values('b');
insert into team values('c');
insert into team values('d');

• 思路：

使用笛卡尔积连接，并使当前大小值只与比它大的值连接。

select t1.name,t2.name from team t1,team t2 where t1.name < t2.name;

3.7 有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value

update  A set A.value = B.value where key in (select key from B where A.key = B.key);

3.8 按结果显示写出sql语句

courseid	coursename	score
1	java	70
2	oracle	90
3	xml	40
4	jsp	30
5	servlet	80

---

为了便于阅读,查询此表后的结果显式如下(及格分数为60):

courseid	coursename	score	mark
1	java	70	pass
2	oracle	90	pass
3	xml	40	fail
4	jsp	30	fail
5	servlet	80	pass

select *,case(when score >= 60 then 'pass' else 'fail' end) mark from tab ;

3.9 计算总订单价格大于100的订单号和总订单价格

o_num	o_item	f_id	quantity	item_price
30001	1	a1	10	5.2
30001	2	b2	3	6.00
30001	3	bs1	5	11.2
30001	4	bs2	15	9.2
30002	1	b3	2	20.00
30003	1	c0	100	10.00
30004	1	o2	50	2.50
30005	1	c0	5	10.00
30005	2	b1	10	8.99
30005	4	a2	10	2.20
30005	4	m1	5	14.99

select   o_num,sum(quantity*item_price) as orderTotal from  orderitems group by o_num having sum(quantity*item_price) >= 100;