目录
SQL query practice with MySQL
0.create table
/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50624 Source Host : localhost Source Database : sqlexam Target Server Type : MySQL Target Server Version : 50624 File Encoding : utf-8 Date: 10/21/2016 06:46:46 AM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for `class`-- ----------------------------DROP TABLE IF EXISTS `class`;CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `class`-- ----------------------------BEGIN;INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');COMMIT;-- ------------------------------ Table structure for `course`-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `course`-- ----------------------------BEGIN;INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');COMMIT;-- ------------------------------ Table structure for `score`-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), -- create index KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `score`-- ----------------------------BEGIN;INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');COMMIT;-- ------------------------------ Table structure for `student`-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `student`-- ----------------------------BEGIN;INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');COMMIT;-- ------------------------------ Table structure for `teacher`-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `teacher`-- ----------------------------BEGIN;INSERT INTO `teacher` VALUES ('1', '张磊'), ('2', '李平'), ('3', '刘海'), ('4', '朱云'), ('5', '李杰');COMMIT;SET FOREIGN_KEY_CHECKS = 1; 1. find student_id where bio score higher than phy score
-- stp0: list columsSELECT aa.student_id,aa.num AS BIO,bb.num AS PHY FROM -- stp1: temporary table aa (SELECT student_id,num FROM score LEFT JOIN course ON score.`course_id`= course.`cid` WHERE course.`cname`="生物") AS aa -- stp3: aa left join bbLEFT JOIN -- stp2: tempo table bb (SELECT student_id,num FROM score LEFT JOIN course ON score.`course_id`= course.`cid` WHERE course.`cname`="物理") AS bb ON aa.student_id = bb.student_id --stpt4: filterWHERE aa.num > IF(ISNULL(bb.num),0,bb.num);
2. 查询平均成绩大于60分的同学的学号和平均成绩
SELECT student_id,AVG(num) AS avsc -- asFROM scoreGROUP BY student_id -- group byHAVING avsc > 60; -- having
3.查询所有同学的学号、姓名、选课数、总成绩
SELECT stu.sid,stu.sname,bb.counter,bb.totalFROM student AS stuLEFT JOIN -- stp2: join(SELECT student_id,COUNT(course_id) AS counter,SUM(num) AS totalFROM scoreGROUP BY student_id) AS bb -- stp1: temp table bbON stu.`sid` = bb.`student_id`;
4. 查询姓“李”的老师的个数
SELECT COUNT(tid)FROM teacher AS tcWHERE tname LIKE '李%'; -- like %
5.查询没学过“李平”老师课的同学的学号、姓名
SELECT sid,snameFROM student AS stuWHERE sid NOT IN -- not in ( SELECT student_id -- select stu_id FROM score AS sc LEFT JOIN -- stp2: join (SELECT cid -- just need cid,not teacher_id FROM course AS cs LEFT JOIN teacher AS tc ON cs.teacher_id = tc.`tid` WHERE tc.`tname`="李平") bb -- stp1: temp tbl bb ON sc.`course_id`= bb.cid GROUP BY student_id );
6. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
SELECT sid, snameFROM studentWHERE sid IN (SELECT student_id FROM score WHERE course_id IN (1,2) -- in (1,2) GROUP BY student_id HAVING COUNT(course_id) = 2 );
SELECT sid, snameFROM studentWHERE sid IN ( SELECT aa.student_id FROM (SELECT student_id FROM score AS sc WHERE sc.`course_id`="1" ) AS aa INNER JOIN (SELECT student_id FROM score AS sc WHERE sc.`course_id`="2" ) AS bb ON aa.student_id = bb.student_id );
7. 查询所有课程成绩小于60分的同学的学号、姓名
SELECT sid,snameFROM studentWHERE sid IN -- in(SELECT student_idFROM scoreGROUP BY student_idHAVING MIN(num) < 60 -- having min());
SELECT sid,snameFROM studentWHERE sid IN(SELECT student_idFROM scoreWHERE num < 60 GROUP BY student_id -- group by);
8. 查询没有学全所有课的同学的学号、姓名
SELECT sid,snameFROM studentWHERE sid IN -- in( SELECT student_idFROM scoreGROUP BY student_idHAVING COUNT(DISTINCT course_id) < -- count() (SELECT COUNT(DISTINCT cid) FROM course));
select sid,sname from student where sid not in (select student_id from score group by student_id having count(course_id)= (select count(cid) from course))
9.查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
SELECT sid, sname FROM student WHERE sid IN -- in (SELECT DISTINCT student_id FROM score WHERE course_id IN -- in (SELECT course_id FROM score WHERE student_id = "1")) ;
10. 查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；
note:
个数相同; 002学过的也学过 SELECT student_id, sname FROM score LEFT JOIN student ON score.student_id = student.sid WHERE student_id IN -- 1 (SELECT student_id FROM score WHERE student_id != 1 GROUP BY student_id HAVING COUNT(course_id) = -- 11 (SELECT COUNT(1) FROM score WHERE student_id = 1)) -- 111 AND course_id IN -- 1 (SELECT course_id FROM score WHERE student_id = 1) -- 11GROUP BY student_id -- 1HAVING COUNT(course_id) = -- 1 (SELECT COUNT(1) FROM score WHERE student_id = 1)
11. 删除学习“李平”老师课的SC表记录
delete -- delete from tblnamefrom score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = "李平")) ;
12. 向SC表中插入一些记录，这些记录要求符合以下条件：
-- ①没有上过编号“002”课程的同学学号；
-- ②插入“002”号课程的平均成绩INSERT INTO score (student_id, course_id, num) -- insert into select from whereSELECT sid, 2, (SELECT AVG(num) FROM score WHERE course_id = "2") -- select avg(num)FROM student WHERE sid NOT IN (SELECT student_id FROM score WHERE course_id != "2") ;
13.按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，
select student_id, (select num from score left join aa on student_id = aa.student_id and course_id = (select cid from course where cname = "生物")) as biosc, (SELECT num FROM score LEFT JOIN aa ON student_id = aa.student_id AND course_id = (SELECT cid FROM course WHERE cname = "物理")) as physc, (SELECT num FROM score LEFT JOIN aa ON student_id = aa.student_id AND course_id = (SELECT cid FROM course WHERE cname = "美术")) as picsc, subs, avscfrom (select student_id,count(course_id) as subs, avg(num) as avsc from score group by student_id order by avsc desc ) as aa; -- temp tbl
14.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select course_id,max(num),min(num) -- max minfrom scoregroup by course_id; -- group by
15. 按各科平均成绩从低到高和及格率的百分数从高到低顺序
思路：case when .. then .. END
select course_id, avg(num), sum( case when num >= 60 -- CASE WHEN exp then 1 -- THEN value1 else 0 -- ELSE value2 end) / count(1) * 100 as percent -- ENDfrom score group by course_id order by avg(num) asc, percent desc ;
wrong answer:
SELECT course_id, AVG(num), (SELECT ( (SELECT COUNT(1) FROM score WHERE num >= "60" GROUP BY course_id) / -- note: return array but value attributed to group by (SELECT -- no array devides array opration in mysql COUNT(1) FROM score GROUP BY course_id) )) AS percent FROM score GROUP BY course_id ORDER BY AVG(num) ASC, percent DESC ;
*** 16. 课程平均分从高到低显示（显示任课老师）
key: 3 tables join
SELECT tname, -- tname of 3rd tbl AVG(num) -- avg of 1st tblFROM score LEFT JOIN course ON score.course_id = course.cid -- tb1 left join tb2 LEFT JOIN teacher ON course.teacher_id = teacher.tid -- tb2 left join tb3 in one selectGROUP BY course_id ORDER BY AVG(num) DESC ;
wrong answer:
select course_id, tname, avsc from (select course_id, teacher_id, avg(num) as avsc from score left join course on course_id = cid group by course_id order by avsc desc) as aa left join teacher -- wrong: left join 2ndon aa.teacher_id = tid ;
*** 17.查询各科成绩前三名的记录(不考虑成绩并列情况)
NOTE
the field after select must be same as group by sentence
select course_id, (select num -- the field after SELECT must be same as group by sentence from score WHERE course_id = aa.course_id GROUP BY num -- group by `num` : num is same as select `num` ORDER BY num desc LIMIT 0, 1) as st, (select num from score WHERE course_id = aa.course_id group by num order by num desc limit 1, 1) as nd, (select num from score WHERE course_id = aa.course_id group by num order by num desc limit 2, 1) as rd FROM score as aa group by course_id ;
18.查询每门课程被选修的学生数
select course_id, count(1) as stus -- count(distinct col)from scoregroup by course_id;
19.查询只选修了一门课程的全部学生的学号和姓名
select student_id,sname,count(1)from score left join student -- from A left join Bon student_id = sid -- on A. = B.group by student_idhaving count(1) = 1;
20. 查询男生、女生人数
select gender,count(1) as personsfrom studentgroup by gender;
21. 查询姓“张”的学生名单
SELECT *FROM studentWHERE sname LIKE "张%";
22. 查询同名同姓学生名单，并统计同名人数
select sname,count(1) as NUM from studentgroup by sname -- group by snamehaving count(1) > 1order by num desc;
23. 查询每门课平均成绩，结果按平均成绩升序排列;
平均成绩相同时，按课程号降序排列
note
order by c1,c2 desc
SELECT course_id,avg(num) AS avscFROM scoreGROUP BY course_idORDER BY avsc,course_id DESC; -- order by col1,col2 : clo2 take effect only when col1 are same
24. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
better ans.
select student_id,sname,avg(num) as avscfrom score as scleft join student as stu -- use left join on, not subqueryon sc.student_id = stu.sid -- must be full name for ON clausegroup by student_idhaving AVG(num) > 85;
my ans.
SELECT aa.student_id,sname,aa.avscFROM(select student_id,avg(num) AS avscFROM scoreGROUP BY student_idHAVING avsc > 85) AS aaLEFT JOIN student -- must be aa LEFT JOIN student,or many NULL yieldON aa.student_id = sid;
***** 25. 查询课程名称为“物理”，且分数低于60的学生姓名和分数
note
join 3 tables along with WHERE clause
先join，再where過濾
SELECT sname, num FROM score LEFT JOIN course ON score.`course_id` = course.`cid` LEFT JOIN student ON score.`student_id` = student.`sid` WHERE course.`cname` = "物理" -- two LEFT JOIN with WHERE AND score.`num` < 60 ;
26.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
note
- on子句必須用全名；
- 先join,再where過濾
select student_id,sname,numfrom score as sc left join student as ston sc.`student_id` = st.`sid`where course_id ="3" and num > 80;
27.求选了课程的学生人数
select count(sid) -- 是學生from student where sid in (select student_id -- 并且選了課的學生 from score) ;
my ans.
select count(distinct student_id)from score;
28.查询选修“刘海”老师所授课程的学生中，成绩最高的学生姓名及其成绩
三表直接用逗號隔開，用where代替join更簡潔
note: 四表关联，三表join一表inselect st.sname, max(sc.num)from course as cs left join score as sc on sc.`course_id` = cs.`cid` left join student as st on sc.`student_id` = st.`sid` where cs.`teacher_id` in (select cid from teacher where tname = "刘海") ;
29.查询各个课程及相应的选修人数
SELECT course_id,COUNT(DISTINCT student_id)FROM scoreGROUP BY course_id;
*** 30.查询不同课程但成绩相同的学生的学号、课程号、学生成绩
SELECT aa.course_id, aa.student_id, aa.num, -- aa.num bb.student_id, bb.num -- bb.numFROM score AS aa, score AS bb where aa.student_id != bb.student_id -- aa.id != bb.id AND aa.course_id != bb.course_id AND aa.num = bb.num ;
等价写法
select aa.course_id, aa.student_id, aa.num, bb.student_id, bb.num from score as aa inner join -- inner join on score as bb on aa.student_id != bb.student_id and aa.course_id != bb.course_id and aa.num = bb.num ;
***** 31.查询每门课程成绩最好的前两名(同17題)
GROUP by合并重复行，同select DISTINCT
course_id | 1 st num | 2 nd numselect course_id, (select num from score where course_id = aa.course_id group by num -- group by num: 去除重複分數 order by num desc limit 0, 1) as st, (select num from score where course_id = aa.course_id group by num order by num desc limit 1, 1) as nd from score as aa group by course_id ; -- 按course_id归并，去重复
32.检索至少选修两门课程的学生学号
select student_id,count(1)from scoregroup by student_idhaving count(1) > 2;
33.查询全部学生都选修的课程的课程号和课程名
两表或三表关联，不用join更简洁
select course_id, cname from score as sc, -- 两表直接用逗号 course as cs where cs.`cid` = sc.`course_id` group by course_id having count(1) = (select count(1) from student) ;
34. 查询没学过“李平”老师讲授的任一门课程的学生姓名
select st.sid, sname from score as sc, student as st where sc.`student_id` = st.`sid` and sc.`course_id` not in (select cid from course as cs, teacher as tc where cs.`teacher_id` = tc.`tid` and tc.`tname` = "李平") group by st.`sid` ; -- group by去除重名
35. 查询两门以上不及格课程的同学的学号及其平均成绩
key:
CASE WHEN THEN ELSE END
select student_id, avg(num), SUM( CASE WHEN num < 60 THEN 1 ELSE 0 END) as failed from score group by student_id having failed > 2 ;
36. 检索课程"4"分数小于90，按分数降序排列的同学学号
so easy
select student_id,numfrom scorewhere course_id= 4 and num < 90order by num desc;
37.删除“002”同学的“001”课程的成绩
too easy
delete from score where student_id = 2 and course_id = 1 ;