PTA——连接查询——Mysql——（21-30）

10-21 查询每门必修课的课程编号，课程名称和选修人数。

select course.cno,cname,count(distinct sno) total
from course join score on course.cno=score.cno
where course.attribute='必修'
group by course.cno;

10-22 查找所有“大学语文”分数在80以上的学生的姓名，所在院系。

select sname,dept
from student join score on student.sno=score.sno
where cno='302566' and grade>=80

10-23 显示每位员工的编号，姓名，工资及工资级别。

select empno,ename,sal,grade
from emp,salgrade
where emp.sal between losal and hisal;

10-24 显示每位员工的编号，姓名及其上级领导的编号和姓名（要求显示出所有的员工）。

select a.empno 员工编号,a.ename 员工姓名,a.mgr 上司编号,b.ename 上司姓名 
from emp a left 
join emp b on(a.mgr=b.empno);

10-25 查询编号‘dep01001’教师的系主任名称 (10 分)

select DepartmentHeader 
from Teacher join Department on Teacher.DepartmentID=Department.DepartmentID
where TeacherID='dep01001' 

10-26 查询没有课程成绩的学生学号、姓名、性别。 (10 分)

select student.StudentID,StudentName,Sex
from Student left join Grade on  student.StudentID=Grade.StudentID
where grade is null;

10-27 查询出学生的选课情况，结果集包括学号、姓名、课号、课名、开设学期和成绩。 (10 分)

select student.sno,sname,course.cno,cname,term,grade
from student,course,score
where student.sno=score.sno and score.cno=course.cno;

10-28 查询平均成绩以上的课程 (10 分)

select sno 学号,cname 课程名, grade 成绩
from cou,sc a
where cou.cno=a.cno and (a.grade>(
select avg(b.grade)
from sc b
where a.sno=b.sno
))

10-29 查询选修张老师讲授所有课程的学生 (10 分)

select sname
from stu
where not exists
(
    select * 
    from cou
    where not exists
(
    select *
    from sc 
    where sc.sno=stu.sno 
    and sc.cno=cou.cno
) 
    and cou.teacher = '张老师')

10-30 检索没被学生选修的课程编号和课程名称。 (10 分)

select cno,cname
from course
where cno not in(select cno from score)