Oracle 之 SQL面試題

多上網查查 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