数据库练习题

MySQL练习

https://github.com/EmorZz1G/DatabaseStudy
在GitHub中查看更多
题目理解有点离谱，多个题目更新

1. 查询平均成绩大于80的学生姓名。
2. 查询课程成绩大于课程平均成绩的选课信息，显示学生姓名、课程名称和成绩。
3. 查询至少选修了C1和C2课程的学生名单。
4. 查询选修了C1课程而没有选修C2课程的学生名单。
5. 统计每门课程成绩大于80分的学生数。
6. 统计计算机系“CS”学生的平均分
7. 统计至少选修了两门课程的学生数
8. 查询至少选修了两门课程的学生名单
9. 查询没有被选修的课程信息
10. 查询没有选修C1课程的学生信息
11. 统计没有选修C1课程的学生人数
12. 查询平均分最高的课程信息
13. 查询平均分最高的课程的选课信息（学号，姓名，课程名程，成绩）
14. 查询平均分最高的学生所在院系。
15. 统计学生平均选修课程数。
16. 统计各院系学生平均选课数。
17. 统计每门课程的选课人数，最高分，平均分和最低分。
18. 查询平均分75以上，并且没有不及格成绩的学生信息。
19. 修改“CS”的学生成绩，不及格学生的成绩增加5分。
20. 删除“IS”系学生的成绩记录。
21. 定义、查询、更新视图。

# 1. 查询平均成绩大于80的学生姓名
SELECT sname
FROM student
JOIN 
(
    SELECT sno,AVG(grade)
    FROM sc
    GROUP BY sno
    HAVING AVG(grade) >80
)p ON student.sno=p.sno;

# 2. 查询课程成绩大于课程平均成绩的选课信息，显示学生姓名、课程名称和成绩。
select sname,cname,grade
from student,course,sc x
where grade>=(
                select avg(grade)
                from sc y
                where x.`cno`=y.`cno`
                )
    and student.`sno`=x.`sno`
    AND x.`cno`=course.`cno`;
 # 2. 更新，子查询条件有误
SELECT sname,cname,grade
FROM student,course,sc X
WHERE x.grade>=(
                SELECT AVG(grade)
                FROM sc Y
                WHERE x.`cno`=y.`cno`
                )
    AND student.`sno`=x.`sno`
    AND x.`cno`=course.`cno`;   
    
# 3. 查询至少选修了C1和C2课程的学生名单。
select distinct s.*
from student s,sc sc1 ,course c
where exists
    (
        select *
        from sc sc2
        where sc2.cno=1 
            and s.`sno`=sc2.`sno`
            and exists
                    (
                        select * 
                        from sc sc3
                        where s.`sno`=sc3.`sno`
                            and sc3.`cno`=2
                    )
                    
    )
    and s.`sno`=sc1.`sno`
    and sc1.`cno`=c.`cno`
    
# 4. 查询选修了C1课程而没有选修C2课程的学生名单。//也可以使用连接
select distinct s.*
from student s,sc sc1 ,course c
where exists
    (
        select *
        from sc sc2
        where sc2.cno=1 
            and s.`sno`=sc2.`sno`
            and not exists
                    (
                        select * 
                        from sc sc3
                        where s.`sno`=sc3.`sno`
                            and sc3.`cno`=2
                    )
                    
    )
    and s.`sno`=sc1.`sno`
    and sc1.`cno`=c.`cno`
    
# 5. 统计每门课程成绩大于80分的学生数。
SELECT s.*,sc1.*
FROM sc sc1,student s
WHERE s.`sno`=sc1.`sno`
GROUP BY s.`sno`
HAVING MIN(sc1.`grade`) > 80

# 5. 修正，有误，应该查询人数
SELECT COUNT(*)
FROM(
    SELECT COUNT(s.sno)
    FROM sc sc1,student s
    WHERE s.`sno`=sc1.`sno`
    GROUP BY s.`sno`
    HAVING MIN(sc1.`grade`) > 80
    )p
   
# 6. 统计计算机系“CS”学生的平均分
select avg(sc.`grade`)
from sc
where sc.`sno` in
            (
                select student.sno
                from student
                where sdept = "MA"
            )
# 6. 第二种理解
SELECT student.`sname`,AVG(sc.`grade`)
FROM sc,student
WHERE sc.`sno` IN
            (
                SELECT student.sno
                FROM student
                WHERE sdept = "CS"
            )
    AND student.`sno`=sc.`sno`
GROUP BY sc.`sno`          
  
# 7. 统计至少选修了两门课程的学生数
select count(p.sno)
from    
    (
    select sc.`sno`
    from sc
    group by sc.`sno`
    having count(sc.`grade`)>=2
    )p
    
# 8. 查询至少选修了两门课程的学生名单
SELECT student.*
FROM    
    (
    SELECT sc.`sno`
    FROM sc
    GROUP BY sc.`sno`
    HAVING COUNT(sc.`grade`)>=2
    )p
    ,student
WHERE student.sno=p.sno

# 9. 查询没有被选修的课程信息
SELECT course.*
FROM course
WHERE 
    NOT EXISTS(
                SELECT *
                FROM sc
                WHERE sc.`cno`=course.cno
              )
              
# 10.查询没有选修C1课程的学生信息
SELECT student.*
FROM student
WHERE
    NOT EXISTS(
                SELECT *
                FROM sc
                WHERE sc.`cno`=1
                    AND student.`sno`=sc.`sno`             
                )
                
# 11.统计没有选修C1课程的学生人数
SELECT COUNT(student.sno)
FROM student
WHERE
    NOT EXISTS(
                SELECT *
                FROM sc
                WHERE sc.`cno`=1
                    AND student.`sno`=sc.`sno`             
                )
                
# 12.查询平均分最高的课程信息
SELECT course.*
FROM course,(
            SELECT sc.`cno`,AVG(sc.`grade`) AVG
            FROM sc
            GROUP BY sc.`cno`
            HAVING AVG(sc.`grade`)
            ORDER BY AVG DESC
            )p
WHERE p.cno=course.`cno`

# 13.查询平均分最高的课程的选课信息（学号，姓名，课程名程，成绩）
SELECT student.sno ,student.`sname`,course.`cname`,sc2.grade
FROM student,course,sc sc2,(
            SELECT sc1.`cno`,AVG(sc1.`grade`) AVG
            FROM sc sc1
            GROUP BY sc1.`cno`
            HAVING AVG(sc1.`grade`)
            ORDER BY AVG DESC
            LIMIT 0,1
            )p
WHERE
    p.cno=sc2.cno
    AND sc2.cno=course.`cno`
    AND student.sno=sc2.sno
    
# 14.查询平均分最高的学生所在院系。
SELECT student.`sdept`
FROM student,(
            SELECT sc.`sno`,AVG(sc.`grade`) AVG
            FROM sc
            GROUP BY sc.`cno`
            ORDER BY AVG DESC
            LIMIT 0,1
            )p
WHERE student.sno = p.sno

# 15. 统计学生平均选修课程数。
select avg(cnt)
from (
        select count(sc.`cno`) cnt
        from sc
        group by sc.`sno`
      )p
      
# 15. 题意理解？"平均"是否包括"没有选课"的学生
SELECT p1.cnt_course/p2.cnt_student AVG
FROM (
        SELECT COUNT(sc.`sno`) cnt_course
        FROM sc
      )p1
      ,(
        SELECT COUNT(student.`sno`) cnt_student
        FROM student
      )p2   
      
# 16. 统计各院系学生平均选课数。
SELECT student.`sdept`,AVG(cnt)
FROM student,(
        SELECT sc.`sno`, COUNT(sc.`cno`) cnt
        FROM sc
        GROUP BY sc.`sno`
      )p
WHERE p.sno=student.`sno`
GROUP BY student.`sdept`

# 17. 统计每门课程的选课人数，最高分，平均分和最低分。
SELECT		 course.`cname`,COUNT(sc.`sno`),MAX(sc.`grade`),AVG(sc.`grade`),MIN(sc.`grade`)
FROM sc,course
WHERE sc.`cno`=course.`cno`
GROUP BY sc.`cno`

# 18. 查询平均分75以上，并且没有不及格成绩的学生信息。
select student.*
from student
where exists
                (
                  select *
                  from sc sc2
                  where sc2.sno=student.`sno`
                  group by sc2.`sno`
                  having min(sc2.`grade`)>=60
                        and avg(sc2.`grade`)>75     
                )
                
# 19. 修改“CS”的学生成绩，不及格学生的成绩增加5分。
update sc,student
set sc.`grade`=sc.`grade`+5
where student.`sdept`="CS"
      and sc.`grade`<60
      and sc.sno = student.`sno`
      
# 20. 删除“IS”系学生的成绩记录。
delete 
from sc
where sc.`sno` in (
                    select student.`sno`
                    from student
                    where student.`sdept`="IS"
                  )
                  
# 21. 定义、查询、更新视图。
# Definition
create view cs_student as
select student.*
from student
where student.`sdept`="CS"
with check option
# Query
select *
from cs_student
# Update success
insert into cs_student
values(201215131,'Emor2','男',20,'CS')
# Update failure
INSERT INTO cs_student
VALUES(201215131,'Emor2','男',20,'IS')

在GitHub中查看更多