直接撸代码
Go!Go!Go!

本文所有查询都是依据本school数据库

  • 查询所有所有学生数

    SELECT count(id) from student;

  • 查询学生信息

    SELECT * from student;

  • 查询所有学生的成绩,要求显示学生姓名,成绩,科目,对应老师

    SELECT st.sname,sc.score,pr.pname,te.tname
    from student st,project pr,teacher te,score sc
    where st.sno=sc.sno and pr.pno=sc.pno and te.tno=pr.pno;

  • 查询平均分大于90的学生

    SELECT student.sname,avg(score.score) as agv from student,score
    where score.sno=student.sno GROUP BY student.sno HAVING agv>90;

  • 姓“小”的学生信息

    SELECT * from student where sname like "小%";

  • 查询所有学生的语文数学成绩

    SELECT student.sname,project.pname,score.score
    FROM student,project,score
    where student.sno=score.sno and score.pno=project.pno and project.pname in("语文","英语");

  • 总分高于200的学生

    SELECT student.sname,sum(score.score) as res
    from student,score
    where student.sno=score.sno GROUP BY score.sno HAVING res>200;

  • 最低分低于60的学生

    SELECT student.sname,MIN(score.score) as res
    from student,score
    where student.sno=score.sno GROUP BY score.sno HAVING res<60;

  • 最高分高于90的学生

    SELECT student.sname,MAX(score.score) as res
    from student,score
    where student.sno=score.sno GROUP BY score.sno HAVING res>90;

  • 桂花老师教的学生中该科分数高于70的学生

    SELECT sname
    from score,student
    WHERE pno in(
    select project.pno
    from project,teacher
    where project.tno=teacher.tno and teacher.tname="桂花")
    and score>70 and score.sno=student.sno;

  • 001课程比002课程成绩高的学生的学号

    SELECT sc1.sno
    from score sc1,score sc2
    where sc1.sno=sc2.sno and sc1.pno='001' and sc2.pno='002' and sc1.score>sc2.score;

  • 没上过大力老师课的学生

    select sname,sno
    from student
    WHERE sno not in(
    select sno
    from score,teacher,project
    where teacher.tname="大力" and teacher.tno=project.tno and project.pno=score.pno);

  • 所有同学的学号,姓名,选课数,总成绩

    SELECT student.sno,student.sname,COUNT(score.pno) as "选课数",sum(score.score)
    from student,score
    WHERE student.sno=score.sno GROUP BY student.sno;

  • 学过001课程和002课程的学生的学号和姓名

    SELECT student.sno,student.sname
    FROM score sc1,score sc2,student
    WHERE student.sno=sc1.sno and sc1.pno='001' and sc2.pno='002' GROUP BY student.sno;

  • 翠花老师所交所有课的同学的学号和姓名

    SELECT student.sno,student.sname
    FROM student,teacher,project,score
    WHERE student.sno=score.sno and project.pno=score.pno and teacher.tno=project.tno and teacher.tname="桂花" GROUP BY student.sno;
    
  • 所有课程成绩都小于80分的学号和姓名

    SELECT student.sno,student.sname
    FROM student
    where student.sno not in(
    SELECT student.sno
    FROM score,student
    where score.score>=90 and score.sno=student.sno GROUP BY student.sno);

  • 没有学全所有课的同学的学号和姓名

-全班所有学生的考试科目数和平均成绩

SELECT
student.sno,
COUNT(score.score),
SUM(score.score) / COUNT(score.score)
FROM
student
LEFT JOIN score ON student.sno = score.sno
GROUP BY
student.sno;