常见的sql 笔试题

最新推荐文章于 2025-05-29 14:09:48 发布

原创最新推荐文章于 2025-05-29 14:09:48 发布 · 565 阅读

0 ·

CC 4.0 BY-SA版权

博客聚焦常见的SQL笔试题，涵盖信息技术领域中数据库相关的知识，有助于了解SQL在实际笔试中的考查要点，提升对SQL的应用能力和应对笔试的技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 各个年级每门课程 选课数量
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