常见的sql 笔试题

-- 各个年级每门课程 选课数量
SELECT count(1) as number, cou.course_name,grade_name
from tb_elective ele
LEFT JOIN tb_course cou ON cou.course_id = ele.course_id
LEFT JOIN tb_student stu ON ele.course_id = stu.stu_id
LEFT JOIN tb_grade grade ON grade.grade_id = stu.fs_grade_id
GROUP BY cou.course_name,grade_name

-- 各个年级没有选课的学生数
SELECT count(1) as number, grade.grade_name FROM 
tb_student a
LEFT JOIN tb_grade grade ON grade.grade_id = a.fs_grade_id
where a.stu_id not in (
        SELECT DISTINCT b.stu_id FROM tb_elective b
)
GROUP BY grade.grade_name

-- 删除表中重复的一个字段 并且保留最小的哪一个字段值

DELETE FROM tb_student WHERE stu_id in(

SELECT stu_id 
FROM tb_student 
WHERE name in (
  SELECT stu.name FROM
  tb_student as stu
  GROUP BY stu.name
  HAVING count(stu.name) >1
  ORDER BY count(stu.name) DESC
) AND
stu_id NOT in (
   SELECT min(stu.stu_id) FROM
   tb_student as stu
   GROUP BY stu.name
   HAVING count(stu.name) >1
   ORDER BY min(stu.stu_id) ASC
) 
)

SELECT * FROM tb_student 
(
SELECT stu_id 
FROM tb_student 
WHERE name in (
  SELECT stu.name FROM
  tb_student as stu
  GROUP BY stu.name
  HAVING count(stu.name) >1
  ORDER BY count(stu.name) DESC
) AND
stu_id NOT in (
   SELECT min(stu.stu_id) FROM
   tb_student as stu
   GROUP BY stu.name
   HAVING count(stu.name) >1
   ORDER BY min(stu.stu_id) ASC
) as ttt
)

--查询分数都大于80分的学生
select s.name
from tb_student s
group by s.name
having min(s.score) >80