access教师管理系统 数据库实验报告

4、 为每个关系模式设计实验数据

5、 使用SQL设计教师管理系统数据库的查询

(1) 查询所有教师的基本信息，并按教师的年龄的降序排列

SELECT teacher.* FROM teacher

ORDER BY teacher.tage desc

(2) 查询所有课程信息

SELECT class.* FROM class

(3) 查询所有女教师的信息，要求显示教师的姓名、性别、年龄、出生日期和 所讲授的课程，并按出生日期的升序排序

SELECT teacher.tname, teacher.tsex, teacher.tage, teacher.tbirth, teacher_class.cname FROM teacher_class INNER JOIN teacher ON teacher_class.tno = teacher.tno WHERE (((teacher.tsex)=\女\ORDER BY teacher.tbirth

(4) 查询参加某项目的教师的姓名、性别、年龄，并按年龄的升序排序

SELECT teacher_item.ino, item.iname, teacher.tname, teacher.tsex, teacher.tage

FROM teacher, item, teacher_item

WHERE (teacher_item.ino=\

AND (teacher.tno=teacher_item.tno)

ORDER BY teacher.tage

(5) 统计每位老师讲授课程的数量，要求输出教师名称、课程数量

SELECT teacher.tname, Count(*) AS cno_num

FROM teacher_class INNER JOIN teacher ON teacher_class.tno = teacher.tno GROUP BY teacher.tname;

(6) 查询讲授2门以上课程的教师号 SELECT teacher_class.tno

FROM teacher_class

GROUP BY teacher_class.tno

HAVING (Count(teacher_class.cno))>2

(7) 统计每个项目参加的教师人数，要求输出项目号、项目人数 SELECT teacher_item.ino, Count(teacher_item.tno) AS tno_num

FROM teacher_item

GROUP BY teacher_item.ino

三、实验结果

1、查询所有教师的基本信息，并按教师的年龄的降序排列

2、查询所有课程信息

3、查询所有女教师的信息，要求显示教师的姓名、性别、年龄、出生日期和 所讲授的课程，并按出生日期的升序排序