1.查询姓“李”的老师的个数;
SELECT COUNT(*)
FROM teacher
WHERE tname LIKE '李%';
(不可以加distinct)
2.查询每门课程的课程号和选修的学生数;
SELECT cno, count(*)
FROM sc
GROUP BY cno;
3.查询平均成绩大于60分的同学的学号和平均成绩;
SELECT sno, AVG(score)
FROM sc
GROUP BY sno
HAVING AVG(score)>60;
- 查询所有的同学的学号、选课数、选课总成绩,并按照学号升序排列,然后按选课门数降序排;
SELECT sno,count(sno) 选课数, sum(score) 选课总成绩
FROM sc
GROUP BY sno
ORDER BY sno, count(*) desc;
5.查询课程名中第3个字符是下横线“_”的课程信息;
SELECT *
FROM course
WHERE cname like ‘__&_%’
ESCAPE '&';
6.查询各科成绩最高和最低的分: 以如下的形式显示:课程号,最高分,最低分;
select cno 课程号, max(score) 最高分, min(score) 最低分
from sc
group by cno;