常见的sql 笔试题

博客聚焦常见的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








 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值