实验四 数据查询实验

use  StudentDB ;

/**********************实验四 数据查询实验******************************/
-- 1．   查询学生的基本信息；
select *from Student;
-- 2．   查询“CS”系学生的基本信息；
select *from Student where Sdept='CS';
-- 3．   查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；
select Sno,Sname from Student
where Sdept='CS' and Sage not between 19 and 21 ;
-- 4．   找出最大年龄；
select max(Sage) 最大年龄 from Student ;
-- 5．   找出“CS”系年龄最大的学生，显示其学号、姓名；
select Sno,Sname from Student
where Sdept='CS' and Sage= (
    select max(Sage) from Student where sdept='CS');
-- 6．   找出各系年龄最大的学生，显示其学号、姓名；************
-- 本题用到了相关子查询，比较的难，需要多加练习
update  student set sage=23 where sno='200215125' ;
select *from student;
select sno,sname,sdept
from student a
where sage = (
    select max(sage) from student b
    where a.sdept=b.sdept );
-- 7．   统计“CS”系学生的人数；
select count(*) 计算机系人数 from Student
where Sdept='CS';
-- 8．   统计各系学生的人数，结果按升序排列；
select Sdept,count(*) cnt
from SC,Student
where Student.Sno=SC.Sno 
group by Sdept
order by cnt;
-- 9．   按系统计各系学生的平均年龄，结果按降序排列；
select Sdept,avg(Sage) age
from SC,Student
where Student.Sno=SC.Sno
group by Sdept
order by age desc;
-- 10．  查询每门课程的课程名；
select Cname from Course;
-- 11．  查询无先修课的课程的课程名和学分数；
select Cname,Ccredit
from Course
where Cpno is null;
-- 12．  统计无先修课的课程的学分总数；
select sum(Ccredit) 
from Course
where Cpno is null ;
-- 13．  统计每位学生选修课程的门数、学分及其平均成绩；
select SC.sno,count(SC.Cno),sum(Ccredit),avg(Grade)
from SC,Course
where SC.cno=Course.cno
group by Sno;
-- 14．  统计选修每门课程的学生人数及各门课程的平均成绩；
select Cno,count(Cno),avg(Grade)
from SC
group by Cno;
-- 15．  找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；
select Student.Sno,avg(grade) avg_grade,Sdept
from SC,Student
where SC.Sno=Student.Sno
group by Student.Sno,Sdept
having avg(grade)>85
order by avg_grade;
select Student.Sno,avg(grade) avg_grade,Sdept
from SC join Student
using(sno)
group by Student.Sno,Sdept
having avg_grade>85
order by avg(grade) ; -- 注意在order by 后面可以用聚集函数
-- 16．  查询选修了“1”或“2”号课程的学生学号和姓名；*********
-- 方法一
select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and (Cno='1' or Cno='2' );
-- 方法二
select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and Cno in('1','2');
-- 方法三
select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and Cno='1'
union
select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and Cno='2' ;
-- 17．  查询选修了“1”和“2”号课程的学生学号和姓名；*********
select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and  Cno='1' and Student.Sno in
    (select  distinct Sno from SC where  Cno='2' );
/*
-- intersect 在MySQL中没有
select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and Cno='1'
intersect
select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and Cno='2' ;
*/
-- 18．  查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩；
insert into SC values('200215125','1',58);
select Student.Sno,Sname,Grade
from SC,Course,Student
where SC.Sno=Student.Sno and SC.Cno=Course.Cno and Cname='数据库' and grade<60 ;
delete from SC 
where Sno='200215125' and Cno='1' ;
-- 19．  查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；
select Student.Sno,Sname,Course.Cno,Cname,grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;
select sno,sname,cno,cname,grade
from student join sc using(sno)
join course using(cno);
-- 20．  查询没有选修课程的学生的基本信息；*************
-- 方法一
select Student.* from Student
where Sno not in (
    select Sno from SC );
-- 方法二
select Student.* from student
where not exists (
    select * from SC
    where sno=Student.sno );
-- 21．  查询选修了3门以上课程的学生学号；
select Sno from SC
group by Sno
having count(Sno)> 2;
-- 22．  查询选修课程成绩至少有一门在85分以上的学生学号；
select Sno from SC
group by Sno
having max(Grade)>85 ;
-- 23．  查询选修课程成绩均在85分以上的学生学号；
select Sno from SC
group by Sno
having min(Grade)>85 ;
-- 24．  查询选修课程平均成绩在85分以上的学生学号；
select Sno from SC
group by Sno
having avg(Grade)>85 ;