SQL 必备面试题库
导读
最近在加强训练SQL 语句,颇有体会,想把我的感悟分享给大家
抽空间在阅读《百年孤独》,为什么选这本书呢? 是因为看完了《霍乱时期的爱情》,觉得作者加西亚·马尔克斯 写的还不错,想多看看他的作品。希望有读者可以推荐推荐一些好书。
[读书可以经历一千种人生,不读书的人只能活一次]————多看题能拓展思维
试题开始
学生表 Student
课程表 Course
老师表 Teacher
成绩表 SC
下面展示 试题及答案
在数据库中建立以上 4 表,并通过 SQL 语句完成数据插入
create table student(Sno int,Sname varchar(50) not null,Sage int,Ssex varchar(10) not null);
insert into student values(1001,'张宇',12,'男'),(1002,'张梅',18,'女'),(1003,'王鑫',16,'男'),(1004,'马东东',13,'男'),
(1005,'孙子涵',12,'女'),(1006,'钱一',12,'男'),(1007,'赵贺',15,'男'),(1008,'周雪',16,'女');
select * from student;
--Teacher
create table teacher(tno int,tname varchar(50) not null);
insert into teacher values(1,'李雷'),(2,'李纯'),(3,'胡悦悦'),(4,'朱清时'),
(5,'赛鸿飞'),(6,'宋三东');
select * from teacher;
--course
create table course(cno varchar(255),cname varchar(50) not null,tno int);
insert into course values('001','PHP',1),('002','C',1),('003','C++',2),('004','JAVA',3),
('005','Python',4),('006','R',5),('007','HTML',6);
select * from course;
--SC
select * into SC from 成绩表_二阶段; ---无法为你们提供SC的数据
select * from sc;
查询“001”课程比“002”课程成绩高的所有学生的学号
select sc_1.sno from sc sc_1,sc sc_2 where sc_1.sno=sc_2.sno and sc_1.cno='001' and sc_2.cno='002' and sc_1.grade > sc_2.grade;
查询平均成绩大于 60 分的同学的学号和平均成绩
select sno,avg(grade) from sc group by sno having avg(grade)>60;
查询所有同学的学号、姓名、选课数、总成绩
select sc.sno,st.sname,count(cno),sum(grade)from sc,student st where sc.sno = st.sno group by sc.sno,st.sname;
查询姓“李”的老师的个数
select count(tno) from teacher where tname like '李%';
查询学过“李纯”老师课的同学的学号、姓名 推荐第二种
法一:
SELECT sc.sno,st.sname from sc,student st WHERE sc.sno = st.sno and sc.cno in
(SELECT cno FROM course c ,teacher t WHERE c.tno=t.tno AND t.tname='李纯');
法二:
SELECT st.sno,sname FROM course c LEFT JOIN teacher t on t.tno=c.tno
LEFT JOIN sc on sc.cno = c.cno
LEFT JOIN student st on st.sno = sc.sno
WHERE t.tname ='李纯' ;
查询没学过“李雷”老师课的同学的学号、姓名
SELECT sno ,sname FROM student where sno not in(
SELECT DISTINCT sc.sno from sc where sc.cno IN(
SELECT cno FROM course c ,teacher t WHERE c.tno=t.tno AND t.tname='李雷'));
查询学过“001”并且也学过编号“002”课程的同学的 学号、姓名
SELECT sno,Sname FROM student WHERE sno in (SELECT t1.sno FROM
(SELECT * FROM sc WHERE cno = '001')t1,
(SELECT * FROM sc WHERE cno = '002')t2 WHERE t1.sno = t2.sno);
查询课程编号“002”的成绩比课程编号“001”课程低 的所有同学的学号、姓名
SELECT sno,Sname FROM student where sno in( SELECT t1.sno FROM (SELECT * from sc where cno ='002')t1,
(SELECT * from sc where cno ='001')t2 where t1.sno =t2.sno AND t1.grade >t2.grade);
查询学过“李雷”老师所教的所有课的同学的学号、姓 名
select DISTINCT st.sno, st.sname from student st, sc
where st.sno = sc.sno and sc.cno in
(select c.cno from course c, teacher t where c.tno = t.tno and t.tname = '李雷');
查询各科成绩最高和最低的分:以如下形式显示:课程 ID,最高分,最低分
SELECT cno ,MAX(grade),MIN(grade) FROM sc GROUP BY cno;
查询没有学全所有课的同学的学号、姓名
SELECT st.sno,st.sname
FROM student st,sc
WHERE st.sno = sc.sno
GROUP BY st.sno,st.sname
HAVING COUNT(sc.cno) < (SELECT COUNT(*) FROM course c);
查询至少有一门课与学号为“1001”的同学所学相同的 同学的学号和姓名
select sno,sname from student where sno in(select sno from sc where cno in(select cno from sc where sno = '1001') and sno!='1001');
查询至少学过学号为“1001”同学所学的一门课的其他 同学学号和姓名
select sno,sname from student where sno in(select sno from sc where cno in(select cno from sc where sno = '1001') and sno!='1001');
按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT sc.cno AS 课程号,
max(c.cname)AS 课程名,
isnull(AVG(grade),0) AS 平均成绩,
100 * SUM(CASE WHEN isnull(grade,0)>=60 THEN 1 ELSE 0 END)/count(1) AS 及格率
FROM sc, course c
where sc.cno = c.cno
GROUP BY sc.cno
ORDER BY 平均成绩,及格率 desc
删除学习“李纯”老师课的 SC 表记录
delete from sc where cno in(
select cno from teacher t,course cs where t.tno=cs.tno and t.tname = '李纯'
);
把成绩表中朱清时老师教的课的成绩都改为此课程的平时成绩
update sc set grade = (select avg(sc_2.grade) from sc sc_2 where sc_2.cno=sc.cno)
from course c,teacher t where c.cno=sc.cno and c.tno=t.tno and t.tname='朱清时';
查询不同课程成绩相同的同学的学号、课程号、学生成绩(学号相同,学号不同两种情况)
SELECT distinct sc1.Sno,sc1.Cno,sc1.grade,sc2.Sno,sc2.Cno,sc2.grade
FROM course c
LEFT JOIN sc sc1 on c.cno = sc1.cno
LEFT JOIN sc sc2 on sc1.grade =sc2.grade
LEFT JOIN student st on st.sno = sc2.sno WHERE sc1.grade = sc2.grade
AND sc1.sno <> sc2.sno AND sc1.cno <> sc2.cno;(学号不同)
SELECT distinct sc1.Sno,sc1.Cno,sc1.grade,sc2.Sno,sc2.Cno,sc2.grade
FROM course c
LEFT JOIN sc sc1 on c.cno = sc1.cno
LEFT JOIN sc sc2 on sc1.grade =sc2.grade
LEFT JOIN student st on st.sno = sc2.sno WHERE sc1.grade = sc2.grade
AND sc1.sno = sc2.sno AND sc1.cno <> sc2.cno;(学号相同)
每位老师教课学生中成绩最好的学生姓名,学号,课程 名,老师名
还有其他方法
SELECT st.sno,sname,cname,tname FROM course c LEFT JOIN teacher t on t.tno=c.tno
LEFT JOIN (SELECT cno,max(grade) as 最高分 from sc GROUP BY cno) sc1 on c.cno = sc1.cno
LEFT JOIN sc sc2 on sc1.cno = sc2.cno AND sc1.[最高分] = sc2.grade
LEFT JOIN student st on st.sno = sc2.sno;
统计出选修人数最多的课程,需要包含课程名,课程编号,学生名称,分数四个字段
推荐第二种
法一:
SELECT DISTINCT cs.Cname,cs.Cno,st.Sname,SC.grade FROM course cs,student st,SC
where Sc.Sno=st.Sno and Sc.Cno=cs.Cno and Sc.Cno in (select Cno from (select Cno,count(*) cnt from SC group by Cno) a
where a.cnt = (select max(cnt) from (select Cno,count(*) cnt from SC group by Cno) a));
法二:
SELECT cname,c.cno,sname,grade from course c
LEFT JOIN sc on c.cno=sc.cno
LEFT JOIN (SELECT cno,COUNT(sno)cnt from sc GROUP BY cno) sc1 on c.cno = sc1.cno
inner JOIN (SELECT max(cnt) 最大 FROM (SELECT cno,COUNT(sno)cnt from sc GROUP BY cno)a) sc2 on sc2.最大=sc1.cnt
LEFT JOIN student st on st.sno = sc.sno;
按性别统计出每门课程男女选修的人数及成绩的平均 值,需要包含性别,课程名,人 数,总成绩,平均成绩 5 字段
SELECT st.ssex,cname,count(*) cnt,sum(grade) 总成绩,avg(grade) 平均成绩 from course c
LEFT JOIN sc on sc.cno= c.cno
LEFT JOIN student st on st.sno = sc.sno
group by st.ssex,cname
总结
我觉得这样的面试题给你,公司主要考察你的 **关联**掌握情况,希望我这份文档对你有用