数据库实验2

1.查询表中前6位学生的学号、姓名、出生日期以及入学年份，查询结果用中文显示列名

SELECT Sno AS "学号", Sname AS "姓名", Sbirth AS "出生日期", LEFT(Sno,4) AS "入学年份" 

FROM Students

LIMIT 6;

2.查询授课表Tutor中，教师编号为T009的教师都给哪些班级讲授过课程

SELECT DISTINCT(去重) Tno, Sclass
FROM Tutors

WHERE Tno='T009';

3.查询院号为11或12的学生的学号、姓名、院号和专业号，结果按院号升序、专业号降序、姓名的汉语拼音升序排序”

SELECT Sno,Sname,Dno,Mno FROM Students
WHERE Dno IN ('11','12')
ORDER BY Dno,Mno DESC,Sname ASC;

4.查询课程名中包含了'计算机'或'原理'的课程的所有信息

SELECT *
FROM Courses
WHERE Cname LIKE '%计算机%' OR Cname LIKE '%原理%';

5.查询名字中第二个字为“一”的男学生的学号、姓名和出生年份，查询结果按学号降序排序”

SELECT Sno,Sname,YEAR(Sbirth)  AS "BornYear"

FROM Students
WHERE Sname LIKE '_一%' AND Sgender='男'

ORDER BY Sno DESC;

6.查询生源地既不在“广东潮州”,也不在“山东”的学生的学号、姓名及生源地

SELECT Sno,Sname,Snative 

FROM Students

WHERE Snative <>'广东潮州' AND Snative NOT LIKE '山东%';

7.查询各门课程(Cno)的选课情况，结果显示课程编号、选课人数、平均分、最高分和最低分，查询结果按课程编号降序排序，并用中文显示属性列名，平均分保留小数点后1位”

SELECT Cno AS "课程编号",COUNT(*) AS "选课人数", FORMAT(AVG(Grade),1) AS "平均分", MAX(Grade) AS "最高分", MIN(Grade) AS "最低分"
FROM Reports 

GROUP BY Cno
ORDER BY Cno DESC;

8.查询至少有5个'11'学院的学生选修的课程的课程编号、选修的人数和平均成绩，选修人数和平均成绩只针对'11'学院的学生进行统计，并用中文显示属性列名，平均成绩保留小数点后1位”

SELECT Cno AS "课程编号", COUNT(*) AS "选课人数", AVG(Grade) AS "平均成绩" 

FROM Reports
WHERE SUBSTRING(Sno,5,2)='11' 

GROUP BY Cno
HAVING COUNT(*)>=5;

9.查询各位老师(Tno)每个学年上的课程(Cno)门数，结果显示教师编号、学年、所上课程门数,按教师编号升序排序，并用中文显示属性列名，分页显示第10条开始的后5条结果

SELECT Tno AS "教师编号",Tacademicyear AS "学年",COUNT(DISTINCT Cno) AS "授课门数" 

FROM Tutors
GROUP BY Tno,Tacademicyear 

ORDER BY Tno

LIMIT 9,5;

10.查询至少有3位老师授课的课程，结果显示课程编号和授课人数，按授课人数降序排序，并用中文显示属性列名”

SELECT Cno AS "课程编号", COUNT(DISTINCT Tno) AS "授课人数"

FROM Tutors

GROUP BY Cno

HAVING COUNT(DISTINCT Tno)>=3

ORDER BY COUNT(DISTINCT Tno) DESC;

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

SELECT Sno AS "学号",Cno AS "课程编号",

(CASE WHEN Grade>=90 THEN '优'

           WHEN Grade BETWEEN 80 AND 89 THEN '良'

           WHEN Grade BETWEEN 70 AND 79 THEN '中'

           WHEN Grade BETWEEN 60 AND 69 THEN '及格'

           ELSE '不及格'

END) AS "成绩等级"

FROM Reports

WHERE Cno='112p0024' AND Grade IS NOT NULL

ORDER BY Grade DESC;