【sql语句练习】

码农-KK

已于 2022-03-14 14:43:37 修改

阅读量864

点赞数 1

文章标签： sql

于 2022-03-13 23:34:42 首次发布

本文链接：https://blog.csdn.net/m0_55830247/article/details/123468626

版权

练习用到的表格相关信息：
总的表格：
所有表格
hand_course表格：

hand_student表格
在这里插入图片描述
hand_student_core表格

hand_teacher表格

hand_teacher_emp表格

sql联系题目及参考解答：

查询没学过“谌燕”老师课的同学，显示（学号、姓名）

SELECT
	s.student_no,
	s.student_name 
FROM
	hand_student s 
WHERE
	s.STUDENT_NO NOT IN (
	SELECT
		s2.STUDENT_NO 
	FROM
		hand_student s2,
		hand_teacher t,
		hand_student_core s_c,
		hand_course c 
	WHERE
		s2.student_no = s_c.STUDENT_NO 
		AND s_c.course_no = c.COURSE_NO 
		AND c.TEACHER_NO = t.TEACHER_NO 
	AND t.TEACHER_NAME = '谌燕' 
	)
	
解法2：
SELECT s.student_no,s.student_name
from hand_student s
left join
(
select DISTINCT(s2.STUDENT_NO)
from hand_student s2,hand_teacher t,hand_student_core s_c,hand_course c
where s2.student_no = s_c.STUDENT_NO and s_c.course_no = c.COURSE_NO and c.TEACHER_NO = t.TEACHER_NO and t.TEACHER_NAME ='谌燕'
) s3
on s.STUDENT_NO=s3.STUDENT_NO
where s3.STUDENT_NO is null;

查询没有学全所有课的同学，显示（学号、姓名）

查询课程数：
select count(*)
from hand_course;
选了课没全选
select s.STUDENT_NO,s.STUDENT_NAME
from hand_student s,hand_student_core s_c
where s.STUDENT_NO=s_c.STUDENT_NO
GROUP BY s_c.STUDENT_NO
having count(s_c.COURSE_NO)<
(select count(*)
from hand_course)
压根没选课
select s.STUDENT_NO,s.STUDENT_NAME
from hand_student s left join hand_student_core s_c
on s.STUDENT_NO=s_c.STUDENT_NO
where s_c.STUDENT_NO is null;

解法2（将选课，没选课的一起统计）
select s.STUDENT_NO,s.STUDENT_NAME,COUNT(s_c.COURSE_NO)
from hand_student s left join hand_student_core s_c
on s.STUDENT_NO=s_c.STUDENT_NO
GROUP BY s.STUDENT_NO
having count(s_c.COURSE_NO)<
(select count(*)
from hand_course)

查询“c001”课程比“c002”课程成绩高的所有学生，显示（学号、姓名）

select s.STUDENT_NO,s.STUDENT_NAME
from hand_student s
where s.STUDENT_NO =(
select s_c.STUDENT_NO
from hand_student_core s_c join hand_student_core s_c1 on s_c1.STUDENT_NO= s_c.STUDENT_NO
where s_c.core > s_c1.core and s_c.COURSE_NO='c001' and s_c1.COURSE_NO='c002')

按各科平均成绩和及格率的百分数，按及格率高到低顺序，显示（课程号、平均分、及格率）

SELECT s_c.COURSE_NO,avg(s_c.CORE),sum(IF(s_c.CORE>=60,1,0))/count(*)*100 及格率
from hand_student_core s_c
GROUP BY COURSE_NO
ORDER BY 及格率 desc

1992 年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄）

select s.STUDENT_NO,s.STUDENT_NAME,s.STUDENT_AGE
from (select * from hand_student where STUDENT_AGE<YEAR(CURDATE())-1921) s
where s.STUDENT_AGE>=
(
select max(s1.STUDENT_AGE) from (select * from hand_student where STUDENT_AGE<YEAR(CURDATE())-1921) s1
)
or s.STUDENT_AGE<=
(
select min(s1.STUDENT_AGE) from (select * from hand_student where STUDENT_AGE<YEAR(CURDATE())-1921) s1
)

解法2
SELECT hs.student_no,
       hs.student_name,
       hs.student_age
  FROM hand_student hs,
       (SELECT MAX(hs.student_age) max_age,
               MIN(hs.student_age) min_age
          FROM hand_student hs
         WHERE 2022 - hs.student_age > 1992) hh
 WHERE hs.student_age = hh.max_age
    OR hs.student_age = hh.min_age;

统计列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60],纵轴为课程号、课程名称

select sc.COURSE_NO,c.COURSE_NAME,
sum(
IF(sc.CORE BETWEEN 85 and 100,1,0)
)as '[100-85]',
sum(
IF(sc.CORE BETWEEN 70 and 84,1,0)
)as '[85-70]',
sum(
IF(sc.CORE BETWEEN 60 and 69,1,0)
)as '[70-60]',
sum(
IF(sc.CORE < 60,1,0)
)as '[<60]'
from hand_student_core sc,hand_course c
where sc.COURSE_NO=c.COURSE_NO
GROUP BY sc.COURSE_NO,c.COURSE_NO;

查询各科成绩前三名的记录:(不考虑成绩并列情况)，显示（学号、课程号、分数）

SELECT * FROM (
              SELECT SC.STUDENT_NO,
              SC.COURSE_NO,
              SC.CORE,
              row_NUMBER() OVER(PARTITION BY SC.COURSE_NO ORDER BY SC.CORE DESC) ranking
              FROM HAND_STUDENT_CORE SC
							) sc2
WHERE sc2.ranking <= 3

查询选修“谌燕”老师所授课程的学生中，成绩最高的学生，显示（学号、姓名、课程名称、成绩）

SELECT s.student_no,
			 s.STUDENT_NAME,
       c.course_name,
       sc.core
  FROM hand_student_core sc
  JOIN hand_course c
    ON sc.course_no = c.course_no
	join hand_student s
	  on s.STUDENT_NO = sc.STUDENT_NO
  JOIN hand_teacher t
    ON c.teacher_no = t.teacher_no
 WHERE sc.core = (SELECT MAX(sc2.core)
                       FROM hand_student_core sc2
                      WHERE sc.course_no = sc2.course_no)
   AND t.teacher_name = '谌燕';

查询两门以上不及格课程的同学及平均成绩，显示（学号、姓名、平均成绩（保留两位小数））

SELECT s.STUDENT_NO,s.STUDENT_NAME,AVG(sc.CORE)
from hand_student s,hand_student_core sc
where s.STUDENT_NO=sc.STUDENT_NO
GROUP BY sc.STUDENT_NO
HAVING sum(if(sc.CORE<60,1,0))>1

查询姓氏数量最多的学生名单，显示（学号、姓名、人数）

SELECT s.STUDENT_NO,s2.name1,s2.nums
from hand_student s,
(SELECT
left(s.student_name, 1) name1,
COUNT(*) nums
from hand_student s
GROUP BY name1) s2
where left(s.STUDENT_NAME,1)=s2.name1 and s2.nums=
(
SELECT MAX(s3.nums)
from((SELECT
left(s.student_name, 1) name1,
COUNT(*) nums
from hand_student s
GROUP BY name1)) s3
)

解法2：
SELECT
	s.STUDENT_NO,s.STUDENT_NAME,t.nums 
FROM
	(
	SELECT
		left( s.student_name, 1) name1,
		COUNT(*) nums,
		dense_rank() OVER ( ORDER BY count(*) DESC ) ranks 
	FROM
		hand_student s
	GROUP BY
		left( s.student_name, 1) 
	) t, hand_student s
WHERE
	t.ranks = 1 
	AND left( s.STUDENT_NAME, 1)= t.name1;

查询课程名称为“J2SE”的学生成绩信息，90 以上为“优秀”、80-90 为“良好”、 60-80 为“及格”、60 分以下为“不及格”，显示（学号、姓名、课程名称、成绩、等级）

SELECT s.STUDENT_NO,s.STUDENT_NAME,c.COURSE_NAME, sc.CORE,
	case when sc.core>90 then '优秀'
	     when sc.core<=90 and sc.core>80 then '良好'
		   when sc.core<=80 and sc.core>=60 then '及格'
		   else '不及格' END grade
FROM hand_student s,hand_course c,hand_student_core sc
where s.STUDENT_NO=sc.STUDENT_NO and sc.COURSE_NO=c.COURSE_NO and c.COURSE_NAME='J2SE';

查询教师“胡明星”的所有主管及姓名，显示（教师编号、教师名称、主管编号、主管名称）

SELECT t.TEACHER_NO,t.TEACHER_NAME,t2.TEACHER_NO,t2.TEACHER_NAME
from hand_teacher t,hand_teacher t2
where t.MANAGER_NO=t2.TEACHER_NO and t.TEACHER_NAME='胡明星'

查询分数高于课程“J2SE”的所有学生课程信息，显示（学号，姓名，课程名称、分数）

SELECT s.STUDENT_NO,s.STUDENT_NAME,c.COURSE_NAME,sc.CORE
from hand_student s,hand_course c,hand_student_core sc
where s.STUDENT_NO=sc.STUDENT_NO and c.COURSE_NO=sc.COURSE_NO and sc.CORE>
(
select max(sc1.core)
from hand_student_core sc1,hand_course c1
where sc1.COURSE_NO=c1.COURSE_NO and c1.COURSE_NAME='J2SE'
)

查询教师、课程及选课的学生数量,显示（教师名称、课程名称、选课数量）

只显老师教授的课程有学生选课的统计情况：
SELECT t.TEACHER_NAME,c.COURSE_NAME,count(*)
from hand_teacher t,hand_course c,hand_student_core sc
where t.TEACHER_NO=c.TEACHER_NO and c.COURSE_NO=sc.COURSE_NO
GROUP BY t.TEACHER_NO,c.COURSE_NO

显示老师教授所有课程的选课情况：
SELECT
	t.teacher_name,
	c.course_name,
	COUNT( STUDENT_NO ) stu_nums 
FROM
	hand_teacher t
	LEFT JOIN hand_course c ON c.teacher_no = t.teacher_no
	LEFT JOIN hand_student_core sc ON sc.course_no = c.course_no 
GROUP BY
	t.teacher_name,
	c.course_name