请教一个面试题-mysql

面试专辑
,
1

共涉及三张表，具体如下，请根据信息回答题1-3，

Student学生表：student_id学号，student_name学生姓名，student_class学生班级，

student_sex学生性别：

STUDENT_SCORE学生成绩表：student_id学号，course._name课程，course_score课程成绩：

student_family学生家庭信息表：student_id学号，address家庭地址，telphone家庭电话

1、请查询出A班各学生的学号、学生姓名、学生班级、各课程、各课程成绩，并且先按课程升序排序，然后按课程成绩降序排序

2、请从学生成绩表STUDENT_SCORE中统计出不及格（成绩小于60）人数大于3人的课程及其不及格人数。如从学生成绩表可知语文不及格2人，数学不及格4人，英语不及格4人，那只要如下图输出即可。
2

3、请列出学生的学号、学生姓名、学生班级、学生性别、家庭住址、家庭电话，注意从学生家庭信息表student_family可以看出不是每一个学生都有家庭住址、家庭电话，要求学生的学号、学生姓名、学生班级、学生性别都必须列出，当没有家庭住址、家庭电话时，查询显示·暂无’
3

4、公司有人员信息表：tablel(ep_id,name,age),请用sql列出同名的人员，显示人员名称及现次数。

2

第1题： select m.student.id,student_name,student_class,course_name,course_score from Student m left join STUDENT_SCORE n on m.student.id=n.student.id where student_class='A' order by course_name asc,course_score desc;

1 Like
3

第二题：select distinct course_name,count(*) from STUDENT_SCORE where course_score<60 group by course_name having count(*)>3;

1 Like
4

select A.student_id,A.student_name,A.student_class,B.course_name,B.course_score
from (select * from student where student_class=‘A’) as A
inner join student_score as B on A.student_id=B.student_id
group by A.student_id,A.student_name,A.student_class,B.course_name,B.course_score
order by B.course_name, B.course_score desc;

select course_name,sum(case when course_score < 60 then 1 else 0 end) as s from student_score
group by course_name having s>3;

select A.student_id,A.student_name,
A.student_class,A.student_sex,
(case when B.address is NULL or length(B.address)=0 then ‘暂无’ else B.address end) as address,
(case when B.telphone is NULL or length(B.telphone)=0 then ‘暂无’ else B.telphone end) as telphone,
from student as A inner join student_family as B on A.student_id=B.student_id;

人员名称重复数量

select name,count(1) as s from table1 group by name having s>1;

1 Like
5

十分感谢 :smiling_face_with_three_hearts:

6

十分感谢 :smiling_face_with_three_hearts: :wave:

7

1,
select s.student_id, s.student_name, s.student_class, c.course._name , c.course_score
from Student s
left join
STUDENT_SCORE c
on s.student_id=c.student_id
order by c.course._name ASC, c.course_score DESC;

2,select course._name, count(student_id)
from STUDENT_SCORE where course_score<60
having count(student_id) >3