[大数据]数据库练习MySQL

数据库练习MySQL

/*6、检索所有比“王华”年龄大的学生姓名、年龄和性别*/
select sname,Sage,Ssex
from student
where sage>(SELECT sage
                from student 
                WHERE Sname='王华' ); 


/*7)检索选修02号课程的学生中成绩最高的学生的学号*/
select Sno
FROM sc
where Cno='002' AND Grade=(
    SELECT max(Grade)
    FROM sc
    where cno='002'
);
/*8)查询没有任何一门课程成绩超过90分的所有学生的信息*/
select *
from student
WHERE sno IN(
    select Sno
    from sc
    GROUP BY Grade
    having AVG(grade)>90
);
/*9)查询没有选修“01”号课程的学生姓名*/
select Sname 
from student
where sno not in(
    select Sno
    FROM SC
    where cno='001'
);

/*10)查询选修了课程名为“数据库”的学生姓名和成绩  */
select student.sname,sc.grade
from sc,student,course
where sc.sno=student.sno and course.Cno=sc.Cno
and course.Cname='数据库'; 

/*11)按分数降序排序，
输出“计科系”学生选修了“数据库”课程的学生的姓名和分数*/
select student.sname,sc.grade
from sc,student,course
where sc.sno=student.sno and course.Cno=sc.Cno
    AND student.Sdept='CS' and course.Cname='数据库'
ORDER BY Grade DESC;

/*12)查询共有多少学生选修了“数据库”*/
select Distinct (COUNT(*))
from course,sc
where course.Cno=sc.Cno and course.Cname='数据库';

/*13)	查询各门课程的课程号及其选课人数*/
SELECT cno,COUNT(DISTINCT(sc.sno))
from sc
GROUP BY Cno;

/*14)	统计每一年龄选修课程的学生人数*/
SELECT student.sage,COUNT(DISTINCT student.Sno)
from sc,student
WHERE sc.sno=student.sno
GROUP BY student.Sage;
select Student.Sage,count(distinct Student.Sno) 
from Student,SC 
where SC.Sno = Student.Sno 
group by Student.Sage;

/*15)	查询每个学生已获得的总学分
（成绩>=60可获得该门课程学分）*/
SELECT Sno,sum(course.Credit)
from sc,course
where sc.cno=course.cno AND sc.Grade>=60
GROUP BY sc.Sno;
/*16)检索选修2门及以上课程的学生的学号和其总成绩
(不统计不及格的课程)，并要求按总成绩的降序排列出来。*/

SELECT student.sno,SumGrade
FROM student,(
    select student.sno,sum(sc.Grade) SumGrade
    from student,sc
    where student.sno=sc.sno and sc.Grade>=60
    GROUP BY student.Sno
    having COUNT(*)>2
) as scc
WHERE student.sno=scc.Sno
ORDER BY SumGrade DESC;

/*17)查询选修过两门及以上课程的学生的学号*/
SELECT student.sno
FROM student,(
    select student.sno,sum(sc.Grade) SumGrade
    from student,sc
    where student.sno=sc.sno and sc.Grade>=60
    GROUP BY student.Sno
    having COUNT(*)>=2
) as scc
WHERE student.sno=scc.Sno;
/*18)查询已获得的总学分高于等于5分的女生的学号*/
SELECT student.Sno
from student,sc,course
where student.ssex='M' and student.sno=sc.Sno
       AND sc.cno=course.Cno 
gROUP BY student.Sno
HAVING max(course.Credit)>=5 and min(sc.Grade>=60);
/*19)查询平均成绩在90分以上的信息系的学生的学号和平均成绩*/
SELECT student.Sno,AVG(sc.Grade)
from student,sc
WHERE student.sno=sc.Sno AND student.Sdept='IS'
GROUP BY student.Sno
having AVG(sc.Grade)>=90;
/*20)查询“levi” 同学没有选修的课程名”*/
SELECT course.Cname
from course ,student LEFT JOIN sc on student.sno=sc.Sno
WHERE student.Sname='张三' and sc.cno=course.cno;
/*由于该实例中没有添加“Levi”所以查询结果为空，实验截图中用张三代替Levi*/

/*21)查询选修了全部课程且全部通过考试的学生的学号和姓名*/
select sno,sname
from student
where sno in(
    select sno 
    from sc
    WHERE sc.grade>60
    group by Sno
    having COUNT(*)=(select COUNT(*) from course)
);
/*22)查询选修了全部课程的信息系的学生的学号和姓名*/
select sno,Sname
from student
where Sdept='IS' and sno in(
    select Sno
    from sc
    GROUP BY Sno
    having COUNT(*)=(SELECT COUNT(*) from course)
);
/*23)把学生“刘晨”所选修的课程的成绩加10分*/
update SC
 set Grade := Grade+10 
where Sno = (
    select Sno
    from student
    where Sname = '刘晨');
/*24)创建学生成绩表视图view_student_course_grade
，包括学号、姓名、课程、成绩*/
create VIEW view_student_course_grade as;

create view view_student_course_grade as 
select Student.Sno,Student.Sname,Course.Cname,SC.Grade 
from Student,SC,Course
where Student.Sno = SC.Sno and SC.Cno = Course.Cno;

/*25)定义一个有关学生学号及其平均成绩视图
view_student_average_grade*/
CREATE VIEW view_student_average_grade as
SELECT student.sno,AVG(sc.grade)
from student,sc
where student.sno=sc.Sno
GROUP BY student.Sno;

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖