目录

  • 一、最爱考的sql笔试题
  • 1.1 创建表及数据
  • 1.2 笔试题
  • 1.2.1 查询每门课程的最高分、最低分、平均分
  • 1.2.2 查询每门课的最高分学生的信息
  • 1.2.3 查询每每门课的前三名
  • 1.2.4 删除自动编号不同其他信息相同的学生
  • 二、课程题
  • 2.1 查询每门课程成绩都大于80分学生的学号
  • 2.2 查询课程001的成绩大于课程002成绩的学号
  • 三、推测题
  • 3.1 已知表中推测sql
  • 3.2 表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列
  • 3.3 请取出当天的记录
  • 3.4 按显示格式写出sql语句
  • 3.5 从tab1,tab2中取出如tab3所列格式数据
  • 3.6 使用一条SQL求出四个球队所有比赛组合
  • 3.7 有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value
  • 3.8 按结果显示写出sql语句
  • 3.9 计算总订单价格大于100的订单号和总订单价格


一、最爱考的sql笔试题

大佬们___先活动活动颈椎

mysql 时间大于15天 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的订单号和总订单价格

mysql 时间大于15天 mysql 时间大于昨天_Server_02

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;