sql面试题（学生表_课程表_成绩表_教师表）

最新推荐文章于 2022-01-14 15:13:45 发布

千百元

最新推荐文章于 2022-01-14 15:13:45 发布

阅读量1.8k

点赞数

分类专栏： # 面试 # mysql

原文链接：https://blog.csdn.net/PGY0000/article/details/83002561?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.control&dist_request_id=98f634ca-12d8-4fd9-b059-859f287bf7d8&depth_1-utm_source=distribute.pc_relevant.none-task-blog

版权

mysql 同时被 2 个专栏收录

161 篇文章 1 订阅

订阅专栏

面试

20 篇文章 0 订阅

订阅专栏

表结构

Student(Sid,Sname,Sage,Ssex) 学生表

Teacher(Tid,Tname) 教师表

Course(Cid,Cname,Tid) 课程表

SC(Sid,Cid,score) 成绩表

表数据

 #教师表
drop table if exists `teacher`;
create table `teacher` (
  `tid` int(11) default null,
  `tname` varchar(16) default null
) engine=myisam default charset=utf8;
  
-- ----------------------------
-- records of teacher
-- ----------------------------
insert into `teacher` values ('1', '叶平');
insert into `teacher` values ('2', '贺高');
insert into `teacher` values ('3', '杨艳');
insert into `teacher` values ('4', '周磊');

#课程表
drop table if exists course;
create table course(
    cid int(4)   ,
    cname varchar(30) not null,
    tid int(4) default null
)engine=innodb default charset=utf8;
 
INSERT INTO course VALUES ('1','语文','1');
INSERT INTO course VALUES ('2','数学','2');
INSERT INTO course VALUES ('3','英语','3');
INSERT INTO course VALUES ('4','物理','4');
 

#学生表
drop table if exists student;
create table student(
    sid int(4),
    sname varchar(32) default null,
    sage int(4) default null,
    ssex varchar(8) default null
)engine=innodb default charset=utf8;
INSERT INTO `student` VALUES ('1', '刘一', '18', '男');
INSERT INTO `student` VALUES ('2', '钱二', '19', '女');
INSERT INTO `student` VALUES ('3', '张三', '17', '男');
INSERT INTO `student` VALUES ('4', '李四', '18', '女');
INSERT INTO `student` VALUES ('5', '王五', '17', '男');
INSERT INTO `student` VALUES ('6', '赵六', '19', '女');
 
 #分数表
drop table if exists sc;
create table sc(
    sid int(4) ,
    cid int(4) default null,
    score int(4) default null
)engine=innodb default charset=utf8;
 
insert into `sc` values ('1', '1', '56');
insert into `sc` values ('1', '2', '78');
insert into `sc` values ('1', '3', '67');
insert into `sc` values ('1', '4', '58');
insert into `sc` values ('2', '1', '79');
insert into `sc` values ('2', '2', '81');
insert into `sc` values ('2', '3', '92');
insert into `sc` values ('2', '4', '68');
insert into `sc` values ('3', '1', '91');
insert into `sc` values ('3', '2', '47');
insert into `sc` values ('3', '3', '88');
insert into `sc` values ('3', '4', '56');
insert into `sc` values ('4', '2', '88');
insert into `sc` values ('4', '3', '90');
insert into `sc` values ('4', '4', '93');
insert into `sc` values ('5', '1', '46');
insert into `sc` values ('5', '3', '78');
insert into `sc` values ('5', '4', '53');
insert into `sc` values ('6', '1', '35');
insert into `sc` values ('6', '2', '68');
insert into `sc` values ('6', '4', '71');

问题：

（1）查询“1”课程的所有学生的学号与分数；

SELECT sid,score FROM sc WHERE cid="1"

（2）查询“1”课程比“2”课程成绩高的所有学生的学号与分数；

第一条有问题

SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="1") a,

(SELECT sid,score FROM sc WHERE cid="2") b

WHERE a.score>b.score AND a.sid=b.sid

select a.sid ,a .score

from SC a JOIN SC b ON a.sid=b.sid

WHERE a.cid='1' AND b.cid='2' AND a.score>b.score

（3）查询平均成绩大于60分的同学的学号和平均成绩；

SELECT sid,AVG(score)

FROM sc

GROUP BY sid HAVING AVG(score)>60

值得注意的是having后面跟的条件判断的字段必须是聚合函数返回的结果，否则sql会报错

（4）查询所有同学的学号、姓名、选课数、总成绩

SELECT s.sid AS "学号", s.sname AS "姓名", COUNT(sc.cid) AS "课程数目", SUM(sc.score) AS "总分数"

FROM student s, sc sc

WHERE s.sid=sc.sid

GROUP BY s.sid

（5）查询姓“李”的老师的个数；

select count(distinct(Tname))

from teacher

where tname like '李%';

（6）查询学过“张三”老师课的同学的学号、姓名

SELECT s.sid AS "学号", s.sname AS "姓名"

FROM student s, sc sc, course c, teacher t

WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="张三"

（7）查询没有学过“张三”老师课的同学的学号、姓名

SELECT s.sid, s.sname

FROM student s

WHERE s.sid NOT IN (

SELECT s.sid

FROM student s, sc sc, course c, teacher t

WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="张三"

)

（8）查询学过“30001”并且也学过编号“30002”课程的同学的学号、姓名

SELECT s.sid, s.sname

FROM student s, sc sc

WHERE s.sid=sc.sid AND sc.cid="30001" AND EXISTS(

SELECT * FROM sc AS sc2 WHERE sc2.sid=sc.sid AND sc2.cid="30002"

)

（9）查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SELECT sid, sname

FROM student

WHERE sid IN (

SELECT sc.sid

FROM sc sc, course c, teacher t

WHERE sc.cid=c.cid AND c.tid=t.tid AND t.tname="张二"

)

（10）查询所有课程成绩小于60分的同学的学号、姓名

SELECT sid, sname FROM student

WHERE sid NOT IN (

SELECT DISTINCT(sc.sid) FROM student s, sc sc

WHERE sc.sid=s.sid AND sc.score>60)

（11）查询没有学全所有课的同学的学号、姓名；

SELECT sid, sname FROM student

WHERE sid NOT IN(

SELECT s.sid FROM student s, sc sc

WHERE sc.sid=s.sid

GROUP BY s.sid

HAVING COUNT(sc.cid)=(

SELECT COUNT(cid) FROM course))

（12）查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT cid AS "课程ID", MAX(score) AS "最高分", MIN(score) AS "最低分"
FROM sc
GROUP BY cid

（13）按各科平均成绩从低到高和及格率的百分数从高到低顺序

(方式一)
SELECT sc.cid AS "课程ID",c.cname AS "课程名", AVG(sc.score) AS "平均成绩",
SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 AS "及格百分数"
FROM sc sc, course c
WHERE sc.cid=c.cid
GROUP BY sc.cid
ORDER BY AVG(sc.score) ASC,
SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 DESC

(方式二)
SELECT sc.cid AS "课程ID",c.cname AS "课程名", IFNULL(AVG(sc.score),0) AS "平均成绩",
100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS "及格百分数"
FROM sc sc, course c
WHERE sc.cid = c.cid
GROUP BY sc.cid
ORDER BY AVG(sc.score) ASC,
100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

（14）查询所有学生的所有科目的成绩单（学号、姓名、语文、数学、英语、物理、平均分、总分（按照总分由高到低排序））

SELECT s.sid AS "学号", s.sname AS "姓名",
SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",
SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",
SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "总分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
ORDER BY IFNULL(SUM(sc.score),0) DESC

（15）查询总分排名在200-300（包含200和第300）之间的学生所有成绩单信息

（16）查询总分排名在前四名的学生所有成绩单信息

（17）查询总分排名在前二名到四名的学生所有成绩单信息（limit 1,3表示从第二条数据开始，连续三条数据）

（18）查询学生平均成绩及其名次

(非本人)
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (
SELECT sid,AVG(score) AS 平均成绩
FROM sc
GROUP BY sid ) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次, S# as 学生学号,平均成绩
FROM (SELECT sid,AVG(score) 平均成绩
FROM sc GROUP BY sid ) AS T2
ORDER BY 平均成绩 desc

千百元

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
sql面试题（学生表_课程表_成绩表_教师表）

表结构Student(S,Sname,Sage,Ssex)学生表Course(C,Cname,T)课程表SC(S,C,score)成绩表Teacher(T,Tname)教师表drop table if exists course;create table course( c int(4) default null primary key auto_increment, cname varchar(30) not null, t ...
复制链接

扫一扫