educoder 数据库

--查询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