- 相關推薦
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以上的記錄。
3.sql面試題一條語句查詢每個部門共有多少人
前提:a 部門表 b 員工表
a表字段(
id --部門編號
departmentName-部門名稱
)
b表字段(
id--部門編號
employee- 員工名稱
)
問題:如何一條sql語句查詢出每個部門共有多少人
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表:學號(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 Student.Sno=SC.Sno and Course.Cno=SC.Cno group by Sname
方法2：對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：對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個表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 s.sname ,avg_grade from sjoin第一步的結果on s.sno = t.snojoin第二步的結果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錯誤的寫法:錯誤在于：求的是所有不及格的課程的平均分，而不是所有課程(包括及格的)的平均分執行順序:首先會執行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 (s.sno = t.sno);----------------------------------------------------------------------------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 (c.id = e.id)
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 (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);
--求每個部門的平均薪水的等級 //多表連接， 子查詢
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);
--求每個部門的平均的薪水等級
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;
--求雇員中有哪些人是經理人
select ename from emp
where empno in (select distinct mgr from emp );
--不準用組函數，求薪水的最高值 (面試題) //很變態，不公平就不公平
自連接：左邊表的數據小于右邊表的 最大的連接不上 //說起來很簡單
select distinct sal from emp
where sal not in (select distinct e1.sal from emp e1 join emp e2
on (e1.sal < e2.sal));
--求平均薪水最高的部門的部門編號
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
【Oracle 之 SQL面試題】相關文章：
SQL面試題07-12
SQL Server數據庫實訓總結11-18
c面試題08-04
華為面試題07-11
「MySQL」經典面試題07-11
面試題與技巧07-12
采購面試題07-11
面試題集錦07-11
Java面試題07-12
數據分析人員必須掌握的一些sql語句07-10