Question

求助 这道数据库 关系代数表达式的题目 的答案

对给定的三个关系模式用关系代数表达式表示查询操作
学生S(sno,sname,age,sex,DEPT ) Key={sno}
课程C(cno,cname,teacher) K={cno}
选课SC(sno,cno,G) K={sno,cno}
1.检索“吴迪”老师讲授的课程号和课程名
2.检索所有女同学和年龄小于20岁的男同学
3.检索至少选修“吴迪”老师讲授课程中一门课的学生姓名
4.检索“李波”同学不学的课程的课程名
5.检索至少选修两门课程的学生的姓名
6.检索未被选修的课程的课程名
7.检索选修全部课程的学生的学号
8.检索选修了“吴迪”老师讲授课程的学生的学号
9.检索选修了“吴迪”老师讲授课程且成绩高于85的学生的学号
10.检索“王虎”同学所学课程的课程号
11.检索选修了C01和C02两门课程的学生的姓名
12.检索未选修课程的学生的学号

Answer 1

--1.检索“吴迪”老师讲授的课程号和课程名
select cno ,cname ,teacher from c where teacher ='吴迪'
--2.检索所有女同学和年龄小于20岁的男同学
select * from s where
s.sex='男' and age <20 or s .sex ='女'
--3.检索至少选修“吴迪”老师讲授课程中一门课的学生姓名
select sname from s where sno in (select sno from sc where cno in (select cno from c where teacher ='吴迪' ))

--4.检索“李波”同学不学的课程的课程名
select cname from c where cno in (select cno from sc where sno not in (select sno from s where sname ='李波'))

--5.检索至少选修两门课程的学生的姓名

select sname from s where sno in (select sno from (select COUNT (sno) as 'scNumber',sno from sc group by sno) scs where scNumber >=2)
--6.检索未被选修的课程的课程名
select cname from c where cno not in (select cno from sc)
--7.检索选修全部课程的学生的学号
select sno from s where sno in (select sno from (select COUNT (sno) as 'scNumber',sno from sc group by sno) scs where scNumber = (select COUNT (distinct c .cname) from c ))

--8.检索选修了“吴迪”老师讲授课程的学生的学号
select sno from s where sno in (select sno from sc where cno in (select cno from c where teacher ='吴迪'))

--9.检索选修了“吴迪”老师讲授课程且成绩高于85的学生的学号sele
select sno from s where DEPT >85 and sno in (select sno from sc where cno in (select cno from c where teacher ='吴迪'))
--10.检索“王虎”同学所学课程的课程号
select cno from sc where sno = (select sno from s where sname ='王虎')
--11.检索选修了C01和C02两门课程的学生的姓名
select sname from s where sno in ( select sc.sno from sc where sc.sno in(select sc.sno from sc where sc.cno = (select c.cno from c where c.cname ='c01'))and sc.cno=(select c.cno from c where c.cname ='c02'))
--12.检索未选修课程的学生的学号
select sno from s where sno not in (select sno from sc ) select * from sc