土豆烤肉_数据库作业1（查询）

SELECT * FROM courses03;
/*T3查询课程表Courses中，所有课程的详细记录*/

SELECT  Sno AS "学号", Sname AS "姓名", Sbirth AS "出生日期", LEFT(Sno, 4) AS "入学年份"
FROM students03
LIMIT 6;
/*T4查询表中前6位学生的学号、姓名、出生日期以及入学年份，查询结果用中文显示列名*/

SELECT DISTINCT Sclass
FROM tutors03
WHERE Tno = 'T009';
/*T5查询授课表Tutor中，教师编号为T009的教师都给哪些班级讲授过课程*/

SELECT DISTINCT Sno
FROM report03
WHERE Grade < 60;
/*T6查询考试成绩有不及格的学生的学号*/

SELECT *
FROM tutors03
WHERE Tno = 'T003'
AND Tacademicyear = 2015
AND Tterm = 1;
/*T7查询教师T003在2015学年第1学期的授课情况*/

SELECT Cno, Cname
FROM courses03
WHERE Pre_Con IN ('112p0015', '112p0055');
/*T8查询前序课程编号是'112p0015'或'112p0055'的课程编号和课程名*/

SELECT Tname AS "姓名", Tprof AS "职称", Tsal AS "工资"
FROM teacher03
WHERE Tsal > 3000
ORDER BY Tsal ASC;
/*T9查询工资(Tsal)在3000元以上的教师的姓名、职称和工资，查询结果按照工资升序排序*/

SELECT Sno AS '学号', Sname AS '姓名', Dno AS '院号', Mno AS '专业号'
FROM students03
WHERE Dno IN (11, 12)
ORDER BY Dno ASC, Mno DESC, CONVERT(Sname USING gbk) ASC;
/*T10查询院号为11或12的学生的学号、姓名、院号和专业号，结果按院号升序、专业号降序、姓名的汉语拼音升序排序*/

SELECT *
FROM courses03
WHERE courses03.Cname LIKE '%计算机%' OR courses03.Cname LIKE '%原理%';
/*T11查询课程名中包含了'计算机'或'原理'的课程的所有信息*/

SELECT Sno, Sname, Sbirth
FROM students03
WHERE SUBSTRING(Sname, 2, 1) = '一' AND Sgender = '男'
ORDER BY Sno DESC;
/*T12查询名字中第二个字为“一”的男学生的学号、姓名和出生年份，查询结果按学号降序排序*/

SELECT Sno AS '学号', Sname AS '姓名', Snative AS '生源地'
FROM students03
WHERE Snative NOT IN ('广东潮州', '山东');
/*T13 查询生源地既不在“广东潮州”,也不在“山东”的学生的学号、姓名及生源地*/

SELECT Cno AS '课程编号', Grade AS '成绩'
FROM report03
WHERE Sno = '2014112103' AND Racademicyear = 2015
ORDER BY Grade DESC;
/*T14查询学号为'2014112103'的学生在2015学年选修的各门课程的课程编号及成绩，查询结果按成绩降序排序*/



SELECT
    students03.Sclass AS "班级编号",/*学生表中的班级编号*/
    teacher03.Tno AS "教师编号",/*教师表中的教师编号*/
    courses03.Cno AS "课程号",/*课程表中的课程编号*/
    report03.Racademicyear AS "学年",/*报告表中的学年*/
    report03.Rterm AS "学期"/*报告表中的学期*/
FROM
    students03
JOIN
    report03 ON students03.Sno = report03.Sno/*学生表与报告表通过学生编号进行连接*/
JOIN
    courses03 ON report03.Cno = courses03.Cno/*报告表与课程表通过课程编号进行连接*/
JOIN
    tutors03 ON courses03.Cno = tutors03.Cno/*课程表和授课表通过课程编号进行连接*/
JOIN
    teacher03 ON tutors03.Tno = teacher03.Tno/*课程表与教师表通过教师编号进行连接*/
WHERE
    students03.Sclass LIKE '2014%'/*筛选出2014级的学生*/
  AND report03.Racademicyear = '2015'  -- 筛选出大二学生
/*T15查询2014级的学生在读大二时，都有哪些教师给他们上过课，结果显示班级编号、教师编号、课程号、学年和学期，并用中文显示属性列名”*/




SELECT
    courses03.Cno AS "课程编号",/*课程表中的课程编号*/
    COUNT(DISTINCT report03.Sno) AS '选课人数',
    ROUND(AVG(report03.Grade), 1) AS '平均分',
    MAX(report03.Grade) AS '最高分',
    MIN(report03.Grade) AS '最低分'
FROM
    report03
JOIN
    courses03 ON report03.Cno = courses03.Cno/*报告表与课程表通过课程编号进行连接*/
GROUP BY
    report03.Cno
ORDER BY
    report03.Cno DESC;
/*T16查询各门课程(Cno)的选课情况，结果显示课程编号、选课人数、平均分、最高分和最低分，
  查询结果按课程编号降序排序，并用中文显示属性列名，平均分保留小数点后1位*/


SELECT
    Cno AS "课程编号",
    COUNT(Sno) AS "选修人数",
    ROUND(AVG(Grade), 1) AS "平均成绩"
FROM
    report03
WHERE
    report03.Sno LIKE '201411%'
   OR report03.Sno LIKE '201511%'
   OR report03.Sno LIKE '201611%'
   OR report03.Sno LIKE '201711%'
GROUP BY
    Cno
HAVING
    COUNT(DISTINCT Sno) >= 5;
/*T17查询至少有5个'11'学院的学生选修的课程的课程编号、选修的人数和平均成绩，
  选修人数和平均成绩只针对'11'学院的学生进行统计，并用中文显示属性列名，平均成绩保留小数点后1位
 */


SELECT
    Sno AS "学号",
    Racademicyear AS "学年",
    ROUND(AVG(Grade), 1) AS "课程平均分"
FROM
    report03
WHERE
    Sno LIKE '20141121%'
GROUP BY
    Sno, Racademicyear
ORDER BY
    Sno ASC, Racademicyear ASC;
/*T18查询20141121班中各位学生(Sno)每个学年所修课程的平均分，结果显示学生的学号、学 年、课程平均分，
  结果按学号、学年升序排序，并用中文显示属性列名，平均分保留小数点后1位*/


SELECT
    Tno AS "教师编号",
    Tacademicyear AS "学年",
    COUNT(DISTINCT Cno) AS "所上课程门数"
FROM
    tutors03
GROUP BY
    Tno, Tacademicyear
ORDER BY
    Tno ASC
LIMIT 5 OFFSET 9;
 /*T19查询各位老师(Tno)每个学年上的课程(Cno)门数，结果显示教师编号、学年、所上课程门数,按教师编号升序排序，
    并用中文显示属性列名，分页显示第10条开始的后5条结果*/


    SELECT
    Tno AS "教师编号",
    Tacademicyear AS "学年",
    COUNT(DISTINCT Cno) AS "所上课程门数"
FROM
    tutors03
WHERE
    Tacademicyear = '2017'
GROUP BY
    Tno, Tacademicyear
HAVING
    COUNT(DISTINCT Cno) >= 2
ORDER BY
    Tno ASC;
 /*T20查询2017学年至少上了两门课程的教师信息，结果显示教师编号、学年、所上课程门数，按教师编号升序排序，并用中文显示属性列名*/


SELECT
    Cno AS "课程编号",
    COUNT(Tno) AS "授课人数"
FROM
    tutors03
GROUP BY
    Cno
HAVING
    COUNT(Tno) >= 3
ORDER BY
    COUNT(Tno) DESC;
 /*T21查询至少有3位老师授课的课程，结果显示课程编号和授课人数，按授课人数降序排序，并用中文显示属性列名*/

SELECT
    Sno AS "学号",
    Cno AS "课程号",
    CASE
        WHEN Grade >= 90 THEN '优'
        WHEN Grade >= 80 THEN '良'
        WHEN Grade >= 70 THEN '中'
        WHEN Grade >= 60 THEN '及格'
        ELSE '不及格'
    END AS "成绩等级"
FROM
    report03
WHERE
    Cno = '112p0024' AND Grade IS NOT NULL/*不包括成绩为空的情况*/
ORDER BY
    Grade DESC;

 /*T22查询所有选修了课程号为112p0024并且有成绩的学生的考试情况，
   结果要求显示学生的学号、课程号和成绩等级('优'、'良'、'中、'及格'和'不及格)，
   并按成绩降序排序(提示：CASE WHEN)*/

   SELECT
    Sno AS "学号",
    Cno AS "课程号",
    CASE
        WHEN Grade >= 90 THEN '优'
        WHEN Grade >= 80 THEN '良'
        WHEN Grade >= 70 THEN '中'
        WHEN Grade >= 60 THEN '及格'
        ELSE '不及格'
    END AS "成绩等级"
FROM
    report03
WHERE
    Cno = '112p0024' /*包括成绩为空的情况*/
ORDER BY
    Grade DESC;