测试派 Give testers a home.

登录 注册

20200325 数据库连接查询面试题作业

本贴最后更新于 1546 天前，其中的信息可能已经沧海桑田

本节课作业：

#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;
1 操作
luojie 在 2020-08-06 17:32:21 更新了该帖
1 操作
59 回帖
27
2
2
2
2
31098 82 2189 2998 5940 100 5032 14444

相关帖子

随便看看

59 回帖
请输入回帖内容 ...
  • youmaobing233 •

    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 = '孤独求败'

  • luojie • 作者

    老铁，没毛病

  • MaiSui •

    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 •

    image.png

  • luojie • 作者

    满分~

  • luojie • 作者

    第一题的解法有点6

  • MaiSui •

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

  • 3ice •

    image.png

  • 15650199152 •

    image.png

  • mo_xiaoxi •

    image.png
    image.png
    image.png

  • jeck •

    image.png

  • kuaile •

    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='孤独求败'

  • kuaile • 
    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='孤独求败'
  • zoe •

    #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 ='孤独求败'

  • ke2beck • 
    #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
  • bottle_7 •

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

  • xiaoh •

    image.png
    image.png
    image.png
    image.png

  • yy717 •

    image.png

  • kyla •

    image.png

  • wangsen •

    image.png

  • zhongyh •

    image.png
    image.png
    image.png
    image.png

  • hljmingxi •

    image.png

  • Iam •

    image.png

  • liuyue824 •

    image.png

  • sunicequeen •

    本节课作业：
    #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;

  • che •

    image.png

  • dengtao •

    2 查询无崖子授课信息

    select * from course c join teacher t using (Tno) where t.Tname='无崖子';
    如果两张表合并后不想要重复的列，可以用using去去重的吧。

  • weiyang77 •

    image.png

  • sxd •

    image.png
    image.png
    image.png
    第9题和第10题都是差不多的，用and 和where 都可以

  • xiaolan •

    image.png

请输入回帖内容 ...