数据库实验三：数据库的SQL基本操作

(31) 创建信息系学生信息的视图

create view IS_Student
as
select *
from student
where sdept = 'IS';

(32) 在信息系的学生视图中查询年龄小于20岁的学生

select *
from IS_Student
where Sage < 20;

(33) 删除马朝阳同学的所有选课记录

delete 
from sc
where sno in(
select sno
from student
where sname = '马朝阳');

(34) 查询选修了3号课程的学生的学号和成绩, 并按分数降序排列

select sno,grade
from sc
where cno = '3'
order by grade DESC;

(35) 查询数据库课程成绩不及格的学生，输入其学号，姓名和成绩

select sc.sno,sname,grade
from sc,student
where sc.sno = student.sno 
and grade < 60;

(36) 查询全体学生的情况,查询结果按所在系号升序排列, 同一系中的学生按年龄降序排列

select *
from student
order by sdept,sage DESC;

(37) 查询每个学生及其选修课程的情况

select sc.sno,sname,ssex,sage,sdept,sc.cno,cname,credit,grade,pcno
from student,sc,course
where student.sno = sc.sno
and sc.cno = course.cno;

(38) 查询每一门课程的间接先行课

select cname,pcno
from course

(39) 查询选修1号课程且成绩在85分以上的所有学生的学号、姓名

select student.sno,sname
from student,sc
where student.sno = sc.sno 
and cno = '1'
and grade > 85;

(40) 查询全体学生的所有信息

select *
from student

(41) 查询选修了课程’1’和课程’2’的学生的学号

select sno
from sc
where cno = '1' or sno = '2';

(42) 创建信息系选修了1号课程的学生的视图

create view IS_S1(sno,sname,grade)
as
select student.sno,sname,grade
from student,sc
where sdept='IS'
and student.sno = sc.sno
and sc.cno = '1';

(43) 建立信息系选修了1号课程且成绩在90分以上的学生的视图

create view IS_S2(sno,sname,grade)
as
select sno,sname,grade
from IS_S1
where grade > 90;

(44) 查询修课总学分在10学分以下的学生姓名

select sname
from student,sc,course
where student.sno = sc.sno
and sc.cno = course.cno
group by sname
having count(credit) < 10;

(45) 查询比’刘晨’年龄小的所有学生的信息

select *
from student
where sage < (
select sage
from student
where sname = '刘晨');

(46) 查询所有选修了2号课程的学生的姓名

select sname
from student,sc
where student.sno = sc.sno
and cno = '2';

(47) 查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄

select sname,sage
from student
where sage < ANY(
select sage
from student
where sdept = 'IS')
and sdept <> 'IS';

(48) 查询学生2号课程的成绩，并按照成绩由高到低输出

select grade
from sc
where cno = '2'
order by grade DESC;

(49) 查询考试成绩有不及格的学生的学号

select sno
from sc
where grade < 60;

(50) 查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄

select sname,sage
from student
where sage < ALL(
select sage
from student
where sdept = 'IS')
and sdept <> 'IS';

(51) 将所有学生的学号和他的平均成绩定义为一个视图

create view VIEW51(sno, GRADE) as
select student.sno,AVG(grade)
from student,sc
where student.sno=sc.sno
group by student.sno; 

(52) 在视图S_G中查询平均成绩在90分以上的学生的学号和平均成绩:

select sno,avg_grade
from S_G
where avg_grade>=90; 

(53) 查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓名和年龄

select distinct sno,sname,sage
from student 
where sdept!='IS' and sage NOT IN(		  
select sage
from student 
where sdept='IS'); 

(54) 查询信息系选修了1号课程的学生

select sc.sno,sname
from student,sc
where sc.cno=1 and sdept='IS' and student.sno=sc.sno;

(55) 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄

select a.sno, a.sname, a.sage
from student a
where sage not in(
	select sage
	from student b
	where a.sno!=b.sno
	); 

(56) 查询选修了全部课程的学生姓名

select sname
from student,sc
where student.sno=sc.sno and sc.cno=all(
	select cno
	from course); 

(57) 求没有人选修的课程号cno和cname

select cno,cname
from course
where cno not in(
	select cno
	from sc
	) ; 

(58) 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号cno的课程

select sno,course.cno
from course,sc
group by sc.sno,course.cno
having course.cno not in(select sc.cno from sc); 

(59) 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade)

select b.sno,b.cno,b.grade
from(select sno,max(grade) maxg from sc group by sno) a,sc b
where a.sno = b.sno and a.maxg = b.grade; 

(60) 查询学生总人数

select COUNT(sno)
from student; 

(61)查询年龄在20-30岁直接的学生的姓名, 姓名, 所在系

select sname,ssex,sdept
from student
where sage>=20 and sage<=30; 

(62) 查询所有课程的总学分数和平均学分数,以及最高学分和最低学分

select SUM(credit) sum, avg(credit) avg, MAX(credit) max ,min(credit) min
from course; 

(64) 求成绩低于该门课程平均成绩的学生的成绩信息(sno,cno,grade)

select sno,cno,grade
from sc
where grade<(
	select avg(a.grade)
	from sc a,sc b
	where a.cno=b.cno); 

(65) 查询各系的学生的人数并按人数从多到少排序

select sdept,count(sno) count
from student
group by sdept
order by count desc; 

(66) 创建年龄大于23岁的学生的视图

create view VIEW66 as
select sage
from student
where sage>23;  

(67) 查询选修了课程的学生总数

select COUNT(sno)
from student
where sno in(
	select sno
	from sc); 

(68) 查询选修了3门课程以上的学生的学号和姓名

select sno
from sc
group by sno
having count(*)>=3; 

(69) 查询平均分超过80分的学生的学号和平均分

select sno,avg(grade) avg
from sc
group by sno
having avg(grade)>80; 

(70)查询”信息系”(IS)中选修了2门课程以上的学生的学号

select sc.sno
from student,sc
where sdept='IS' and student.sno=sc.sno
group by sc.sno 
having COUNT(sc.sno)>=2; 

(71) 查询选修了1号课程或2号课程的学生的学号

select distinct a.sno
from sc a,sc b
where a.cno=1 or b.cno=2 and a.sno=b.sno;

(72) 查询平均成绩少于70分的学生的学号

select sno
from sc
group by sno
having avg(grade)<=70; 

(73) 将信息系学生视图IS_Student中学号为”95002”的学生姓名改为”刘辰”

create view IS_Student
as
select *
from student
where sdept='IS'
update student
set sname='刘辰'
where sno = '95002' and sdept='IS'; 

(74) 向信息系学生视图IS_Student中插入一个新的学生记录, 学号为95029,姓名为”刘一梦”, 年龄为20岁

insert
into IS_Student
values('95029','刘一梦','女','20','IS'); 

(75) 删除信息系学生视图IS_Student中学号为95004的学生的记录

delete
from IS_STUDENT
where sno=95001;