欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

2018年最新关于sql查询的面试笔试题

程序员文章站 2022-07-05 23:13:09
一、试用sql查询语句表达下列对教学中三个基本表 s、sc 、c 的查询: s(sno,sname,sage,ssex) 各字段表示学号,姓名,年龄,性别 sc(sno,cno,grade) 各字段...

一、试用sql查询语句表达下列对教学中三个基本表 s、sc 、c 的查询:

s(sno,sname,sage,ssex) 各字段表示学号,姓名,年龄,性别

sc(sno,cno,grade) 各字段表示学号,课程号,成绩c(cno,cname, teacher) 各字段表示课程号,课程名和教师名 其 中 sage, grade 是数值型,其他均为字符型。

要求用 sql 查询语句实现如下处理:

1 .统计有学生选修的课程门数。

2 .求选修 c4 课程的学生的平均年龄。

3 . 求 liu 老师所授课程的每门课程的学生平均成绩。

4 .统计每门课程的学生选修人数 (超过 2 人的课程才统计) 。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。

5 . 检索学号比 wang 同学大,而年龄比他小的学生姓名。

6 .检索姓名以 wang 打头的所有学生的姓名和年龄。

7 .在 sc 中检索成绩为空值的学生学号和课程号。

8 . 求年龄大于女同学平均年龄的男学生姓名和年龄。

9 .求年龄大于所有女同学年龄的男学生姓名和年龄。

其中涉及单表题: 1.4.6.7

参考答案:

1.统计有学生选修的课程门数。

select count(distinct cno) from sc

2 .求选修 c4 课程的学生的平均年龄。

select avg(sage )

froms where sno

in(select sno from sc where cno='4')

或者,

select avg(sage)

from s,sc wheres.sno=sc.sno and cno='4'

3 .求liu 老师所授课程的每门课程的学生平均成绩。

select avg(grade)

from sc join c on sc.cno=c.cno where teacher='liu'

group by c.cno

另:

selectcname,avg(grade) from sc ,c where sc.cno=c.cno and teacher='liu'

group by c.cno,cname

4 .统计每门课程的学生选修人数 (超过 2 人的课程才统计) 。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。

selectdistinct cno,count(sno) from sc

groupby cno having count(sno)>2

order by 2 desc, cno asc

或:

select distinct cno,count(sno) as 人数

fromsc group by cno

having count(sno)>2

order by 人 数 desc, cno asc

5 .检索学号比 wang 同学大,而年龄比他小的学生姓名。

selectx.sname from s as x, s as y

where y .sname='wang' and x.sno>y.sno and x.sage

或:

select sname

from s

where sno>(select sno from s where sname='wang') andsage<(select sage from s where sname='wang')

6 .检索姓名以 wang 打头的所有学生的姓名和年龄。

select sname,sage from s

where sname like 'wang%'

7.在 sc 中检索成绩为空值的学生学号和课程号。

select sno,cnofrom sc where grade is null

8 .求年龄大于女同学平均年龄的男学生姓名和年龄。 select sname,sage

from s

where ssex=' 男'

and sage>(selectavg(sage) from s where ssex='女')

9 .求年龄大于所有女同学年龄的男学生姓名和年龄。


selectsname,sage from s as x

where x.ssex=' 男'and x.sage >all (select sage froms as y where

y.ssex=' 女')

二、试用 sql 更新语句表达对教学数据库中三个基本表 s、

sc 、c的各个更新操作:

要求用 sql 更新语句实现如下处理:

1.往基本表 s 中插入一个学生元组( ‘ s9’,‘ wu ’,18 )。

2 .在基本表 s 中检索每一门课程成绩都大于等于 80 分的学生学号、姓名和性别, 并把检索到的值送往另一个已存在的基本表 s1 ( sno , sname , ssex )。

3 .在基本表 sc 中删除尚无成绩的选课元组。

4 .把wang 同学的学习选课和成绩全部删去。

5 .把选修数据库原理课不及格的成绩全改为空值。

6 .把低于总平均成绩的女同学成绩提高 5% 。

7 .在基本表 sc 中修改 4 号课程的成绩,若成绩小于等于 75 分时提高 5% , 若成绩大于 75 分时提高 4% (用两个 update 语句实现)。

参考答案:

1.往基本表 s 中插入一个学生元组( ‘ s9’,‘ wu ’,18 )。

insert into s(sno,sname,sage) values('59','wu',18)

2 .在基本表 s 中检索每一门课程成绩都大于等于 80 分的学生学号、姓名和性别, 并把检索到的值送往另一个已存在的基本表 s1 ( sno , sanme , ssex )。

select sno,sname,ssex intos1 from student delete from s1

insert into s1(sno,sname,ssex) select sno,sname,ssex

froms where not exists(select * from sc where grade<80 and s.sno=sc.sno)

select * from s1

考虑:以上会有什么问题?

insertinto s1(sno,sname,ssex) select sno,sname,ssex

from s where not exists(select * from sc where

grade<80 and s.sno=sc.sno or s.sno=sc.sno and gradeis null) and sno in (select sno from sc)


3 .在基本表 sc 中删除尚无成绩的选课元组。

delete from scwhere grade is null

4 .把wang 同学的学习选课和成绩全部删去。

deletefrom sc where sno in(select sno from s

where sname='wang')

5 .把选修数据库原理课不及格的成绩全改为空值。 update sc set grade=null

where grade<60 and cno in(select cno from c

where cname=' 数据库原理 ')

6 .把低于总平均成绩的女同学成绩提高 5% 。

update sc

setgrade=grade*1.05

where grade<(select avg(grade) from sc)

and sno in (select sno from swhere ssex=' 女')

7 .在基本表 sc 中修改 4 号课程的成绩,若成绩小于等于 75 分时提高 5% , 若成绩大于 75 分时提高 4% (用两个 update 语句实现)。

update sc

setgrade=grade*1.05 where cno='4' and grade<=75 update sc

set grade=grade*1.04 where cno='4' and grade>75

三、问题描述:为管理岗位业务信息,建立 3 个表 :

s (sno,sn,sd,sa) sno,sn,sd,sa 分别代表学号、学员姓名、所属单位、学员年龄

c (cno,cn ) cno,cn 分别代表课程编号、课程名称

sc ( sno,cno,g ) sno,cno,g 分别代表学号、所选修的课程编号、学习成绩

要求实现如下 5 个处理:

1.使用标准 sql 嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

2.使用标准 sql 嵌套语句查询选修课程编号为’ c2 ’的学员姓名和所属单位

3.使用标准 sql 嵌套语句查询不选修课程编号为’ c5 ’的学员姓名和所属单位


4.使用标准 sql 嵌套语句查询只选修了一门课程的学员姓名和所属单位

5.查询选修了课程的学员人数

6.查询选修课程超过 5 门的学员学号和所属单位

参考答案:

1 .使用标准 sql 嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

select sn,sd from s where [sno] in(

select [sno] from c,sc

where c.[cno]=sc.[cno] and cn=n' 税收基础')

2 .使用标准 sql 嵌套语句查询选修课程编号为’ c2 ’的学员姓名和所属单位

selects.sn,s.sd from s,sc where s.[sno]=sc.[sno]

andsc.[cno]='c2'

3 .使用标准 sql 嵌套语句查询不选修课程编号为’ c5 ’的学员姓名和所属单位

select sn,sd from s where [sno] not in(

select [sno] from sc

where[cno]='c5')

4 .使用标准 sql 嵌套语句查询只选修了一门课程的学员姓名和所属单位

select sn,sd from s where [sno] in(

select [sno] from sc inner join c on sc.[cno]=c.[cno] group by [sno]

havingcount(*)=1)

5.查询选修了课程的学员人数

select 学员人数 =count(distinct[sno]) from sc


6 .查询选修课程超过 5 门的学员学号和所属单位

select sn,sd from s where [sno] in(

select [sno] from sc group by [sno]

having count(distinct [cno])>5)

四、问题描述:已知关系模式:

s(sno,sname ) 学生关系。 sno 为学号, sname 为姓名

c (cno,cname,teacher) 课程关系。 cno 为课程号, cname 为课程名,

teacher 为任课教师

sc(sno,cno,grade) 选课关系。 grade 为成绩

要求实现如下 5 个处理:

1 . 找出没有选修过“李明”老师讲授课程的所有学生姓名

2 .列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

3 . 列出既学过“ 1 ”号课程,又学过“ 2 ”号课程的所有学生姓名

4 .列出“ 1 ”号课成绩比“ 04010002 ”号同学该门课成绩高的所有学生的学号

5 . 列出“ 1 ”号课成绩比“ 2 ”号课成绩高的所有学生的学号及其“ 1 ”号课和“ 2 ”号课的成绩

参考答案:

1 .找出没有选修过“李明”老师讲授课程的所有学生姓名

select sname from swhere not exists (select * from c,sc where c.cno=sc.cno and c.teacher=n' 李明 ' and s.sno=sc.sno)

参考: select sno,sname from s where sno not in

(select sno from sc,c where c.cno=sc.cno andc.teacher=n'liu')

2 .列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

select s.sno,sname,avg_grade=avg(sc.grade) from s,sc

where grade<60 and s.sno=sc.sno group by s.sno,s.sname

havingcount(distinct cno)>=2


select s.sno,s.sname,avg_grade=avg(sc.grade) from s,sc,(

select sno from sc

where grade<60

group by sno

havingcount(distinct cno)>=2

)a where s.sno=a.sno and sc.sno=a.sno group by s.sno,s.sname

3.列出既学过“ 1 ”号课程,又学过“ 2 ”号课程的所有学生姓名select s.sno,s.sname

from s,sc

where s.sno=sc.sno and cno='1' and s.sno in (select s.snofrom s,sc

where s.sno=sc.sno and cno='2')

select s.sno,s.sname from s,(

select sc.sno from sc,c

where sc.cno=c.cno

and c.cno in('1','2') group by sno

havingcount(distinct c.cno)=2

)sc wheres.sno=sc.sno

4 。列出“ 1 ”号课成绩比“ 04010002 ”号同学该门课成绩高的所有学生的学号

select s.sno,s.sname from s,sc

wheresc.cno='1'and sc.sno=s.sno

andgrade>(select grade from s,sc

wheres.sno='04010002'and sc.cno='1'and sc.sno=s.sno)

5 。列出“ 1 ”号课成绩比“ 2 ”号课成绩高的所有学生的学号及其“ 1 ”号课和“ 2 ”号课的成绩

select sc1.sno,[1 号课成绩 ]=sc1.grade,[2 号课成绩 ]=sc2.grade fromsc sc1,sc sc2

where sc1.cno='1' and sc2.cno='2'


andsc1.sno=sc2.sno

andsc1.grade>sc2.grade

四、通配符理解题(请说出下列通配符的含义)

1.like 'mc%' 将搜索以字母 mc 开头的所有字符串(如 mcbadden)。2.like '%inger' 将搜索以字母 inger 结尾的所有字符串(如 ringer 、stringer )。

3.like '%en%' 将搜索在任何位置包含字母 en 的所有字符串(如 bennet 、

green、mcbadden)。

4.like '_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称(如

cheryl、sheryl )。

5.like '[m-z]inger' 将搜索以字符串 inger 结尾、以从 m 到 z 的任何单个字母开头的所有名称(如 ringer )。

6.like 'm[^c]%' 将搜索以字母 m 开头,并且第二个字母不是 c 的所有名称

(如 macfeather )。

7.like '5[%]' -- 5%

8.like '5%' -- 5 后跟 0 个或更多字符的字符串

9.like '[_]n' -- _n

10.like '_n' --an,in,on (and so on) 11.like '[a-cdf]' -- a, b, c, d, or f

12.like '[-acdf]' -- -, a, c, d, or f 13.like '[ [ ]' --[

14.like ']' -- ]