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

数据表

创建学生数据表

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;

步骤一

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;