Oracle 之 SQL面試題
1.學號(自動編號) 姓名 性別 年齡
0001 xw 男 18
0002 mc 女 16
0003 ww 男 21
0004 xw 男 18
請寫出實現如下功能的SQL語句:
刪除除了學號(自動編號)字段以外,其它字段都相同的宂餘記錄!
DELETE FROM table1
WHERE (學號 NOT IN
(SELECT MAX(學號) AS xh
FROM TABLE1
GROUP BY 姓名, 性別, 年齡))
2.數據庫有3個表 teacher表 student表 tea_stu關係表 teacher表 teaID name age student表 stuID name age teacher_student表 teaID stuID 要求用一條sql查詢出這樣的結果: 1.顯示的字段要有老師id age 每個老師所帶的學生人數 2.只列出老師age為40以下 學生age為12以上的記錄。
面試題一條語句查詢每個部門共有多少人
前提:a 部門表 b 員工表
a表字段(
id --部門編號
departmentName-部門名稱
)
b表字段(
id--部門編號
employee- 員工名稱
)
問題:如何一條sql語句查詢出每個部門共有多少人
select count()as employeecount,rtmentName from a left join b on = group by ,rtmentName
4.有3張表,Student表、SC表和Course表
Student表:學號(Sno)、姓名(Sname)、性別(Ssex)、年齡(Sage)和系名(Sdept)
Course表:課程號(Cno)、課程名(Cname)和學分(Ccredit);
SC表:學號(Sno)、課程號(Cno)和成績(Grade)
請使用SQL語句查詢學生姓名及其課程總學分
(注:如果課程不及格,那麼此課程學分為0)
方法1:select Sname,sum(Ccredit) as totalCredit from Student,Course,SC where Grade>=60 and = and = group by Sname
方法2:對xyphoenix的修改
select sname,sum(case when e<60 then 0 else dit end) as totalCredit from Student,sc,course where = and = group by sname
方法3:對napolun180410的修改
select Sname,SUM(case when Grade<60 then 0 else Ccredit end) as totalGrade FROM SC JOIN Student ON( = ) JOIN Course ON( = ) GROUP BY e;
-------------------------------------------------------------------------有3個表S,C,SCS(SNO,SNAME)代表(學號,姓名)C(CNO,CNAME,CTEACHER)代表(課號,課名,教師)SC(SNO,CNO,SCGRADE)代表(學號,課號成績)問題:1,找出沒選過“黎明”老師的所有學生姓名。2,列出2門以上(含2門)不及格學生姓名及平均成績。3,即學過1號課程又學過2號課所有學生的姓名。請用標準SQL語言寫出答案,方言也行(請説明是使用什麼方言)。-----------------------------------------------------------------------------答案:S(SNO,SNAME)代表(學號,姓名)C(CNO,CNAME,CTEACHER)代表(課號,課名,教師)SC(SNO,CNO,SCGRADE)代表(學號,課號成績)select sno,sname from s;select cno,cname,cteacher from c;select sno,cno,scgrade from sc;問題1.找出沒選過“黎明”老師的所有學生姓名。第一步:求黎明老師教的所有課的課號select distinct cno from c where cteacher=黎明第二步:選了黎明老師的所有學生的編號select sno from sc where cno in (第一步的結果)第三步:沒有選黎明老師的所有學生的姓名select sname from s where sno not in (第二步的結果)即:select sname from s where sno not in (select sno from sc where cno in (select distinct cno from c where cteacher=黎明))----------------------------------------------------------------------------問題2:列出2門以上(含2門)不及格學生姓名及平均成績。第一步:2門以上不及格的學生的學號select sno from sc where scgrade < 60 group by sno having count(*) >= 2第二步:每個學生平均分select sno, avg(scgrade) as avg_grade from sc group by sno第三步:第一步中得到的學號對應的學生姓名以及平均分select e ,avg_grade from sjoin第一步的結果on = oin第二步的結果on = 即:select e ,avg_grade from sjoin(select sno, count(*) from sc where scgrade < 60 group by sno having count(*) >= 2)ton = oin(select sno, avg(scgrade) as avg_grade from sc group by sno )t1on = 錯誤的寫法:錯誤在於:求的是所有不及格的課程的`平均分,而不是所有課程(包括及格的)的平均分執行順序:首先會執行Where語句,將不符合選擇條件的記錄過濾掉,然後再將過濾後的數據按照group by子句中的字段進行分組,接着使用having子句過濾掉不符合條件的分組,然後再將剩下的數據排序顯示。select sname, avg_scgrade from s join(select sno, avg(scgrade) avg_scgrade from sc where scgrade < 60 group by sno having count(*) >= 2) ton ( = );----------------------------------------------------------------------------select sno,sname from s;select cno,cname,cteacher from c;select sno,cno,scgrade from sc;問題3:即學過1號課程又學過2號課所有學生的姓名。第一步:學過1號課程的學號select sno from sc where cno = 1第二步:學過2號課程的學號select sno from sc where cno = 2第三步:即學過1號課程又學過2號課的學號select sno from sc where cno =1 and sno in (select sno from sc where cno = 2)第四步:得到姓名select sname from s where sno in (select sno from sc where cno = 1 and sno in (select sno from sc where cno = 2))或者:select sname from s wheresno in (select sno from sc where cno = 1)andsno in (select sno from sc where cno = 2)
company 公司名(companyname) 編號(id)
LS 6
DG 9
GR 19
employeehired
公司(id) 人數(number) 財季(fiscalquarter)
6 2 1
9 2 4
19 4 1
1.找出表中的主鍵: company(id) employeehired (id)+(fiscalquarter)
2.找出表之間關係: 外鍵關係, employeehired (id) 參考 company (id)
3.求第四財季招聘過員工的公司名稱:
select companyname from company c join employeehired e
on ( = )
where fiscalquarter = 4;
4.求從1到3財季從沒有招聘過員工的公司名稱 //同理1到4財季
select companyname from company
where id not in
(select distinct id from employeehired
where fiscalquarter not in(1,2,3)
);
5.求從1到4財季之間招聘過員工的公司名稱和他們各自招聘的員工總數
select companyname , sum_numhired from company c join
(
select sum(numhired) sum_numhired from employeehired group by id
) t
on (_numhired = _numhired);
--求部門中哪些人的薪水最高
select ename, sal from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on ( = _sal and no = no);
--求每個部門的平均薪水的等級 //多表連接, 子查詢
select deptno, avg_sal, grade from //從下面表中取,下表必須有字段
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (_sal between l and l);
--求每個部門的平均的薪水等級
select deptno, avg(grade) from
(select deptno, ename, grade from emp join salgrade s
on ( between l and l)) t
group by deptno;
--求僱員中有哪些人是經理人
select ename from emp
where empno in (select distinct mgr from emp );
--不準用組函數,求薪水的最高值 (面試題) //很變態,不公平就不公平
自連接:左邊表的數據小於右邊表的 最大的連接不上 //説起來很簡單
select distinct sal from emp
where sal not in (select distinct from emp e1 join emp e2
on ( < ));
--求平均薪水最高的部門的部門編號
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno)
);
///////////另解../////////////////////////////
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno);
////////組函數嵌套,不過只能套2層,因為多行輸入,單行輸出//////////
--求平均薪水最高的部門的部門名稱
select dname from dept where deptno =
(
select deptno from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno)
)
);
--求平均薪水的等級最低的部門的部門名稱 //太複雜了 PL SQL
//從裏到外
1.平均薪水:select deptno, avg(sal) from emp group by deptno;
2.平均薪水的等級:
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal fr
相關文章
-
葡萄牙語的歷史:Revolucionários apossam-se de Porto Alegre
No dia seguinte, os revolucionários penetraram na cidade sem encontrar oposi??o. O Corpo de Permanentes aderiu logo ao movimento, com exce??o do comandante, do subcomandante, de um cabo, de um soldad -
英語面試問題:Which college classes did you like the
謎面不負所望謎語類型打一棋牌詞語謎底贏張 -
i recall with embarrassing clarity the flavor of those particular ashes ?
餐飲店服務員工管理制度(一):1、遵守考勤制度,上崗前檢查個人儀容儀表,以飽滿精神狀態投入工作。2、瞭解例會內容,及當天工作安排,熟記當天菜品酒水供應情況,急推、沽清與特色菜品等。3、餐前檢查各區域的設施、照明系統,餐 -
Jason Mraz & Colbie Caillat的Lucky樂評
Jason Mraz & Colbie Caillat演唱的歌曲《Lucky》,以輕快柔美的旋律和兩位演唱者動聽的聲音征服了嚴格的格萊美評委們,獲得了2010年第52屆格萊美最佳流行合作歌曲。下面就讓我們來看看相關樂評吧。Jason Mraz & -
英語面試問題:Tell Me About Yourself
You walk into the interview room, shake hands with your interviewer and sit down with your best interviewing smile on. Guess what their first question is? "Tell me about yourself."Do you "wing it" and -
冀教版五下Lesson11《Mr. Wood Teaches a Lesson》教學反思
在日復一日的學習、工作或生活中,許多人都有過寫作文的經歷,對作文都不陌生吧,藉助作文人們可以反映客觀事物、表達思想感情、傳遞知識信息。寫起作文來就毫無頭緒?以下是小編精心整理的感動的作文400字3篇,希望能夠幫助到 -
《Kelly Clarkson: Stronger》歌詞及相關介紹
《Kelly Clarkson: Stronger》歌詞及相關介紹Kelly Clarkson: StrongerYou know the bed feels warmerSleeping here aloneYou know I dream in colourAnd do the things I wantYou think you got the best of me -
Kelly Clarkson《Stronger》中文歌詞對照震撼獻唱視頻
導語:《Stronger(WhatDoesn'tKillYou)》一曲獲得年度最佳錄音和年度最佳歌曲兩項提名,而Kelly本人憑藉這一曲也獲得最佳流行歌手提名!以下是本站小編整理的Kelly Clarkson《Stronger》中文歌詞對照震撼獻唱視頻,歡迎 -
Kelly Clarkson because of you諧音歌詞
六月的第三個星期天就要到來了,同學們那一天是什麼節日呢?對!那一天就是父親節啊!同學們你們知道父親節的由來嗎?原來它源於美國,在州士波肯市有一個杜德夫人,當她參加完教會舉辦的母親節主日崇拜之後,杜德夫人的心裏有了 -
有關Oracle 數據庫連接查詢SQL語句
內連接(inner join)。外連接:全連接(full join)、左連接(left join)、右連接(right join)。交叉聯接(cross join)。外連接與內連接不一樣,外連接返回的查詢結果中不僅包含符合條件的行,還包括左表(左外連接),右表(右外連接)或者