SQL query practice with MySQL

[toc]

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 ;