- 相關(guān)推薦
Oracle 之 SQL面試題
多上網(wǎng)查查 SQL 面試題
1.學(xué)號(hào)(自動(dòng)編號(hào)) 姓名 性別 年齡
0001 xw 男 18
0002 mc 女 16
0003 ww 男 21
0004 xw 男 18
請(qǐng)寫出實(shí)現(xiàn)如下功能的SQL語句:
刪除除了學(xué)號(hào)(自動(dòng)編號(hào))字段以外，其它字段都相同的冗余記錄!
DELETE FROM table1
WHERE (學(xué)號(hào) NOT IN
(SELECT MAX(學(xué)號(hào)) AS xh
FROM TABLE1
GROUP BY 姓名, 性別, 年齡))
2.數(shù)據(jù)庫有3個(gè)表 teacher表 student表 tea_stu關(guān)系表 teacher表 teaID name age student表 stuID name age teacher_student表 teaID stuID 要求用一條sql查詢出這樣的結(jié)果: 1.顯示的字段要有老師id age 每個(gè)老師所帶的學(xué)生人數(shù) 2.只列出老師age為40以下 學(xué)生age為12以上的記錄。
3.sql面試題一條語句查詢每個(gè)部門共有多少人
前提:a 部門表 b 員工表
a表字段(
id --部門編號(hào)
departmentName-部門名稱
)
b表字段(
id--部門編號(hào)
employee- 員工名稱
)
問題:如何一條sql語句查詢出每個(gè)部門共有多少人
select count(b.id)as employeecount,a.departmentName from a left join b on a.id=b.id group by b.id,a.departmentName
4.有3張表，Student表、SC表和Course表
Student表:學(xué)號(hào)(Sno)、姓名(Sname)、性別(Ssex)、年齡(Sage)和系名(Sdept)
Course表:課程號(hào)(Cno)、課程名(Cname)和學(xué)分(Ccredit);
SC表：學(xué)號(hào)(Sno)、課程號(hào)(Cno)和成績(Grade)
請(qǐng)使用SQL語句查詢學(xué)生姓名及其課程總學(xué)分
(注：如果課程不及格，那么此課程學(xué)分為0)
方法1：select Sname,sum(Ccredit) as totalCredit from Student,Course,SC where Grade>=60 and Student.Sno=SC.Sno and Course.Cno=SC.Cno group by Sname
方法2：對(duì)xyphoenix的修改
select sname,sum(case when sc.grade<60 then 0 else course.Ccredit end) as totalCredit from Student,sc,course where sc.sno=student.sno and sc.cno=course.cno group by sname
方法3：對(duì)napolun180410的修改
select Sname,SUM(case when Grade<60 then 0 else Ccredit end) as totalGrade FROM SC JOIN Student ON(Student.sno = SC.sno) JOIN Course ON(SC.Cno = Course.Cno) GROUP BY Student.Sname;
-------------------------------------------------------------------------有3個(gè)表S，C，SCS(SNO，SNAME)代表(學(xué)號(hào)，姓名)C(CNO，CNAME，CTEACHER)代表(課號(hào)，課名，教師)SC(SNO，CNO，SCGRADE)代表(學(xué)號(hào)，課號(hào)成績)問題：1，找出沒選過“黎明”老師的所有學(xué)生姓名。2，列出2門以上(含2門)不及格學(xué)生姓名及平均成績。3，即學(xué)過1號(hào)課程又學(xué)過2號(hào)課所有學(xué)生的姓名。請(qǐng)用標(biāo)準(zhǔn)SQL語言寫出答案，方言也行(請(qǐng)說明是使用什么方言)。-----------------------------------------------------------------------------答案:S(SNO，SNAME)代表(學(xué)號(hào)，姓名)C(CNO，CNAME，CTEACHER)代表(課號(hào)，課名，教師)SC(SNO，CNO，SCGRADE)代表(學(xué)號(hào)，課號(hào)成績)select sno,sname from s;select cno,cname,cteacher from c;select sno,cno,scgrade from sc;問題1.找出沒選過“黎明”老師的所有學(xué)生姓名。第一步:求黎明老師教的所有課的課號(hào)select distinct cno from c where cteacher=黎明第二步:選了黎明老師的所有學(xué)生的編號(hào)select sno from sc where cno in (第一步的結(jié)果)第三步:沒有選黎明老師的所有學(xué)生的姓名select sname from s where sno not in (第二步的結(jié)果)即: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門)不及格學(xué)生姓名及平均成績。第一步:2門以上不及格的學(xué)生的學(xué)號(hào)select sno from sc where scgrade < 60 group by sno having count(*) >= 2第二步:每個(gè)學(xué)生平均分select sno, avg(scgrade) as avg_grade from sc group by sno第三步:第一步中得到的學(xué)號(hào)對(duì)應(yīng)的學(xué)生姓名以及平均分select s.sname ,avg_grade from sjoin第一步的結(jié)果on s.sno = t.snojoin第二步的結(jié)果on s.sno = t1.sno即:select s.sname ,avg_grade from sjoin(select sno, count(*) from sc where scgrade < 60 group by sno having count(*) >= 2)ton s.sno = t.snojoin(select sno, avg(scgrade) as avg_grade from sc group by sno )t1on s.sno = t1.sno錯(cuò)誤的寫法:錯(cuò)誤在于：求的是所有不及格的課程的平均分，而不是所有課程(包括及格的)的平均分執(zhí)行順序:首先會(huì)執(zhí)行Where語句，將不符合選擇條件的記錄過濾掉，然后再將過濾后的數(shù)據(jù)按照group by子句中的字段進(jìn)行分組，接著使用having子句過濾掉不符合條件的分組，然后再將剩下的數(shù)據(jù)排序顯示。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 (s.sno = t.sno);----------------------------------------------------------------------------select sno,sname from s;select cno,cname,cteacher from c;select sno,cno,scgrade from sc;問題3:即學(xué)過1號(hào)課程又學(xué)過2號(hào)課所有學(xué)生的姓名。第一步:學(xué)過1號(hào)課程的學(xué)號(hào)select sno from sc where cno = 1第二步:學(xué)過2號(hào)課程的學(xué)號(hào)select sno from sc where cno = 2第三步:即學(xué)過1號(hào)課程又學(xué)過2號(hào)課的學(xué)號(hào)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) 編號(hào)(id)
LS 6
DG 9
GR 19
employeehired
公司(id) 人數(shù)(number) 財(cái)季(fiscalquarter)
6 2 1
9 2 4
19 4 1
1.找出表中的主鍵: company(id) employeehired (id)+(fiscalquarter)
2.找出表之間關(guān)系: 外鍵關(guān)系， employeehired (id) 參考 company (id)
3.求第四財(cái)季招聘過員工的公司名稱:
select companyname from company c join employeehired e
on (c.id = e.id)
where fiscalquarter = 4;
4.求從1到3財(cái)季從沒有招聘過員工的公司名稱 //同理1到4財(cái)季
select companyname from company
where id not in
(select distinct id from employeehired
where fiscalquarter not in(1,2,3)
);
5.求從1到4財(cái)季之間招聘過員工的公司名稱和他們各自招聘的員工總數(shù)
select companyname , sum_numhired from company c join
(
select sum(numhired) sum_numhired from employeehired group by id
) t
on (c.sum_numhired = t.sum_numhired);
--求部門中哪些人的薪水最高
select ename, sal from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
--求每個(gè)部門的平均薪水的等級(jí) //多表連接， 子查詢
select deptno, avg_sal, grade from //從下面表中取，下表必須有字段
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
--求每個(gè)部門的平均的薪水等級(jí)
select deptno, avg(grade) from
(select deptno, ename, grade from emp join salgrade s
on (emp.sal between s.losal and s.hisal)) t
group by deptno;
--求雇員中有哪些人是經(jīng)理人
select ename from emp
where empno in (select distinct mgr from emp );
--不準(zhǔn)用組函數(shù)，求薪水的最高值 (面試題) //很變態(tài)，不公平就不公平
自連接：左邊表的數(shù)據(jù)小于右邊表的 最大的連接不上 //說起來很簡單
select distinct sal from emp
where sal not in (select distinct e1.sal from emp e1 join emp e2
on (e1.sal < e2.sal));
--求平均薪水最高的部門的部門編號(hào)
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);
////////組函數(shù)嵌套，不過只能套2層，因?yàn)槎嘈休斎�，單行輸�?/////////
--求平均薪水最高的部門的部門名稱
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)
)
);
--求平均薪水的等級(jí)最低的部門的部門名稱 //太復(fù)雜了 PL SQL
//從里到外
1.平均薪水：select deptno, avg(sal) from emp group by deptno;
2.平均薪水的等級(jí)：
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal fr
【Oracle 之 SQL面試題】相關(guān)文章：
SQL面試題07-12
SQL Server數(shù)據(jù)庫實(shí)訓(xùn)總結(jié)11-18
c面試題08-04
華為面試題07-11
「MySQL」經(jīng)典面試題07-11
面試題與技巧07-12
采購面試題07-11
面試題集錦07-11
Java面試題07-12