SELECT SNo,Score FROM SC
WHERE CNo=’00001’AND Score IS NOT NULL ORDER BY Score DESC
3查询职工的年薪，并按年薪的升序排列 ○
SELECT TName,sal*12
FROM Teacher ORDER BY 2
4查询所有职工信息，按部门号的降序排列，在部门相同的情况下，按年龄的升序排列 ○
SELECT *
FROM Teacher
ORDER BY DNo,DESC,age (4)聚合函数的应用
1统计职工的工资总额 ○
SELECT SUM(sal) FROM Teacher
2统计职工的工资总额，并显示每个职工的工资情况 ○
SELECT TNo,sal FROM Teacher
ORDER BY TNo COMPUTE SUM(sal)
3统计职工的工资总额，显示每个职工的工资情况及各个部门的工资总额 ○
SELECT TNo,sal FROM Teacher
ORDER BY DNo,TNo COMPUTE SUM(sal) BY DNo 4求职工的最高工资、最低工资和平均工资 ○
SELECT MAX(sal),MIN(sal),AVG(sal) FROM Teacher 5求女学生的学生总数 ○
SELECT COUNT(*) FROM Student WHERE SSex=’女’
6查询有多少名学生的物理课成绩不及格 ○
SELECT COUNT(SNo) FROM SC,Course
WHERE CName=’物理’AND Course.CNO=SC.CNo AND Score<60
(5)聚合操作与分组统计的应用
1求每个班的学生数，希望得到下面的输出 ○
CLno COUNT(*) 060001 40 060002 35 ? ?
SELECT CLno,COUNT(*) FROM Student GROUP BY CLno
2查询每个学生已获得的学分（成绩及格即取得相应课程的学分） ○
SELECT SNo,SUM(CCredits) FROM SC,Course
WHERE Score>60 AND SC.CNo=Course.CNo GROUP BY SNo
3使用HAVING子句，查询学分大于30的学生，输出学生的学号和学分 ○
SELECT SNo,SUM(CCredits) FROM SC,Course
WHERE Score>60 AND SC.CNo=Course.CNo GROUP BY SNo
HAVING SUM(CCredits)>30