【绝对不要错过的sql面试题--2022年】

create table s (
sno varchar(10) primary key not null,
sname varchar(10),
sage int ,
ssex varchar(5))

insert into s values (‘03’,‘石头满’,28,‘女’)
insert into s values (‘02’,‘徐志勇’,16,‘男’)
insert into s values (‘01’,‘田丹’,36,‘男’)
insert into s values (‘04’,‘许新宇’,20,‘女’)
insert into s values (‘05’,‘王测验’,16,‘男’)
insert into s values (‘06’,‘餐含混’,36,‘男’)

create table sc (
sno varchar(10) not null,
cno varchar(10),
grade int ,
)

insert into sc values (‘03’,‘001’,60)
insert into sc values (‘02’,‘002’,75)
insert into sc values (‘01’,‘003’,86)
insert into sc values (‘04’,‘007’,99)
insert into sc values (‘05’,‘008’,98)
insert into sc values (‘06’,‘009’,88)
insert into sc values (‘03’,‘001’,70)
insert into sc values (‘02’,‘001’,85)
insert into sc values (‘01’,‘002’,76)

create table c (
cno varchar(10) primary key not null,
cname varchar(10),
techer varchar(10)
)
insert into c values (‘001’,‘语文’,‘翟东发’)
insert into c values (‘002’,‘数学’,‘孙晓飞’)
insert into c values (‘003’,‘英语’,‘网经书’)
insert into c values (‘007’,‘物理’,‘个连锁’)
insert into c values (‘008’,‘化学’,‘蔡建数’)
insert into c values (‘009’,‘生物’,‘们仿若’)

select * from s
select * from sc
select * from c

–1.求年龄大于所有女同学年龄的男学生姓名和年龄。
select s.sname,s.sage from s where ssex=‘男’ and sage>(select max(sage) from s where ssex=‘女’)

–2.求年龄大于女同学平均年龄的男学生姓名和年龄
select sname,sage from s where ssex=‘男’ and sage>(select avg(sage) from s where ssex=‘女’)

–3.在 SC 中检索成绩为空值的学生学号和课程号
select sno,cno from sc where grade is null

–4.检索姓名以 WANG 打头的所有学生的姓名和年龄
select sname,sage from s where sname like ‘%WANG%’;

–5.检索学号比 WANG同学大，而年龄比他小的学生姓名。
select a.sname from s ,(select * from s where sname like ‘%王%’) a
where s.sno>a.sno and a.sage<s.sage;

–6.统计每门课程的学生选修人数 （超过 2 人的课程才统计） 。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
select a.cno 课程号,count(1) 选修人数
from sc a inner join c b on a.cno = b.cno
group by a.cno having count(1)>2 order by count(1) desc ,a.cno asc

–7.求翟老师所授课程的每门课程的学生平均成绩。
select c.cname 课程名字,avg(sc.grade) 平均成绩
from sc join c on sc.cno=c.cno
where c.techer like ‘%翟%’ group by c.cname

–8.求选修 001 课程的学生的平均年龄。
select avg(a.sage) 平均年龄
from s a join sc b on a.sno=b.sno join c on b.cno=c.cno
where b.cno=‘001’;

–9．统计学生姓名和各自选修的课程门数。
select s.sname 学生姓名,count(1) 课程门数
from s join sc a on s.sno=a.sno
group by sname having count(1)>=1 order by count(1)