本篇內(nèi)容介紹了“java中數(shù)據(jù)庫的面試題有哪些”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

成都創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站建設(shè)、網(wǎng)站設(shè)計、如東網(wǎng)絡(luò)推廣、小程序定制開發(fā)、如東網(wǎng)絡(luò)營銷、如東企業(yè)策劃、如東品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；成都創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供如東建站搭建服務(wù)，24小時服務(wù)熱線：18980820575，官方網(wǎng)址：www.cdcxhl.com

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

        student(sno,sname,sage,ssex)學(xué)生表
        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的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號和姓名
這一題分兩步查：

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表中插入一些記錄，這些記錄要求符合以下條件：
將沒有課程3成績同學(xué)的該成績補齊, 其成績?nèi)∷袑W(xué)生的課程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、按平平均分從高到低顯示所有學(xué)生的如下統(tǒng)計報表：
-- 學(xué)號,企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,物理,課程數(shù),平均分
select sno as 學(xué)號
,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ù)庫
,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、查詢各科成績最高分和最低分：

以如下形式顯示：課程號，最高分，最低分
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、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低順序
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、查詢?nèi)缦抡n程平均成績和及格率的百分?jǐn)?shù)(用"1行"顯示):

企業(yè)管理（001），馬克思（002），UML （003），數(shù)據(jù)庫（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程成績均在第3名到第6名之間的學(xué)生的成績：
-- [學(xué)生ID],[學(xué)生姓名],企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,平均成績
select top 6 max(a.sno) 學(xué)號, 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ù)庫,
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中數(shù)據(jù)庫的面試題有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！