面试题，sql经典问题，老师、学生、课程、分数

最新推荐文章于 2022-12-15 04:30:00 发布

战争迷雾

最新推荐文章于 2022-12-15 04:30:00 发布

阅读量814

点赞数

分类专栏： sql 文章标签： sql 面试学生成绩

本文链接：https://blog.csdn.net/w88193363/article/details/89517349

版权

sql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1新建一个数据库，然后导入sql数据表，以及测试数据

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50505
Source Host           : localhost:3306
Source Database       : couse

Target Server Type    : MYSQL
Target Server Version : 50505
File Encoding         : 65001

Date: 2019-04-25 15:32:09
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(200) NOT NULL,
  `tid` int(11) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='课程表，学生选择了哪门课，哪个老师';

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'english', '1');
INSERT INTO `course` VALUES ('2', 'math', '9');
INSERT INTO `course` VALUES ('3', 'art', '7');
INSERT INTO `course` VALUES ('4', 'dance', '8');
INSERT INTO `course` VALUES ('5', 'physic', '3');
INSERT INTO `course` VALUES ('6', 'chemistry', '2');
INSERT INTO `course` VALUES ('7', 'paint', '8');
INSERT INTO `course` VALUES ('8', 'panio', '5');
INSERT INTO `course` VALUES ('9', 'computer', '4');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `score` double(11,2) NOT NULL,
  UNIQUE KEY `sid_cid` (`sid`,`cid`) USING BTREE COMMENT '每个学生，同样的课，只有一个成绩记录'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表，学生的课程，分数记录';

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '90.00');
INSERT INTO `sc` VALUES ('1', '2', '95.00');
INSERT INTO `sc` VALUES ('1', '3', '80.00');
INSERT INTO `sc` VALUES ('1', '5', '98.00');
INSERT INTO `sc` VALUES ('1', '6', '99.00');
INSERT INTO `sc` VALUES ('1', '8', '96.00');
INSERT INTO `sc` VALUES ('1', '9', '97.00');
INSERT INTO `sc` VALUES ('2', '1', '60.00');
INSERT INTO `sc` VALUES ('2', '2', '50.00');
INSERT INTO `sc` VALUES ('2', '3', '80.00');
INSERT INTO `sc` VALUES ('2', '4', '90.00');
INSERT INTO `sc` VALUES ('2', '5', '100.00');
INSERT INTO `sc` VALUES ('2', '6', '110.00');
INSERT INTO `sc` VALUES ('2', '8', '130.00');
INSERT INTO `sc` VALUES ('2', '9', '140.00');
INSERT INTO `sc` VALUES ('3', '1', '80.00');
INSERT INTO `sc` VALUES ('3', '2', '97.00');
INSERT INTO `sc` VALUES ('3', '3', '69.00');
INSERT INTO `sc` VALUES ('3', '5', '81.00');
INSERT INTO `sc` VALUES ('3', '6', '88.00');
INSERT INTO `sc` VALUES ('3', '7', '52.00');
INSERT INTO `sc` VALUES ('3', '8', '89.00');
INSERT INTO `sc` VALUES ('3', '9', '53.00');
INSERT INTO `sc` VALUES ('4', '1', '90.00');
INSERT INTO `sc` VALUES ('4', '2', '85.00');
INSERT INTO `sc` VALUES ('4', '3', '81.00');
INSERT INTO `sc` VALUES ('4', '4', '53.00');
INSERT INTO `sc` VALUES ('4', '5', '70.00');
INSERT INTO `sc` VALUES ('4', '6', '90.00');
INSERT INTO `sc` VALUES ('4', '7', '51.00');
INSERT INTO `sc` VALUES ('4', '8', '76.00');
INSERT INTO `sc` VALUES ('4', '9', '51.00');
INSERT INTO `sc` VALUES ('5', '1', '81.00');
INSERT INTO `sc` VALUES ('5', '2', '86.00');
INSERT INTO `sc` VALUES ('5', '3', '65.00');
INSERT INTO `sc` VALUES ('5', '4', '51.00');
INSERT INTO `sc` VALUES ('5', '5', '87.00');
INSERT INTO `sc` VALUES ('5', '6', '99.00');
INSERT INTO `sc` VALUES ('5', '7', '70.00');
INSERT INTO `sc` VALUES ('5', '8', '97.00');
INSERT INTO `sc` VALUES ('5', '9', '60.00');
INSERT INTO `sc` VALUES ('6', '1', '58.00');
INSERT INTO `sc` VALUES ('6', '2', '71.00');
INSERT INTO `sc` VALUES ('6', '3', '87.00');
INSERT INTO `sc` VALUES ('6', '4', '71.00');
INSERT INTO `sc` VALUES ('6', '5', '69.00');
INSERT INTO `sc` VALUES ('6', '6', '60.00');
INSERT INTO `sc` VALUES ('6', '7', '85.00');
INSERT INTO `sc` VALUES ('6', '8', '79.00');
INSERT INTO `sc` VALUES ('6', '9', '82.00');
INSERT INTO `sc` VALUES ('7', '1', '98.00');
INSERT INTO `sc` VALUES ('7', '2', '99.00');
INSERT INTO `sc` VALUES ('7', '3', '66.00');
INSERT INTO `sc` VALUES ('7', '4', '84.00');
INSERT INTO `sc` VALUES ('7', '5', '80.00');
INSERT INTO `sc` VALUES ('7', '6', '83.00');
INSERT INTO `sc` VALUES ('7', '7', '93.00');
INSERT INTO `sc` VALUES ('7', '8', '55.00');
INSERT INTO `sc` VALUES ('7', '9', '61.00');
INSERT INTO `sc` VALUES ('8', '1', '77.00');
INSERT INTO `sc` VALUES ('8', '2', '64.00');
INSERT INTO `sc` VALUES ('8', '3', '87.00');
INSERT INTO `sc` VALUES ('8', '4', '58.00');
INSERT INTO `sc` VALUES ('8', '5', '65.00');
INSERT INTO `sc` VALUES ('8', '6', '98.00');
INSERT INTO `sc` VALUES ('8', '7', '52.00');
INSERT INTO `sc` VALUES ('8', '9', '63.00');
INSERT INTO `sc` VALUES ('9', '1', '66.00');
INSERT INTO `sc` VALUES ('9', '2', '61.00');
INSERT INTO `sc` VALUES ('9', '3', '66.00');
INSERT INTO `sc` VALUES ('9', '4', '67.00');
INSERT INTO `sc` VALUES ('9', '5', '73.00');
INSERT INTO `sc` VALUES ('9', '6', '81.00');
INSERT INTO `sc` VALUES ('9', '7', '79.00');
INSERT INTO `sc` VALUES ('9', '8', '87.00');
INSERT INTO `sc` VALUES ('9', '9', '63.00');
INSERT INTO `sc` VALUES ('10', '1', '83.00');
INSERT INTO `sc` VALUES ('10', '2', '51.00');
INSERT INTO `sc` VALUES ('10', '3', '60.00');
INSERT INTO `sc` VALUES ('10', '4', '89.00');
INSERT INTO `sc` VALUES ('10', '5', '66.00');
INSERT INTO `sc` VALUES ('10', '6', '94.00');
INSERT INTO `sc` VALUES ('10', '7', '51.00');
INSERT INTO `sc` VALUES ('10', '8', '67.00');
INSERT INTO `sc` VALUES ('10', '9', '56.00');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(200) NOT NULL,
  `sage` int(11) NOT NULL,
  `ssex` varchar(10) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='学生表';

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '24', 'male');
INSERT INTO `student` VALUES ('2', '李四', '26', 'male');
INSERT INTO `student` VALUES ('3', '王五', '28', 'male');
INSERT INTO `student` VALUES ('4', '赵六', '30', 'male');
INSERT INTO `student` VALUES ('5', '夜华', '30', 'male');
INSERT INTO `student` VALUES ('6', '白浅', '90', 'female');
INSERT INTO `student` VALUES ('7', '扶摇', '18', 'female');
INSERT INTO `student` VALUES ('8', '无极', '30', 'male');
INSERT INTO `student` VALUES ('9', '王菊', '30', 'female');
INSERT INTO `student` VALUES ('10', '杨超越', '20', 'female');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(200) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='教师信息表，教师的名字';

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '何炅');
INSERT INTO `teacher` VALUES ('2', '墨渊');
INSERT INTO `teacher` VALUES ('3', '悟汪涵');
INSERT INTO `teacher` VALUES ('4', '悟空');
INSERT INTO `teacher` VALUES ('5', '唐僧');
INSERT INTO `teacher` VALUES ('6', '陶渊明');
INSERT INTO `teacher` VALUES ('7', '翼天临');
INSERT INTO `teacher` VALUES ('8', '孙俪');
INSERT INTO `teacher` VALUES ('9', '刘译君');

2试题

1、查询课程编号为001的课程比002的课程成绩高的学生的学号

select x.sid,x.score,y.score from sc x,sc y 
where x.cid = 1 and y.cid = 2 and x.sid = y.sid and x.score > y.score

2.查询平均成绩大于80分的学生的学号和平均成绩

select sid,avg(score) from sc group by sid having avg(score) > 80

3.查询所有学生的学号、姓名、选课数、总成绩

以下两种方式都可以

select sc.sid,sname,count(cid),sum(score) from sc,student where sc.sid = student.sid group by sc.sid

select sc.sid,sname,count(sc.sid),sum(score) from student join sc on 
student.sid = sc.sid group by sc.sid

4.查询姓“悟”的老师的个数

select count(tid) from teacher where tname like '悟%';

5、查询没学过“孙俪”老师课的学生的学号、姓名

select sid,sname from student where sid not in (
select sid from sc where cid in 
(select cid from course where tid in 
(select tid from teacher where tname='孙俪')));

6、查询学过“孙俪”老师所教的所有课的同学的学号、姓名

select sid,sname from student where sid in (
select sid from sc where cid in 
(select cid from course where tid in 
(select tid from teacher where tname='孙俪')));

7、查询学过编号为“4”的课程并且也学过编号为“7”的课程的学生的学号、姓名

select x.sid from sc x,sc y where x.sid = y.sid and x.cid = 4 and y.cid = 7 group by x.sid;

8、查询课程编号为“2”的总成绩

select sum(score) from sc where sc.cid = 2;

9、查询有课程成绩小于60分的学生的学号、姓名

select sid,sname from student where sid in 
(select sid from sc where score < 60 );

10、查询没有学全所有课的学生的学号、姓名

select sid,sname from student where sid in (
select sid from sc group by sid 
having count(sid) < (select count(*) from course)
)

引用文献：
[1]https://blog.csdn.net/codema/article/details/80915311

战争迷雾

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
面试题，sql经典问题，老师、学生、课程、分数

1新建一个数据库，然后导入sql数据表，以及测试数据/*Navicat MySQL Data TransferSource Server : localhostSource Server Version : 50505Source Host : localhost:3306Source Database : couseTarget ...
复制链接

扫一扫