挑战SQL经典题（一）
2018-10-24 本文已影响0人
莫问以
建表语句：
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) DEFAULT NULL COMMENT 'ID',
`cname` varchar(32) DEFAULT NULL COMMENT '课程名',
`tid` int(11) DEFAULT NULL COMMENT '教师Id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '英语', '3');
INSERT INTO `course` VALUES ('4', '物理', '4');
-- ----------------------------
-- Table structure for `sc`
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sid` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sc
-- ----------------------------
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');
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL COMMENT 'ID',
`sname` varchar(32) NOT NULL COMMENT '姓名',
`sage` int(11) NOT NULL COMMENT '年龄',
`ssex` varchar(8) NOT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
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', '女');
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) DEFAULT NULL,
`tname` varchar(16) DEFAULT NULL
) ENGINE=InnoDB 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', '周磊');
表说明：
student(sid,sname,sage,ssex) 学生表
course(cid,cname,tid) 课程表
sc(sid,cid,score) 成绩表
teacher(tid,tname) 教师表
数据图.png
问题：
1、查询“1”课程比“2”课程成绩高的所有学生的学号；（3号-张三）
SELECT a.sid 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
2、查询平均成绩大于60分的同学的学号和平均成绩；（1、2、3、4号）
SELECT sid,AVG(score) FROM sc
GROUP BY sid HAVING AVG(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT a.sid,a.sname,COUNT(b.cid),SUM(b.score) FROM student a
LEFT OUTER JOIN sc b ON a.sid=b.sid GROUP BY a.sid,a.sname
4、查询姓“李”的老师的个数；（0个）
SELECT COUNT(DISTINCT(tname)) FROM teacher
WHERE tname LIKE '李%'
5、查询没学过“叶平”老师课的同学的学号、姓名； （4号-李四）
SELECT sid,sname FROM student WHERE sid NOT IN
(SELECT a.sid FROM student a,course b,sc c,teacher d
WHERE d.tname='叶平' AND d.tid=b.tid AND b.cid=c.cid AND c.sid=a.sid)
6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
（1号-刘一 2号-钱二 3号-张三 6号-赵六 除了4号和5号）---考查EXISTS
SELECT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid=sc_sc.sid AND sc_sc.cid='1'
AND EXISTS
(SELECT * FROM sc as b WHERE b.sid=sc_sc.sid AND b.cid='2')
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； （除了4号）
SELECT sid,sname FROM student WHERE sid IN
(SELECT c.sid FROM course b,sc c,teacher d
WHERE d.tname='叶平' AND d.tid=b.tid AND b.cid=c.cid )
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
（3号-张三）
SELECT stu_stu.sid,stu_stu.sname FROM
(SELECT student.sid,student.sname,score,
(SELECT score FROM sc sc_sc WHERE sc_sc.sid=student.sid AND sc_sc.cid='2') score2
FROM student,sc WHERE student.sid=sc.sid and cid='1') stu_stu
WHERE score2 <score
9、查询所有课程有成绩小于60分的同学的学号、姓名；
（1号、3号、5号、6号 除了2号和4号）
SELECT sid,sname FROM student WHERE sid IN(
SELECT sid FROM sc WHERE score<60)
10、查询没有学全所有课的同学的学号、姓名； （4号、5号、6号）
SELECT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid=sc_sc.sid GROUP BY stu.sid,stu.sname
HAVING COUNT(cid)!='4'
写法二（推荐）：
SELECT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid=sc_sc.sid GROUP BY stu.sid,stu.sname
HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course)
11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名； （All）
SELECT DISTINCT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid=sc_sc.sid AND sc_sc.cid IN
(SELECT cid FROM sc WHERE sid='1')
12、查询跟学号为“1”的同学学的所有课一样的其他同学学号和姓名； （2号和3号）
SELECT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid!='1' AND stu.sid=sc_sc.sid GROUP BY stu.sid,stu.sname
HAVING COUNT(cid) = (SELECT COUNT(cid) FROM sc WHERE sid='1')
13、把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； （61.4）
思路：
1）叶平老师教语文，cid='1'，先查cid为1的所有成绩，一共5条
MYSQL>SELECT sc_sc.score,sc_sc.cid FROM sc sc_sc,course cou,teacher tea
WHERE tea.tname='叶平' AND tea.tid=cou.tid AND cou.cid=sc_sc.cid
2）计算出平均成绩
MYSQL>SELECT AVG(score) FROM sc sc_sc,course cou,teacher tea
WHERE tea.tname='叶平' AND tea.tid=cou.tid AND cou.cid=sc_sc.cid
3）修改成绩为平均成绩
UPDATE sc SET score=(第2步) WHERE cid=(第1步)
14、删除学习“叶平”老师课的SC表记录；
看图其实是这么一句话：DELETE FROM sc WHERE cid='1'，所以：
DELETE sc FROM course cou,teacher tea
WHERE tea.tname='叶平' AND tea.tid=cou.tid AND cou.cid=sc_sc.cid
15、向sc表中插入一条记录，要求符合以下条件：没有上过编号“3”课程的同学学号、2号课的平均成绩；（6号童鞋没有上过3号课程）
INSERT sc SELECT sid,'2',(SELECT AVG(score) FROM sc WHERE cid='2')
FROM student WHERE sid NOT IN (SELECT sid FROM sc WHERE cid='3')
Insert Result.png
16、
17、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT nn.cid As 课程ID,nn.score AS 最高分,mm.score AS 最低分 FROM sc nn,sc AS mm
WHERE nn.cid=mm.cid AND nn.score=(
SELECT MAX(sc_sc.score) FROM sc AS sc_sc,student AS stu
WHERE nn.cid=sc_sc.cid AND stu.sid=sc_sc.sid GROUP BY sc_sc.cid)
AND mm.score=(
SELECT MIN(sc_sc_min.score) FROM sc AS sc_sc_min
WHERE mm.cid=sc_sc_min.cid GROUP BY sc_sc_min.cid)
执行结果.png
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
