SQL 数据库面试题及其答案

原创已于 2023-04-06 11:26:42 修改 · 561 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql # 数据库

于 2019-11-07 14:47:20 首次发布

MySQL 专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了一个学生成绩管理系统的数据库设计，包括学生、课程、教师和成绩四个主要表的结构和内容，以及如何通过SQL查询实现对学生信息、课程成绩、教师授课情况的统计和分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表结构介绍

s(sid,sName,sAge,sSex) 学生表
sid：学号；sName：学生姓名；sAge：学生年龄；sSex：学生性别

c(cid,cName,tid) 课程表
cid,课程编号；cName：课程名字；tid：教师编号

sc(scid,sid,cid,score) 成绩表
sid：学号；cid,课程编号；score：成绩

t(tid,tName) 教师表
tid：教师编号；tName：教师名字

建表 sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for c
-- ----------------------------
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
  `cid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程id',
  `cName` varchar(255) NOT NULL COMMENT '课程姓名',
  `tid` tinyint(255) NOT NULL COMMENT '老师id',
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of c
-- ----------------------------
BEGIN;
INSERT INTO `c` VALUES (1, '数学', 1);
INSERT INTO `c` VALUES (2, '英语', 1);
INSERT INTO `c` VALUES (3, '数据库', 3);
INSERT INTO `c` VALUES (4, '企业管理', 4);
INSERT INTO `c` VALUES (5, '生物', 5);
INSERT INTO `c` VALUES (6, '地理', 6);
INSERT INTO `c` VALUES (7, '物理', 7);
INSERT INTO `c` VALUES (8, '化学', 8);
INSERT INTO `c` VALUES (9, '思想', 9);
INSERT INTO `c` VALUES (10, '马克思', 10);
COMMIT;

-- ----------------------------
-- Table structure for s
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
  `sid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `sName` varchar(255) NOT NULL COMMENT '学生姓名',
  `sAge` tinyint(255) NOT NULL COMMENT '学生年龄',
  `sSex` tinyint(255) NOT NULL COMMENT '学生性别',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of s
-- ----------------------------
BEGIN;
INSERT INTO `s` VALUES (1, '彭老三', 23, 1);
INSERT INTO `s` VALUES (2, '何老大', 23, 1);
INSERT INTO `s` VALUES (3, '赫连淑育', 23, 0);
INSERT INTO `s` VALUES (4, '彭老三', 22, 0);
INSERT INTO `s` VALUES (5, '何老大', 21, 1);
INSERT INTO `s` VALUES (6, '苏敬明', 20, 1);
INSERT INTO `s` VALUES (7, '匡安娟', 24, 0);
INSERT INTO `s` VALUES (8, '张融梦', 25, 1);
INSERT INTO `s` VALUES (9, '尹邦凡', 26, 1);
INSERT INTO `s` VALUES (10, '曲勤威', 27, 1);
INSERT INTO `s` VALUES (11, '赫连淑', 28, 1);
INSERT INTO `s` VALUES (12, '萧冠博', 29, 0);
INSERT INTO `s` VALUES (13, '司徒雪', 38, 0);
INSERT INTO `s` VALUES (14, '庾锦佳', 31, 0);
INSERT INTO `s` VALUES (15, '柯倩时', 32, 1);
INSERT INTO `s` VALUES (16, '方利秋', 33, 0);
INSERT INTO `s` VALUES (17, '刘洋彬', 44, 1);
INSERT INTO `s` VALUES (18, '谈成韵', 33, 0);
INSERT INTO `s` VALUES (19, '陶飞士', 23, 1);
INSERT INTO `s` VALUES (20, '胡维睿', 22, 1);
INSERT INTO `s` VALUES (21, '江东凤', 21, 0);
INSERT INTO `s` VALUES (22, '汤承榕', 31, 0);
INSERT INTO `s` VALUES (23, '龙凝泽', 23, 1);
INSERT INTO `s` VALUES (24, '林羽婉', 23, 0);
INSERT INTO `s` VALUES (25, '夏睿翰', 23, 0);
INSERT INTO `s` VALUES (26, '柯灵素', 23, 1);
INSERT INTO `s` VALUES (27, '宗邦坚', 23, 1);
INSERT INTO `s` VALUES (28, '夏侯芝', 23, 1);
INSERT INTO `s` VALUES (29, 'sb', 23, 0);
COMMIT;

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `scid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sid` int(255) NOT NULL COMMENT '学号',
  `score` decimal(4,1) NOT NULL COMMENT '分数',
  `cid` int(11) NOT NULL COMMENT '课程 id',
  PRIMARY KEY (`scid`)
) ENGINE=InnoDB AUTO_INCREMENT=174 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of sc
-- ----------------------------
BEGIN;
INSERT INTO `sc` VALUES (1, 1, 100.0, 1);
INSERT INTO `sc` VALUES (2, 1, 100.0, 2);
INSERT INTO `sc` VALUES (3, 1, 100.0, 3);
INSERT INTO `sc` VALUES (4, 1, 100.0, 4);
INSERT INTO `sc` VALUES (5, 1, 100.0, 5);
INSERT INTO `sc` VALUES (6, 1, 100.0, 6);
INSERT INTO `sc` VALUES (7, 1, 100.0, 7);
INSERT INTO `sc` VALUES (8, 1, 100.0, 8);
INSERT INTO `sc` VALUES (9, 1, 100.0, 9);
INSERT INTO `sc` VALUES (10, 1, 100.0, 10);
INSERT INTO `sc` VALUES (11, 2, 100.0, 1);
INSERT INTO `sc` VALUES (12, 2, 100.0, 2);
INSERT INTO `sc` VALUES (13, 2, 100.0, 3);
INSERT INTO `sc` VALUES (14, 2, 100.0, 4);
INSERT INTO `sc` VALUES (15, 2, 100.0, 5);
INSERT INTO `sc` VALUES (16, 2, 100.0, 6);
INSERT INTO `sc` VALUES (17, 2, 100.0, 7);
INSERT INTO `sc` VALUES (18, 2, 100.0, 8);
INSERT INTO `sc` VALUES (19, 2, 100.0, 9);
INSERT INTO `sc` VALUES (20, 2, 100.0, 10);
INSERT INTO `sc` VALUES (21, 3, 85.0, 1);
INSERT INTO `sc` VALUES (22, 3, 46.0, 2);
INSERT INTO `sc` VALUES (23, 3, 56.0, 3);
INSERT INTO `sc` VALUES (24, 3, 56.0, 4);
INSERT INTO `sc` VALUES (25, 3, 56.0, 5);
INSERT INTO `sc` VALUES (26, 3, 56.0, 6);
INSERT INTO `sc` VALUES (27, 4, 85.0, 1);
INSERT INTO `sc` VALUES (28, 4, 56.0, 3);
INSERT INTO `sc` VALUES (29, 4, 56.0, 4);
INSERT INTO `sc` VALUES (30, 4, 56.0, 5);
INSERT INTO `sc` VALUES (31, 4, 56.0, 7);
INSERT INTO `sc` VALUES (32, 5, 56.0, 1);
INSERT INTO `sc` VALUES (33, 5, 56.0, 3);
INSERT INTO `sc` VALUES (34, 5, 56.0, 5);
INSERT INTO `sc` VALUES (35, 5, 56.0, 7);
INSERT INTO `sc` VALUES (36, 5, 56.0, 9);
INSERT INTO `sc` VALUES (37, 5, 56.0, 10);
INSERT INTO `sc` VALUES (38, 6, 56.0, 1);
INSERT INTO `sc` VALUES (39, 6, 56.0, 2);
INSERT INTO `sc` VALUES (40, 6, 56.0, 4);
INSERT INTO `sc` VALUES (41, 6, 56.0, 6);
INSERT INTO `sc` VALUES (42, 6, 56.0, 8);
INSERT INTO `sc` VALUES (43, 6, 56.0, 10);
INSERT INTO `sc` VALUES (44, 7, 56.0, 1);
INSERT INTO `sc` VALUES (45, 7, 56.0, 2);
INSERT INTO `sc` VALUES (46, 7, 67.0, 3);
INSERT INTO `sc` VALUES (47, 7, 67.0, 4);
INSERT INTO `sc` VALUES (48, 7, 78.0, 5);
INSERT INTO `sc` VALUES (49, 8, 56.0, 1);
INSERT INTO `sc` VALUES (50, 8, 60.0, 3);
INSERT INTO `sc` VALUES (51, 8, 56.0, 4);
INSERT INTO `sc` VALUES (52, 8, 56.0, 5);
INSERT INTO `sc` VALUES (53, 8, 56.0, 7);
INSERT INTO `sc` VALUES (54, 9, 56.0, 1);
INSERT INTO `sc` VALUES (55, 9, 56.0, 3);
INSERT INTO `sc` VALUES (56, 9, 60.0, 5);
INSERT INTO `sc` VALUES (57, 9, 56.0, 7);
INSERT INTO `sc` VALUES (58, 9, 56.0, 9);
INSERT INTO `sc` VALUES (59, 9, 56.0, 10);
INSERT INTO `sc` VALUES (60, 10, 56.0, 1);
INSERT INTO `sc` VALUES (61, 10, 56.0, 2);
INSERT INTO `sc` VALUES (62, 10, 56.0, 4);
INSERT INTO `sc` VALUES (63, 10, 56.0, 6);
INSERT INTO `sc` VALUES (64, 10, 56.0, 8);
INSERT INTO `sc` VALUES (65, 10, 56.0, 10);
INSERT INTO `sc` VALUES (66, 11, 56.0, 1);
INSERT INTO `sc` VALUES (67, 11, 56.0, 2);
INSERT INTO `sc` VALUES (68, 11, 67.0, 3);
INSERT INTO `sc` VALUES (69, 11, 67.0, 4);
INSERT INTO `sc` VALUES (70, 11, 78.0, 5);
INSERT INTO `sc` VALUES (71, 12, 56.0, 1);
INSERT INTO `sc` VALUES (72, 12, 56.0, 3);
INSERT INTO `sc` VALUES (73, 12, 56.0, 4);
INSERT INTO `sc` VALUES (74, 12, 56.0, 5);
INSERT INTO `sc` VALUES (75, 12, 56.0, 7);
INSERT INTO `sc` VALUES (76, 13, 56.0, 1);
INSERT INTO `sc` VALUES (77, 13, 56.0, 3);
INSERT INTO `sc` VALUES (78, 13, 56.0, 5);
INSERT INTO `sc` VALUES (79, 13, 56.0, 7);
INSERT INTO `sc` VALUES (80, 13, 56.0, 9);
INSERT INTO `sc` VALUES (81, 13, 56.0, 10);
INSERT INTO `sc` VALUES (82, 14, 56.0, 1);
INSERT INTO `sc` VALUES (83, 14, 56.0, 2);
INSERT INTO `sc` VALUES (84, 14, 56.0, 4);
INSERT INTO `sc` VALUES (85, 14, 56.0, 6);
INSERT INTO `sc` VALUES (86, 14, 56.0, 8);
INSERT INTO `sc` VALUES (87, 14, 56.0, 10);
INSERT INTO `sc` VALUES (88, 15, 56.0, 1);
INSERT INTO `sc` VALUES (89, 15, 56.0, 2);
INSERT INTO `sc` VALUES (90, 15, 67.0, 3);
INSERT INTO `sc` VALUES (91, 15, 67.0, 4);
INSERT INTO `sc` VALUES (92, 15, 78.0, 5);
INSERT INTO `sc` VALUES (93, 16, 56.0, 1);
INSERT INTO `sc` VALUES (94, 16, 56.0, 3);
INSERT INTO `sc` VALUES (95, 16, 56.0, 4);
INSERT INTO `sc` VALUES (96, 16, 56.0, 5);
INSERT INTO `sc` VALUES (97, 16, 56.0, 7);
INSERT INTO `sc` VALUES (98, 17, 56.0, 1);
INSERT INTO `sc` VALUES (99, 17, 56.0, 3);
INSERT INTO `sc` VALUES (100, 17, 56.0, 5);
INSERT INTO `sc` VALUES (101, 17, 56.0, 7);
INSERT INTO `sc` VALUES (102, 17, 56.0, 9);
INSERT INTO `sc` VALUES (103, 17, 56.0, 10);
INSERT INTO `sc` VALUES (104, 18, 56.0, 1);
INSERT INTO `sc` VALUES (105, 18, 56.0, 2);
INSERT INTO `sc` VALUES (106, 18, 56.0, 4);
INSERT INTO `sc` VALUES (107, 18, 56.0, 6);
INSERT INTO `sc` VALUES (108, 18, 56.0, 8);
INSERT INTO `sc` VALUES (109, 18, 56.0, 10);
INSERT INTO `sc` VALUES (110, 19, 56.0, 1);
INSERT INTO `sc` VALUES (111, 19, 56.0, 2);
INSERT INTO `sc` VALUES (112, 19, 67.0, 3);
INSERT INTO `sc` VALUES (113, 19, 67.0, 4);
INSERT INTO `sc` VALUES (114, 19, 78.0, 5);
INSERT INTO `sc` VALUES (115, 20, 100.0, 2);
INSERT INTO `sc` VALUES (116, 20, 100.0, 3);
INSERT INTO `sc` VALUES (117, 20, 100.0, 4);
INSERT INTO `sc` VALUES (118, 20, 100.0, 5);
INSERT INTO `sc` VALUES (119, 20, 100.0, 6);
INSERT INTO `sc` VALUES (120, 20, 100.0, 7);
INSERT INTO `sc` VALUES (121, 20, 100.0, 8);
INSERT INTO `sc` VALUES (122, 20, 100.0, 9);
INSERT INTO `sc` VALUES (123, 20, 100.0, 10);
INSERT INTO `sc` VALUES (124, 21, 95.0, 1);
INSERT INTO `sc` VALUES (125, 21, 100.0, 2);
INSERT INTO `sc` VALUES (126, 21, 100.0, 3);
INSERT INTO `sc` VALUES (127, 21, 100.0, 4);
INSERT INTO `sc` VALUES (128, 21, 100.0, 5);
INSERT INTO `sc` VALUES (129, 21, 100.0, 6);
INSERT INTO `sc` VALUES (130, 21, 100.0, 7);
INSERT INTO `sc` VALUES (131, 21, 100.0, 8);
INSERT INTO `sc` VALUES (132, 21, 100.0, 9);
INSERT INTO `sc` VALUES (133, 21, 100.0, 10);
INSERT INTO `sc` VALUES (134, 22, 80.0, 1);
INSERT INTO `sc` VALUES (135, 22, 42.2, 2);
INSERT INTO `sc` VALUES (136, 22, 56.0, 3);
INSERT INTO `sc` VALUES (137, 22, 56.0, 4);
INSERT INTO `sc` VALUES (138, 22, 56.0, 5);
INSERT INTO `sc` VALUES (139, 22, 56.0, 6);
INSERT INTO `sc` VALUES (140, 23, 56.0, 1);
INSERT INTO `sc` VALUES (141, 23, 56.0, 3);
INSERT INTO `sc` VALUES (142, 23, 56.0, 4);
INSERT INTO `sc` VALUES (143, 23, 56.0, 5);
INSERT INTO `sc` VALUES (144, 23, 56.0, 7);
INSERT INTO `sc` VALUES (145, 24, 56.0, 1);
INSERT INTO `sc` VALUES (146, 24, 56.0, 3);
INSERT INTO `sc` VALUES (147, 24, 56.0, 5);
INSERT INTO `sc` VALUES (148, 24, 56.0, 7);
INSERT INTO `sc` VALUES (149, 24, 56.0, 9);
INSERT INTO `sc` VALUES (150, 24, 56.0, 10);
INSERT INTO `sc` VALUES (151, 25, 56.0, 1);
INSERT INTO `sc` VALUES (152, 25, 56.0, 2);
INSERT INTO `sc` VALUES (153, 25, 56.0, 4);
INSERT INTO `sc` VALUES (154, 25, 56.0, 6);
INSERT INTO `sc` VALUES (155, 25, 56.0, 8);
INSERT INTO `sc` VALUES (156, 25, 56.0, 10);
INSERT INTO `sc` VALUES (157, 26, 56.0, 1);
INSERT INTO `sc` VALUES (158, 26, 56.0, 2);
INSERT INTO `sc` VALUES (159, 26, 67.0, 3);
INSERT INTO `sc` VALUES (160, 26, 67.0, 4);
INSERT INTO `sc` VALUES (161, 26, 78.0, 5);
INSERT INTO `sc` VALUES (162, 27, 5.1, 1);
INSERT INTO `sc` VALUES (163, 27, 5.3, 3);
INSERT INTO `sc` VALUES (164, 27, 56.0, 4);
INSERT INTO `sc` VALUES (165, 27, 56.0, 5);
INSERT INTO `sc` VALUES (166, 27, 56.0, 7);
INSERT INTO `sc` VALUES (167, 28, 56.0, 1);
INSERT INTO `sc` VALUES (168, 28, 56.0, 3);
INSERT INTO `sc` VALUES (169, 28, 56.0, 5);
INSERT INTO `sc` VALUES (170, 28, 56.0, 7);
INSERT INTO `sc` VALUES (171, 28, 56.0, 9);
INSERT INTO `sc` VALUES (172, 28, 56.0, 10);
INSERT INTO `sc` VALUES (173, 29, 10.0, 1);
COMMIT;

-- ----------------------------
-- Table structure for t
-- ----------------------------
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `tid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '老师id',
  `tName` varchar(255) NOT NULL COMMENT '老师姓名',
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of t
-- ----------------------------
BEGIN;
INSERT INTO `t` VALUES (1, '叶平');
INSERT INTO `t` VALUES (2, '李国立');
INSERT INTO `t` VALUES (3, '李平');
INSERT INTO `t` VALUES (4, '张晓梅');
INSERT INTO `t` VALUES (5, '张飞');
INSERT INTO `t` VALUES (6, '钱学森');
INSERT INTO `t` VALUES (7, '钱三强');
INSERT INTO `t` VALUES (8, '彭哈哈');
INSERT INTO `t` VALUES (9, '彭三木');
INSERT INTO `t` VALUES (10, '彭思博');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

查询1课程比2课程成绩高的所有学生学号

select a.sid from 
(SELECT cid,sid,score FROM sc WHERE cid=1 )as a,
(SELECT cid,sid,score FROM sc WHERE cid=2 )as b 
WHERE a.score > b.score AND a.sid= b.sid;

查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;

select s.sid,s.sname from s where sid in (
select a.sid from 
(SELECT cid,sid,score FROM sc WHERE cid=2)as a,
(SELECT cid,sid,score FROM sc WHERE cid=1)as b 
WHERE a.score > b.score AND a.sid= b.sid
);

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

select s.sid,s.sName,count(sc.cid),sum(score) from s 
left join sc 
on s.sid=sc.sid 
group by s.sid;

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

select count(1) as "姓李老师数量" from t 
where tName like "李%";

查询没学过“叶平”老师课的同学的学号、姓名；

select sid,sName from s 
where sid not in 
(select sid from sc where cid = 
(select cid from c where tid = 
(select tid from t where tName = "叶平")
)
);
# 执行时间长

select s.sid,s.sName from s 
where sid not in 
(
select distinct( sc.sid) from sc,c,t 
where sc.cid=c.cid and t.tId=c.tId and t.tName='叶平'
);
# 执行时间短

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

# 注意:这里要找出学过 叶平 老师所有课的同学

select sid,sName from s 
where sid in 
(
select sid from sc,c,t 
where sc.cid=c.cid and t.tid=c.tid and t.tName='叶平' 
group by sid 
having count(sc.cid)=(
select count(cid) from c,t where t.tid=c.tid and tName='叶平')
);
# 最后的 having 不能少,起到了关键作用;

查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

select s.sid,s.sname from s,sc 
where s.sid = sc.sid and sc.cid = 1 and 
EXISTS 
(
select * from sc as sc2 
where sc2.sid = sc.sid and sc2.cid = 2
);

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

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

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

select sid,sname from s 
where sid not in 
(select sc.sid from sc where  score >= 60);

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

select sid,sname from s 
where sid not in 
(
select sc.sid from sc 
group by sid 
having count(sc.cid)=
(select count(1) from c)
);
# 先找出学了所有课程的学生的 id

select s.sid,s.sname from s,sc where s.sid=sc.sid and s.sid 
group by s.sid,s.sname 
having count(1) <(select count(1) from c);

查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

select DISTINCT(s.sid),s.sname from s,sc 
where s.sid = sc.sid and s.sid!=1 and sc.cid in 
(select cid from sc where sid = 1);

查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；

select DISTINCT(sid),sname from s 
where sid!=1 and sid in 
(
select sid from sc 
group by sid having count(cid) =
(select count(cid) from sc where sid = 1)
);

把“Scores”表中“叶平”老师教的课的成绩都更改叶老师所教课程的平均成绩(叶平老师在这里教两门课程)；

create table temp as select avg(sscc.score) as score from sc as sscc where sscc.cid in (select cid from c,t where c.tid=t.tid AND t.tname = "叶平");
UPDATE sc set score = (select score from temp) WHERE cid in (select cid from c,t where c.tid=t.tid AND t.tname = "叶平");
drop table temp;

查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名；

同12

删除学习“叶平”老师课的Scores表记录；

delete from sc 
where cid in 
(select cid from c,t where c.tid=t.tid AND t.tname = "叶平");

向Scores表中插入一些记录，这些记录要求符合以下条件：没有上过编号“3”课程的同学学号、2号课的平均成绩；

INSERT into sc 
(select  0 as scid,DISTINCT sid,
(Select avg(score)from sc where cid=2) as score,2 as cid from s 
where sid not in 
(Select sid from sc where cid=3)
);

按所有成绩的平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,数据库,企业管理,英语,有效课程数,有效平均分;

select sid as '学生 ID',
(select score from sc where cid =(select cid from c where cname = "数据库") and sc.sid = sscc.sid) as 数据库,
(select score from sc where cid =(select cid from c where cname = "企业管理") and sc.sid = sscc.sid) as 企业管理,
(select score from sc where cid =(select cid from c where cname = "英语") and sc.sid = sscc.sid) as 英语,
count(1) as 有效课程数, avg(sscc.score) as 平均成绩
from sc as sscc
group by sid
order by avg(sscc.score) DESC,sid asc;

查询各科成绩最高和最低的分,以如下形式显示：

课程ID，最高分，最低分;

select DISTINCT L.cid 课程ID,L.score 最高分,R.score 最低分 from sc L,sc as R 
where 
L.cid = R.cid 
and 
L.score = (select max(score) FROM sc where L.cid = sc.cid group by sc.cid)
and 
R.score = ( SELECT min(sc.score) from sc where R.cid = sc.cid GROUP BY sc.cid) 
ORDER BY L.cid;

查询各科成绩课程名称平均成绩,及格率并且按照平均成绩从低到高和及格率的百分数从高到低顺序;

SELECT sc.cid AS 课程号,max(c.cname)AS 课程名,ifnull(AVG(score),0) AS 平均成绩,100 * SUM(CASE WHEN ifnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 FROM sc,c
where sc.cid=c.cid
GROUP BY sc.cid
ORDER BY avg(score) ASC,
100 * SUM(CASE WHEN ifnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC;
# 注意: 这里求及格率的方法 
# MySQL 语句写逻辑方法
# case when ifnull(score,0)>=60 then 1 else 0 end

知识延伸:

isnull(exper): 判断exper是否为null，是则返回1，否则返回0;

ifnull(exper1,exper2): 判断exper1是否为null，是则用exper2代替);

nullif(exper1,exper2): 如果expr1= expr2 成立，那么返回值为NULL，否则返回值为expr1

示例:

# isnull: 是 null 就返回1,不是就返回0
mysql> select isnull(1);
+-----------+
| isnull(1) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select isnull(0); # 0不是 null
+-----------+
| isnull(0) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select isnull(null);
+--------------+
| isnull(null) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
mysql> select isnull(1/0);
+-------------+
| isnull(1/0) |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)


ifnull: 如果是 null 则返回后面的值
mysql> select ifnull(1/0,11);
+----------------+
| ifnull(1/0,11) |
+----------------+
|        11.0000 |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select ifnull(22,11);   
+---------------+
| ifnull(22,11) |
+---------------+
|            22 |
+---------------+
1 row in set (0.00 sec)

mysql> select ifnull(0,11);  
+--------------+
| ifnull(0,11) |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)


nullif: 如果两者相等则返回 null
mysql> select nullif(0,11);  
+--------------+
| nullif(0,11) |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> select nullif(0/0,11);
+----------------+
| nullif(0/0,11) |
+----------------+
|           NULL |
+----------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select nullif(11,11);   
+---------------+
| nullif(11,11) |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)

mysql> select nullif(11,'11');
+-----------------+
| nullif(11,'11') |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)

查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(4)，马克思(10),数据库(3);

SELECT 
SUM(CASE WHEN cid =4 THEN score ELSE 0 END) /
SUM(CASE cid WHEN 4 THEN 1 ELSE 0 END) 
AS 企业管理平均分,
100 * SUM(CASE WHEN cid = 4 AND score >= 60 THEN 1 ELSE 0 END) /
SUM(CASE WHEN cid = 4 THEN 1 ELSE 0 END) 
AS 企业管理及格百分数,

SUM(CASE WHEN cid = 10 THEN score ELSE 0 END) /
SUM(CASE cid WHEN 10 THEN 1 ELSE 0 END) 
AS 马克思平均分,
100 * SUM(CASE WHEN cid = 10 AND score >= 60 THEN 1 ELSE 0 END) /
SUM(CASE WHEN cid = 10 THEN 1 ELSE 0 END) 
AS 马克思及格百分数,

SUM(CASE WHEN cid = 3 THEN score ELSE 0 END) /
SUM(CASE cid WHEN 3 THEN 1 ELSE 0 END) 
AS 数据库平均分,
100 * SUM(CASE WHEN cid = 3 AND score >= 60 THEN 1 ELSE 0 END) /
SUM(CASE WHEN cid = 3 THEN 1 ELSE 0 END) 
AS 数据库及格百分数

FROM sc;

查询不同老师所教不同课程平均分从高到低显示;

select t.tname 教师名,c.cname 课程名,avg(sc.score) 平均分 from sc,t,c 
where t.tid = c.tid and c.cid = sc.cid
GROUP BY sc.cid 
order by avg(sc.score) desc;

查询如下课程总成绩第 3 名到第 6 名的学生成绩单：
企业管理（4），马克思（10），数据库（3）;
学生ID,学生姓名,企业管理,马克思,数据库,总成绩

SELECT
DISTINCT(sc.sid) As 学生学号,
s.sname AS 学生姓名 ,
IFNULL(T1.score,0) AS 企业管理,
IFNULL(T2.score,0) AS 马克思,
IFNULL(T3.score,0) AS 数据库,

IFNULL(T1.score,0) + IFNULL(T2.score,0) + IFNULL(T3.score,0) as 总分
FROM s,sc 
left JOIN sc as T1
on sc.sid = T1.sid and T1.cid = 4
left JOIN sc as T2
on sc.sid = T2.sid and T2.cid = 10
left JOIN sc as T3
on sc.sid = T3.sid and T3.cid = 3

WHERE s.sid = sc.sid ORDER BY 总分 desc ,sc.sid asc LIMIT 2,4;

统计各科成绩,各分数段人数:课程ID,课程名称,[100-85],(85-70],(70-60],[ <60];

# between A and B 包含 A,B
# 所以在本题中为了区别,我们认为[100-85]包含85,(85-70] 不包含85;

SELECT sc.cid as 课程ID, cName as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]'
,SUM(CASE WHEN score BETWEEN 70 AND 84.9 THEN 1 ELSE 0 END) AS '(85-70]'
,SUM(CASE WHEN score BETWEEN 60 AND 69.9 THEN 1 ELSE 0 END) AS '(70-60]'
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS '[<60]'
FROM sc,c
where sc.cId=c.cId
GROUP BY sc.cId,cName;

查询学生平均成绩及其名次;

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

查询各科成绩前三名的记录:(不考虑成绩并列情况);

不会

查询每门课程被选修的学生数;

select cid,count(sid) from sc 
group by cid;

查询出只选修了一门课程的全部学生的学号和姓名;

select sid from sc 
group by sid 
having count(cid) =1;

查询男生、女生人数;

select 
(select count(1) from s where ssex =1 )as 男同学,
(select count(1) from s where ssex =0 )as 女同学;

查询姓“张”的学生名单;

select sname from s 
where sname like "张%";

查询同名同性学生名单，并统计同名人数;

select sname 同名同性学生,count(*) 同名人数 from s 
group by sname,sSex 
having count(*)>1;

1981年出生的学生名单;

select sname from s 
where sAge = (YEAR(CURDATE())-1981);

查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列;

select cid,avg(score) from sc 
GROUP BY cid 
order by AVG(score),cid DESC;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩;

select s.sid,s.sname,avg(score) as 平均分 from sc,s 
WHERE s.sid = sc.sid 
GROUP BY sid 
having avg(score)>85 
ORDER BY 平均分 desc ;

查询课程名称为“数据库”，且分数低于60的学生姓名和分数;

select s.sname,score from sc,s 
where sc.sid = s.sid AND 
sc.cid = (select cid from c WHERE cname = "数据库") and
 score > 60;

查询所有学生的选课情况:
学生 id,学生姓名,课程名；

select s.sid,s.sname,c.cname from s,sc,c
where c.cid = sc.cid and s.sid=sc.sid;

查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select s.sname,c.cname,sc.score from s,sc,c
where c.cid = sc.cid and s.sid=sc.sid AND score > 70;

查询所有有不及格的课程id,课程名,不及格人数，并按课程号从大到小排列;

select c.cid,c.cname,count(1) as 不及人数 from c,sc 
where c.cid = sc.cid and sc.score < 60 
GROUP BY sc.cid 
ORDER BY cid desc;

查询课程编号为3且课程成绩在80分以上的学生的学号和姓名并且按照学号升序排列；

select s.sid 学号 ,s.sname 姓名 from s,sc
where s.sid = sc.sid and sc.score > 80 AND sc.cid = 3 
ORDER BY sc.sid;

求选了课程的学生人数;

select count(1) 选了课的总人数 from
(select sid from sc group by sid) as temp;

查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩;

select s.sName,score,cname
from s,sc,c C,t
where s.sid=sc.sid and 
sc.cid=C.cid and 
C.tid=t.tid and 
t.tname='叶平' and
sc.score=(
elect max(score)from sc where cid=C.cid 
);

查询各个课程及相应的选修人数;

select sc.cid,cname,count(1) 选修人数 from sc,c 
where sc.cid = c.cid GROUP BY sc.cid;

查询不同课程成绩相同的学生的学号、课程号、学生成绩;

select distinct A.sid,B.score from sc A ,sc B 
where A.score=B.score and 
A.cid != B.cid ;

查询每门功课成绩最好的前两名;

SELECT t1.sid as 学生ID,t1.cid as 课程ID,score as 分数
FROM sc t1
WHERE score IN 
    (select temp.score from 
        (SELECT score FROM sc
        ORDER BY score DESC
        limit 2) 
    as temp)
ORDER BY t1.cid;

统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列;

select sc.cid,cname,count(1) 选修人数 from sc,c 
where sc.cid = c.cid 
GROUP BY sc.cid 
HAVING 选修人数 >=10 
ORDER BY 选修人数 desc,cid asc ;

检索至少选修两门课程的学生学号;

select sid,count(1) 选修课数 from sc 
GROUP BY sid 
HAVING count(1) >=2 
ORDER BY sid asc;

查询全部学生都选修的课程的课程号和课程名;

select sc.cid,cname from sc,c 
where sc.cid = c.cid 
GROUP BY cid 
HAVING cid = (SELECT count(1) from c);

查询两门以上不及格课程的同学的学号及其平均成绩;

select sid,avg(ifnull(score,0)) from sc 
where sid in 
(select sid from sc 
    where score <60 
    group by sid 
    having count(*)>2
)
group by sid;

检索“4”课程分数小于60，按分数降序排列的同学学号;

select sid from sc 
where cid = 4 and score < 60 
ORDER BY score desc;

删除“2”同学的“1”课程的成绩;

delete from sc where sid =2 and cid = 1;