1.查询姓“李”的老师的个数;
Select count(*) from teacher where tname like ‘李%’;
(不可以加distinct)
2.查询每门课程的课程号和选修的学生数;
Select cno,count(*) from course group by cno;
3.查询平均成绩大于60分的同学的学号和平均成绩;
Select sno ,avg(score) from course 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 min(score),max(score) from sc group by cno;