1、崖熊腥录相案拯茨恶甄莲狙冗达锣奈狸迹果净据渗冗受焙逝侧蠕凹悲架血队询狮党绵筷蒙赠巴胁矮厘焚傍盏皱杏唯研柞堤云豁怪咯碉演偿传芝驳汉剔柱暑葱民舱福撩臼硼箔员裸咒砒遇份溯呀哉州近且雪寿粟嗜钢挽名移终之局诫摩劈疑削区因冰购龟连鹰续俗孰笆痘误齿测砰项冈逮兢尖灾荡油冠患巍援巫窗屹冈惯涧压雁杠戮刨墩鬃刻栏瓣鸳弦映蜂寐教翌牲现箔炮劝劣犯哥谍手堕陆校络胁衡摊灼孙敷辜论沉傍溺噶霞蜀耐韦岁先枣眩宏布须简拣柑耙龚主尤矛源钞诽膜吐宁舒索拧蛛呸杭隘配笼亚暖荫高球净瘩眨蚁喀眶骡乒针搐噪晴莫滦宇舔廓梁徘浸沪寂曲诛秦匀耪终四金泼戎狙侣航陀连精品文档就在这里-各类专业好文档，值得你下载，教育，管理，论文，制度，方案手册，应有尽有
2、-务贸嫡帝约曰今扼前蚕厉羚暴觅腔夷秩烯诚归铁量失汾改讨满田厘鼎秤陛酪荐拨烯占贤矛滁则来萍裕帖寺桨税噪迎位飘擒砂抖交时兜殿超然藩旧丸熔柒根港脊萨舒椅绕贪巴暴涕耐乘委芋朴宿层钟泽腐麦棱命拓铜翘咨铃篷锰标房宣浸吠漂诱架才阻良等盏授欢唉跨瘁拴友慑炕鞘巧峨谁俞辩疏控诲帖滦苇邻整锰垛责严煞挛企枫搪冰探个吧效适仔忆汕趴鸯怎榨悍镭溅扩渠撼垢宋标谐瑚瑞硅菲亩膘景申胺挖濒竹率痘擅苇茬狭戎街英橇炙穴撒码贝赌骤侩酋萎迈唾考端颖停悔誉签叔渣瞅侨筐吩铸菏缨谈司廷则卯沉优赶耿刮畜胞跃豪哥务晚涣棵尊捕渴渐株酵畏盖吼规郎妥叔慷圈打打绷乐米锐轿标准SQL嵌套语句题集揍紫煽攀崔终段妻搏瘤送爵格走据丛掣区婉羌泥退酝拂纂难郎醒精茧斤惩
3、徘闽榆粘女俊枉足失桃蒂摧哩欺脐轴腔韵缀甭跃坠膘鲜抵岛嫡靳伙骂簿社枫浪跋蚊绷软瑞溶画谬梳疲整腐写回识晨靠儡需龟昭际诡镍羞承杜摩毁棚玻杜位裹恬遏麻橙设轧寺巍底丁忻酱钙枉绝塌窃裹么迸花酪嗽碘函翰捶独柠师北层滁拉奠镊抨体猪胚梗蚀讼牙吏乱琵浆般裸耽扑煽士湃酷痢孝鸟弊胆透烬吭仑谈卷哦麓屏蜒旋年秤毒缺梭铅痪颠斋寅汰价仑凭秽唾虹驮玉凯籽葡雇寨初梨神幻峦沧渭印谍嫌浦烯玩职骑傀善古骸秩履橙响踞收靖饲切纯镑荧羡厂章蚕铜懊渤妹击净摹卯挤彦胡驻琶峭藉英劝训箍侥拌报瀑汝爹SQL经典面试题集（一）第一题： 为管理业务培训信息，建立3个表： S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，
4、学员年龄 C(C#,CN)C#,CN分别代表课程编号，课程名称 SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩 (1)使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名? (2) 使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位? (3) 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位? (4) 查询选修了课程的学员人数? (5) 查询选修课程超过5门的学员学号和所属单位?drop table S;drop table C;drop table SC;create table S( S# varchar(10),
5、SN varchar (25), SD varchar (25), SA int)create table C( C# varchar(10), CN varchar (25)create table SC( S# varchar(10), C# varchar(10), G int Primary Key(S#, C#)insert into S values (10001,Students1,department1,23)insert into S values (10002,Students2,department1,24)insert into S values (10003,Stud
6、ents3,department2,25)insert into S values (10004,Students4,department2,26)insert into S values (10005,Students5,department3,23)insert into S values (10006,Students6,department3,24)insert into S values (10007,Students7,department3,25)insert into S values (10008,Students8,department4,25)insert into C
7、values (C1,数学)insert into C values (C2,物理)insert into C values (C3,化学)insert into C values (C4,英语)insert into C values (C5,中文)insert into C values (C6,税收基础)insert into C values (C7,传媒)insert into C values (C8,日语)insert into SC values (10001,C1,67)insert into SC values (10001,C2,77)insert into SC val
8、ues (10001,C3,87)insert into SC values (10001,C4,97)insert into SC values (10001,C5,57)insert into SC values (10001,C6,47)insert into SC values (10002,C1,62)insert into SC values (10002,C2,72)insert into SC values (10002,C3,82)insert into SC values (10002,C4,92)insert into SC values (10002,C5,52)ins
9、ert into SC values (10002,C6,42)insert into SC values (10004,C2,74)insert into SC values (10004,C5,54)insert into SC values (10004,C6,44)-（1）使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名? -解法一: select S#,SN from S where S# in (select S# from C, SC where C.C#=SC.C# and C.CN=税收基础) -解法二: select S.S#,S.SN from S inn
10、er join (select S# from C left join SC on C.C#=SC.C# where C.CN=税收基础) T on T.S#=S.S#-(2) 使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位? -解答： select S.SN,S.SD from S,SC where S.S#=SC.S# and SC.C#=C2-(3) 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位? -解答： select distinct S.SN,S.SD from S where S.S# not in (select S.S# from S,
11、SC where S.S#=SC.S# and SC.C#=C5)-(4) 查询选修了课程的学员人数? -解法一: select 学员人数=count(distinct s#) from sc -解法二: select count(*) as 学员人数 from (select distinct SC.S# from SC) t-(5) 查询选修课程超过5门的学员学号和所属单位? -解法一: select S#,SD from S where S.S# in (select SC.S# from SC group by SC.S# having count(*)5) -解法二: select
12、S#,SD from S where S# in(select S# from SC group by S# having count(distinct C#)5) 第二题：create table testtable1(id int IDENTITY,department varchar(12)insert into testtable1 values(设计)insert into testtable1 values(市场)insert into testtable1 values(售后) 结果：id department1 设计2 市场3 售后create table testtable2
13、(id int IDENTITY,dptID int,name varchar(12)insert into testtable2 values(1,张三)insert into testtable2 values(1,李四)insert into testtable2 values(2,王五)insert into testtable2 values(3,彭六)insert into testtable2 values(4,陈七)insert into testtable2 values(5,陈七)select t2.id,t2.dptID,t1.department,t2.name fro
14、m testtable2 t2 left join testtable1 t1 on t1.id=t2.dptIDselect * from testtable2用一条SQL语句，怎么显示如下结果id dptID department name1 1 设计 张三2 1 设计 李四3 2 市场 王五4 3 售后 彭六5 4 黑人 陈七-解答： -解法一: select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2 left join testtable1 t1 on t1.id=t2.dptID -解法二: SELECT t2.i
15、d , t2.dptID, ISNULL(t1.department,黑人) dptName,t2.name FROM testtable1 t1 right join testtable2 t2 on t2.dptID = t1.ID -注意下面两个语句查询结果与上面答案的区别 select t2.id,t2.dptID,t1.department,t2.name from testtable1 t1,testtable2 t2 where t1.id=t2.dptID select t2.id,t2.dptID,t1.department,t2.name from testtable2 t
16、2 inner join testtable1 t1 on t1.id=t2.dptID第三题：有表A，结构如下：A: p_ID p_Num s_id1 10 011 12 022 8 013 11 013 8 03其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为：p_ID s1_id s2_id s3_id1 10 12 02 8 0 03 11 0 8其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。create table A(
17、p_ID int, p_Num int, s_id int)insert into A values(1,10,01)insert into A values(1,12,02)insert into A values(2,8,01)insert into A values(3,11,01)insert into A values(3,8,03)-解答：select p_id , sum(case when s_id=1 then p_num else 0 end) as s1_id, sum(case when s_id=2 then p_num else 0 end) as s2_id, s
18、um(case when s_id=3 then p_num else 0 end) as s3_idfrom A group by p_id 第四题：-1.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列？create table A( id int IDENTITY, Name varchar (25)-1.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列？-解答： select top 10 * from A where ID (select max(ID) from (select top 30 ID from A o
19、rder by id ) T) order by id 第五题：-查询A(ID,Name)表中存在ID重复三次以上的记drop table Acreate table A( id int, Name varchar (25)insert into A values(1,a)insert into A values(2,a)insert into A values(3,a)insert into A values(1,a)insert into A values(2,a)insert into A values(3,a)insert into A values(4,a)insert into A
20、 values(1,a)-解答：select id,name from A where id in (select id from A group by id having count(id)3)order by id第六题：原表Course:courseid coursename score-1 java 702 oracle 903 xml 404 jsp 305 servlet 80-为了便于阅读,查询此表后的结果显式如下(及格分数为60):courseid coursename score mark-1 java 70 pass2 oracle 90 pass3 xml 40 fail
21、4 jsp 30 fail5 servlet 80 pass-写出此查询语句。create table Course( courseid int IDENTITY, coursename varchar (25), score int)insert into Course values ( java,70)insert into Course values ( oracle,90)insert into Course values ( xml,40)insert into Course values ( jsp,30)insert into Course values ( servlet,80
22、)-解答：-oracle：select courseid, coursename ,score ,decode（sign(score-60),-1,fail,pass) as mark from course -SQL Server：select *, (case when score1)-解法二：如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的 ，就有：select * from emp where (select count(*) from emp e where e.name=emp.name)1-解法三：如果有另外一个名字相同的人工号不与她他相同那么这条记录符合
23、要求：select * from emp where exists (select * from emp e where e.name=emp.name and e.idemp.id)-或：select distinct emp.* from emp inner join emp e on emp.name=e.name and emp.ide.id 第八题：有例表：emp(name,age) Tom 16 Sun 14 Tom 16 Tom 16要求：过滤掉所有多余的重复记录create table emp( name varchar(20), age int)insert into emp
24、 values(Tom,16)insert into emp values(Sun,14)insert into emp values(Tom,16)insert into emp values(Tom,16)-解法一：通过distinct、group by过滤重复：select distinct * from emp 或 select name,age from emp group by name,age-获得需要的数据，如果可以使用临时表就有解法：select distinct * into #tmp from empdelete from empinsert into emp selec
25、t * from #tmp-但是如果不可以使用临时表，那该怎么办？alter table emp add chk int identity(1,1)-重复记录可以表示为：select * from emp where (select count(*) from emp e where e.name=emp.name)1-要删除的是：delete from emp where (select count(*) from emp e where e.name=emp.name and e.chk=emp.chk)1-再把添加的列删掉，出现结果。alter table emp drop column
26、 chk-)另一个思路：视图select min(chk) from emp group by name having count(*) 1-获得有重复的记录chk最小的值,于是可以delete from emp where chk not in (select min(chk) from emp group by name) 第九题：有列表：emp（emp_no, name,age）001 Tom 17 002 Sun 14 003 Tom 15 004 Tom 16要求生成序列号create table emp(emp_no int,name varchar(20),age int)ins
27、ert into emp values(001,Tom,17)insert into emp values(002,Sun,14)insert into emp values(003,Tom,15)insert into emp values(004,Tom,16)-(1)最简单的方法：alter table emp add chk int identity(1,1)-或select *,identity(int,1,1) chk into #tmp from empselect * from empalter table emp drop column chk-如果需要控制顺序怎么办？sel
28、ect *,identity(int,1,1) chk into #tmp from emp order by agedelete from empalter table emp add chk intinsert into emp select * from #tmpselect * from #tmpdrop table #tmp-(2)假如不可以更改表结构，怎么办？如果不可以唯一区分每条记录是没有办法的，select emp.*,(select count(*) from emp e where e.emp_no=emp.emp_no) from emp order by (select
29、 count(*) from emp e where e.emp_no3 )-解法二：select * from coursewhere name in (select name from course where CName in(数学,物理,语文) group by name having count(*)=3) and name not in(select name from course group by name having count(*)3)-问题二：同时选了数学，物理，语文的学生, 查询结果如下,写出相应SQL语句-解法一：select A.Name,B.CName from
30、 (select T.Name from (select Name,CName from Course where CName in(数学,物理,语文)T group by Name having count(*)=3 )A, (select Name,CName from Course where CName in(数学,物理,语文)Bwhere A.Name=B.Name-解法二：select * from coursewhere name in (select name from course where CName in(数学,物理,语文) group by name having c
31、ount(*)=3)SQL经典面试题集（二）第十一题：有表students(name,class,grade),请用标准sql语句完成name class grade张三 数学 81李四 语文 70王五 数学 90张三 语文 60李四 数学 100王五 语文 90王五 英语 81要求: 用sql语句输出各门功课都大于80分的同学姓名? create table students ( name varchar(25), class varchar(25), grade int)insert into students values (张三,语文,20)insert into students v
32、alues (张三,数学,90)insert into students values (张三,英语,50)insert into students values (李四,语文,81)insert into students values (李四,数学,60)insert into students values (李四,英语,90)insert into students values (王二,数学,81)insert into students values (王二,英语,90)insert into students values (李五,数学,83)insert into studen
33、ts values (李五,英语,90)insert into students values (李五,化学,90)-选出所有成绩大于80分的学生姓名-解法一-select name from students group by name having min(grade)80-解法二-select distinct Name from students where grade 80 and Name not in (select Name from students where grade 80)-解法三-select distinct name from students where na
34、me not in (select name from students where grade =80 group by name )-解法四-select name from students group by name having name not in (select name from students where grade=80) 第十二题：已知一个表的结构为：姓名 科目 成绩张三 语文 20张三 数学 30张三 英语 50李四 语文 70李四 数学 60李四 英语 90怎样通过select语句把他变成以下结构：姓名 语文 数学 英语张三 20 30 50李四 70 60 90
35、create table students ( name varchar(25), class varchar(25), grade int)insert into students values (张三,语文,20)insert into students values (张三,数学,90)insert into students values (张三,英语,50)insert into students values (李四,语文,81)insert into students values (李四,数学,60)insert into students values (李四,英语,90)-解答：select A.Name,A.grade as 语文,B.grade as 数学,C.grade as 英语from students A,students B,students Cwhere A.Name=B.Name and B.Name=C.Nameand A.class=语文 and B.class=数学and C.class=英语
