Java面試題-數據庫篇
來源：Java知音
作爲一枚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
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），馬克思（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],[學生姓名],企業管理,馬克思,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
