下面是oracle数据库的语法
SQL数据库面试题以及答案（50例题）
-
--可执行的oracle建表SQL:
-
--学生表
-
create
table Student
-
(S
# varchar2(100) ,
-
Sname
varchar2(
100),
-
Sage
int,
-
Ssex
varchar2(
100)
-
);
-
-
comment
on
table Student
is
'学生表';
-
comment
on
column Student.S
# is '学号';
-
comment
on
column Student.Sname
is
'学生姓名';
-
comment
on
column Student.Sage
is
'学生年龄';
-
comment
on
column Student.Ssex
is
'学生性别';
-
-
--课程表
-
create
table Course
-
(C
# varchar2(100) ,
-
Cname
varchar2(
100) ,
-
T
# varchar2(100)
-
);
-
-
comment
on
table Course
is
'课程表';
-
comment
on
column Course.C
# is '课程编号';
-
comment
on
column Course.Cname
is
'课程名称';
-
comment
on
column Course.T
# is '教师编号';
-
-
--成绩表
-
create
table SC
-
(S
# varchar2(100) ,
-
C
# varchar2(100) ,
-
score
number
-
);
-
-
comment
on
table SC
is
'成绩表';
-
comment
on
column SC.S
# is '学号';
-
comment
on
column SC.C
# is '课程编号';
-
comment
on
column SC.score
is
'成绩';
-
-
--教师表
-
create
table Teacher
-
(T
# varchar2(100) ,
-
Tname
varchar2(
100)
-
);
-
-
comment
on
table Teacher
is
'成绩表';
-
comment
on
column Teacher.T
# is '教师编号';
-
comment
on
column Teacher.Tname
is
'教师名字';
个人评价：这50套题很经典，都是一些常用的查询语句。有利于锻炼思维，理解关系数据库表数据如下：
-
--数据插入脚本
-
--Course-------------------------------
-
insert
into Course (C
#, CNAME, T#)
-
values (
'001',
'语文',
'T001');
-
-
insert
into Course (C
#, CNAME, T#)
-
values (
'002',
'数学',
'T002');
-
-
insert
into Course (C
#, CNAME, T#)
-
values (
'003',
'数据库',
'T003');
-
-
--SC-------------------------------
-
insert
into SC (S
#, C#, SCORE)
-
values (
'1001',
'001',
89);
-
-
insert
into SC (S
#, C#, SCORE)
-
values (
'1001',
'002',
92);
-
-
insert
into SC (S
#, C#, SCORE)
-
values (
'1002',
'003',
78);
-
-
insert
into SC (S
#, C#, SCORE)
-
values (
'1003',
'002',
87);
-
-
--Student---------------------------
-
-
insert
into Student (S
#, SNAME, SAGE, SSEX)
-
values (
'1001',
'王三',
18,
'M');
-
-
insert
into Student (S
#, SNAME, SAGE, SSEX)
-
values (
'1002',
'李四',
19,
'F');
-
-
insert
into Student (S
#, SNAME, SAGE, SSEX)
-
values (
'1003',
'亦一',
17,
'M');
-
-
--Teacher------------------------
-
-
insert
into Teacher (T
#, TNAME)
-
values (
'T001',
'王平');
-
-
insert
into Teacher (T
#, TNAME)
-
values (
'T002',
'孙仲谋');
-
-
insert
into Teacher (T
#, TNAME)
-
values (
'T003',
'叶平');
-
-
-------------------------------
问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号
-
select a.S# from (select S#,score from SC where C#='001')a,
-
(select s#,score from SC where c#='002')b Where a.score>b.score and a.s# = b.s#;
select S#, avg(score) from sc group by S# having avg(score)>60
-
select student.S_id, student.Sname,
count(sc.C_id),
sum(score)
from student
-
left
join SC
on student.S_id = SC.S_id
group
by Student.S_id, Sname
-
select
count(
distinct(Tname))
-
from teacher
-
where tname
like
'李%';
-
select student.S
#, student.Sname
-
from Student
-
where S
# not in (select distinct(SC.S#) from SC,Course,Teacher
-
where sc.c
#=course.c# AND teacher.T#=course.T# AND Teahcer.Tname ='叶平');
-
select S
#,Sname from Student
-
where S
# in (select S# from SC ,Course ,Teacher
-
where SC.C
#=Course.C# and Teacher.T#=Course.T#
-
and Teacher.Tname=
'叶平'
group
by S
#
-
having count(SC.C#)=(
select count(C#) from Course,Teacher
-
where Teacher.T#=Course.T
# and Tname='叶平'));
7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名：
-
select Student.S
#,Student.Sname
-
from Student,SC
where Student.S
#=SC.S#
-
and SC.C
#='001'and
-
exists(
Select *
from SC
as SC_2
where SC_2.S
#=SC.S# and SC_2.C#='002');
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：
-
Select S
#,Sname
-
from (
select Student.S
#,Student.Sname,score ,
-
(
select score
from SC SC_2
where SC_2.S
#=Student.S# and SC_2.C#='002') score2
-
from Student,SC
-
where Student.S
#=SC.S# and C#='001') S_2
-
where score2 < score;
-
select S
#, sname
-
from student
-
where s
# not in
-
(
select student.s
# from student, sc where s.s# = sc.s# and score>60);
-
select student.s
#, student.sname
-
from student, sc
-
where student.s
#=sc.s#
-
group
by student.s
#, student.sname
-
having
count(c
#)<(select count(c#) from course);
11、查询至少有一门课与学号为“1001”同学所学相同的同学的学号和姓名：
-
select s
#, Sname
-
from Student, SC
-
where student.s
# = sc.s#
-
and c
# in (select c# from SC where s#='1001');
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
-
select
distinct sc.s
# , sname
-
from student, sc
-
where student.s
#=sc.s#
-
and c
# in (select C# from sc where s#='001');
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩：
-
Update Sc
Set Score=(
Select
Avg(s2_Score)
From sc s2
Where s2.c
#=sc.c#)
-
Where c
# IN
-
(
Select c
# From sc cs INNER JOIN Teacher tc ON cs.t#=tc.t# WHERE tname ='叶平')
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名：
-
select s
# from sc where c# in
-
(
select c
# from sc where s#='1002')
-
group
by s
# having count(*)=
-
(
select
count(*)
from sc
where s
#='1002');
这个写法是有问题的，如果“1002”同学的学习课程是其它同学的子集，那么也会筛选出来；正确写法如下：子集且记录数相同
-
select t1.s_id from
-
(select s_id,count(distinct c_id) as cnt1 from sc where c_id in
-
(select c_id from sc where s_id=2) and s_id
<> 2 group by s_id
-
having count(distinct c_id)=(select count(distinct c_id) from sc where s_id=2)) t1,
-
(select s_id,count(distinct c_id) as cnt2 from sc group by s_id) t2
-
where t1.s_id=t2.s_id and t1.cnt1=t2.cnt2
-
delect
sc
-
from
course, Teacher
-
where
course.c#=sc.c#
-
and
course.t#=teacher.t#
-
and
tname='叶平';
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、002号课的平均成绩：
-
Insert SC
select S
#,'002',
-
(
Select
avg(score)
from SC
where C
#='002')
-
from Student
where S
# not in (Select S# from SC where C#='002');
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID，数据库，企业管理，英语，有效课程数，有效平均分：
-
select s_id as 学生ID,
-
(select score from sc where sc.s_id=t.s_id and c_id=1) as 数据库,
-
(select score from sc where sc.s_id=t.s_id and c_id=2) as 企业管理,
-
(select score from sc where sc.s_id=t.s_id and c_id=3) as 英语,
-
count(*) as 有效课程数, avg(t.score) as 平均成绩,rank() over(order by avg(t.score) desc) as 名次
-
from sc t
-
group by s_id
-
order by avg(t.score) asc
此题很经典，没做出来。rank over() 里面是是计算名次的排序；order by是记录展示的排序18、查询各科成绩最高和最低的分： 以如下的形式显示：课程ID，最高分，最低分
-
select L.c
# as 课程ID, L.score as 最高分,
-
R.score
as 最低分
-
from sc L, sc R
-
where L.c
# = R.c#
-
and L.score = (
select
max(IL.score)
-
from sc IL, student
as IM
-
where L.c
#=IL.c# and IM.s#=IL.s#
-
group
by IL.c
#)
-
and R.score = (
select
min(IR.score)
-
from sc
as IR
-
where R.c
#=IR.c#
-
group
by IR.c
#);
select c_id,max(score),min(score) from sc group by c_Id;
-
SELECT t.C
# AS 课程号,
-
max(course.Cname)
AS 课程名,
-
isnull(
AVG(score),
0)
AS 平均成绩,
-
100 *
SUM(
CASE
WHEN
isnull(score,
0)>=
60
THEN
1
ELSE
0
END)/
COUNT(*)
AS 及格百分数
-
FROM SC T,Course
-
where t.C
#=course.C#
-
GROUP
BY t.C
#
-
ORDER
BY
100 *
SUM(
CASE
WHEN
isnull(score,
0)>=
60
THEN
1
ELSE
0
END)/
COUNT(*)
DESC
-
<strong><span style="color:
#ff0000">select c_id 课程编号,round(avg(score),2) 平均分,
-
round(sum(case when score>59 then 1 else 0
end)/
count(
1)*
100.00,
2)||
'%' 及格率
from sc
-
group
by c_id
order
by 平均分
asc,及格率
desc;</span></strong>
20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）：
-
SELECT
max(Z.T
#) AS 教师ID,
-
MAX(Z.Tname)
AS 教师姓名,
-
C.C
# AS 课程ID,
-
AVG(Score)
AS 平均成绩
-
FROM SC
AS T,Course
AS C ,Teacher
AS Z
-
where T.C
#=C.C# and C.T#=Z.T#
-
GROUP
BY C.C
#
-
ORDER
BY
AVG(Score)
DESC
22、查询如下课程成绩第3名到第6名的学生成绩单：企业管理(001)，马克思(002)，UML(003)，数据库(004)：
23、统计下列各科成绩，各分数段人数：课程ID，课程名称，[100-85],[85-70],[70-60],[ 小于60] ：
-
SELECT SC.C
# as 课程ID, Cname as 课程名称,
-
SUM(
CASE
WHEN score
BETWEEN
85
AND
100
THEN
1
ELSE
0
END)
AS [
100 -
85] ,
-
SUM(
CASE
WHEN score
BETWEEN
70
AND
85
THEN
1
ELSE
0
END)
AS [
85 -
70],
-
SUM(
CASE
WHEN score
BETWEEN
60
AND
70
THEN
1
ELSE
0
END)
AS [
70 -
60],
-
SUM(
CASE
WHEN score <
60
THEN
1
ELSE
0
END)
AS [
60 -]
-
FROM SC,Course
-
where SC.C
#=Course.C#
-
GROUP
BY SC.C
#,Cname;
-
select c_id 课程ID,
-
(select cname from course t1 where t1.c_id=t2.c_id) 课程名称,
-
count(distinct case when score between 85 and 100 then s_id end ) "[85-100分]人数",
-
count(distinct case when score between 70 and 85 then s_id end ) "[75-85分]人数",
-
count(distinct case when score between 60 and 70 then s_id end ) "[60-70]人数",
-
count(distinct case when score <60 then s_id end ) "小于60分人数"
-
from sc t2 group by c_id ;
注：这种写法更好一点，可以排除，同人同科目错误录入了两条记录的情况；
-
SELECT
1+(
SELECT
COUNT(
distinct 平均成绩)
-
FROM (
SELECT S
#,AVG(score) AS 平均成绩
-
FROM SC
-
GROUP
BY S
# ) AS T1 WHERE 平均成绩 > T2.平均成绩) as 名次,
-
S
# as 学生学号,平均成绩
-
FROM (
SELECT S
#,AVG(score) 平均成绩
-
FROM SC
-
GROUP
BY S
# ) AS T2
-
ORDER
BY 平均成绩
desc;
select s_id 学号,avg(score) 平均成绩,rank() over(order by avg(score) desc) 名次 from sc group by s_id;
-
SELECT t1.S
# as 学生ID,t1.C# as 课程ID,Score as 分数
-
FROM SC t1
-
WHERE score
IN
-
(
SELECT TOP
3 score
-
FROM SC
-
WHERE t1.C
#= C#
-
ORDER
BY score
DESC)
select s_id,c_id,dense_rank() over(partition by c_id order by score desc) rank from sc; 注：oracle没有top N的写法；
-
select c
#, count(s#)
-
from sc
-
group
by c
#;
-
select t2.s_Id,t2.sname from student t2 where t2.s_id
-
in(select s_Id from sc group by sc.s_id having count(distinct sc.c_id)=1);
-
-
select t1.s_id,t2.sname from sc t1,student t2 where t1.s_id=t2.s_id
-
group by t1.s_id,t2.sname having count(distinct t1.c_id)=1
注：写法2 可加深对group by的理解，实际根据t1.s_id已经能够唯一定位，加上t2.sname完全是语法需要
-
select
count(Ssex)
as 男生人数
-
from student
-
group
by Ssex
-
having Ssex=
'男'；
-
select
count(Ssex)
as 女生人数
-
from student
-
group
by Ssex
-
having Ssex=
'女';
select (case when ssex='M' then '男' else '女' end) 性别,count(1) 人数 from student group by ssex;
注：注意单引号，数据库里只识别单引号。
-
select sname
-
from student
-
where sname
like
'张%';
-
select sanme,count
(*)
-
from student
-
group by sname
-
havang count(*)>
1;
31、1981年出生的学生名单（注：student表中sage列的类型是datetime）:
-
select sname,
convert(
char(
11),
DATEPART(
year,sage))
as age
-
from student
-
where
convert(
char(
11),
DATEPART(
year,Sage))=
'1981';
32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩：
-
select Sname,SC.S
# ,avg(score)
-
from Student,SC
-
where Student.S
#=SC.S#
-
group
by SC.S
#,Sname
-
having
avg(score)>
85;
33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列：
-
select C
#, avg(score)
-
from sc
-
group
by c
#
-
order
by
avg(score), c
# desc;
34、查询课程名称为“数据库”，且分数低于60的学生名字和分数：
-
select sname,
isnull(score,
0)
-
from student, sc ,course
-
where sc.s
#=student.s# and sc.c#=course.c# and course.cname='数据库' and score<60;
-
select sc.s
#,sc.c#,sname,cname
-
from sc,student course
-
where sc.s
#=student.s# and sc.c#=course.c#;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数：
-
select
distinct student.s
#,student.sname,sc.c#,sc.score
-
from student,sc
-
where sc.score>=
70
and sc.s
#=student.s#;
-
select c
#
-
from sc
-
where score<
60
-
order
by c
#;
select c_id , count(1) from sc where score < 60 group by c_id order by c_id;
注：很好的考察了对group by 用法的理解38、查询课程编号为“003”且课程成绩在80分以上的学生的学号和姓名：
-
select sc.s
#,student.sname
-
from sc,student
-
where sc.s
#=student.s# and score>80 and c#='003';
select count(*) from sc;
select count(distinct c_id) from sc where score is not null;
注：感觉这样更严谨40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩：
-
select student.sname,score
-
from student,sc,course c, teacher
-
where student.s
#=sc.S# and sc.c#=c.c#
-
and c.T
#=teacher.T#
-
and teacher.tname=
'叶平'
-
and sc.score=(
select
max(score)
from sc
where c
#=c.c#);
-
select t4.sname 姓名,t3.score 成绩 from sc t3,student t4 where t3.s_id=t4.s_id and t3.score=
-
(
select
max(score)
from sc
-
where c_id
in(
select t1.c_id
from course t1,teacher t2
where t1.t_id=t2.t_id
and t2.tname=
'叶平'));
注：连接4张表select count(*) from sc group by c#;
-
select
distinct a.s
#,b.score
-
from sc a ,sc b
-
where a.score=b.score
-
and a.c
#<>b.c#;
-
select t1.* from
-
sc t1,sc t2 where
-
t1.score=t2.score and t1.s_id<>t2.s_id and t1.c_id<>t2.c_id order by t1.score;
注：使用自连接的例子
-
select t1.s
# as 学生ID,t1.c# 课程ID, Score as 分数
-
from sc t1
-
where score
in (
select top
2 score
from sc
-
where t1.c
#=c#
-
order
by score
desc)
-
order
by t1.c
#;
-
select (select cname from course where c_id=t1.c_id) 课程名,t2.sname 学生姓名,t1.rank 排名 from
-
(select c_id,s_id,rank() over(partition by c_id order by score desc) rank from sc) t1,student t2
where t1.s_id=t2.s_id and t1.rank <3 注：partition by 以前没用过，注意下用法；44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序：
-
select c_id,count(distinct s_id) from sc group by c_id
-
having count(distinct s_id)>10 order by count(distinct s_id) desc,c_id asc;
-
select s
#
-
from sc
-
group
by s
#
-
having
count(*)>=
2;
-
select c
# ,cname
-
from course
-
where c
# in (select c# from sc group by c#);
-
select sname
-
from student
-
where s
# not in (select s# from course,teacher,sc where course.t#=teacher.t# and sc.c#=course.c#
-
and tname=
'叶平');
-
select distinct t3.s_id,t3.sname from student t3,sc t4 where t3.s_id=t4.s_id and t4.c_id
-
not in(select t1.c_id from course t1,teacher t2 where t1.t_id=t2.t_id and t2.tname='叶平');
注：此题集合的思想，1、学过叶平的课的学生；2、叶平教过的课 取相反数
-
select s
#,avg(isnull(score,0))
-
from sc
-
where s
# in (select s# from sc where score<60 group by s# having count(*)>2)
-
group
by s
#;
-
select s_id,avg(score) from sc group by s_id
-
having count(distinct case when score
< 60 then c_id end)>2
49、检索“004”课程分数小于60，按分数降序排列的同学学号：
-
select s
#
-
from sc
-
where c
#='004'
-
and score<
60
-
order
by score
desc;
-
delect
from sc
-
where s
#='002'
-
and c
#='001';
转载：https://blog.csdn.net/DH2442897094/article/details/78132667
查看评论