二、SQL编程题(总分40分) 要求:提交效果截图或演示视频到码云
1、题目一
有如下三张数据表
学生表:student(studentNo,studentName,age,sex)
课程表:course(courseNo,courseName,tearcher)
学生得分表:student_course(studentNo,courseNo,grade)
编写SQL语句,完成如下需求
1)查询有课程大于80分又有课程低于60分的学生的学号(5分)
2) 查询各门课程去掉一个最高分和最低分后的平均成绩(5分)
解题思路:
先创建3张表并填入测试数据
第1)题:
先查出分数小于60的学生学号,然后在查出分数大于80的学生学号,最后取并集即可
第2)题:
先按课程号分组,求出每门课程总成绩之后减去最高分和最低分,之后除以剩下的课程门数即可求出平均分
2、题目二
表a
±—+
| userid |
±—+
| 111 |
| 222 |
| 333 |
| 777|
±—+
表b
±—+
| userid |
±—+
| 111 |
| 444|
| 333|
±—+
1)编写一段sql,查找a、b表都有的userid(5分)
解题思路:直接使用inner join即可
2)编写一段sql,查找a中有b中没有的userid(5分)
解题思路:直接not in即可
3、题目三
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱(10分)。
示例:
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
根据以上输入,你的查询应返回以下结果:
±--------+
| Email |
±--------+
| a@b.com |
±--------+
说明:所有电子邮箱都是小写字母。
解题思路:
直接按照Email分组计数,将数量大于1的值取出来即可
4、题目四
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary)(10分) 。
±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+
解题思路:
先按照salary倒叙排,取前2条,然后在从这2条中按salary正序排,取第一条即可