❶ sql查询面试题与答案
SQL查询面试题与答案
SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。下面是我搜集的SQL查询面试题与答案，欢迎大家阅读。
SQL查询面试题与答案一
1.一道SQL语句面试题，关于group by表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')
1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
2) select N.rq,N.胜,M.负 from (
select rq,胜=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,负=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001
2.请教一个面试中遇到的SQL语句的查询问题
表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面试题：一个日期判断的sql语句?
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路)：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table
5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
------------------------------------------
用户临时表:create table #xx(ID int, IDValues int)
系统临时表:create table ##xx(ID int, IDValues int)
区别:
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
6.sqlserver2000是一种大型数据库，他的`存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。
------------------------------------------
它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.
SQL Server 2000 数据库有三种类型的文件：
主要数据文件
主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。
次要数据文件
次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
日志文件
日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。
7.请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
如使用存储过程也可以。
table1
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 (result)
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部门dep = b.部门dep and b.月份mon = '一月份' and
a.部门dep = c.部门dep and c.月份mon = '二月份' and
a.部门dep = d.部门dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
8.华为一道面试题
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
SQL查询面试题与答案二
1、查询不同老师所教不同课程平均分从高到低显示
SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,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
2、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理(001)，马克思(002)，UML (003)，数据库(004)
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
SELECT DISTINCT top 3
SC.S# As 学生学号,
Student.Sname AS 学生姓名 ,
T1.score AS 企业管理,
T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
3、统计打印各科成绩,各分数段人数:课程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;
4、查询学生平均成绩及其名次
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;
5、查询各科成绩前三名的记录:(不考虑成绩并列情况)
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
)
ORDER BY t1.C#;
6、查询每门课程被选修的学生数
select c#,count(S#) from sc group by C#;
7、查询出只选修了一门课程的全部学生的学号和姓名
select SC.S#,Student.Sname,count(C#) AS 选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
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
9、查询所有课程成绩小于60分的同学的学号、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名;
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(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.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');
15、删除学习“叶平”老师课的SC表记录;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩;
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# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS 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#
);
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
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
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001)，马克思(002)，OO&UML (003)，数据库(004)
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
;❷ sql面试题50题（mysql版）
--插入学生表测试数据
insert into Student values(༽' , '赵雷' , -01-01' , '男');
insert into Student values(༾' , '钱电' , -12-21' , '男');
insert into Student values(༿' , '孙风' , -05-20' , '男');
insert into Student values(ཀ' , '李云' , -08-06' , '男');
insert into Student values(ཁ' , '周梅' , -12-01' , '女');
insert into Student values(ག' , '吴兰' , -03-01' , '女');
insert into Student values(གྷ' , '郑竹' , -07-01' , '女');
insert into Student values(ང' , '王菊' , -01-20' , '女');
--课程表测试数据
insert into Course values(༽' , '语文' , ༾');
insert into Course values(༾' , '数学' , ༽');
insert into Course values(༿' , '英语' , ༿');
--教师表测试数据
insert into Teacher values(༽' , '张三');
insert into Teacher values(༾' , '李四');
insert into Teacher values(༿' , '王五');
--成绩表测试数据
insert into Score values(༽' , ༽' , 80);
insert into Score values(༽' , ༾' , 90);
insert into Score values(༽' , ༿' , 99);
insert into Score values(༾' , ༽' , 70);
insert into Score values(༾' , ༾' , 60);
insert into Score values(༾' , ༿' , 80);
insert into Score values(༿' , ༽' , 80);
insert into Score values(༿' , ༾' , 80);
insert into Score values(༿' , ༿' , 80);
insert into Score values(ཀ' , ༽' , 50);
insert into Score values(ཀ' , ༾' , 30);
insert into Score values(ཀ' , ༿' , 20);
insert into Score values(ཁ' , ༽' , 76);
insert into Score values(ཁ' , ༾' , 87);
insert into Score values(ག' , ༽' , 31);
insert into Score values(ག' , ༿' , 34);
insert into Score values(གྷ' , ༾' , 89);
insert into Score values(གྷ' , ༿' , 98);
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select c.*,a.s_score as 01课程score,b.s_score as 02课程score from
score a,score b
left join student c
on b.s_id = c.s_id
where a.s_id = b.s_id and a.c_id = ༽' and b.c_id = ༾' and a.s_score > b.s_score;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.* ,b.s_score as 01课程,c.s_score as 02课程 from student a
join score b
on a.s_id=b.s_id and b.c_id = ༽'
left join score c
on b.s_id = c.s_id and c.c_id = ༾'
where b.s_score < c.s_score ;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id,a.s_name,round(avg(b.s_score),2) as 平均成绩 from student a
join score b
on a.s_id = b.s_id
group by b.s_id having 平均成绩 >= 60;
备注：round[avg(成绩),1]里,round是四舍五入函数，1代表保留1位小数
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select b. ,round(avg(a.s_score),2) as 平均成绩 from
student b
left join score a on b.s_id = a.s_id group by a.s_id having 平均成绩 < 60
union
select b. ,0 as 平衡成绩 from student b where b.s_id not in (select s_id from score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as 选课总数 ,sum(b.s_score) as 总分 from student a
left join score b
on a.s_id = b.s_id group by s_id ;
-- 6、查询"李"姓老师的数量
select count(*) as 李姓老师数量 from teacher where t_name like '李%'
-- 7、查询学过"张三"老师授课的同学的信息
select a.* from student a join score b
on a.s_id = b.s_id
where b.c_id in (select c.c_id from course c
join teacher d on c.t_id = d.t_id where d.t_name = '张三');
-- 8、查询没学过"张三"老师授课的同学的信息
select a.* from student a left join score b on a.s_id = b.s_id where a.s_id not in
(select s_id from score where c_id =
(select c_id from course where t_id =
(select t_id from teacher where t_name = '张
三'))) group by a.s_id;
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student where s_id in
(select a.s_id from score a join score b on a.s_id = b.s_id
where a.c_id = ༽' and b.c_id = ༾');
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student where s_id in
(select s_id from score where c_id = ༽' )
and s_id not in (select s_id from score where c_id = ༾' );
-- 11、查询没有学全所有课程的同学的信息
select * from student where s_id not in
(select s_id from score group by s_id having count(c_id) = 3);
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct a.* from student a left join score b
on a.s_id = b.s_id where b.c_id in
(select c_id from score where s_id = ༽') and a.s_id != ༽' ;
注意：distinct是去重的
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student where s_id in
(select s_id from score group by s_id having count(c_id) =
(select count(c_id) from score where s_id = ༽') and s_id not in
(select s_id from score where c_id not in
(select c_id from score where s_id = ༽')) and s_id != ༽');
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select s_name from student where s_id not in
(select s_id from score where c_id in
(select c_id from course where t_id in
(select t_id from teacher where t_name ='张三')));
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id
where s_score < 60 group by s_id having count(1) >=2;
或者试试
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id
where a.s_score < 60 group by a.s_id having count(*) >=2;
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.* ,b.c_id ,b.s_score from student a
left join score b on a.s_id = b.s_id
where b.c_id = ༽' and b.s_score < 60
order by b.s_score desc;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_name ,
sum(case when b.c_id = ༽' then s_score else null end ) as 语文,
sum(case when b.c_id = ༾' then s_score else null end ) as 数学,
sum(case when b.c_id = ༿' then s_score else null end ) as 英语,
round(avg(s_score),2) as 平均成绩
from student a left join score b on a.s_id = b.s_id group by a.s_name
order by 平均成绩 desc;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select b.c_id,b.c_name,
max(a.s_score) as 最高分,
min(a.s_score) as 最低分,
round(avg(a.s_score),2) as 平均分,
round(sum(case when a.s_score>= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,
round(sum(case when a.s_score>= 70 and a.s_score <80 then 1 else 0 end)/count(s_id),2) as 中等率,
round(sum(case when a.s_score>= 80 and a.s_score <90 then 1 else 0 end)/count(s_id),2) as 优良率,
round(sum(case when a.s_score>= 90 then 1 else 0 end)/count(s_id),2) as 优秀率
from score a
left join course b
on a.c_id = b.c_id group by b.c_id;
-- 19、按各科成绩进行排序，并显示排名
第一种：
set @pre_c_id:= ༽'
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2;
如果看不懂用第二种方法：
SELECT a.c_id,a.s_id,a.s_score,COUNT(b.s_score)+1 AS 排名
FROM score a LEFT JOIN score b ON a.s_score<b.s_score AND a.c_id = b.c_id
GROUP BY a.c_id,a.s_id,a.s_score ORDER BY a.c_id,排名,a.s_id ASC
-- 20、查询学生的总成绩并进行排名
set @rank:=0;
select * ,(@rank:=@rank+1) as rank from
(select s_id ,sum(s_score) as 总成绩 from score
group by s_id order by 总成绩 desc) tb1;
-- 21、查询不同老师所教不同课程平均分从高到低显示
select a.c_id, d.t_name,round(avg(a.s_score)) as 平均分 from score a
left join student b on a.s_id = b.s_id
left join course c on a.c_id = c.c_id
left join teacher d on c.t_id = d.t_id group by a.c_id
order by 平均分 desc;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
set @pre_c_id:= ༽'
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 = 2 or 排名 =3;
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],(85-70],(70-60],(0-60]及所占百分比
select b.c_id,b.c_name ,
sum(case when a.s_score >=85 then 1 else 0 end) as 100-85 ,
concat(round(100 sum(case when a.s_score >=85 then 1 else 0 end)/count( ),2), '%') as 百分比,
sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end) as 85-70 ,
concat(round(100 sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end)/count( ),2),'%') as 百分比,
sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end) as 70-60 ,
concat(round(100 sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end)/count( ),2) ,'%')as 百分比,
sum(case when a.s_score <60 and a.s_score >=0 then 1 else 0 end) as 60-0 ,
concat(round(100 sum(case when a.s_score <60 and a.s_score >=0 then 1
else 0 end)/count( ),2),'%') as 百分比
from score a left join course b on a.c_id = b.c_id group by b.c_id;
-- 24、查询学生平均成绩及其名次
select tb1.*,(@rank:=@rank +1 ) as rank from
(select s_id ,round(avg(s_score),2) as 平均成绩 from score
group by s_id order by 平均成绩 desc) tb1,(select @rank:=0) b;
-- 25、查询各科成绩前三名的记录
set @pre_c_id:= ༽'
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 <4;
-- 26、查询每门课程被选修的学生数
select c_id ,count(s_id) as 选修人数 from score group by c_id;
-- 27、查询出只有两门课程的全部学生的学号和姓名
select a.s_id ,b.s_name from score a left join student b on a.s_id = b.s_id group by s_id having count(*) = 2;
-- 28、查询男生、女生人数
select sum(case s_sex when '男' then 1 else 0 end) as 男生人数,
sum(case s_sex when '女' then 1 else 0 end) as 女生人数 from student;
-- 29、查询名字中含有"风"字的学生信息
select * from student where s_name like '%风%'
-- 30、查询同名同性学生名单，并统计同名人数
--略，不想写
-- 31、查询1990年出生的学生名单
select * from student where s_birth like %'
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id ,round(avg(s_score),2) as 平均成绩 from score group by c_id order by 平均成绩 desc, c_id asc;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id,b.s_name ,round(avg(s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id group by a.s_id having 平均成绩>=85;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select b.s_name ,a.s_score from score a
left join student b on a.s_id = b.s_id
where a.c_id=(select c_id from course where c_name = '数学')and a.s_score < 60;
-- 35、查询所有学生的课程及分数情况；
select b.s_name,
sum(case when a.c_id = ༽' then a.s_score else null end) as 语文,
sum(case when a.c_id = ༾' then a.s_score else null end) as 数学,
sum(case when a.c_id = ༿' then a.s_score else null end) as 英语
from score a right join student b on a.s_id = b.s_id group by b.s_name
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select b.s_name,
sum(case when a.c_id = ༽' then a.s_score else null end) as 语文,
sum(case when a.c_id = ༾' then a.s_score else null end) as 数学,
sum(case when a.c_id = ༿' then a.s_score else null end) as 英语
from score a right join student b on a.s_id = b.s_id group by b.s_name having 语文>= 70 or 数学>= 70 or 英语>= 70 ;
-- 37、查询不及格的课程
select a.s_id,a.c_id,b.c_name,a.s_score from score a
left join course b on a.c_id = b.c_id where a.s_score<60;
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select a.s_id,b.s_name from score a left join student b on a.s_id = b.s_id where a.c_id = ༽' and a.s_score>=80;
-- 39、求每门课程的学生人数
select c_id,count(*) as 学生人数 from score group by c_id ;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*,b.c_id,max(b.s_score) as 最高成绩 from student a
right join score b on a.s_id = b.s_id
group by b.c_id
having b.c_id = (select c_id from course
where t_id = (select t_id from teacher where t_name = '张三'));
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
--（这题我搞不清题目是什么意思,是指查找学生个体参加了的所有课程的成绩各不相同的那个学生信息呢？还是所有课程之间做对比呢，我更倾向于理解为前者）
--理解为前者的写法
select * from
(select * from score group by s_id,s_score) tb1
group by s_id having count(*) = 1;
--理解为后者的写法
select distinct a.s_id,a.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score;
-- 42、查询每门课程成绩最好的前两名
set @pre_c_id:= ༽'
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2
join student b on tb2.s_id = b.s_id where 排名 <3;
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人相同，按课程号升序排列
select c_id ,count(*) as 选修人数 from score group by c_id having 选修人数>5 order by 选修人数 desc , c_id asc;
-- 44、检索至少选修两门课程的学生学号
select s_id from score group by s_id having count(*) >= 2;
-- 45、查询选修了全部课程的学生信息
select * from student where s_id in
(select s_id from score group by s_id having count(*) = 3)
--46、查询各学生的年龄
select s_name ,(date_format(now(),'%Y')-date_format(s_birth,'%Y') + (CASE when date_format(now(),'%m%d')>=date_format(s_birth,'%m%d') then 0 else 1 end)) as age
from student
-- 47、查询本周过生日的学生
---(实现得并不完全，因为例如出生月日为‘01-01’在每一年可能会输入不同周）
select * from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;
-- 48、查询下周过生日的学生
select * from student
where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));
-- 49、查询本月过生日的学生
select * from student where date_format(s_birth,'%m') = date_format(now(),'%m')
-- 50、查询下月过生日的学生
select * from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')
❸ SQL数据库面试题 急急急
a)select pname as '商品名',avg(qty) as 平均销售量 from s,p,m where m.city='上海' and s.mno=m.mno and p.pno=s.pno，select p.Pno,p.pname,sum(s.qty)
from s left join p on s.pno=p.pno left join m on p.Mno=m.Mno
where m.city='上海市'
group by p.Pno,p.pname,p.city,p.color
b)、先删除Sale表的外键PNO，再删除gds表。
c)联系：视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系
区别：1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时四对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，视图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。
❹ 度小满人工审核会问什么
度小满人工审核会一般问借款人填写的表单上的问题，具体包括以下几项：
1、个人基本情况
一般会询问借款人的个人姓名、身份证号、收入情况、职业单位、婚姻状况等，银行根据这些基本情况对你的还款能力进行评估。这些内容都是你在提交的资料里面填写过的，再次询问主要是做一个真实性调查，看你有没有弄虚作假，排除掉一些骗贷的客户。
2、逾期情况
在贷前调查中，个人征信也是银行审查的重点，银行会根据信用记录对你的还款意愿做出预期，从而影响到贷款的审批。如果你没有过任何逾期记录，你只需要如实回答即可。而对于有过逾期的人，不同银行对征信的要求不一，有的一次逾期就拒贷，而有的要求则比较宽松，你只需对你逾期的原因做出合情合理的解释，除了恶意拖欠等大的信用污点，一般还是不影响贷款的。
3、经营状况
如果是个体户或者申请经营类贷款的借款人，银行还会询问你的经营状况，例如贷款用途、销售额、库存量、盈利情况等问题。如果你的经营状况不好，银行不会轻易放款。所以在贷款审核电话中，为避免说错话，借款人应该认真思考后再慎重地回答问题。
拓展资料：
1、度小满金融，原网络金融。2018年4月，网络宣布旗下金融服务事业群组正式完成拆分融资协议签署，拆分后网络金融将启用全新品牌“度小满金融”，实现独立运营。网络互联网金融业务始于2013年，在2015年将既有金融业务整合组成了金融事业群组，并将金融业务上升至网络战略级位置，由网络高级副总裁朱光先生全面负责。
2、2020年12月，联合国全球契约中国网络发布了“2020实现可持续发展目标企业最佳实践”企业榜单。共有20家中国企业入选最佳实践企业，其中度小满金融帮扶小微企业、扶贫助农、创造社会就业等方面的实践获联合国点赞，成功入选“消除贫困和促进繁荣最佳实践企业”。
❺ SQL面试题，求解释
select count(*)
from fans t2
where t1.team=t2.team
and t2.fanCount>=t1.fanCount
获取当前TEAM中，FANCOUNT比当前记录的FANCOUNT多的记录的数量。
当这个数量小于等于3时，就是这个TEAM的FANCOUNT的前三名。
例如：
第一个记录时，有四条记录符合这个条件，所以第一个记录不是前三名的。
第二个记录时， 只有一条记录符合这个条件，所以这个记录是第一名。
第三个记录时， 有三条记录符合这个条件，所以这个记录是第三名。
第四个记录时， 有二条记录符合这个条件，所以这个记录是第二名。
以此类推。。。
❻ sql 面试问题
错了。全删了。
——————————————————————————————————————
删除2条完全一样的数据
SQL Server 的处理办法（SQL2005及以上）
-- 首先创建测试表
CREATE TABLE test_delete(
name varchar(10),
value INT
);
go
-- 测试数据，其中 张三100 与 王五80 是完全一样的
INSERT INTO test_delete
SELECT '张三', 100
UNION ALL SELECT '张三', 100
UNION ALL SELECT '李四', 80
UNION ALL SELECT '王五', 80
UNION ALL SELECT '王五', 80
UNION ALL SELECT '赵六', 90
UNION ALL SELECT '赵六', 70
go
-- 首先查询一下， ROW_NUMBER 效果是否满足预期
SELECT
ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY (SELECT 1) ) AS no,
name,
value
FROM
test_delete
no name value
----- ---------- -----------
1 李四 80
1 王五 80
2 王五 80
1 张三 100
2 张三 100
1 赵六 70
1 赵六 90
从结果上可以看到，如果有重复的，完全一样的话， no 是有大于1的。
-- 创建视图
CREATE VIEW tmp_view AS
SELECT
ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY (SELECT 1) ) AS no,
name,
value
FROM
test_delete
-- 删除 视图中的 no 不等于 1 的数据。
1> DELETE FROM tmp_view WHERE no != 1
2> go
(2 行受影响)
1>
2> select * from test_delete;
3> go
name value
---------- -----------
张三 100
李四 80
王五 80
赵六 90
赵六 70
(5 行受影响)
结果看上去是满足预期的。
——————————————————————————————————————————
此文来自我朋友wangqing999的空 间 博 文
❼ SQL 面试题
select t1.产品 as 商品名称,t1.qty-t2.qty as 差额数量 from
(select 产品,sum(数量) as qty from 表 group by 产品 where 颜色='红色') as t1,
(select 产品,sum(数量) as qty from 表 group by 产品 where 颜色='蓝色') as t2
where t1.产品=t2.产品 and t1.qty>t2.qty
一分都没。。。。。。。。够呛
❽ 问几题数据库面试题++++++++++++++++++
14: C
select e.ename form emp e where e.empno not in (select m.mgr from emp m)
1:
1)
select A.sid, S.sname from
(select sid from sc where cid in (select cid from sc where sid = 123456)
group by sid having count(*) = (select count(*) from sc where sid = 123456)) as A inner join student as S on A.sid = S.sid
2)
select sc.cid, s.sname, sc.cid, c.cname, sc.scscore from sc, student as s, course as c
where s.sid = sc.sid and c.cid = sc.cid and sc.sid in
(
select sc.sid from sc,
(select cid, avg(scscore) as avg_score from sc group by cid) as a
where sc.cid = a.cid and sc.scscore < a.avg_score
group by sc.sid having count(*) > 2
)
1:
select 日期字段,sum(case when 胜负字段 = '胜' then 1 else 0 end) as [胜],sum(case when 胜负字段 = '负' then 1 else 0 end) as [负] from 比赛表
group by 日期字段