95992828九五至尊2

九五至尊老品牌值信赖SQL面试笔试经典题,数据库篇

二月 1st, 2019  |  九五至尊老品牌值信赖

 

作为一枚Java后端开发者,数据库知识必不可少,对数据库的主宰熟知度的观测也是对此人是或不是有扎实基础的洞察。尤其对于初级开发者,面试可能不会去问框架相关文化,但是相对不会不去观望数据库知识,那里采访一些周边类型的SQL语句,无论对于日常开发依旧准备面试,都会有助益。

基本表结构:

        student(sno,sname,sage,ssex)学生表
        course(cno,cname,tno) 课程表
        sc(sno,cno,score) 成绩表

*        teacher(tno,tname) 教师表*

* *

101,查询课程1的成就比课程2的成就高的拥有学员的学号
select a.sno from
(select sno,score from sc where cno=1) a,
(select sno,score from sc where cno=2) b
where a.score>b.score and a.sno=b.sno

 

102,查询平均成绩当先60分的同窗的学号和平均成绩*
select a.sno as “学号”, avg(a.score) as “平均战表” 
from
(select sno,score from sc) a 
group by sno having avg(a.score)>60
*

* *

103,查询所有同学的学号、姓名、选课数、总成绩*
select a.sno as 学号, b.sname as 姓名,
count(a.cno) as 选课数, sum(a.score) as 总成绩
from sc a, student b
where a.sno = b.sno
group by a.sno, b.sname
*

或者:

*selectstudent.sno as 学号, student.sname as 姓名,
 count(sc.cno) as 选课数, sum(score) as 总成绩
from student left Outer join sc on student.sno = sc.sno
group by student.sno, sname

*104,查询姓“张”的先生的个数

selectcount(distinct(tname)) from teacher where tname like ‘张%‘
或者:
select tname as “姓名”, count(distinct(tname)) as “人数” 
from teacher 
where tname like’张%’
group by tname

 

105,查询没学过“张三”老师课的同校的学号、姓名
select student.sno,student.sname from student
where sno not in (select distinct(sc.sno) from sc,course,teacher
where sc.cno=course.cno and teacher.tno=course.tno and
teacher.tname=’张三’)

 

106,查询同时学过课程1和学科2的同窗的学号、姓名
select sno, sname from student
where sno in (select sno from sc where sc.cno = 1)
and sno in (select sno from sc where sc.cno = 2)
或者:

selectc.sno, c.sname from
(select sno from sc where sc.cno = 1) a,
(select sno from sc where sc.cno = 2) b,
student c
where a.sno = b.sno and a.sno = c.sno
或者:

select student.sno,student.sname from student,sc where
student.sno=sc.sno and sc.cno=1
and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and
sc_2.cno=2)

 

107,查询学过“李四”老师所教所有课程的有着同学的学号、姓名
select a.sno, a.sname from student a, sc b
where a.sno = b.sno and b.cno in
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname =
‘李四’)

或者:

select a.sno, a.sname from student a, sc b,
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname =
‘李四’) e
where a.sno = b.sno and b.cno = e.cno

 

108,查询课程编号1的大成比课程编号2的大成高的保有同学的学号、姓名
select a.sno, a.sname from student a,
(select sno, score from sc where cno = 1) b,
(select sno, score from sc where cno = 2) c
where b.score > c.score and b.sno = c.sno and a.sno = b.sno

 

109,查询所有科目成绩小于60分的同校的学号、姓名
select sno,sname from student
where sno not in (select distinct sno from sc where score > 60)

 

110,查询至少有一门课程与学号为1的同班所学课程相同的同班的学号和姓名
select distinct a.sno, a.sname
from student a, sc b
where a.sno <> 1 and a.sno=b.sno and
b.cno in (select cno from sc where sno = 1)

或者:

select s.sno,s.sname 
from student s,
(select sc.sno 
from sc
where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and
sc.sno<>1
group by sc.sno)r1
where r1.sno=s.sno

 

Java知音公众号整理一些各大集团常用的面试笔试题,供大家在每天闲暇之余学习其中几道问题,日积月累,等到出来面试时,一切都马到成功,面试时就自然会游刃有余了。

九五至尊老品牌值信赖 1

 

用作一枚Java后端开发者,数据库知识必不可少,对数据库的操纵熟识度的观测也是对此人是或不是有扎实基础的体察。尤其对于初级开发者,面试可能不会去问框架相关文化,然则相对不会不去观看数据库知识,这里收集一些广泛类型的SQL语句,无论对于平日开发仍旧准备面试,都会有亮点。

基本表结构:

        student(sno,sname,sage,ssex)学生表
        course(cno,cname,tno) 课程表
        sc(sno,cno,score) 成绩表

*        teacher(tno,tname) 教师表*

 

111、把“sc”表中“王五”所教师的成就都改变为此课程的平均战绩
update sc set score = (select avg(sc_2.score) from sc sc_2
wheresc_2.cno=sc.cno)
from course,teacher where course.cno=sc.cno and course.tno=teacher.tno
andteacher.tname=’王五’

112、查询和号码为2的同室学习的课程完全相同的其余同学学号和姓名
这一题分两步查:

1,

select sno
from sc
where sno <> 2
group by sno
having sum(cno) = (select sum(cno) from sc where sno = 2)

2,
select b.sno, b.sname
from sc a, student b
where b.sno <> 2 and a.sno = b.sno
group by b.sno, b.sname
having sum(cno) = (select sum(cno) from sc where sno = 2)

113、删除学习“王五”老师课的sc表记录
delete sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname =
‘王五’

114、向sc表中插入一些记下,这一个记录必要符合以下条件:
将没有课程3成就同学的该战绩补齐, 其成就取所有学生的课程2的平均战绩

insert sc select sno, 3, (select avg(score) from sc where cno = 2)
from student
where sno not in (select sno from sc where cno = 3)

115、按平平均分从高到低呈现所有学员的如下计算报表:
— 学号,公司管理,马克思,UML,数据库,物理,课程数,平均分

select sno as 学号
,max(case when cno = 1 then score end) AS 公司管理
,max(case when cno = 2 then score end) AS 马克思
,max(case when cno = 3 then score end) AS UML
,max(case when cno = 4 then score end) AS 数据库
,max(case when cno = 5 then score end) AS 物理
,count(cno) AS 课程数
,avg(score) AS 平均分
FROM sc
GROUP by sno
ORDER by avg(score) DESC

116、查询各科成绩最高分和压低分:

以如下方式浮现:课程号,最高分,最低分
*select cno as 课程号, max(score) as 最高分, min(score) 最低分
from sc group by cno

select  course.cno as ‘课程号’
,MAX(score) as ‘最高分’
,MIN(score) as ‘最低分’
from sc,course
where sc.cno=course.cno
group by course.cno*

117、按各科平均成绩从低到高和及格率的百分比从高到低依次
SELECT t.cno AS 课程号,
max(course.cname)AS 课程名,
isnull(AVG(score),0) AS 平均战绩,
100 \
SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/count(1)
AS 及格率
FROM sc t, course
where t.cno = course.cno
GROUP BY t.cno
ORDER BY 及格率 desc*

118、查询如下课程平均成绩和及格率的比重(用”1行”显示): 

商厦管理(001),Marx(002),UML (003),数据库(004) 
select 
avg(case when cno = 1 then score end) as 平均分1,
avg(case when cno = 2 then score end) as 平均分2,
avg(case when cno = 3 then score end) as 平均分3,
九五至尊老品牌值信赖,avg(case when cno = 4 then score end) as 平均分4,
100 \
sum(case when cno = 1 and score > 60 then 1 else 0 end) /
sum(casewhen cno = 1 then 1 else 0 end) as 及格率1,
100 * sum(case when cno = 2 and score > 60 then 1 else 0 end) /
sum(casewhen cno = 2 then 1 else 0 end) as 及格率2,
100 * sum(case when cno = 3 and score > 60 then 1 else 0 end) /
sum(casewhen cno = 3 then 1 else 0 end) as 及格率3,
100 * sum(case when cno = 4 and score > 60 then 1 else 0 end) /
sum(casewhen cno = 4 then 1 else 0 end) as 及格率4
from sc*

119、查询差距老师所教分歧科目平均分, 从高到低突显
select max(c.tname) as 教师, max(b.cname) 课程, avg(a.score) 平均分
from sc a, course b, teacher c
where a.cno = b.cno and b.tno = c.tno
group by a.cno
order by 平均分 desc

或者:
select r.tname as ‘教师’,r.rname as ‘课程’ , AVG(score) as ‘平均分’
from sc,
(select  t.tname,c.cno as rcso,c.cname as rname
from teacher t ,course c
where t.tno=c.tno)r
where sc.cno=r.rcso
group by sc.cno,r.tname,r.rname 
order by AVG(score) desc

120、查询如下课程成绩均在第3名到第6名以内的学生的成就:
— [学生ID],[学生姓名],公司管理,Marx,UML,数据库,平均成绩

select top 6 max(a.sno) 学号, max(b.sname) 姓名,
max(case when cno = 1 then score end) as 集团管理,
max(case when cno = 2 then score end) as 马克思,
max(case when cno = 3 then score end) as UML,
max(case when cno = 4 then score end) as 数据库,
avg(score) as 平均分
from sc a, student b
where a.sno not in 

(select top 2 sno from sc where cno = 1 order by score desc)
  and a.sno not in (select top 2 sno from sc where cno = 2 order by
scoredesc)
  and a.sno not in (select top 2 sno from sc where cno = 3 order by
scoredesc)
  and a.sno not in (select top 2 sno from sc where cno = 4 order by
scoredesc)
  and a.sno = b.sno
group by a.sno

 

 

* *

Java知音公众号整理一些各大公司常用的面试笔试题,供大家在每一天闲暇之余学习其中几道标题,有加无已,等到出来面试时,一切都马到成功,面试时就自然会游刃有余了。

SQL面试笔试经典题(Part 1),sqlpart

正文是在Cat
Qi的原贴的底子之上,经自己逐题分别在MySql数据库中贯彻的笔记,持续更新…**

参照原贴:http://www.cnblogs.com/qixuejia/p/3637735.html 


01 表结构

  Student(Sno,Sname,Sage,Ssex)    学生表 
  Course(Cno,Cname,Tno)       课程表 
  SC(Sno,Cno,score)          成绩表 
  Teacher(Tno,Tname)        教师表


02 建表及插入测试数据

  (1) 建表:

九五至尊老品牌值信赖 2 1 DROP
TABLE IF EXISTS student ; 2 DROP TABLE IF EXISTS course ; 3 DROP TABLE
IF EXISTS sc ; 4 DROP TABLE IF EXISTS teacher ; 5 6 CREATE TABLE Student
7 ( 8 Sno int, 9 Sname varchar(32), 10 Sage int, 11 Ssex varchar(8) 12
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 13 14
CREATE TABLE Course 15 ( 16 Cno INT, 17 Cname varchar(32), 18 Tno INT 19
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 20 21
CREATE TABLE Sc 22 ( 23 Sno INT, 24 Cno INT, 25 score INT 26
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 27 28
CREATE TABLE Teacher 29 ( 30 Tno INT, 31 Tname varchar(16) 32
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; View Code

  【注】MySQL数据库建表时需求添加“ENGINE=InnoDB DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci”命令,否则中文会发生乱码。

  (2) 插入测试数据:

九五至尊老品牌值信赖 3 1 insert
into Student select 1,’刘一’,18,’男’ union all 2 select 2,’钱二’,19,’女’
union all 3 select 3,’张三’,17,’男’ union all 4 select 4,’李四’,18,’女’
union all 5 select 5,’王五’,17,’男’ union all 6 select 6,’赵六’,19,’女’
7 8 insert into Teacher select 1,’叶平’ union all 9 select 2,’贺高’
union all 10 select 3,’杨艳’ union all 11 select 4,’周磊’; 12 13 insert
into Course select 1,’语文’,1 union all 14 select 2,’数学’,2 union all
15 select 3,’英语’,3 union all 16 select 4,’物理’,4; 17 18 insert into
SC 19 select 1,1,56 union all 20 select 1,2,78 union all 21 select
1,3,67 union all 22 select 1,4,58 union all 23 select 2,1,79 union all
24 select 2,2,81 union all 25 select 2,3,92 union all 26 select 2,4,68
union all 27 select 3,1,91 union all 28 select 3,2,47 union all 29
select 3,3,88 union all 30 select 3,4,56 union all 31 select 4,2,88
union all 32 select 4,3,90 union all 33 select 4,4,93 union all 34
select 5,1,46 union all 35 select 5,3,78 union all 36 select 5,4,53
union all 37 select 6,1,35 union all 38 select 6,2,68 union all 39
select 6,4,71; View Code


03 难题及已毕代码

  (1)查询“1”课程比“2”课程成绩高的兼具学员的学号; 

select a.sno from
(select sno,score from sc where cno=1) a,
(select sno,score from sc where cno=2) b
where a.sno=b.sno and a.score>b.score;

   (2)查询平均战绩超乎60分的同校的学号和平均战绩; 

 select Sno,AVG(Score) as AvgScore 
 from SC
 group by Sno
 having AVG(Score)>60

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

 select student.sno,student.sname,count(sc.cno),sum(sc.score) from
 student left outer join sc
 on student.sno = sc.sno
 group by student.sno
 order by student.sno;

   (4)查询姓“李”的师资的个数; 

 select count(distinct tname) as count
 from teacher
 where tname like '李%';

   (5)查询没学过“叶平”老师课的同窗的学号、姓名;

 select s.sno,s.sname 
 from student s
 where s.sno not in
 (
 select distinct(sc.sno) from sc ,course c,teacher t
 where sc.cno = c.cno and c.tno = t.tno and t.tname = '叶平'
 )

  (6)查询学过“1”并且也学过数码“2”课程的同校的学号、姓名;

select s.sno,s.sname from student s, (select sno from sc where cno=1) a,
(select sno from sc where cno=2) b where s.sno = a.sno and a.sno =
b.sno;

  方法二 用exist函数

 select s.Sno,s.Sname
 from Student s,SC sc
 where s.Sno=sc.Sno and sc.Cno=1 and exists
 (
     select * from SC sc2 where sc.Sno=sc2.Sno and sc2.Cno=2
 )

  (7)查询学过“叶平”老师所教的所有课的同班的学号、姓名;

select s.sno,s.sname from student s,teacher t, course c left outer join
sc on c.cno = sc.cno where t.tname=”叶平” and t.tno = c.cno and s.sno =
sc.sno ;

  或者:

 select s.sno,s.sname 
 from student s
 where s.sno in 
 (
 select sc.sno
 from sc,course c,teacher t
 where c.cno=sc.cno and c.tno=t.tno and t.tname ="叶平"
 group by sc.sno
 having count(sc.cno)=
 (
  select count(c1.cno)
  from course c1,teacher t1
  where c1.tno=t1.tno and t1,tname ="叶平"
 )
 );

  (8)查询课程编号“2”的大成比课程编号“1”课程低的所有同学的学号、姓名;

select s.sno,s.sname from student s where s.sno in ( select a.sno from
(select sno,score from sc where cno=2) a, (select sno,score from sc
where cno=1) b where a.sno = b.sno and a.score < b.score );

  (9)查询有学科战绩小于60分的同室的学号、姓名;

select s.sno,s.sname from student s,sc where sc.score<60 and
s.sno=sc.sno group by s.sno;

 

  (10)查询没有学全所有课的校友的学号、姓名;

select s.sno,s.sname from student s where s.sno not in ( select sc.sno
from sc group by sc.sno having count(distinct sc.cno)= ( select
count(distinct c.cno) from course c ) );

  (11)查询至少有一门课与学号为“1”的同班所学相同的校友的学号和人名;

select distinct(s.sno),s.sname from student s,sc where s.sno=sc.sno and
sc.cno in ( select distinct(cno) from sc where sno=1 );

  (12)查询至少学过学号为“1”同学所有一门课的其余同学学号和人名;

select distinct(s.sno),s.sname from student s,sc where s.sno=sc.sno and
s.sno != 1 and sc.cno in ( select distinct(cno) from sc where sno=1 );

  (13)把“SC”表中“叶平”老师教的课的实绩都更改为此课程的平均成绩;

 

 update sc set score =
 (
 select avg(sc1.score) from sc sc1,course c,teacher t
 where sc1.cno = c.cno and c.tno = t.tno and t.tname="叶平"
 )
 where cno in
 (
 select cno from course c,teacher t 
 where c.tno = t.tno and t.tname="叶平"
 );

  (14)查询和“2”号的同校学习的教程完全相同的其余同学学号和人名;

select s.sno,s.sname from student s where s.sno != 2 and s.sno in (
select distinct(sno) from sc where cno in (select cno from sc where
sno=2) group by sno having count(distinct cno)= ( select count(distinct
cno) from sc where sno=2 ) );

  (15)删除学习“叶平”老师课的SC表记录;

delete from sc where cno in ( select c.cno from course c,teacher t
where c.tno = t.tno and t.tname=”叶平” );

  (16)向SC表中插入一些记录,那几个记录必要符合以下原则:①不曾上过编号“2”课程的同学学号作为学号;②将“2”号课程的平分战表作为其成就; 

insert into sc select s.sno,2,(select avg(score) from sc where cno=2)
from student s where s.sno not in (select distinct(sno) from sc where
cno=2);

  (17)按平均战绩从低到高显示所有学生的“语文”、“数学”、“丹麦语”三门的科目成绩,按如下格局显得:
学生ID,语文,数学,保加利亚语,有效课程数,有效平均分; 【此处已补回15题中被删除的多寡】 

select sc0.sno as “学生ID”, (select score from sc where sno=sc0.sno and
cno =1) as “语文” , (select score from sc where sno=sc0.sno and cno =2)
as “数学” , (select score from sc where sno=sc0.sno and cno =3) as
“德语” , count(sc0.cno) as “有效课程数”, avg(sc0.score) as “有效平均分”
from sc sc0 group by sc0.sno order by avg(sc0.score);

  (18)查询各科战表最高和最低的分:以如下格局显得:课程ID,最高分,最低分; 

select cno as “课程ID”,max(score) as “最高分”,min(score) as “最低分”
from sc group by cno;

  (19)按各科平均战表从低到高和及格率的百分比从高到低依次;

select sc.cno as “课程ID”, c.cname as “课程名称”, avg(sc.score) as
“平均分”, 100*sum(case when sc.score >= 60 then 1 else 0
end)/count(sc.score) as “Percent(%)” from sc ,course c where sc.cno =
c.cno group by sc.cno order by avg(sc.score) desc ;

  (20)查询如下课程平均战绩和及格率的百分比(备注:须要在1行内显示): 公司管理(2),OO&UML
(3),数据库(4) 

select sum(case when cno=2 then score else 0 end)/sum(case when cno=2
then 1 else 0 end) as “集团管理平均战绩”, 100\
sum(case when cno=2 and
score >= 60 then 1 else 0 end)/sum(case when cno=2 then 1 else 0 end)
as “企业管理及格率(%)”, sum(case when cno=3 then score else 0
end)/sum(case when cno=3 then 1 else 0 end) as “OO&UML平均战绩”,
100*sum(case when cno=3 and score >= 60 then 1 else 0 end)/sum(case
when cno=3 then 1 else 0 end) as “OO&UML及格率(%)”, sum(case when cno=4
then score else 0 end)/sum(case when cno=4 then 1 else 0 end) as
“数据库平均成绩”, 100*sum(case when cno=4 and score >= 60 then 1
else 0 end)/sum(case when cno=4 then 1 else 0 end) as “数据库及格率(%)”
from sc;*

  (21)查询分歧老师所教分裂科目平均分从高到低突显; 

select t.tname as “老师姓名”, c.cname as “课程名称”, avg(sc.score) as
“平均分” from sc,teacher t,course c where t.tno=c.tno and c.cno=sc.cno
group by t.tno order by avg(sc.score) desc;

  (22)查询如下课程战绩第 3 名到第 6
名的学生成绩单:公司管理(1),马克思(2),UML (3),数据库(4)  

select distinct SC.Sno As “学生学号”, Student.Sname as “学生姓名” ,
T1.score as “集团管理”, T2.score as “马克思”, T3.score as “UML”,
T4.score as “数据库”, ifnull(T1.score,0) + ifnull(T2.score,0) +
ifnull(T3.score,0) + ifnull(T4.score,0) as “总分” from Student,SC left
join SC as T1 on SC.Sno = T1.Sno and T1.Cno = 1 left join SC as T2 on
SC.Sno = T2.Sno and T2.Cno = 2 left join SC as T3 on SC.Sno = T3.Sno and
T3.Cno = 3 left join SC as T4 on SC.Sno = T4.Sno and T4.Cno = 4 where
student.Sno=SC.Sno order by ifnull(T1.score,0) + ifnull(T2.score,0) +
ifnull(T3.score,0) + ifnull(T4.score,0) desc ;

  

 

http://www.bkjia.com/Mysql/1180975.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1180975.htmlTechArticleSQL面试笔试经典题(Part 1),sqlpart 本文是在 Cat
Qi的原贴的底子之上,经我逐题分别在MySql数据库中贯彻的笔记,持续更新…
参考原贴:…

相关文章

Your Comments

近期评论

    功能


    网站地图xml地图