数据库原理实验2解答
（1）查询性别为“男”的所有学生的名称并按学号升序排列。
SELECT Sname
FROM Students
WHERE Ssex='男'
ORDER BY Sno
（2）查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。积分的计算公式为：[1+(考试成绩-60)*]*Ccredit。考试成绩>=60 否则=0
SELECT Sno, Tno, , Score, 'Point of Score', CONVERT(FLOAT(1),
(Score-60)**Ccredit+Ccredit)
FROM Courses, Reports
WHERE Score>=60 AND =
UNION
SELECT Sno, Tno, , Score, 'Point of Score', 0
FROM Courses, Reports
WHERE = AND (Score < 60 OR Score IS NULL)
（3）查询学分是3或4的课程的名称。
SELECT Cname
FROM Courses
WHERE Ccredit IN('3','4')
（4）查询所有课程名称中含有“算法”的课程编号。
SELECT Cname
FROM Courses
WHERE Cname LIKE '%算法%'
/*查询得到算法分析与设计、数据结构与算法分析*/
（5）查询所有选课记录的课程号（不重复显示）。
SELECT DISTINCT Cno FROM Reports
（6）统计所有老师的平均工资。
SELECT AVG(Tsalary) FROM Teachers
（7）查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。
SELECT Tno,AVG(Score)
FROM Reports
GROUP BY Tno
ORDER BY AVG(Score) DESC
（8）统计各个课程的选课人数和平均成绩。
SELECT Cno,COUNT(Sno),AVG(Score)
FROM Reports
GROUP BY Cno
（9）查询至少选修了三门课程的学生编号和姓名。
SELECT Sno,Sname
FROM Students
WHERE Sno IN
(
SELECT Sno
FROM Reports
GROUP BY Sno
HAVING COUNT(*)>=3
)
（10）查询编号S26的学生所选的全部课程的课程名和成绩。
SELECT ,
FROM Courses,Reports
WHERE ='S26' AND =
（11）查询所有选了“数据库原理及其应用”课程的学生编号和姓名。
SELECT Sno,Sname
FROM Students
WHERE Sno IN
(
SELECT
FROM Reports,Courses
WHERE = AND ='数据库原理及其应用'
)
（12）求出至少被两名学生选修的课程编号。
SELECT DISTINCT
FROM Reports X,Reports Y
WHERE = AND <
（13）查询选修了编号S26的学生所选的某个课程的学生编号。
SELECT
FROM Reports X,Reports Y
WHERE = AND ='S26'
/*注：在本题中，如果要求是“查询选修了编号S26的学生所选的某个课程的其他学生编号”时，也就是不包含编号S26学生自身的情况时，在查询条件WHERE中，还需要加上条件<*/
（14）查询学生的基本信息及选修课程编号和成绩。
SELECT ,,,,,,
FROM Students JOIN Reports ON =
（15）查询学号S52的学生的姓名和选修的课程名称及成绩。
SELECT ,,
FROM Students,Courses,Reports
WHERE = AND = AND ='S52'
（16）查询和学号S52的学生同性别的所有学生资料。
SELECT *
FROM Students
WHERE Ssex=(
SELECT Ssex
FROM Students
WHERE Sno='S52'
)
（17）查询所有选课的学生的详细信息。
SELECT *
FROM Students
WHERE Sno IN
(
SELECT Sno
FROM Reports
)
（18）查询没有学生选的课程的编号和名称。
SELECT Cno,Cname
FROM Courses
WHERE Cno NOT IN
(
SELECT Cno
FROM Reports
)
（19）查询选修了课程名为C++的学生学号和姓名。
SELECT Sno,Sname
FROM Students
WHERE Sno IN
(
SELECT Sno
FROM Reports
WHERE Cno IN
(
SELECT Cno
FROM Courses
WHERE Cname='C++'
)
)
（20）找出选修课程UML或者课程C++的学生学号和姓名。
SELECT Sno,Sname
FROM Students
WHERE Sno IN
SELECT Sno
FROM Reports
WHERE Cno IN
(
SELECT Cno
FROM Courses
WHERE Cname='C++' OR Cname='UML'
)
)
（21）找出和课程UML或课程C++的学分一样课程名称。
SELECT Cname
FROM Courses
WHERE Ccredit=SOME
(
SELECT Ccredit
FROM Courses
WHERE Cname='UML' OR Cname='C++'
)
/*注：在本题中，将=SOME换成=ANY或IN都可行。*/（22）查询所有选修编号C01的课程的学生的姓名。
SELECT Sname
FROM Students
WHERE EXISTS
(
FROM Reports
WHERE ='C01' AND =
)
（23）查询选修了所有课程的学生姓名。
SELECT Sname
FROM Students
WHERE NOT EXISTS
(
SELECT *
FROM Reports X
WHERE NOT EXISTS
(
SELECT *
FROM Reports Y
WHERE = AND =
)
)
（24）利用集合查询方式，查询选修课程C++或选择课程JAVA的学生的编号、姓名和积分。
SELECT Sno,Sname,Scredit
FROM Students
WHERE Sno IN
(
SELECT Sno
FROM Reports
WHERE =
(
SELECT
FROM Courses
WHERE ='C++'
)
)
UNION
SELECT Sno,Sname,Scredit
FROM Students
WHERE Sno IN
(
SELECT Sno
FROM Reports
WHERE =
(
SELECT
FROM Courses
WHERE ='JAVA'
)
)
（25）实现集合交运算，查询既选修课程C++又选修课程JAVA的学生的编号、姓名和积分。
SELECT Sno,Sname,Scredit
FROM Students
WHERE Sno IN
(
SELECT
FROM Reports X,Reports Y
WHERE=(
SELECT Cno
FROM Courses
WHERE Cname='C++'
)AND =(
SELECT Cno
FROM Courses
WHERE Cname='JAVA'
)
)AND =
)
（26）实现集合减运算，查询选修课程C++而没有选修课程JAVA的学生的编号。
基本的语句模式如下：
SELECT Sno FROM Reports
WHERE Cno='C01'
AND Sno NOT IN(SELECT Sno FROM Reports WHERE Cno='C03')本题具体的SQL语句
SELECT * FROM Students
WHERE Sno IN
( SELECT Sno FROM Reports
WHERE Cno=(SELECT Cno FROM Courses WHERE Cname='C++')
AND Sno NOT IN (SELECT Sno FROM Reports WHERE
Cno=(SELECT Cno FROM Courses WHERE Cname='JAVA'))) (28)求平均成绩在75分以上的课程名
select ,avg(Score) as pjcj
from COURSE, Reports
where =
group by
having avg(Score)>75
(29) 检索“张三”同学不学的课程的课程号
SELECT distinct cno
from Courses
where cno not in (select
from Students, Reports
where =
and ='张三' )