-- MySQL dump 10.13  Distrib 5.6.16, for Linux (x86_64)
--
-- Host: localhost    Database: luffysql
-- ------------------------------------------------------
-- Server version   5.6.16

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(255) NOT NULL,
  `grade_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `cls_grade_id` (`grade_id`),
  CONSTRAINT `cls_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `class_grade` (`gid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class`
--

LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'一年一班',1),(2,'二年一班',2),(3,'三年二班',3),(5,'一年二班',1),(6,'一年三班',1),(7,'二年二班',2),(8,'一年四班',1),(9,'二年三班',2),(10,'四年一班',4),(11,'四年二班',4),(12,'六年一班',6),(13,'五年一班',5),(14,'五年二班',5),(15,'四年三班',4);
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `class_grade`
--

DROP TABLE IF EXISTS `class_grade`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class_grade` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `gname` varchar(255) NOT NULL,
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class_grade`
--

LOCK TABLES `class_grade` WRITE;
/*!40000 ALTER TABLE `class_grade` DISABLE KEYS */;
INSERT INTO `class_grade` VALUES (1,'一年级'),(2,'二年级'),(3,'三年级'),(4,'四年级'),(5,'五年级'),(6,'六年级');
/*!40000 ALTER TABLE `class_grade` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `course`
--

DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `course_teacher_id` (`teacher_id`),
  CONSTRAINT `cos_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `course`
--

LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (1,'生物',1),(2,'体育',1),(3,'物理',2),(4,'数学',1),(5,'英语',1),(6,'化学',3),(7,'政治',3),(8,'语文',3);
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `score`
--

DROP TABLE IF EXISTS `score`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `score_student_id` (`student_id`),
  KEY `score_course_id` (`course_id`),
  CONSTRAINT `score_stu_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `score_cour_id` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `score`
--

LOCK TABLES `score` WRITE;
/*!40000 ALTER TABLE `score` DISABLE KEYS */;
INSERT INTO `score` VALUES (1,1,1,60),(2,1,2,59),(3,2,2,99),(4,1,3,66),(5,3,1,59),(6,3,2,70),(7,2,1,61),(8,2,3,60),(9,1,4,60),(10,1,5,30),(11,4,2,76),(12,12,2,76),(13,7,2,76),(14,8,2,76),(15,9,2,76),(16,11,2,76),(17,10,2,76),(18,6,2,76),(19,5,2,76),(26,1,5,60),(27,1,8,80),(28,13,4,50),(29,4,3,59),(30,2,3,52);
/*!40000 ALTER TABLE `score` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) NOT NULL,
  `gender` enum('男','女') NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `sname` (`sname`) USING BTREE,
  KEY `stu_class_id` (`class_id`),
  CONSTRAINT `stu_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'乔丹','女',1),(2,'艾弗森','女',1),(3,'科比','男',2),(4,'alex','男',3),(5,'agon','女',15),(6,'aliyun','男',13),(7,'大王','男',10),(8,'dog','女',11),(9,'佩奇','男',11),(10,'菲菲','女',12),(11,'糖果','女',11),(12,'大象','男',8),(13,'张飞','男',8);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `teach2cls`
--

DROP TABLE IF EXISTS `teach2cls`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teach2cls` (
  `tcid` int(11) NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY (`tcid`),
  KEY `teach_tid` (`tid`),
  KEY `teach_cid` (`cid`),
  CONSTRAINT `teacls_tid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `teacls_cid` FOREIGN KEY (`cid`) REFERENCES `class` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teach2cls`
--

LOCK TABLES `teach2cls` WRITE;
/*!40000 ALTER TABLE `teach2cls` DISABLE KEYS */;
INSERT INTO `teach2cls` VALUES (1,1,1),(2,1,2),(3,2,1),(4,3,2),(5,2,3),(6,1,6);
/*!40000 ALTER TABLE `teach2cls` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `teacher`
--

DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teacher`
--

LOCK TABLES `teacher` WRITE;
/*!40000 ALTER TABLE `teacher` DISABLE KEYS */;
INSERT INTO `teacher` VALUES (1,'张三'),(2,'李四'),(3,'王五'),(4,'王刚'),(5,'李飞');
/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-04-10 16:15:44

-- 查询学生总人数；
SELECT COUNT(1) FROM student;

-- 查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
SELECT
    s1.student_id,
    s2.sname 
FROM
    score AS s1
    LEFT JOIN course AS c1 ON s1.course_id = c1.cid
    LEFT JOIN student AS s2 ON s1.student_id = s2.sid 
WHERE
    cid IN ( SELECT cid FROM course WHERE cname = '生物' OR cname = '物理' ) 
    AND score >= 60 
GROUP BY
    student_id 
HAVING
    COUNT( 1 ) > 1;


-- 查询每个年级的班级数，取出班级数最多的前三个年级；
SELECT
    cg.gname 
FROM
    class AS c1
    LEFT JOIN class_grade AS cg ON c1.grade_id = cg.gid 
GROUP BY
    cg.gid 
ORDER BY
    COUNT( 1 ) DESC 
    LIMIT 3;

-- 查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
( SELECT
    se.student_id,
    st.sname,
    AVG( score ) 
    FROM
        score AS se
        LEFT JOIN student AS st ON se.student_id = st.sid 
    GROUP BY
        student_id 
    ORDER BY
        AVG( score ) DESC 
        LIMIT 1 
    ) UNION ALL
    (
    SELECT
        se.student_id,
        st.sname,
        AVG( score ) 
    FROM
        score AS se
        LEFT JOIN student AS st ON se.student_id = st.sid 
    GROUP BY
        student_id 
    ORDER BY
        AVG( score ) ASC 
        LIMIT 1 
    );

-- 查询每个年级的学生人数；
SELECT
    COUNT( * ),
    s.class_id,
    c.caption,
    cg.gname 
FROM
    student s,
    class c,
    class_grade cg 
WHERE
    c.cid = s.class_id 
    AND cg.gid = c.grade_id 
GROUP BY
    cg.gid
-- # 方式二
SELECT
    COUNT( * ),
    s.class_id,
    c.caption,
    cg.gname 
FROM
    student s
    LEFT JOIN class c ON c.cid = s.class_id
    LEFT JOIN class_grade cg ON cg.gid = c.grade_id 
GROUP BY
    cg.gid


-- 查询每位学生的学号，姓名，选课数，平均成绩；
SELECT
    st.sid,
    st.sname,
    COUNT( cname ),
    AVG( score ) 
FROM
    score AS se
    LEFT JOIN student AS st ON se.student_id = st.sid
    LEFT JOIN course AS ce ON se.course_id = ce.cid 
GROUP BY
    student_id

-- 查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
SELECT
    st.sname,
    ce.cname AS high_course,
    (
    SELECT
        course.cname 
    FROM
        score
        LEFT JOIN course ON score.course_id = course.cid 
    WHERE
        student_id = 2 
    ORDER BY
        score ASC 
        LIMIT 1 
    ) AS min_couse,
    se.score 
FROM
    score se
    LEFT JOIN course ce ON se.course_id = ce.cid
    LEFT JOIN student st ON se.student_id = st.sid 
WHERE
    se.student_id = 2 
ORDER BY
    se.score DESC 
    LIMIT 1

-- 查询姓“李”的老师的个数和所带班级数；
SELECT
    COUNT( 1 ) AS '带班个数',
    (
    SELECT
        COUNT( 1 ) 
    FROM
        teacher tr 
    WHERE
        tr.tname LIKE '李%' 
    ) AS '李老师个数' 
FROM
    teach2cls ts
    LEFT JOIN teacher tr ON ts.tid = tr.tid 
WHERE
    tr.tname LIKE '李%' 
GROUP BY
    ts.tid

-- 查询班级数小于5的年级id和年级名；
SELECT
    cg.gid,
    cg.gname 
FROM
    class cs
    LEFT JOIN class_grade cg ON cs.grade_id = cg.gid 
WHERE
    cs.cid < 5

-- 查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；
SELECT
    c.cid AS '班级id',
    c.caption AS '班级名称',
    cg.gname AS '年级',
    (
    CASE

            WHEN cg.gid <= 12 THEN
            '低年级' 
            WHEN cg.gid <= 34 AND cg.gid > 12 THEN
            '中年级' 
            WHEN cg.gid <= 56 AND cg.gid > 34 THEN
            '高年级' ELSE '无' 
        END 
        ) AS '年级级别' 
    FROM
        class c
        LEFT JOIN class_grade cg ON c.grade_id = cg.gid

-- 查询学过“张三”老师2门课以上的同学的学号、姓名；
SELECT
    s.sid,
    s.sname 
FROM
    score
    LEFT JOIN student s ON score.student_id = s.sid 
WHERE
    course_id IN (
    SELECT
        cid 
    FROM
        course c
        LEFT JOIN teacher t ON c.teacher_id = t.tid 
    WHERE
        t.tname = '张三' 
    ) 
GROUP BY
    student_id 
HAVING
    COUNT( 1 ) > 2

-- 查询教授课程超过2门的老师的id和姓名；
SELECT
    t.tid,
    t.tname 
FROM
    course c
    LEFT JOIN teacher t ON c.teacher_id = t.tid 
GROUP BY
    tname 
HAVING
    COUNT( 1 ) > 2

-- 查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
SELECT
    st.sid,
    st.sname 
FROM
    score s
    LEFT JOIN student st ON s.student_id = st.sid 
WHERE
    course_id IN ( 1, 2 ) 
GROUP BY
    student_id 
HAVING
    COUNT( 1 ) > 1

-- 查询没有带过高年级的老师id和姓名；
SELECT
    tr.tid,
    tr.tname 
FROM
    teach2cls t
    LEFT JOIN teacher tr ON t.tid = tr.tid 
WHERE
    cid BETWEEN 34 
    AND 56

-- 查询学过“张三”老师所教的所有课的同学的学号、姓名；
SELECT
    s.sid,
    s.sname 
FROM
    score
    LEFT JOIN student s ON score.student_id = s.sid 
WHERE
    course_id IN (
    SELECT
        cid 
    FROM
        course c
        LEFT JOIN teacher t ON c.teacher_id = t.tid 
    WHERE
        t.tname = '张三' 
    ) 
GROUP BY
    student_id 
HAVING
    COUNT( 1 ) = 4

-- 查询带过超过2个班级的老师的id和姓名；
SELECT
    tr.tid,
    tr.tname 
FROM
    teach2cls ts
    LEFT JOIN teacher tr ON ts.tid = tr.tid 
GROUP BY
    ts.tid 
HAVING
    COUNT( 1 ) > 2

-- 查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名
SELECT
    A.student_id,
    st.sname 
FROM
    (
    SELECT
        student_id,
        score AS sw 
    FROM
        score 
    WHERE
        course_id = 1 
    ) AS A
    LEFT JOIN (
    SELECT
        student_id,
        score AS ty 
    FROM
        score 
    WHERE
        course_id = 2 
    ) AS B ON A.student_id = B.student_id
    LEFT JOIN student st ON A.student_id = st.sid 
WHERE
    sw >
IF
    ( ISNULL( ty ), 0, ty )

-- 查询所带班级数最多的老师id和姓名；
SELECT
    tr.tid,
    tr.tname 
FROM
    teach2cls ts
    LEFT JOIN teacher tr ON ts.tid = tr.tid 
GROUP BY
    ts.tid 
ORDER BY
    COUNT( 1 ) DESC 
    LIMIT 1

-- 查询有课程成绩小于60分的同学的学号、姓名；
SELECT
    st.sid,
    st.sname 
FROM
    score s
    LEFT JOIN student st ON s.student_id = st.sid 
WHERE
    score < 60 
GROUP BY
    st.sid

-- 查询没有学全所有课的同学的学号、姓名；
SELECT
    st.sid,
    st.sname 
FROM
    score se
    LEFT JOIN student st ON se.student_id = st.sid 
GROUP BY
    se.student_id 
HAVING
    COUNT( 1 ) < ( SELECT COUNT( cid ) FROM course )

-- 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
SELECT
    st.sid,
    st.sname 
FROM
    score se
    LEFT JOIN student st ON se.student_id = st.sid 
WHERE
    se.student_id != 1 
    AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) 
GROUP BY
    student_id

-- 查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
SELECT
    st.sid,
    st.sname 
FROM
    score se
    LEFT JOIN student st ON se.student_id = st.sid 
WHERE
    se.student_id != 1 
    AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) 
GROUP BY
    student_id

-- 查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；
SELECT
    st.sid,
    st.sname 
FROM
    score se
    LEFT JOIN student st ON se.student_id = st.sid 
WHERE
    student_id IN (
    SELECT
        student_id 
    FROM
        score 
    WHERE
        student_id != 2 
    GROUP BY
        student_id 
    HAVING
        COUNT( 1 ) = (
        SELECT
            COUNT( 1 ) 
        FROM
            score 
        WHERE
            student_id = 2 
        ) 
    ) 
    AND course_id IN ( SELECT course_id FROM score WHERE student_id = 2 ) 
GROUP BY
    student_id 
HAVING
    COUNT( course_id ) = (
    SELECT
        COUNT( 1 ) 
    FROM
        score 
    WHERE
        student_id = 2 
    )

-- 删除学习“张三”老师课的score表记录
DELETE 
FROM
    score 
WHERE
    course_id IN (
    SELECT
        cid 
    FROM
        course ce
        LEFT JOIN teacher tr ON ce.teacher_id = tr.tid 
    WHERE
        tr.tname = '张三' 
    )

-- 向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
INSERT INTO score ( student_id, course_id, score ) SELECT
sid,
2,
(
    SELECT
        AVG( score ) 
    FROM
        score 
    WHERE
        course_id = 2 
    ) 
FROM
    student 
WHERE
    sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )

-- 按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,课程数和平均分；
SELECT
    sc.student_id,
    (
    SELECT
        score 
    FROM
        score se
        LEFT JOIN course ce ON se.course_id = ce.cid 
    WHERE
        ce.cname = '语文' 
        AND se.student_id = sc.student_id 
    ) AS yw,
    (
    SELECT
        score 
    FROM
        score se
        LEFT JOIN course ce ON se.course_id = ce.cid 
    WHERE
        ce.cname = '数学' 
        AND se.student_id = sc.student_id 
    ) AS sx,
    (
    SELECT
        score 
    FROM
        score se
        LEFT JOIN course ce ON se.course_id = ce.cid 
    WHERE
        ce.cname = '英语' 
        AND se.student_id = sc.student_id 
    ) AS yy,
    COUNT( sc.course_id ),
    AVG( sc.score ) 
FROM
    score AS sc 
GROUP BY
    student_id DESC


-- 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT
    course_id,
    max( score ) AS max_num,
    min( score ) AS min_num 
FROM
    score 
GROUP BY
    course_id;

-- 按各科平均成绩从低到高和及格率的百分数从高到低顺序；
SELECT
    course_id,
    avg( score ) AS avgnum,
    sum(
    CASE

            WHEN score.score > 60 THEN
            1 ELSE 0 
        END 
        ) / count( 1 ) * 100 AS percent 
    FROM
        score 
    GROUP BY
        course_id 
    ORDER BY
        avgnum ASC,
        percent DESC;

-- 课程平均分从高到低显示（现实任课老师）；
SELECT
    tr.tname,
    AVG( score ) 
FROM
    score se
    LEFT JOIN course ce ON se.course_id = ce.cid
    LEFT JOIN teacher tr ON tr.tid = ce.teacher_id 
GROUP BY
    course_id 
ORDER BY
    AVG( score ) DESC

-- 查询各科成绩前三名的记录(不考虑成绩并列情况) ；
SELECT
    score.sid,
    score.course_id,
    score.score,
    T.first_num,
    T.second_num 
FROM
    score
    LEFT JOIN (
    SELECT
        sid,
        (
        SELECT
            score 
        FROM
            score AS s2 
        WHERE
            s2.course_id = s1.course_id 
        ORDER BY
            score DESC 
            LIMIT 0,
            1 
        ) AS first_num,
        (
        SELECT
            score 
        FROM
            score AS s2 
        WHERE
            s2.course_id = s1.course_id 
        ORDER BY
            score DESC 
            LIMIT 3,
            1 
        ) AS second_num 
    FROM
        score AS s1 
    ) AS T ON score.sid = T.sid 
WHERE
    score.score <= T.first_num AND score.score >= T.second_num


-- 查询每门课程被选修的学生数；
SELECT
    course_id,
    COUNT( 1 ) 
FROM
    score 
GROUP BY
    course_id


-- 查询选修了2门以上课程的全部学生的学号和姓名；
SELECT
    st.sid,
    st.sname 
FROM
    score se
    LEFT JOIN student st ON se.student_id = st.sid 
GROUP BY
    student_id 
HAVING
    COUNT( 1 ) > 2

-- 查询男生、女生的人数，按倒序排列；
SELECT
    * 
FROM
    (
    SELECT
        COUNT( 1 ) 
    FROM
        student 
    WHERE
        gender = '男' 
    ) AS A,
    (
    SELECT
        COUNT( 1 ) 
    FROM
        student 
    WHERE
        gender = '女' 
    ) AS B

-- 查询姓“张”的学生名单；
SELECT
    * 
FROM
    student 
WHERE
    sname LIKE '张%'


-- 查询同名同姓学生名单，并统计同名人数；
SELECT
    sname,
    COUNT( 1 ) AS '同名人数' 
FROM
    student 
GROUP BY
    sname

-- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
SELECT
    course_id,
    AVG(
    IF
        ( ISNULL( score ), 0, score ) 
    ) AS avg 
FROM
    score 
GROUP BY
    course_id 
ORDER BY
    avg ASC,
    course_id DESC

-- 查询课程名称为“数学”，且分数低于60的学生姓名和分数；
SELECT
    st.sname,
    se.score 
FROM
    score se
    LEFT JOIN course ce ON se.course_id = ce.cid
    LEFT JOIN student st ON se.student_id = st.sid 
WHERE
    ce.cname = '数学' 
    AND se.score < 60

-- 查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
SELECT
    st.sid,
    st.sname 
FROM
    score se
    LEFT JOIN course ce ON se.course_id = ce.cid
    LEFT JOIN student st ON se.student_id = st.sid 
WHERE
    se.course_id = 3 
    AND se.score < 80

-- 求选修了课程的学生人数
SELECT
    COUNT( 1 ) 
FROM
    ( SELECT student_id FROM score GROUP BY student_id ) AS A

-- 查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
SELECT
    st.sname,
    se.score 
FROM
    score se
    LEFT JOIN student st ON se.student_id = st.sid
    LEFT JOIN course AS ce ON se.course_id = ce.cid
    LEFT JOIN teacher tr ON ce.teacher_id = tr.tid 
WHERE
    tr.tname = '王五' 
ORDER BY
    se.score DESC 
    LIMIT 1 

-- 方法2：
SELECT
    sname,
    score 
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid 
WHERE
    score.course_id IN (
    SELECT
        course.cid 
    FROM
        course
        LEFT JOIN teacher ON course.teacher_id = teacher.tid 
    WHERE
        tname = '王五' 
    ) 
ORDER BY
    score DESC 
    LIMIT 1;

-- 查询各个课程及相应的选修人数；
SELECT
    course_id,
    COUNT( 1 ) 
FROM
    score 
GROUP BY
    course_id

-- 查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
SELECT DISTINCT
    s1.student_id,
    s2.student_id,
    s1.course_id,
    s2.course_id,
    s1.score,
    s2.score 
FROM
    score AS s1,
    score AS s2 
WHERE
    s1.score = s2.score 
    AND s1.course_id != s2.course_id

-- 查询每门课程成绩最好的前两名学生id和姓名；
SELECT
    score.student_id,
    student.sname 
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN (
    SELECT
        sid,
        (
        SELECT
            score 
        FROM
            score AS s2 
        WHERE
            s2.course_id = s1.course_id 
        ORDER BY
            score DESC 
            LIMIT 0,
            1 
        ) AS first_score,
        (
        SELECT
            score 
        FROM
            score AS s2 
        WHERE
            s2.course_id = s1.course_id 
        ORDER BY
            score DESC 
            LIMIT 1,
            1 
        ) AS second_score 
    FROM
        score AS s1 
    ) AS T ON score.sid = T.sid 
WHERE
    score.score <= first_score AND score.score >= T.second_score


-- 检索至少选修两门课程的学生学号；
SELECT
    student_id 
FROM
    score 
GROUP BY
    student_id 
HAVING
    COUNT( 1 ) > 2

-- 查询没有学生选修的课程的课程号和课程名；
SELECT
    cid,
    cname 
FROM
    course 
WHERE
    cid NOT IN ( SELECT course_id FROM score GROUP BY course_id )


-- 查询没带过任何班级的老师id和姓名；
SELECT
    tid,
    tname 
FROM
    teacher 
WHERE
    tid NOT IN ( SELECT tid FROM teach2cls GROUP BY tid )

-- 查询有两门以上课程超过80分的学生id及其平均成绩；
SELECT
    se.student_id,
    AVG( se.score ) 
FROM
    score se 
WHERE
    score > 80 
GROUP BY
    course_id 
HAVING
    COUNT( 1 ) > 2

-- 检索“3”课程分数小于60，按分数降序排列的同学学号；
SELECT
    student_id 
FROM
    score 
WHERE
    course_id = 3 
    AND score < 60 
ORDER BY
    score DESC

-- 删除编号为“2”的同学的“1”课程的成绩；
DELETE 
FROM
    score 
WHERE
    student_id = 2 
    AND course_id = 1

-- 查询同时选修了物理课和生物课的学生id和姓名；
SELECT
    student_id,
    sname,
    course_id 
FROM
    score se
    LEFT JOIN student st ON se.student_id = st.sid 
GROUP BY
    se.student_id 
HAVING
    COUNT( 1 ) > 1 
    AND se.course_id IN (
    SELECT
        cid 
    FROM
        course 
    WHERE
        cname = '物理' 
        OR cname = '生物' 
    )