练习用到的表格相关信息:
总的表格:
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