你必知必会的sql练习

末蓝、 2023-07-15 13:56 63阅读 0赞

废话不多说；玩sql之前还是画一张关系图吧，便于更好的进行理解哈！

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNTY1MjY3_size_16_color_FFFFFF_t_70][]

## Part 1 练习题和参考解 ##

（1）查询“001”课程比“002”课程成绩低的所有学生的学号、001学科成绩、002学科成绩

SELECT 
    s1.StudentNo,
    s1.score AS '001',
    s2.score AS '002'
    FROM
       score s1,
    	 score s2
    WHERE
      s1.CourseNo = 1
    	AND
    	s2.CourseNo = 2
    	AND
    	s1.score < s2.score
      order  BY s1.StudentNo

（2）查询平均成绩大于60分的同学的学号和平均成绩

SELECT 
     s1.StudentNo,
     AVG(s1.score)
    FROM 
      score s1
    GROUP BY s1.StudentNo
    HAVING 
      AVG(s1.score) > 60

3.查询所有同学的学号、姓名、选课数、总成绩

SELECT 
      s1.StudentNo,
    	pe.`name`,
    	COUNt(s1.CourseNo),
    	SUM(s1.score)
    FROM
     score s1 
    LEFT JOIN student pe  on s1.StudentNo = pe.studentNo
    GROUP BY s1.StudentNo

4.查询姓“李”的老师的个数

SELECT 
        COUNT(*)
    FROM 
        teacher
    where 
        `name` like '李%'

5.查询没学过“叶平”老师课的同学的学号、姓名

select 
      s1.`name`,
    	s1.studentNo
    from 
      student s1
    where 
      s1.studentNo
    not in 
    (
      select 
    	  sc.StudentNo
      from
          score sc,
    	  course co,
    	  teacher te
      where 
    	  sc.courseNo = co.courseNo
      and 
          co.teacherNo = te.teacherNo
      and 
          te.name = '叶平'	 
    )

6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select 
      s1.studentNo,
    	s1.`name`
    from 
      student s1,
    	score sc
    where 
       s1.studentNo = sc.StudentNo
    	 and 
    	sc.CourseNo in (1,2)
    group by s1.studentNo
    having count(*) = 2

或者：

SELECT
      s1.StudentNo,
      stu1.name
    FROM
      score s1,
      student stu1
    WHERE
      s1.StudentNo = stu1.StudentNo
      AND
      s1.CourseNo = 1
      AND
      s1.StudentNo IN
      (
      SELECT
        s2.StudentNo
      FROM
        score s2
      WHERE
        s2.CourseNo = 2
      )

7.查询学过“叶平”老师所教的所有课的同学的学号、姓名

#这个地方就是 根据课程主键值的不同，那摩他们的和也一定不相同，
    #思路：查出叶平老师的课程的所有主键值和 如果和 学生所学的叶萍老师的逐渐和相等的话，就说明学了该老师的所偶课程
    select 
      st.studentNo,
    	st.name
    from 
      score sc,
      student st,
      course  co,
    	teacher te
    where 
      sc.StudentNo = st.studentNo
    	and 
    	co.courseNo = sc.CourseNo
    	and 
    	te.teacherNo = co.teacherNo
    	and 
    	te.`name` = '叶平'
    	GROUP BY
    	sc.StudentNo
    	
    	having sum(sc.CourseNo) = 
    	(
    	  select 
    	    sum(c2.courseNo)
    		from
    		 course c2,
    		 teacher t2
    		where 
    	   c2.teacherNo = t2.teacherNo
    		and 
    	   t2.name = '叶平' 
    	)

# 这个思路跟上面的一个类似，只不过是查询出叶平老师所教的数量之和 与 学生的所学课程之和 相同就代表学了该来时的所有课程
    select 
      st.studentNo,
    	st.name
    from 
      score sc,
      student st,
      course  co,
    	teacher te
    where 
      sc.StudentNo = st.studentNo
    	and 
    	co.courseNo = sc.CourseNo
    	and 
    	te.teacherNo = co.teacherNo
    	and 
    	te.`name` = '叶平'
    	GROUP BY
    	sc.StudentNo
    	having count(*) = 
    	(
    	  select 
    	   count(*)
    		from
    		 course c2,
    		 teacher t2
    		where 
    	   c2.teacherNo = t2.teacherNo
    		and 
    	   t2.name = '叶平' 
    	)

今天先写这么多，后面再补上！！！！

参考： 非常感谢[Dulk's Blog][Dulk_s Blog]大佬的帮助 [https://www.cnblogs.com/deng-cc/p/6515166.html][https_www.cnblogs.com_deng-cc_p_6515166.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNTY1MjY3_size_16_color_FFFFFF_t_70]: /images/20230528/20065874920b41a394a557147e297b96.png
[Dulk_s Blog]: https://www.cnblogs.com/deng-cc/
[https_www.cnblogs.com_deng-cc_p_6515166.html]: https://www.cnblogs.com/deng-cc/p/6515166.html