对于以下关系模式:
Teacher(tno , tname ,tage,tsex,title)
project(pno,pname, tno,classify)
work(tno,pno,pt)
其中teacher为教师关系,tno为教师编号,tname为教师名,tage为年龄tsex为性别,title为教师的职称;
project为项目关系,pno为项目号pname为项目名,tno为负责人教师编号,classify为项目分类;
work为参与关系,pt为某个教师在参与某个项目的工作量。
1. 查询张丽老师完成的工作量
SELECT SUM(pt)
FROM teacher ,work
WHERE tname= ‘张丽’ AND teacher.tno=work.tno;
2. 查询完成省级项目的教师姓名
SELECT DISTINCT tname
FROM project,work,teacher
WHERE classity= ‘省级’AND project.pno=work.pno AND work.tno=teacher.tno;
3. 查询参与了全部项目的教师号
SELECT tno
FROM teacher
WHERE NOT EXISTS
(
SELECT*
FROM project
WHERE NOT EXISTS
( SELECT *
FROM work
WHERE tno=teacher.tno AND pno=work.pno));
4. 查询职称为讲师的教师参与的项目的分类
SELECT DISTINCT classify
FROM teacher,work
WHERE title=‘讲师’AND teacher.tno=work.tno;
5. 查询工作量为20到30之间的教师姓名,参与的项目号
SELECT tname,pno
FROM teacher,work
GROUP BY tno
HAVING sum(pt) BETWEEN 20 AND 30;
6. 统计职称为副教授的教师参与的项目个数
SELECT COUNT(DISTINCT pno)
FROM teacher,work
WHERE title=’副教授’AND teacher.tno=work.tno;