來源:Java知音

Java面試題-數據庫篇

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

相關文章