python接口自动化+测试开发面试题

关于面试总结1-SQL学生表

 

前言

每次面试必考SQL,小编这几年一直吃SQ的亏，考题无非就是万年不变学生表，看起来虽然简单，真正写出来，还是有一定难度。于是决定重新整理下关于SQL的面试题，也可以帮助更多的人过SQL这一关。
作为一个工作3年以上测试人员，不会sql基本上能拿到offer的希望渺茫，虽然平常也会用到数据库，都是用的简单的查询语句。困难一点的就直接找开发了，面试想留个好印象，还是得熟练掌握，能在纸上快速写出来。

• 1.查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低
• 2.统计每个学生的总成绩,显示字段：姓名，总成绩
• 3.统计每个学生的总成绩（由于学生可能有重复名字）,显示字段：学生id，姓名，总成绩
• 4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩
• 5.列出各门课程成绩最好的2位学生， 要求显示字段: 学号，姓名, 科目，成绩

万年不变学生表

有2张表，学生表（student）基本信息如下

科目和分数表（grade）

排序order by

1.查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低

SELECT a.name, b.score
FROM student a, grade b
WHERE a.id = b.id
AND kemu = '数学'
ORDER BY score
DESC

统计总成绩sum

2.统计每个学生的总成绩,显示字段：姓名，总成绩

SELECT a.name, sum(b.score) as sum_score
FROM student a, grade b
WHERE a.id = b.id
GROUP BY name
DESC

统计总成绩

3.统计每个学生的总成绩（由于学生可能有重复名字）,显示字段：学生id，姓名，总成绩

SELECT a.id, a.name, c.sum_score
from student a, 
(SELECT b.id, sum(b.score) as sum_score
FROM grade b
GROUP BY id
) c
WHERE a.id = c.id
ORDER BY sum_score
DESC

统计单科最好成绩

4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩

第一步先group by找出单科最好成绩，作为第一张表

SELECT b.kemu, MAX(b.score)
FROM grade b
GROUP BY kemu

再结合学生表和分数表，得到单科最好成绩

-- 单科最好的成绩
SELECT c.id , a.name, c.kemu, c.score

FROM grade c, student a,
(SELECT b.kemu, MAX(b.score) as max_score
FROM grade b
GROUP BY kemu) t
WHERE c.kemu = t.kemu
AND c.score = t.max_score
AND a.id = c.id

总结 group by相关用法

函数	作用	支持性
sum(列名)	求和
max(列名)	最大值
min(列名)	最小值
avg(列名)	平均值
first(列名)	第一条记录	仅Access支持
last(列名)	最后一条记录	仅Access支持
count(列名)	统计记录数	注意和count(*)的区别

各门课程成绩最好的2位学生

5.列出各门课程成绩最好的2位学生， 要求显示字段: 学号，姓名, 科目，成绩

SELECT t1.id, a.name, t1.kemu,t1.score
FROM grade t1, student a
WHERE
	(SELECT count(*) FROM grade t2 
	WHERE t1.kemu=t2.kemu AND t2.score>t1.score
	)<2
and a.id = t1.id
ORDER BY t1.kemu,t1.score 
DESC

交流QQ群：779429633

 

标签: 面试题

好文要顶 关注我 收藏该文  

上海-悠悠
关注 - 73
粉丝 - 2821

 

 

+加关注

5

0

 

 

 

« 上一篇： python测试开发django-10.django连接mysql
» 下一篇： 关于面试总结2-SQL学生表

posted @ 2018-12-04 17:45  上海-悠悠  阅读(4672)  评论(3)  编辑  收藏

 

评论

 

   回复 引用

#1楼 2019-10-10 17:58 | 候鸟迁徙的信念

第5题，看不太懂呀

支持(0) 反对(0)

   回复 引用

#2楼 [楼主] 2019-10-10 22:29 | 上海-悠悠

@ 候鸟迁徙的信念
看不懂就跳过吧，考试不会考

支持(1) 反对(0)

   回复 引用

#3楼 2020-04-10 18:19 | vini

数据表
创建学生数据表
CREATE TABLE students(
stu_id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(32) NOT NULL,
stu_age INT NOT NULL
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
插入数据
INSERT INTO students(stu_id, stu_name, stu_age)
VALUES(1001, "张三", 18), (1002, "张四", 19), (1003, "张五", 20),
(1004, "张六", 19), (1005, "张六", 18);
创建成绩数据表
CREATE TABLE transcripts(
tscp_no INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
tscp_id INT(4) NOT NULL,
tscp_subject VARCHAR(32) NOT NULL,
tscp_score FLOAT NOT NULL,
foreign key(tscp_id) references students(stu_id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
插入数据
INSERT INTO transcripts(tscp_no, tscp_id, tscp_subject, tscp_score)
VALUES( 1, 1001, "语文", 85), ( 2, 1001, "数学", 86), ( 3, 1002, "英语", 98),
( 4, 1002, "语文", 94), ( 5, 1002, "数学", 98), ( 6, 1003, "数学", 56),
( 7, 1003, "语文", 69), ( 8, 1003, "英语", 68), ( 9, 1004, "英语", 99),
(10, 1004, "数学", 97), (11, 1005, "数学", 93), (12, 1005, "英语", 45);
01、查询所有学生的数学成绩，显示学生姓名、科目和分数，由高到低；
语句一
SELECT s.stu_name, t.tscp_subject, t.tscp_score
FROM students s, transcripts t WHERE t.tscp_id = s.stu_id AND t.tscp_subject = "数学"
ORDER BY t.tscp_score DESC;
语句二
SELECT s.stu_name, t.tscp_subject, t.tscp_score FROM transcripts t
INNER JOIN students s ON t.tscp_id = s.stu_id AND t.tscp_subject="数学"
ORDER BY t.tscp_score DESC;

02、统计每个学生的总成绩，显示字段：姓名和总成绩；
语句一
SELECT s.stu_name, SUM(t.tscp_score) AS tscp_all_score
FROM students s, transcripts t WHERE s.stu_id = t.tscp_id GROUP BY t.tscp_id;
语句二
SELECT s.stu_name, SUM(t.tscp_score) AS tscp_all_score FROM transcripts t
INNER JOIN students s ON t.tscp_id = s.stu_id GROUP BY t.tscp_id;

03、统计每个学生的总成绩（由于学生可能有重复名字），显示字段：学生ID、姓名和总成绩；
SELECT s.stu_id, s.stu_name, SUM(t.tscp_score) AS tscp_all_score FROM transcripts t
INNER JOIN students s ON t.tscp_id = s.stu_id GROUP BY t.tscp_id;

04、列出各门课程成绩最好的学生，要求显示字段: 学号、姓名、科目和成绩；
SELECT s.stu_id, s.stu_name, t.tscp_subject, MAX(t.tscp_score) FROM transcripts t
INNER JOIN students s ON t.tscp_id = s.stu_id GROUP BY t.tscp_subject;

05、列出各门课程成绩最好的两位学生，要求显示字段: 学号、姓名、科目和成绩。
SELECT s.stu_id, s.stu_name, t1.tscp_subject, t1.tscp_score FROM transcripts t1
INNER JOIN students s ON t1.tscp_id = s.stu_id
LEFT JOIN transcripts t2 ON t1.tscp_subject = t2.tscp_subject AND t1.tscp_score ⇐ t2.tscp_score
GROUP BY t1.tscp_subject, t1.tscp_score HAVING COUNT(t2.tscp_score) < 3
ORDER BY t1.tscp_subject, t1.tscp_score DESC;

第五题参考博客：http://blog.sina.com.cn/s/blog_53d137da0102wx9t.html

步骤一

SELECT s.stu_id, s.stu_name, t1.tscp_subject, t1.tscp_score FROM transcripts t1
INNER JOIN students s ON t1.tscp_id = s.stu_id
ORDER BY t1.tscp_subject, t1.tscp_score DESC;

步骤二

SELECT s.stu_id, s.stu_name, t1.tscp_subject, t1.tscp_score, t2.tscp_score FROM transcripts t1
INNER JOIN students s ON t1.tscp_id = s.stu_id
LEFT JOIN transcripts t2 ON t1.tscp_subject = t2.tscp_subject AND t1.tscp_score ⇐ t2.tscp_score
ORDER BY t1.tscp_subject, t1.tscp_score DESC;

步骤三

SELECT s.stu_id, s.stu_name, t1.tscp_subject, t1.tscp_score, t2.tscp_score FROM transcripts t1
INNER JOIN students s ON t1.tscp_id = s.stu_id
LEFT JOIN transcripts t2 ON t1.tscp_subject = t2.tscp_subject AND t1.tscp_score ⇐ t2.tscp_score
GROUP BY t1.tscp_subject, t1.tscp_score HAVING COUNT(t2.tscp_score) < 3
ORDER BY t1.tscp_subject, t1.tscp_score DESC;

步骤四

SELECT s.stu_id, s.stu_name, t1.tscp_subject, t1.tscp_score FROM transcripts t1
INNER JOIN students s ON t1.tscp_id = s.stu_id
LEFT JOIN transcripts t2 ON t1.tscp_subject = t2.tscp_subject AND t1.tscp_score ⇐ t2.tscp_score
GROUP BY t1.tscp_subject, t1.tscp_score HAVING COUNT(t2.tscp_score) < 3
ORDER BY t1.tscp_subject, t1.tscp_score DESC;