SELECT S.Sno, Sname, M.Mname, R.Racademicyear, Rterm, C.Cno, Cname, R.Grade
FROM students03 S,major03 M, courses03 C,report03 R
WHERE S.Sno =R.Sno
AND M.Mno = S.Mno
AND R.Cno = C.Cno
AND Sclass = '20161151'
AND R.Racademicyear = '2016'
ORDER BY S.Sno ASC, R.Racademicyear ASC, R.Rterm ASC;
/*T2请写出下列查询操作的SQL语句："查询20161151班的学生在大学一年级选修的课程情况，
查询结果要显示学号(Sno)、姓名(Sname)、专业名(Mname)、学年(Racademicyear)、
学期(Rterm)、选课的课程号(Cno)、选课的课程名称(Cname)及成绩（Grade)，并按照学号、学年、学期升序排序"*/
SELECT T.Tno,T.Tname, O.Tacademicyear, O.Tterm, O.Cno, O.Sclass,C.Cname
FROM teacher03 T,tutors03 O,courses03 C
WHERE T.Tname = '张雪'
AND T.Tno = O.Tno
AND O.Cno = C.Cno
ORDER BY O.Tacademicyear ASC, O.Tterm ASC;
/*T3请写出下列查询操作的SQL语句："查询教师张雪的授课信息，查询结果显示教师编号(Tno)、姓名(Tname）、
授课学年(Tacademicyear)、授课学期(Tterm)、授课的课程编号(Cno)、课程名(Cname)以及授课班级
(Sclass)，结果按授课学年、授课学期升序排序*/
SELECT M.Mno AS "专业号", M.Mname AS "专业名称", D.Dname,COUNT(S.Sno) AS "学生人数"
FROM major03 M,department03 D,students03 S
WHERE D.Dname = '计算机与信息工程学院'
AND M.Mno = S.Mno
AND M.Dno = D.Dno
GROUP BY M.Mno, M.Mname
ORDER BY M.Mno ASC;
/*T4请写出下列查询操作的SQL语句："查询计算机与信息工程学院的各个专业的学生数，查询结果显示专业号(Mno)、
专业名称(Mname）、学生人数，按专业号升序排序，并用中文名显示属性列"*/
SELECT A.Tno AS "教师编号", A.Tname AS "姓名", (A.Tsal + A.Tcomm) AS "总工资"
FROM teacher03 A
JOIN teacher03 B ON A.Dno = B.Dno
WHERE B.Tname = '王一凡' AND (A.Tsal + A.Tcomm) > (B.Tsal + B.Tcomm);
/*T5请写出下列查询操作的SQL语句："用自身连接查询和'王一凡'在同一个学院，且总工资（基本工资Tsal+岗位津贴Tcomm）
比'王一凡'高的教师的信息，结果显示教师编号(Tno)、姓名(Tname)和总工资，并用中文显示属性列名"
*/
SELECT C1.Cno, C1.Cname, C1.Ccredit
FROM courses03 C1
INNER JOIN courses03 C2 ON C1.Pre_Cno = C2.Cno
WHERE C2.Cname = '数据结构';
/*T6请写出下列查询操作的SQL语句："用自身连接查询前序课程是数据结构的课程的信息，查询结果显示课程编号（Cno）、
课程名称（Cname）和学分（Ccredit）"
*/
SELECT DISTINCT A.Tno AS "教师编号", T.Tname AS "教师姓名"
FROM tutors03 A
JOIN teacher03 T ON T.Tno = A.Tno
JOIN tutors03 T1 ON T1.Tno = A.Tno AND T1.sclass = '20161121'
JOIN tutors03 T2 ON T2.Tno = A.Tno AND T2.sclass = '20161151';
/*T7请写出下列查询操作的SQL语句："用自身连接查询既给20161121班上过课，也给20161151班上过课的教师信息，
查询结果显示教师编号(Tno)、教师姓名(Tname)"
*/
SELECT C1.Cname AS "课程名称", C1.Cno AS "课程编号", R.Racademicyear AS "选课学年",
R.Rterm AS "选课学期", COUNT(R.Sno) AS "选课人数"
FROM courses03 C1
INNER JOIN report03 R ON C1.Cno = R.Cno
GROUP BY C1.Cname, C1.Cno, R.Racademicyear, R.Rterm
ORDER BY C1.Cno, R.Racademicyear, R.Rterm;
/*T8请写出下列查询操作的SQL语句："查询课程表中的所有课程在每个学期的选课情况，
查询结果以中文名显示课程编号(Cno)、课程名称(Cname)、选课学年(Racademicyear)、
选课学期(Rterm)及该学年该学期的选课人数(SelNumber)，按课程编号、选课学年、选课学期升序排序" */
SELECT T.Tno AS "教师编号",T.Tname AS "教师姓名",O.Tacademicyear AS"授课学年",O.Tterm AS"授课学期",
C.Cno AS"课程编号",C.Cname AS"课程名称"
FROM teacher03 T
JOIN department03 d on T.Dno = d.Dno
JOIN tutors03 O ON T.Tno = O.Tno
JOIN courses03 C ON C.Cno =O.Cno
WHERE D.Dname ='数学与统计学院'
ORDER BY T.Tno, O.Tacademicyear, O.Tterm;
/*T9请写出下列查询操作的SQL语句："查询教师表中的每一位数学与统计学院的教师的授课课情况，
查询结果显示教师编号(Tno)、教师姓名(Tname)、授课学年(Tacademicyear)、授课学期(Tterm)、
课程编号(Cno)及课程名称(Cname)，按教师编号、开课学年、授课学期升序排序"
*/
SELECT Sno AS "学号", Sname AS "姓名", Sclass AS "所在班级"
FROM students03
WHERE Sno IN (
SELECT Sno
FROM report03
WHERE Grade < 60
);
/*T10请写出下列查询操作的SQL语句："使用谓词IN嵌套查询，查询有课程成绩不及格的学生的信息，
结果显示学号(Sno)、姓名(Sname)及所在班级(Sclass)"*/
SELECT Tno AS "教师编号", Tname AS "教师姓名", Tprof AS "职称"
FROM teacher03
WHERE Tno IN (
SELECT Tno
FROM tutors03
WHERE Tacademicyear = '2014'
);
/*T11请写出下列查询操作的SQL语句："使用谓词IN嵌套查询，查询在2014学年有授课的教师的信息，
结果显示教师编号(Tno)、教师姓名(Tname)及职称(Tprof)"
*/
SELECT Tno AS "教师编号",Tname AS "教师姓名",Tprof AS "职称",(Tsal + Tcomm) AS "总工资"
FROM teacher03
WHERE Dno IN (
SELECT Dno
FROM department03
WHERE dname = '计算机与信息工程学院'
)
AND (Tsal + Tcomm) > (
SELECT AVG(Tsal + Tcomm)
FROM teacher03
WHERE Dno IN (
SELECT Dno
FROM department03
WHERE dname = '计算机与信息工程学院'
)
);
/*T12请写出下列查询操作的SQL语句："使用嵌套查询，查询计算机与信息工程学院中总工资高于本学院的平均总工资
的教师信息，结果以中文名称显示教师编号(Tno)、教师姓名(Tname)、职称（Tprof）及总工资"
*/
SELECT
T.Tno AS "教师编号",
T.Tname AS "教师姓名",
D.dname AS "学院名称",
T.Tprof AS "职称",
(Tsal + Tcomm) AS "总工资"
FROM
teacher03 T
JOIN
department03 D ON T.Dno = D.Dno
WHERE
(Tsal + Tcomm) > (
SELECT AVG(Tsal + Tcomm)
FROM teacher03
);
/*T13请写出下列查询操作的SQL语句："使用嵌套查询，查询总工资高于所有学院的平均总工资的教师信息，
结果显示教师编号(Tno)、教师姓名(Tname)、学院名称(Dname)、职称(Tprof)及总工资，并用中文显示属性列名"
*/
SELECT S.Sno AS "学生学号",S.Sname AS "姓名",SUM(C.Ccredit) AS "已获得的总学分"
FROM students03 S
JOIN report03 R ON S.Sno = R.Sno
JOIN courses03 C ON R.Cno = C.Cno
JOIN department03 D on S.Dno = D.Dno
WHERE
D.Dname = '计算机与信息工程学院' AND
R.Grade >= 60
GROUP BY S.Sno, S.Sname
HAVING
SUM(C.Ccredit) > (
SELECT MAX(ccredit_sum)
FROM (
SELECT SUM(C.Ccredit) AS ccredit_sum
FROM students03 S2
JOIN report03 R2 ON S2.Sno = R2.Sno
JOIN courses03 C2 ON R2.Cno = C2.Cno
WHERE S2.Sno LIKE '2014%'
AND R2.Grade >= 60
GROUP BY S2.Sno
)AS max_ccredit
);
/*T14请写出下列查询操作的SQL语句："使用嵌套查询，查询计算机与信息工程学院中已获学分高于所有2014级学生获得的学分
的学生学号(Sno)、姓名(Sname)及已获得的总学分，并用中文显示属性列名"
（提示：① 已获学分是指已有成绩且成绩大于等于60分的课程的学分；
② 先统计出计算机与信息工程学院的每位学生获得的学分；
③ 统计的每位学生获得的学分，再筛选出满足条件的学生）
*/
SELECT S.Sno AS "学生学号", S.Sname AS "姓名", SUM(C.Ccredit) AS "已获得的总学分"
FROM students03 S
JOIN report03 R ON S.Sno = R.Sno
JOIN courses03 C ON R.Cno = C.Cno
JOIN department03 D ON S.Dno = D.Dno
WHERE
D.Dname = '计算机与信息工程学院' AND
R.Grade >= 60
GROUP BY S.Sno, S.Sname;
SELECT S.Sno AS"学号",Sname AS "姓名",SUM(Ccredit) AS"已获得的总学分"
FROM students03 AS S,report03 AS R,courses03 AS C
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Grade>=60
GROUP BY S.Sno
HAVING SUM(Ccredit)>ALL
(SELECT SUM(Ccredit)
FROM courses03 AS C,
report03 AS R
WHERE LEFT(Sno,4)='2014' AND Grade>=60 AND C.Cno=R.Cno
AND SUBSTRING(Sno,5,2)IN
(SELECT Dno
FROM department03
WHERE Dname='计算机与信息工程学院')
GROUP BY R.Sno
);
SELECT Tno AS "教师编号",Tname AS "姓名",Tprof AS "职称"
FROM teacher03 T
WHERE
NOT EXISTS (
SELECT 1
FROM tutors03 C
WHERE C.Tno = T.Tno
AND C.Cno = '112p0054'
)
AND
T.Dno IN (
SELECT Dno
FROM department03 D
WHERE D.dname = '计算机与信息工程学院'
);
/*T15请写出下列查询操作的SQL语句："使用EXISTS谓词查询计算机与信息工程学院中没有讲授过课程号为112p0054课程
的教师编号(Tno)、姓名(Tname)和职称(Tprof)"*/
SELECT Sno, Sname
FROM students03 s
WHERE EXISTS (
SELECT 1
FROM report03 R
WHERE R.Sno = s.Sno
GROUP BY R.Sno
HAVING COUNT(DISTINCT R.Cno) >= (
SELECT COUNT(DISTINCT Cno)
FROM report03
WHERE Sno = '2014112104'
)
);
/*T16请写出下列查询操作的SQL语句："使用EXISTS谓语查询至少选修了学生2014112104选修的全部课程
的学生的学号(Sno)、姓名(Sname)"*/
SELECT
C.Cno AS "课程编号",
C.Cname AS "课程名称",
COALESCE(COUNT(R.Sno), '尚无人选') AS "选课人数",
CASE
WHEN COUNT(R.Sno) = 0 THEN NULL
WHEN AVG(R.Grade) IS NULL THEN '尚无成绩'
ELSE FORMAT(AVG(R.Grade), 2)
END AS "平均分",
FORMAT(MAX(R.Grade), 2) AS "最高分",
FORMAT(MIN(R.Grade), 2) AS "最低分"
FROM
courses03 C
LEFT JOIN
report03 R ON C.Cno = R.Cno
GROUP BY
C.Cno, C.Cname
ORDER BY
C.Cno;
/*T17请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询课程表中每一门课程的选课情况，
查询结果要显示课程编号(Cno)、课程名称(Cname)、选课人数、平均分、最高分、最低分。
若某门课程还没有被选，则要选课人数显示为‘尚无人选’,平均分等显示为NULL；若某门课程有人选，
但平均分等结果为NULL，则显示为’尚无成绩’；非空的数据结果保留两位小数。结果按课程编号升序排序，
并用中文显示属性列名"
提示：① 本查询会使用到COALESCE()函数、FORMAT()函数；② 本查询需要使用CASE WHEN语句；③先统计出每门课程的选修情况，再与课程表建立左连接
*/
SELECT
S.Sclass AS '所在班级',
S.Sno AS '学号',
S.Sname AS '姓名',
O.Cno AS '选修的课程编号',
C.Cname AS '课程名',
T.Tname AS '授课的教师'
FROM
students03 S
JOIN
tutors03 O ON S.Sclass = O.Sclass
JOIN
courses03 C ON O.Cno = C.Cno
JOIN
teacher03 T ON O.Tno = T.Tno
WHERE
C.Cname = '计算机科学概论' AND O.Tacademicyear = '2015' AND O.Tterm = '1' AND T.Tname = '刘伟';
/*T18请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询在2015学年的第1学期，
选修了刘伟老师上的计算机科学概论课程的学生的基本信息，查询结果显示所在班级(Sclass)、
学号(Sno)、姓名(Sname）、选修的课程编号(Cno)、课程名(Cname)以及授课的教师(Tname)"
*/
SELECT
R.Grade AS '所在年级',
S.Sno AS '学号',
S.Sname AS '姓名',
R.Grade AS '成绩'
FROM students03 S
JOIN report03 R ON S.Sno = R.Sno
JOIN courses03 c on R.Cno = C.Cno
WHERE
C.Cname = '数据结构' AND R.Grade >= (
SELECT AVG(Grade)
FROM report03 R2
JOIN
students03 S2 ON R2.Sno = S2.Sno
WHERE
C.Cname = '数据结构'
AND
R2.Racademicyear = R.Racademicyear
);
/*T19 请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询选修了数据结构
562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
