Java的數(shù)據(jù)庫(kù)面試題有哪些

這篇文章主要介紹“Java的數(shù)據(jù)庫(kù)面試題有哪些”，在日常操作中，相信很多人在Java的數(shù)據(jù)庫(kù)面試題有哪些問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”Java的數(shù)據(jù)庫(kù)面試題有哪些”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

基本表結(jié)構(gòu)：

        student(sno,sname,sage,ssex)學(xué)生表
        course(cno,cname,tno) 課程表
        sc(sno,cno,score) 成績(jī)表

        teacher(tno,tname) 教師表

111、把“sc”表中“王五”所教課的成績(jī)都更改為此課程的平均成績(jī)
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、查詢和編號(hào)為2的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名
這一題分兩步查：

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、刪除學(xué)習(xí)“王五”老師課的sc表記錄
delete sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'

114、向sc表中插入一些記錄，這些記錄要求符合以下條件：
將沒(méi)有課程3成績(jī)同學(xué)的該成績(jī)補(bǔ)齊, 其成績(jī)?nèi)∷袑W(xué)生的課程2的平均成績(jī)
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、按平平均分從高到低顯示所有學(xué)生的如下統(tǒng)計(jì)報(bào)表：
-- 學(xué)號(hào),企業(yè)管理,馬克思,UML,數(shù)據(jù)庫(kù),物理,課程數(shù),平均分
select sno as 學(xué)號(hào)
,max(case when cno = 1 then score end) AS 企業(yè)管理
,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 數(shù)據(jù)庫(kù)
,max(case when cno = 5 then score end) AS 物理
,count(cno) AS 課程數(shù)
,avg(score) AS 平均分
FROM sc
GROUP by sno
ORDER by avg(score) DESC

116、查詢各科成績(jī)最高分和最低分：

以如下形式顯示：課程號(hào)，最高分，最低分
select cno as 課程號(hào), max(score) as 最高分, min(score) 最低分
from sc group by cno

select  course.cno as '課程號(hào)'
,MAX(score) as '最高分'
,MIN(score) as '最低分'
from sc,course
where sc.cno=course.cno
group by course.cno

117、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序
SELECT t.cno AS 課程號(hào),
max(course.cname)AS 課程名,
isnull(AVG(score),0) AS 平均成績(jī),
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、查詢?nèi)缦抡n程平均成績(jī)和及格率的百分?jǐn)?shù)(用"1行"顯示):

企業(yè)管理（001），馬克思（002），UML （003），數(shù)據(jù)庫(kù)（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、查詢?nèi)缦抡n程成績(jī)均在第3名到第6名之間的學(xué)生的成績(jī)：
-- [學(xué)生ID],[學(xué)生姓名],企業(yè)管理,馬克思,UML,數(shù)據(jù)庫(kù),平均成績(jī)
select top 6 max(a.sno) 學(xué)號(hào), max(b.sname) 姓名,
max(case when cno = 1 then score end) as 企業(yè)管理,
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 數(shù)據(jù)庫(kù),
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

到此，關(guān)于“Java的數(shù)據(jù)庫(kù)面試題有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！