Question

数据库题 很急

学生关系S(Sno，Sname，Ssex,，Sage，Sdept)，属性表示：学号、姓名、性别、年龄、所在系。
课程关系C(Cno，Cname，Cpno，Ccredit)，属性表示：课程号、课程名、先行课号、学分。
选课关系SC(Sno，Cno，Grade)，属性表示：学号、课程号、成绩。
1、用关系代数完成下列查询：
（1）查询计算机系（CS）年龄小于18岁的学生。
（2）查询选修了1号课程和3号课程的学生名。
（3）查询选修了3号课程的学生的学号。
（4）查询至少选修了一门其直接先行课为5号课程的学生姓名。
2、用SQL语言完成下列操作：
（1）查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排序。
（2）查询选修了课程名为“信息系统”的学生学号和姓名。
（3）查询选修了全部课程的学生姓名。
（4）将学生200215121的年龄改为22岁。
（5）删除计算机科学系（CS）所有学生的选课记录。（6）把查询学生S表和修改学生学号的权限授给用户U4
第一题是要求关系代数

Answer 1

1、
(1) 关系代数关系式：π（sname）(σ(sage<18)∧(sdept='计算机系'))
sql语句：select sname from s where sdept='计算机系' and sage < 18;
(2) select sname from sc,s where sc.sno = s.sno and sc.cno in (1号课程,3号课程) ;
(3) select sno from sc where cno = ‘3';
(4) select s.sname from s,sc,c where s.sno=sc.sno and s.cno=c.cno and c.cname='5'
2、
(1) select sno,grade from sc where cno='3' order by grade desc;
(2) select sname,sc.sno from s,sc,c where s.sno = sc.sno and sc.cno = c.cno and cname='IS' ;
(3) select sname from s where not exists (select * from c where not exists (select * from sc,s,c where sc.sno =s.sno and sc.cno=c.cno)
(4) update s set sage = 22 where sno = '200215121';
(5) delete from sc where sno in (select sno from s where sdept='计算机系');
(6) grant select,update(sno) on s to u4;

Answer 2

1.select sname from s where sage < 18 and sdept = 'cs'
2.select sname from s where sno in (select sno from sc where cno = 1 or cno = 3)
3.select sno from s where sno in (select sno from sc where cno = 3)
4.select sname from s where sno in (select sno from sc where cno = 5)--题意不清
1.select sno,grade from sc where cno = 3 order by grade desc
2.select sc.sno,s.sname from sc,s,c where (sc.sno = s.sno) and (sc.cno = c.cno) and (c.cname = '信息系统')
3.单条语句应该查询不出来吧，首先要确定总课程的数量，如果无变化，可以用and，如果有变化，我觉得应该用循环要好些。
--无变化，未验证，不确定。
select sname from s where sno in (select sno from sc where cno = '' and cno = ''......)
4.update sage = 22 from s where sno = 200215121
5.delete sc where sno in (select sno from s where sdept = 'cs')
6.不知道
--以上未建表验证，供参考。

Answer 3

1、(1) select sname from s where sdept='计算机系' and sage < 18;
(2) select sname from sc,s where sc.cno in (1号课程,3号课程) and sc.sno = s.sno;
(3) select sno from sc where cno = 3号课程;
(4) select distinct sname from sc,c,s
where sc.sno = s.sno and sc.cno = c.cno and c.cpno=5号课程;
2、(1) select sno,grade from sc where cno=3号课程 order by grade desc;
(2) select sc.sno,sname from sc,s,c
where cname='信息系统' and sc.cno = c.cno and sc.sno = s.sno;
(3) select distinct sname from sc,s,c where sc.cno = c.cno and sc.sno = s.sno;
(4) update s set sage = 22 where sno = 200215121;
(5) delete from sc where sno in (select sno from s where sdept='计算机系');
(6) grant select on s to u4;
grant update on s(sno) to u4;

Answer 4

参考答案：！！
1、(1) select sname from s where sdept='计算机系' and sage < 18;
(2) select sname from sc,s where sc.cno in (1号课程,3号课程) and sc.sno = s.sno;
(3) select sno from sc where cno = 3号课程;
(4) select distinct sname from sc,c,s
where sc.sno = s.sno and sc.cno = c.cno and c.cpno=5号课程;
2、(1) select sno,grade from sc where cno=3号课程 order by grade desc;
(2) select sc.sno,sname from sc,s,c
where cname='信息系统' and sc.cno = c.cno and sc.sno = s.sno;
(3) select distinct sname from sc,s,c where sc.cno = c.cno and sc.sno = s.sno;
(4) update s set sage = 22 where sno = 200215121;
(5) delete from sc where sno in (select sno from s where sdept='计算机系');
(6) grant select on s to u4;
grant update on s(sno) to u4;

参考答案！！

Answer 5

作业题