java 自动化 20200325 数据库连接查询面试题作业

luojie · 2020年03月25日 · 最后由 yimh 回复于 2020年03月27日 · 577 次阅读

本节课作业:
#6 查询出语文最高分
#8 查询语数外三门成绩的平均分
#9 查询报名孤独求败老师课程的学生信息

表结构:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `Cno` int(11) NOT NULL COMMENT '课程编号',
  `Cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名称',
  `Tno` int(11) NULL DEFAULT NULL COMMENT '老师编号',
  PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '数学', 1);
INSERT INTO `course` VALUES (2, '语文', 2);
INSERT INTO `course` VALUES (3, '英文', 1);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `Sno` int(11) NOT NULL COMMENT '学号',
  `Cno` int(11) NOT NULL COMMENT '课程编号',
  `score` int(255) NULL DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`Sno`, `Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (1, 1, 99);
INSERT INTO `sc` VALUES (1, 2, 80);
INSERT INTO `sc` VALUES (1, 3, 50);
INSERT INTO `sc` VALUES (2, 1, 70);
INSERT INTO `sc` VALUES (2, 2, 90);
INSERT INTO `sc` VALUES (3, 1, 90);
INSERT INTO `sc` VALUES (4, 1, 60);
INSERT INTO `sc` VALUES (4, 2, 50);
INSERT INTO `sc` VALUES (4, 3, 40);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `Sno` int(255) NOT NULL COMMENT '学号',
  `Sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `Sage` int(255) NULL DEFAULT NULL COMMENT '年龄',
  `Ssex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三丰', 108, '男');
INSERT INTO `student` VALUES (2, '李小龙', 20, '男');
INSERT INTO `student` VALUES (3, '小龙女', 17, '女');
INSERT INTO `student` VALUES (4, '白发魔女', 18, '女');
INSERT INTO `student` VALUES (5, '韦小宝', 19, '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `Tno` int(11) NOT NULL COMMENT '老师编号',
  `Tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '老师名称',
  PRIMARY KEY (`Tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '无崖子');
INSERT INTO `teacher` VALUES (2, '孤独求败');
INSERT INTO `teacher` VALUES (3, '洪七公');

SET FOREIGN_KEY_CHECKS = 1;

实战sql:

# 15 - 20 
#1 查询年龄小于18岁的学员信息 (5)
select * from student a where a.Sage < 18;
#2 查询无崖子授课信息
select * from teacher d 
INNER JOIN course c on d.Tno = c.Tno
where d.TName = '无崖子';
#3 查询没有参与任意课程的学生信息
select * from student a LEFT JOIN sc b on a.Sno = b.Sno 
where b.Sno is null;
#4 查询无崖子每个授课课程的学员人数 统计 + 分组 
select c.Cno,c.Cname,count(*) from teacher d 
INNER JOIN course c on d.Tno = c.Tno 
INNER JOIN sc b on b.Cno = c.Cno 
where d.TName = '无崖子'
GROUP BY c.Cno,c.Cname;
#5 查询张三丰数学成绩
select * from student a INNER JOIN sc b on a.Sno = b.Sno
INNER JOIN course c on c.Cno = b.Cno
where a.Sname = '张三丰' and c.Cname = '数学';

#6 查询出语文最高分

#7 查询没有参与语文考试的学生信息
# 先查询出参加了语文课程学生
select * from course c 
INNER JOIN sc b on c.Cno = b.Cno and c.Cname = '语文'
RIGHT JOIN Student a on a.Sno = b.Sno 
where b.Sno is null;
# 和学生做外连接
#8 查询语数外三门成绩的平均分 

#9 查询报名孤独求败老师课程的学生信息

#10 没有报名孤独求败老师课程的学生信息
select * from teacher d 
INNER JOIN course c on d.Tno = c.Tno and d.Tname = '孤独求败'
INNER JOIN sc b on c.Cno = b.Cno
RIGHT JOIN Student a on a.Sno = b.Sno 
where b.Sno is null;

共收到 57 条回复

1: select MAX(score) from sc s inner join course c on s.Cno = c.Cno and c.Cname = '语文'

2: select AVG(s.score),c.Cname from sc s inner join course c on s.Cno = c.Cno GROUP BY c.Cname;

3: SELECT * from student s inner join sc c on s.Sno = c.Sno inner join course r on r.Cno = c.Cno inner join teacher t on t.Tno = r.Tno where t.Tname = '孤独求败'

youmaobing233 回复

老铁,没毛病

6 查询出语文最高分

select * from course a inner join sc b on a.Cno=b.Cno where a.Cname="语文" order by b.score limit 0, 1 desc;

8 查询语数外三门成绩的平均分

select b.Cname, avg(a.score), count(*) from sc a inner join course b on a.Cno=b.Cno inner join student c on c.Sno=a.Sno group by b.Cname, a.score;

9 查询报名独孤求败老师课程的学生信息

select * from teacher a inner join course b on a.Tno=b.Tno and a.Tname="独孤求败" inner join sc d on d.Sno=b.Sno right join student c on c.Sno=d.Sno where b.Cno is not null;

yingchun_x 回复

满分~

MaiSui 回复

第一题的解法有点6

luojie 回复

我发现我一个也没写对,全错了😤

12楼 已删除
1    查询出语文最高分 
select max(score) as '语文最高分' from sc inner join course as c on c.Cno=sc.Cno where c.Cname='语文' 
2    查询语数外三门成绩的平均分
select c.Cname,avg(sc.score)as '平均分' from course as c inner join sc on c.Cno=sc.Cno group by c.Cno,c.Cname 
3    查询报名孤独求败老师课程的学生信息 
select * from student as s inner join sc on s.Sno=sc.Sno inner join course as c on sc.Cno=c.Cno right join teacher as t on t.Tno=c.Tno where t.Tname='孤独求败'

#6 查询出语文最高分 SELECT MAX(sc.score),course.Cname from lemon_sc as sc INNER JOIN lemon_course as course on course.Cno=sc.Cno and course.Cname like '%语文'

#8 查询语数外三门成绩的平均分 SELECT AVG(sc.score),course.Cname from lemon_sc as sc INNER JOIN lemon_course as course on course.Cno=sc.Cno GROUP BY course.Cno

#9 查询报名孤独求败老师课程的学生信息 SELECT stu.* from lemon_student as stu INNER JOIN lemon_sc as sc on stu.Sno=sc.Sno INNER JOIN lemon_course as cou on sc.Cno=cou.Cno INNER JOIN lemon_teacher as tea on tea.Tno=cou.Tno where tea.Tname ='孤独求败'

#6 查询出语文最高分
SELECT c.Cno,c.Cname,MAX(sc.score) FROM sc 
    INNER JOIN course c ON c.Cno = sc.Cno 
    WHERE c.Cname = '语文';
#8 查询语数外三门成绩的平均分 
SELECT c.Cname,AVG(sc.score) FROM sc 
    INNER JOIN course c ON c.Cno = sc.Cno 
    GROUP BY c.Cname;
#9 查询报名孤独求败老师课程的学生信息
SELECT s.* FROM teacher t
    INNER JOIN course c ON c.Tno = t.Tno AND t.Tname = '孤独求败'
    INNER JOIN sc ON sc.Cno = c.Cno
    INNER JOIN student s ON s.Sno = sc.Sno

不是很熟练,有关联之间的表如果不去熟悉会搞混,有些雷同,毕竟是最后提交的

本节课作业: #6 查询出语文最高分 select Max(score) from SC b inner join Course c on b.Cno=c.Cno where c.Cname="语文"; #8 查询语数外三门成绩的平均分 select avg(score) from sc b inner join course c on b.Cno=c.Cno group by c.Cno #9 查询报名孤独求败老师课程的学生信息 select * from teacher d inner join course c on d.Tno=c.Tno and d.Tname="孤独求败" inner joinsc b on c.Cno=b.Cno right join Student a on a.Sno=b.Sno;

27楼 已删除

第9题和第10题都是差不多的,用and 和where 都可以

#6 查询出语文最高分

子查询

select max(score) from sc where Cno=(select Cno from course where Cname='语文');

联表查询

select max(score) from sc join course c using (Cno) where Cname='语文';

#7 查询没有参与语文考试的学生信息

子查询

select * from student where Sno not in (select distinct Sno from sc where Cno=(select Cno from course where Cname='语文'));

联表查询

select * from student where Sno not in (select Sno from student join sc using (Sno) join course c using (Cno) where Cname='语文');

#8 查询语数外三门成绩的平均分 select Cname, avg(score) from sc join course c using (Cno) group by Cno;

#9 查询报名孤独求败老师课程的学生信息

方法一:子查询

select * from student where Sno in (select Sno from sc where Cno in (select Cno from course where Tno in (select Tno from teacher where Tname='孤独求败')));

方法二:联表查询

select * from student where Sno in (select Sno from course join sc using (Cno) join teacher using (Tno) where Tname='孤独求败');

#10 没有报名孤独求败老师课程的学生信息

方法一:子查询

select * from student where Sno not in (select Sno from sc where Cno in (select Cno from course where Tno in (select Tno from teacher where Tname='孤独求败')));

方法二:联表查询

select * from student where Sno not in (select Sno from course join sc using (Cno) join teacher using (Tno) where Tname='孤独求败');

#6 查询出语文最高分 SELECT Max(sc.score) from sc inner join course as c on sc.Cno=c.Cno and c.Cname='语文' #8 查询语数外三门成绩的平均分 select c.Cname,avg(sc.score) from course as c inner join sc on c.Cno=sc.Cno Group by c.Cname #9 查询报名孤独求败老师课程的学生信息 select * from teacher as t inner join course as c on t.Tno=c.Tno and t.Tname='孤独求败'
inner join sc on c.Cno=sc.Cno

3ice 回复

top 1 看来这位同学用的是sql server 666

15650199152 回复

666 满分

mo_xiaoxi 回复

满分 666~

jeck 回复

满分~太棒了~

kuaile 回复

都作对了~

zoe 回复

为啥用like呢?

ke2beck 回复

666,满分~

bottle_7 回复

挺好的都对了,加油~

xiaoh 回复

满分~~~~

yy717 回复

满分~!!!

kyla 回复

满分 ~ 满分~~~

wangsen 回复

666~ 满分~

zhongyh 回复

强!10个题目都做了~

hljmingxi 回复

多种做法~666

Iam 回复

第一题不完美,如果用a.Cno=2就没有必要关联另外一张表了

liuyue824 回复

第三题不用子查询怎么实现?

sunicequeen 回复

大兄弟虽然你都最对了,但是格式看得好累啊~

che 回复

第三题有没有想过怎么用连接查询实现呢

weiyang77 回复

满分~~~~~~~~

sxd 回复

满分!!!

xiaolan 回复

满分满分~~~

donghe 回复

666满分~~

dengtao 回复

棒棒哒,分别用子查询和连接查询实现~满分~

满分~~~~!

luojie 回复

老师不好意思,我想自己补充一下,也希望你帮忙看一下我这里的语句出错在哪,各位同学也可以参与帮忙解决 我想补充一下 我想让语文最高分的学生和性别也显示出来,而我这里通过group by分组显示不对,不拉不拉不知道怎么表达

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册