多上網(wǎng)查查 SQL 面試題
1.學號(自動編號) 姓名 性別 年齡-
0001 xw 男 18-
0002 mc 女 16-
0003 ww 男 21-
0004 xw 男 18-
請寫出實現(xiàn)如下功能的SQL語句:-
刪除除了學號（自動編號）字段以外，其它字段都相同的冗余記錄！-
DELETE FROM table1-WHERE (學號 NOT IN-(SELECT MAX(學號) AS xh-FROM TABLE1-GROUP BY 姓名, 性別, 年齡))
2.數(shù)據(jù)庫有3個表 teacher表 student表 tea_stu關(guān)系表 teacher表 teaID name age student表 stuID name age teacher_student表 teaID stuID 要求用一條sql查詢出這樣的結(jié)果: 1.顯示的字段要有老師id age 每個老師所帶的學生人數(shù) 2.只列出老師age為40以下 學生age為12以上的記錄。
select a.teaID,a.age count(*)from teacher a,student b,teacher_student cwhere a.teaID=c.teaIDand b.stuID=c.stuIDand a.age>40and b.age>12group by a.teaID,a.age;
-
3.sql面試題一條語句查詢每個部門共有多少人-
前提:a 部門表 b 員工表 -
a表字段( -
id --部門編號 -
departmentName-部門名稱 -
) -
b表字段( -
id--部門編號 -
employee- 員工名稱 -
) -
問題:如何一條sql語句查詢出每個部門共有多少人-
select a.department,count fromtA a,tB bwhere a.id=b.idgroup by b.id,a,deparment
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，SC
S（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 (
第一步的結(jié)果
)
第三步:沒有選黎明老師的所有學生的姓名
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門）不及格學生姓名及平均成績。
第一步: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
第三步:第一步中得到的學號對應(yīng)的學生姓名以及平均分
select s.sname ,avg_grade from s
join
第一步的結(jié)果
on s.sno = t.sno
join
第二步的結(jié)果
on s.sno = t1.sno
即:
select s.sname ,avg_grade from s join (select sno, count(*) from sc where scgrade < 60 group by sno having count(*) >= 2)t on s.sno = t.sno join (select sno, avg(scgrade) as avg_grade from sc group by sno )t1 on s.sno = t1.sno
錯誤的寫法:
錯誤在于：求的是所有不及格的課程的平均分，而不是所有課程（包括及格的）的平均分
執(zhí)行順序:
首先會執(zhí)行Where語句，將不符合選擇條件的記錄過濾掉，
然后再將過濾后的數(shù)據(jù)按照group by子句中的字段進行分組，
接著使用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:即學過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 where sno in (select sno from sc where cno = 1) and sno in (select sno from sc where cno = 2)company 公司名(companyname) 編號(id)
LS 6
DG 9
GR 19
employeehired
公司(id) 人數(shù)(number) 財季(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.求第四財季招聘過員工的公司名稱:
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財季之間招聘過員工的公司名稱和他們各自招聘的員工總數(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);
--求部門中哪些人的薪水最高----此處開始使用的是scott賬戶下的自帶表
select ename, sal from empjoin (select max(sal) max_sal, deptno from emp group by deptno) ton (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) tjoin 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 son (emp.sal between s.losal and s.hisal)) tgroup by deptno;
--求雇員中有哪些人是經(jīng)理人
select ename from empwhere empno in (select distinct mgr from emp );
--不準用組函數(shù)，求薪水的最高值 （面試題） //很變態(tài)，不公平就不公平
自連接：左邊表的數(shù)據(jù)小于右邊表的 最大的連接不上 //說起來很簡單
select distinct sal from empwhere sal not in (select distinct e1.sal from emp e1 join emp e2on (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);
////////組函數(shù)嵌套，不過只能套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) ));
--求平均薪水的等級最低的部門的部門名稱 //太復(fù)雜了 PL SQL
//從里到外
---先求出每個員工的薪水等級，然后再按照部門求出平均薪水等級
select avg_grade,deptno from(select avg(grade) avg_grade,deptno( select grade,empno,deptno from emp e join salgrade son(e.sal between s.losal adn s.hisal))group by deptno)
----完整的----
select empname ,avg_gradedept d join(select deptno,avg(grade) as avg_grade from (select deptno,empno,grade from emp e join salgrade son(e.sal between s.losal and s.hisal))group by deptno)t1on d.depno=t1.deptno;
1.平均薪水：select deptno, avg(sal) from emp group by deptno;
2.平均薪水的等級：
select deptno, grade, avg_sal 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);
3.平均薪水最低的等級：
select min (grade) from ( select deptno, grade, avg_sal 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) );
4.平均薪水最低的等級的部門：顯示部門 2.連接dept表
select dname, t1.deptno, grade, avg_sal from // deptno 未明確定義列 (select deptno, grade, avg_sal 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) ) t1join dept on (t1.deptno = dept.deptno)where t1.grade =( select min (grade) from ( select deptno, grade, avg_sal 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) )); //有完全重復(fù)的地方
：：：：：：：創(chuàng)建視圖，視圖就是表，子查詢:虛表 ，鏈接：：：：：：：：
create view v$_dept_avg_sal_info as select deptno, grade, avg_sal 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);
//視圖已創(chuàng)建；
/////////不能建表 ，權(quán)限不足
conn sys/10023 as sysdba;
grant create table, create view to sctt;
/////////默認是可以建表的；
select * from v$_dept_avg_sal_info;
5.化簡
select dname, t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1join dept on (t1.deptno = dept.deptno)where t1.grade =( select min (grade) from v$_dept_avg_sal_info);
--求部門經(jīng)理人中平均薪水最低的部門名稱 (思考題)
--求比普通員工的最高薪水還要高的經(jīng)理人名稱
1.select distinct mgr from emp; //king'mgr is null;2.select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);3.select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > ( select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null) );
--求薪水最高的前5名雇員
--求薪水最高的第6到第10名雇員(重點掌握)
--練習： 求最后入職的5名員工
--面試題： 比較效率
select * from emp where deptno = 10 and ename like '%A%';select * from emp where ename like '%A%' and deptno = 10;
////////數(shù)字不對，后面就不用看了 ，先比較數(shù)字快；//也許Oracle有優(yōu)化
//CSDN - 專家門診 MS-SQL Server
:::::::::::::::::::::::::::::::::::回家作業(yè):::::::::::::::::::::::::::::::::::::::::::
一個簡單的表TABLE 有100條以上的信息， 其中包括：
產(chǎn)品 顏色 數(shù)量
產(chǎn)品1 紅色 123
產(chǎn)品1 藍色 126
產(chǎn)品2 藍色 103
產(chǎn)品2 紅色 NULL
產(chǎn)品2 紅色 89
產(chǎn)品1 紅色 203
…………………………
請用SQL語句完成一下問題： 沒有主鍵
1.按產(chǎn)品分類，僅列出各類商品中紅色多于藍色的商品名稱及差額數(shù)量：
2.按產(chǎn)品分類，將數(shù)據(jù)按下列方式進行統(tǒng)計顯示
產(chǎn)品 紅色 藍色
聯(lián)系客服
