--查询CS系学生选择的课程,列出学号,课程号,成绩
select sno,cno,grade from Sc where Sno in(select Sno from Student where Sdept ='CS');
-- 查询没有选C06(课程号)课程的同学的学号,姓名,性别
select sno,sname,ssex from Student where Sno not in(select Sno from Sc where Cno='C06')
--查询成绩最高的选课信息,列出学号,课程号和成绩
select sno,cno,grade from SC where grade in( select max(Grade) from SC)
-- 查询CS系没有选择'DB'课程学生的姓名,列出学生姓名
select sname from Student
where sdept='CS' and Sno not in(select Sno from Sc
where Cno in(select Cno from Course
where Cname='DB'))
--查询'DB'课程考最高分的选课信息。列出学号,课程号,成绩
select sno,cno,grade
from SC
where grade in (select max(grade) from SC
where cno in(select cno from Course where Cname='DB'))and
cno in (select cno from Course where Cname='DB')
select sno,SC.cno,grade from SC,Course
where SC.cno = Course.cno
and cname = 'DB'
and grade >= all(select grade from SC
where grade in(select grade from SC,Course
where SC.cno = Course.cno
and cname = 'DB') )
--选修了先行课为'DB'的课程的学生,列出学生学号,姓名,性别,所在系
select sno,sname,ssex,sdept
from Student,Course
where cpno in (select cno from Course where cname = 'DB')
--将'DB'课程不及格的成绩加5分
update SC set grade=grade+5
where cno in (select cno from Course where cname='DB')
and grade<60
--删除'English'(课程名)课程CS系学生的选课记录
delete from SC
where cno in (select cno from Course where cname='English')
and sno in (select sno from Student where sdept='CS')
--为CS系添加必修课,课程号为C02
insert into SC(sno,cno,grade)
select sno,'c02',null from student where sdept='CS' and
sno not in(select sno from sc where cno='c02')
--查询‘001’号的学生不及格的课程数,列出不及格课程数(列名为:scnt)
select count(Sno) as scnt from sc
where Sno='001' and Grade<60
group by Sno;
--查询每个学生不及格的课程数,列出学号和不及格课程数(列名为:scnt)
select sno,Count(Sno) as scnt from sc
where grade<60
group by Sno;
--每个系女同学的平均年龄,列出所在系和平均年龄(列名为:sageavg)
select sdept,avg(sage) as sageavg from Student
where ssex='f'
group by sdept;
--查询Niki(姓名)同学的平均分,列出平均分(列名为:savg)
select avg(grade) savg from Sc
where Sno in (select sno from Student where Sname='Niki');
--查询CS系每个同学的平均分,列出学号和平均分(列名为:savg)
select sno,avg(grade) savg from Sc
where Sno in (select Sno from Student where Sdept='CS')
group by Sno;
--学分为2的每门课程的选课人数,列出课程号和选课人数(列名为:scnt)
select cno,count(sno) scnt
from SC
where cno in (select cno from Course where ccredit = 2)
group by cno
--平均分最高的学生的姓名
select sname from Student
where sno in
(select sno from SC
group by sno
having avg(grade) >= all (select avg(grade) from SC group by sno))
--不及格人数大于等于2人的课程,列出课程号,课程名,不及格人数(列名为scnt)
select Course.cno,cname,Count(sno) as scnt from Sc,Course
where Course.Cno=Sc.Cno and Grade<60
group by Course.cno,Cname
having Count(sno)>=2
--E系平均成绩最高的同学的姓名,列出姓名
select sname from Student,SC
where sdept = 'E' and Student.sno = SC.sno
group by sname
having avg(grade) >= all (select avg(grade) from SC where sno in (select sno from Student where sdept = 'E') group by sno)
--为Student表的Sage列添加约束,使其取值小于30岁(约束名:stu_chk_sage)
alter table student
add constraint stu_chk_sage check(sage < 30)
--student表的Ssex列添加约束,使其只能取值‘m’或‘f’(约束名:stu_chk_ssex)
alter table student
add constraint stu_chk_ssex check(ssex = 'f' or ssex = 'm')
--为Student表的ssex列添加缺省约束,缺省值为‘m’(约束名:stu_def_ssex)
alter table student
add constraint stu_def_ssex default('m') for ssex
--为SC表的sno列添加外码(约束名:stu_ref_sno)
alter table sc
add constraint stu_ref_sno foreign key (sno) references student(sno)
--为SC表的cno列添加外码(约束名:stu_ref_cno)
alter table sc
add constraint stu_ref_cno foreign key (cno) references course(cno)
--为SC表的grade列添加检查约束(1到100分)(约束名:stu_chk_grade)
alter table sc
add constraint stu_chk_grade check(grade >= 1 and grade <= 100)
--为Course表的cname列添加唯一约束(约束名:Course_un_cname)
alter table course
add constraint Course_un_cname unique (cname)
--为Course表的ccredit列添加检查约束,使其值为(1到10之间)(约束名:Course_chk_ccredit)
alter table course
add constraint Course_chk_ccredit check (ccredit between 1 and 10)
--为Course表的cpno列添加外码约束(约束名:Course_ref_cpno)
create unique index idx_cpno on Course(cpno)
alter table course
add constraint Course_ref_cpno foreign key (cpno) references course(cpno)
--为Student表的sname列创建唯一索引(索引名:idx_student_sname)
create unique index idx_student_sname on Student (sname)
--为sc表的grade列创建降序索引(索引名:idx_sc_grade)
create index idx_sc_grade
on sc(grade DESC)
--为course表的ccredit列创建升序索引(索引名:idx_course_cname)
create index idx_course_cname
on Course (ccredit)
--创建视图vcg,查询课程名为'Math'的课程中,成绩不及格的学生,列出学号,课程号,成绩
create view vcg as
select sno,cno,grade
from SC
where cno in (select cno from Course where cname = 'MAth' and grade < 60)
--修改视图vcg中的数据,将成绩加5分
update vcg set grade = grade + 5
--删除视图vcg中的数据,将成绩小于40分的选课信息删除
delete from vcg where grade < 40
--创建vsumc视图,使其包含每个学生的获得的学分(成绩及格才能得学分)。列出学号和总学分(列名:ssumc)
create view vsumc as
select sno,sum(ccredit) as ssumc
from SC,Course
where SC.cno = Course.cno and grade >= 60
group by sno
--使用vsumc视图,查询男同学总学分高于12分的学生的学号,姓名,性别
select vsumc.sno,sname,ssex
from vsumc,Student
where ssumc > 12 and vsumc.sno = Student.sno and ssex ='m'
--使用vsumc视图,查询总学分最高的学生,列出学号,姓名,性别,年龄,所在系
select vsumc.sno,sname,ssex,sage,sdept
from vsumc,Student
where ssumc >= all(select ssumc from vsumc) and Student.sno = vsumc.sno
--使用vsumc视图,查询平均分大于等于65分的学生的学号,平均分(列名:savg),总学分(列名:ssumc)
select SC.sno, avg(grade) savg,ssumc from vsumc,SC
where SC.sno=vsumc.sno
group by SC.sno,ssumc
having avg(grade)>=65
--为utest用户赋予student表的查询权限
grant select on student to utest;
--为utest用户赋予SC表的查询权限和修改权限
grant select,update on sc to utest;
--为utest用户赋予Course表的插入权限和删除权限
grant insert,delete on Course to utest;
--收回utest对student表的修改权限
revoke update on student from utest
--收回utest对SC表的插入权限和修改权限
revoke insert,update on sc from utest
--收回utest对Course表的查询权限和删除权限
revoke select,delete on Course from utest