学生-课程数据库练习

select Sno,Sname from Student where Ssex='女'and Sage>25;
--查询选修了“数据库”课程的学生姓名。
select Sname from Student where Sno in(select Sno from SC where Cno in(select Cno
from Course where Cname='数据库'));

select Sname from Student,Course,SC Where
 Student.Sno=SC.Sno and Course.Cno=SC.Cno and Cname='数据库';
 
--统计选修2号课程的人数。
select count(*) from Student where Sno in(select Sno from SC where Cno='2');

--查询平均成绩大于80分的学生的学号。
select Sno,Sname from Student where Sno in
(select Sno from SC group by Sno having avg(grade)>80);

--统计每个系的学生人数。
select Count(*)from Student group by Sdept;

--查询选修数据库课程并且成绩不及格的学生学号和姓名
select Student.Sno,Student.Sname 
from Student,SC,Course
where Course.Cname='数据库' and SC.Grade<60 and SC.Cno=Course.Cno and Student.Sno=SC.Sno;

--自身链接查询
--查询每门课程先修课的学分。
select first.Cno,second.Ccredit from Course first,Course second
where first.Cpno=second.Cno;

--查询成绩在60到80之间的所有记录。
select * from SC where Grade between 60 and 80;
--查询成绩为85，86或88的记录
select * from SC where Grade='85'or Grade='86' or Grade='88';
--　　查询所有不姓“王”的学生记录。
select * from Student where Sname not like '王%';
--以系别和年龄从大到小的顺序查询Student表中的全部记录。
select * from Student order by Sdept,Sage Desc;
---　　统计男女生分别有多少人。
select Ssex,count(*) from Student group by Ssex;
--　　查询姓名的第二个字为“小”字的女生信息。
select *from Student where Sname like '_小%' and Ssex='女';
select *from Student where Sname like '%小%';
--　　查询成绩最高的三个学生的学号和成绩。
select top(3) Sno,SUM(Grade)from SC group by Sno order by SUM(Grade) desc;
--　　查询学生的成绩信息，先按学号升序排序，再按成绩降序排序。
Select Student.Sno,Student.Sname,Grade from Student,SC
where Student.Sno=SC.Sno order by Sno asc, Grade desc;

--查询至少选修了两门课的学生的学号，选修课程数和选修的平均分数
select Sno,COUNT(*),AVG(Grade) from sc group by Sno having count(*)>=2;
--　　查询所有比刘晨大的学生的学号，姓名，年龄。
select Sno,Sname,Sage from Student where Sage>(select Sage from Student where Sname='刘晨');
--　　求出总分大于150的学生的学号、姓名。
select Sno,Sname from Student where sno in(
select Sno from SC group by Sno having SUM(Grade)>150);

select Student.Sno,Student.Sname from Student,SC where
 Student.Sno=SC.Sno group by SC.Sno,Student.Sno,Student.Sname having sum(Grade)>150

--实验数据----select SUM(Grade)from SC  group by Sno;
--列出那些专业相同的学生相应的姓名及专业信息
--实验数据----select * from Student  group by Sdept having COUNT(*)>=2;
select * from Student where Sdept in 
(select Sdept from Student group by Sdept having COUNT(*)>=2)

--求至少选修1号课和2号课的学生的学号。
select Sno from SC where Sno in (select Sno from SC where Cno=1) and Cno=2;

--求出所有学生的总成绩   SELECT SUM(成绩) AS 总成绩 FROM 选课
select SUM(Grade) as g from SC group by Sno;

---列出非电院的所有学生
select * from Student where Sdept!='IS';
select * from Student where Sdept<>'IS';
select * from Student where not Sdept='IS';
--列出那些专业相同的学生相应的姓名及专业信息。
select a.Sname,b.Sname,a.Sdept
from Student a,Student b 
where a.Sno<>b.Sno and a.Sdept=b.Sdept;

--、求至少选修1号课和2号课的学生的学号
--SELECT X.学号 FROM 选课 X,选课 Y WHERE
-- X.学号=Y.学号AND X.课号="1" AND Y.课号="2"
select a.Sno from SC a,SC b 
where a.Sno=b.Sno and a.Cno='1' and b.Cno='2';
--、求选修2号课的学生中，成绩比选修1号课的
--最低成绩要高的学生的学号和成绩。
select sno,sname from Student where sno in
(select Sno from SC where Cno='2' and Grade>(
select MIN(Grade)from SC where Cno='1'));


--打开查询分析器用sql语句给u1授予student的查询权限。
grant select on Student to u1;
-- 把对表SC的查询授予所有用户
grant select on SC to public ;

--给用户u1授予对sc表的查询权限并具有给其他用户授权的权利。

 grant select on SC to u1 with grant option;
 
 ---让用户u1对用户u2授予对sc表的查询权限。

grant select on SC to u2;

--分别回收u2和u1所拥有的权限。
revoke all privileges to u2,u1
grant select on Student to r1