SQL笔试50题（上）

fireWang

发布于 2020-02-18 23:22:32

82000

代码可运行

文章被收录于专栏：零维领域零维领域

运行总次数：0

代码可运行

正文共：8202 字 26 图预计阅读时间：21 分钟

本文目录：

5.1 表结构
- 创建表以及插入数据
5.2 sql笔试50题前25题

5. SQL面试50题

本节内容，我们使用在入门内容部分介绍的在线SQL平台sql fiddle进行测试。

5.1 表结构

-- 创建数据库
create database school;
use school;

-- 建表
-- 学生表：学生编号,学生姓名, 出生年月,学生性别
create table Student(sid varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男');
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男');
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男');
insert into Student values('04' , N'李云' , '1990-08-06' , N'男');
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女');
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女');
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女');
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女');
-- 课程表：课程编号, 课程名称, 教师编号
create table Course(cid varchar(10),cname nvarchar(10),tid varchar(10));
insert into Course values('01' , N'语文' , '02');
insert into Course values('02' , N'数学' , '01');
insert into Course values('03' , N'英语' , '03');
-- 教师表：教师编号,教师姓名
create table Teacher(tid varchar(10),tname nvarchar(10));
insert into Teacher values('01' , N'张三');
insert into Teacher values('02' , N'李四');
insert into Teacher values('03' , N'王五');
-- 成绩表：学生编号,课程编号,分数
create table Score(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

table_scheme

建表完成以后我们查看下数据内容，之后做题的时候可以快速地判断正误。

-- 查看每个人的年龄，性别，三门课成绩
select
sid,sname,sage,ssex,[语文],[数学],[英语]
from
(
select a.sid,a.sname,a.sage,a.ssex,c.cname,b.score
  from Student a
  left join Score b 
  on a.sid=b.sid
  left join Course c
  on b.cid = c.cid
) source_table
pivot(
  sum(score) for
cname in (
  [语文],[数学],[英语]
)
     ) t

all_info

5.2 50题

1.查询“01”课程比“02”课程成绩高的所有学生的学号

select * from 
(select * from Score where Score.cid = '01') s1,
(select * from Score where Score.cid = '02') s2
where 
s1.sid = s2.sid and
s1.score > s2.score

sql50_1

2.查询平均成绩大于60分的同学的学号和平均成绩

SELECT sid,AVG( score )  as mean_score
FROM Score 
GROUP BY sid 
HAVING AVG( score ) > 60;

sql50_2

3.查询所有同学的学号、姓名、选课数、总成绩

SELECT a.sid,a.sname, 
count(b.cid) as '选课数', 
sum(b.score) as '总成绩'
FROM Student a
left join Score b
on a.sid = b.sid
group by a.sid,a.sname
order by a.sid

sql50_3

4.查询姓“李”的老师的个数；

SELECT 
count(1)
FROM Teacher
where tname like N'李%'  --建表时字段设置为了Unicode,因此查询也需要加上N

5.查询没学过“张三”老师课的同学的学号、姓名；

-- 子查询将张三老师课程的学生id找出来
SELECT 
sid, sname
FROM Student
where 
sid not in (
  select s.sid
  from Score s, Course c, Teacher t 
  where s.cid = c.cid
  and c.tid=t.tid 
  and t.tname=N'张三')

sql50_5

6.查询学过“张三”老师所教的课的同学的学号、姓名；

  select s.sid, st.sname
  from Score s, Course c, Teacher t ,Student st
  where s.cid = c.cid
  and c.tid=t.tid 
  and t.tname=N'张三'
  and s.sid = st.sid

sql50_6

7.查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；

select * from
Student where sid in 
(
select s1.sid from 
(select * from Score where Score.cid = '01') s1,
(select * from Score where Score.cid = '02') s2
where 
s1.sid = s2.sid)

sql50_7

8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；

-- 和第一题，第七题相似
select sid,sname from
Student where sid in 
(
select s1.sid from 
(select sid,score from Score where cid = '01') s1,
(select sid,score from Score where cid = '02') s2
where 
s1.sid = s2.sid and
s1.score < s2.score)

sql50_8

9.查询所有课程成绩小于60分的同学的学号、姓名；

SELECT t.sid, s.sname
FROM
    (SELECT DISTINCT sid
    FROM Score
    GROUP BY sid
    HAVING MAX(score) < 60) t
LEFT JOIN Student s
ON t.sid = s.sid

sql50_9

10.查询没有学全所有课的同学的学号、姓名

-- 利用第三题的选课数
SELECT a.sid,a.sname, 
count(b.cid) as '选课数'
FROM Student a
left join Score b
on a.sid = b.sid
group by a.sid,a.sname
having count(b.cid) <> (select count(distinct cid) from Course)
order by a.sid

sql50_10

11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名

select distinct st.sid,st.sname from
Score s, Student st
where st.sid = s.sid
and s.cid in 
(select s.cid from
Score s, Student st
where st.sid = s.sid
and st.sid = '01')
and st.sid <> '01'
order by st.sid

sql50_11

12.查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名

-- 此题和11题类似，在11题基础上加上课程数量的限制即可
select st.sid,st.sname from
Score s, Student st
where st.sid = s.sid
group by st.sid, st.sname
having count(s.cid) = 
(select count(s.cid) from
Score s, Student st
where st.sid = s.sid
and st.sid = '01')
and st.sid <> '01'
order by st.sid

sql50_12

13.把“Score”表中“张三”老师教的课的成绩都更改为此课程的平均成绩

-- update题

14.查询没学过"张三"老师讲授的任一门课程的学生姓名

-- 和第六题一样
SELECT 
sid, sname
FROM Student
where 
sid not in (
  select s.sid
  from Score s, Course c, Teacher t 
  where s.cid = c.cid
  and c.tid=t.tid 
  and t.tname=N'张三')

15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT 
s.sid, s.sname,AVG(sc.score) as mean_score
FROM Student s, Score sc
where 
s.sid = sc.sid
and sc.score < 60
group by s.sid, s.sname
having count(sc.cid) >1

sql50_15

16.检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT 
s.*, sc.score
FROM Student s, Score sc
where 
s.sid = sc.sid
and sc.cid = '01'
and sc.score < 60
order by sc.score desc

sql50_16

17.按平均成绩从高到低显示所有学生的平均成绩

SELECT 
s.sid,s.sname, AVG(sc.score) as mean_score
FROM Student s, Score sc
where 
s.sid = sc.sid
group by s.sid,s.sname
order by AVG(sc.score) desc

sql50_17

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率

select 
  s.cid, 
  c.cname, 
  max(s.score) as max_score,
  min(s.score) as min_score,
  AVG(s.score) as mean_score,
  AVG (case when s.score >= 60 then 1.0 else 0.0 end ) as passrate 
from Score s, Course c
where s.cid = c.cid
group by s.cid,c.cname

sql50_18

19.按各科平均成绩从低到高和及格率的百分数从高到低顺序

-- 就是第十八题的排序
select 
  s.cid, 
  c.cname, 
  AVG(s.score) as mean_score,
  AVG (case when s.score >= 60 then 1.0 else 0.0 end ) as passrate 
from Score s, Course c
where s.cid = c.cid
group by s.cid,c.cname
order by AVG(s.score) asc, AVG (case when s.score > 60 then 1.0 else 0.0 end ) desc

sql50_19

20.查询学生的总成绩并进行排名

-- 使用rank()进行排名
select 
  s.sid,
  s.sname,
  sum(sc.score) as total_score,
  rank() over(order by sum(sc.score) desc) as score_rank
from Student s, Score sc
where s.sid = sc.sid
group by  s.sid,  s.sname
order by sum(sc.score) desc

sql50_20

21.查询不同老师所教不同课程平均分从高到低显示

select 
  c.cname,
  t.tname,
  AVG(s.score) as mean_score
from Course c,Score s, Teacher t
where c.tid = t.tid
and c.cid = s.cid
group by c.cname,t.tname
order by AVG(s.score) desc

sql50_21

22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

-- row_number() over(partition by 分组字段 order by 排序字段 排序方式) as 别名
select * from (
select 
  sc.sid,
  s.sname,
  s.ssex,
  s.sage,
  c.cname,
  sc.score,
  ROW_NUMBER() over(partition BY sc.cid order by score desc) as myrank
from Score sc,Student s,Course c
where sc.sid = s.sid
and sc.cid = c.cid) t
where t.myrank in (2,3)

sql50_22

23.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

-- 有点琐碎，不知道有没有简便方法
select 
  c.cid,
  c.cname,
  SUM(case when sc.score >= 85 and sc.score <= 100 then 1.0 else 0.0 end ) as '[100-85]',
  SUM(case when sc.score >= 85 and sc.score <= 100 then 1.0 else 0.0 end ) / count(sc.sid) as '[100-85]百分比',
  SUM(case when sc.score >= 70 and sc.score <  85 then 1.0 else 0.0 end ) as '[85-70]',
  SUM(case when sc.score >= 70 and sc.score <  85 then 1.0 else 0.0 end )/ count(sc.sid) as '[85-70]百分比',
  SUM(case when sc.score >= 60 and sc.score <  70 then 1.0 else 0.0 end ) as '[70-60]',
  SUM(case when sc.score >= 60 and sc.score <  70 then 1.0 else 0.0 end )/ count(sc.sid) as '[70-60]百分比',
  SUM(case when sc.score >= 0 and sc.score  <  60 then 1.0 else 0.0 end ) as '[60-0]',
  SUM(case when sc.score >= 0 and sc.score  <  60 then 1.0 else 0.0 end ) / count(sc.sid) as '[60-0]百分比'
from Score sc,Course c
where c.cid =sc.cid
group by c.cid,c.cname

sql50_23

24.查询学生平均成绩及其名次

--这题和第二十题是一样的
select 
  s.sid,
  s.sname,
  AVG(sc.score) as mean_score,
  rank() over(order by AVG(sc.score) desc) as score_rank
from Student s, Score sc
where s.sid = sc.sid
group by  s.sid,  s.sname
order by AVG(sc.score) desc

sql50_24

25.查询各科成绩前三名的记录

-- 和第二十二题一样
-- row_number() over(partition by 分组字段 order by 排序字段 排序方式) as 别名
select * from (
select 
  sc.sid,
  s.sname,
  s.ssex,
  s.sage,
  c.cname,
  sc.score,
  ROW_NUMBER() over(partition BY sc.cid order by score desc) as myrank
from Score sc,Student s,Course c
where sc.sid = s.sid
and sc.cid = c.cid) t
where t.myrank <4