SQL query practice with MySQL
SQL query practice with MySQL
目录
- SQL query practice with MySQL
- 0.create table
- 1. find
student_idwherebio score higher than phy score - 2. 查询平均成绩大于60分的同学的学号和平均成绩
- 3.查询所有同学的学号、姓名、选课数、总成绩
- 4. 查询姓“李”的老师的个数
- 5.查询没学过“李平”老师课的同学的学号、姓名
- 6. 查询学过“001”并且
也学过编号“002”课程的同学的学号、姓名 - 7. 查询
所有课程成绩小于60分的同学的学号、姓名 - 8. 查询没有学
全所有课的同学的学号、姓名 - 9.查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
- 10. 查询至少学过学号为“001”同学所选课程中任意一门课的
其他同学学号和姓名； - 11. 删除学习“李平”老师课的SC表记录
- 12. 向SC表中插入一些记录，这些记录要求符合以下条件：
- 13.按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，
- 14.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
- 15. 按各科平均成绩从低到高和及格率的百分数从高到低顺序
- *** 16. 课程平均分从高到低显示（显示任课老师）
- *** 17.查询各科成绩前三名的记录(不考虑成绩并列情况)
- 18.查询每门课程被选修的学生数
- 19.查询只选修了一门课程的全部学生的学号和姓名
- 20. 查询男生、女生人数
- 21. 查询姓“张”的学生名单
- 22. 查询同名同姓学生名单，并统计同名人数
- 23. 查询每门课平均成绩，结果按平均成绩升序排列;
- 24. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
- ***** 25. 查询课程名称为“物理”，且分数低于60的学生姓名和分数
- 26.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
- 27.求选了课程的学生人数
- 28.查询选修“刘海”老师所授课程的学生中，成绩最高的学生姓名及其成绩
- 29.查询各个课程及相应的选修人数
- *** 30.查询不同课程但成绩相同的学生的学号、课程号、学生成绩
- ***** 31.查询每门课程成绩最好的前两名(同17題)
- 32.检索至少选修两门课程的学生学号
- 33.查询全部学生都选修的课程的课程号和课程名
- 34. 查询没学过“李平”老师讲授的任一门课程的学生姓名
- 35. 查询两门以上不及格课程的同学的学号及其平均成绩
- 36. 检索课程"4"分数小于90，按分数降序排列的同学学号
- 37.删除“002”同学的“001”课程的成绩
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 colums
SELECT 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 bb
LEFT 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: filter
WHERE aa.num > IF(ISNULL(bb.num),0,bb.num);
2. 查询平均成绩大于60分的同学的学号和平均成绩
SELECT student_id,AVG(num) AS avsc -- as
FROM score
GROUP BY student_id -- group by
HAVING avsc > 60; -- having
3.查询所有同学的学号、姓名、选课数、总成绩
SELECT stu.sid,stu.sname,bb.counter,bb.total
FROM student AS stu
LEFT JOIN -- stp2: join
(SELECT student_id,COUNT(course_id) AS counter,SUM(num) AS total
FROM score
GROUP BY student_id) AS bb -- stp1: temp table bb
ON stu.`sid` = bb.`student_id`;
4. 查询姓“李”的老师的个数
SELECT COUNT(tid)
FROM teacher AS tc
WHERE tname LIKE '李%'; -- like %
5.查询没学过“李平”老师课的同学的学号、姓名
SELECT sid,sname
FROM student AS stu
WHERE 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, sname
FROM student
WHERE 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, sname
FROM student
WHERE 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,sname
FROM student
WHERE sid IN -- in
(
SELECT student_id
FROM score
GROUP BY student_id
HAVING MIN(num) < 60 -- having min()
);
SELECT sid,sname
FROM student
WHERE sid IN
(
SELECT student_id
FROM score
WHERE num < 60
GROUP BY student_id -- group by
);
8. 查询没有学全所有课的同学的学号、姓名
SELECT sid,sname
FROM student
WHERE sid IN -- in
(
SELECT student_id
FROM score
GROUP BY student_id
HAVING 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) -- 11
GROUP BY student_id -- 1
HAVING COUNT(course_id) = -- 1
(SELECT
COUNT(1)
FROM
score
WHERE student_id = 1)
11. 删除学习“李平”老师课的SC表记录
delete -- delete from tblname
from
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 where
SELECT
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,
avsc
from
(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 min
from score
group 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 -- END
from
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 tbl
FROM
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 select
GROUP 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 2nd
on 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 score
group by course_id;
19.查询只选修了一门课程的全部学生的学号和姓名
select student_id,sname,count(1)
from score left join student -- from A left join B
on student_id = sid -- on A. = B.
group by student_id
having count(1) = 1;
20. 查询男生、女生人数
select gender,count(1) as persons
from student
group by gender;
21. 查询姓“张”的学生名单
SELECT *
FROM student
WHERE sname LIKE "张%";
22. 查询同名同姓学生名单，并统计同名人数
select sname,count(1) as NUM
from student
group by sname -- group by sname
having count(1) > 1
order by num desc;
23. 查询每门课平均成绩，结果按平均成绩升序排列;
平均成绩相同时，按课程号降序排列
note
order by c1,c2 desc
SELECT course_id,avg(num) AS avsc
FROM score
GROUP BY course_id
ORDER 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 avsc
from score as sc
left join student as stu -- use left join on, not subquery
on sc.student_id = stu.sid -- must be full name for ON clause
group by student_id
having AVG(num) > 85;
my ans.
SELECT aa.student_id,sname,aa.avsc
FROM
(select student_id,avg(num) AS avsc
FROM score
GROUP BY student_id
HAVING avsc > 85) AS aa
LEFT JOIN student -- must be aa LEFT JOIN student,or many NULL yield
ON 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,num
from score as sc left join student as st
on 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一表in
select
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 score
GROUP BY course_id;
*** 30.查询不同课程但成绩相同的学生的学号、课程号、学生成绩
SELECT
aa.course_id,
aa.student_id,
aa.num, -- aa.num
bb.student_id,
bb.num -- bb.num
FROM
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 num
select
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 score
group by student_id
having 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,num
from score
where course_id= 4 and num < 90
order by num desc;
37.删除“002”同学的“001”课程的成绩
too easy
delete
from
score
where student_id = 2
and course_id = 1 ;
