标准SQL嵌套语句题集.docx
《标准SQL嵌套语句题集.docx》由会员分享，可在线阅读，更多相关《标准SQL嵌套语句题集.docx（51页珍藏版）》请在冰豆网上搜索。
标准SQL嵌套语句题集
SQL经典面试题集
（一）
第一题：
为管理业务培训信息，建立3个表：
S（S#,SN,SD,SA）S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄
C（C#,CN）C#,CN分别代表课程编号，课程名称
SC（S#,C#,G）S#,C#,G分别代表学号，所选的课程编号，学习成绩
（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
（2）使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
（3）使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
（4）查询选修了课程的学员人数?
（5）查询选修课程超过5门的学员学号和所属单位?
droptableS;
droptableC;
droptableSC;
createtableS
（
S#varchar（10）,
SNvarchar（25）,
SDvarchar（25）,
SAint
）
createtableC
（
C#varchar（10）,
CNvarchar（25）
）
createtableSC
（
S#varchar（10）,
C#varchar（10）,
Gint
PrimaryKey（S#,C#）
）
insertintoSvalues（'10001','Students1','department1',23）
insertintoSvalues（'10002','Students2','department1',24）
insertintoSvalues（'10003','Students3','department2',25）
insertintoSvalues（'10004','Students4','department2',26）
insertintoSvalues（'10005','Students5','department3',23）
insertintoSvalues（'10006','Students6','department3',24）
insertintoSvalues（'10007','Students7','department3',25）
insertintoSvalues（'10008','Students8','department4',25）
insertintoCvalues（'C1','数学'）
insertintoCvalues（'C2','物理'）
insertintoCvalues（'C3','化学'）
insertintoCvalues（'C4','英语'）
insertintoCvalues（'C5','中文'）
insertintoCvalues（'C6','税收基础'）
insertintoCvalues（'C7','传媒'）
insertintoCvalues（'C8','日语'）
insertintoSCvalues（'10001','C1',67）
insertintoSCvalues（'10001','C2',77）
insertintoSCvalues（'10001','C3',87）
insertintoSCvalues（'10001','C4',97）
insertintoSCvalues（'10001','C5',57）
insertintoSCvalues（'10001','C6',47）
insertintoSCvalues（'10002','C1',62）
insertintoSCvalues（'10002','C2',72）
insertintoSCvalues（'10002','C3',82）
insertintoSCvalues（'10002','C4',92）
insertintoSCvalues（'10002','C5',52）
insertintoSCvalues（'10002','C6',42）
insertintoSCvalues（'10004','C2',74）
insertintoSCvalues（'10004','C5',54）
insertintoSCvalues（'10004','C6',44）
--
（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
--解法一:
selectS#,SNfromSwhereS#in（selectS#fromC,SCwhereC.C#=SC.C#andC.CN='税收基础'）
--解法二:
selectS.S#,S.SNfromSinnerjoin（selectS#fromCleftjoinSConC.C#=SC.C#whereC.CN='税收基础'）TonT.S#=S.S#
--
（2）使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
--解答：
selectS.SN,S.SDfromS,SCwhereS.S#=SC.S#andSC.C#='C2'
--（3）使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
--解答：
selectdistinctS.SN,S.SDfromSwhereS.S#notin（selectS.S#fromS,SCwhereS.S#=SC.S#andSC.C#='C5'）
--（4）查询选修了课程的学员人数?
--解法一:
select学员人数=count（distincts#）fromsc
--解法二:
selectcount（*）as学员人数from（selectdistinctSC.S#fromSC）t
--（5）查询选修课程超过5门的学员学号和所属单位?
--解法一:
selectS#,SDfromSwhereS.S#in（selectSC.S#fromSCgroupbySC.S#havingcount（*）>5）
--解法二:
selectS#,SDfromSwhereS#in（selectS#fromSCgroupbyS#havingcount（distinctC#）>5）
第二题：
createtabletesttable1
（
idintIDENTITY,
departmentvarchar（12）
）
insertintotesttable1values（'设计'）
insertintotesttable1values（'市场'）
insertintotesttable1values（'售后'）
结果：
iddepartment
1设计
2市场
3售后
createtabletesttable2
（
idintIDENTITY,
dptIDint,
namevarchar（12）
）
insertintotesttable2values（1,'张三'）
insertintotesttable2values（1,'李四'）
insertintotesttable2values（2,'王五'）
insertintotesttable2values（3,'彭六'）
insertintotesttable2values（4,'陈七'）
insertintotesttable2values（5,'陈七'）
selectt2.id,t2.dptID,t1.department,t2.namefromtesttable2t2leftjointesttable1t1ont1.id=t2.dptID
select*fromtesttable2
用一条SQL语句，怎么显示如下结果
iddptIDdepartmentname
11设计张三
21设计李四
32市场王五
43售后彭六
54黑人陈七
--解答：
--解法一:
selectt2.id,t2.dptID,t1.department,t2.namefromtesttable2t2leftjointesttable1t1ont1.id=t2.dptID
--解法二:
SELECTt2.id,t2.dptID,ISNULL（t1.department,'黑人'）dptName,t2.nameFROMtesttable1t1rightjointesttable2t2ont2.dptID=t1.ID
--注意下面两个语句查询结果与上面答案的区别
selectt2.id,t2.dptID,t1.department,t2.namefromtesttable1t1,testtable2t2wheret1.id=t2.dptID
selectt2.id,t2.dptID,t1.department,t2.namefromtesttable2t2innerjointesttable1t1ont1.id=t2.dptID
第三题：
有表A，结构如下：
A:
p_IDp_Nums_id
11001
11202
2801
31101
3803
其中：
p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。
请用SQL语句实现将上表中的数据合并，合并后的数据为：
p_IDs1_ids2_ids3_id
110120
2800
31108
其中：
s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。
如果该产品在某仓库中无库存量，那么就是0代替。
createtableA
（
p_IDint,
p_Numint,
s_idint
）
insertintoAvalues（1,10,01）
insertintoAvalues（1,12,02）
insertintoAvalues（2,8,01）
insertintoAvalues（3,11,01）
insertintoAvalues（3,8,03）
--解答：
selectp_id,
sum（casewhens_id=1thenp_numelse0end）ass1_id,
sum（casewhens_id=2thenp_numelse0end）ass2_id,
sum（casewhens_id=3thenp_numelse0end）ass3_id
fromAgroupbyp_id
第四题：
--1.查询A（ID,Name）表中第31至40条记录，ID作为主键可能是不是连续增长的列？
createtableA
（
idintIDENTITY,
Namevarchar（25）
）
--1.查询A（ID,Name）表中第31至40条记录，ID作为主键可能是不是连续增长的列？
--解答：
selecttop10*fromAwhereID>（selectmax（ID）from（selecttop30IDfromAorderbyid）T）orderbyid
第五题：
--查询A（ID,Name）表中存在ID重复三次以上的记
droptableA
createtableA
（
idint,
Namevarchar（25）
）
insertintoAvalues（1,'a'）
insertintoAvalues（2,'a'）
insertintoAvalues（3,'a'）
insertintoAvalues（1,'a'）
insertintoAvalues（2,'a'）
insertintoAvalues（3,'a'）
insertintoAvalues（4,'a'）
insertintoAvalues（1,'a'）
--解答：
selectid,namefromAwhereidin（selectidfromAgroupbyidhavingcount（id）>3）orderbyid
第六题：
原表Course:
courseidcoursenamescore
-------------------------------------
1java70
2oracle90
3xml40
4jsp30
5servlet80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下（及格分数为60）:
courseidcoursenamescoremark
---------------------------------------------------
1java70pass
2oracle90pass
3xml40fail
4jsp30fail
5servlet80pass
---------------------------------------------------
写出此查询语句。
createtableCourse（
courseidintIDENTITY,
coursenamevarchar（25）,
scoreint
）
insertintoCoursevalues（'java',70）
insertintoCoursevalues（'oracle',90）
insertintoCoursevalues（'xml',40）
insertintoCoursevalues（'jsp',30）
insertintoCoursevalues（'servlet',80）
--解答：
--oracle：
selectcourseid,coursename,score,decode（sign（score-60）,-1,'fail','pass'）asmarkfromcourse
--SQLServer：
select*,（casewhenscore<60then'failed'else'pass'end）asmarkfromCourse
第七题：
有表：
emp（id,name,age）
要求：
列出所有名字重复的人的记录?
createtableemp（
idintIDENTITY,
namevarchar（25）,
ageint
）
insertintoempvalues（'Zhang1',26）
insertintoempvalues（'Zhang2',27）
insertintoempvalues（'Zhang3',28）
insertintoempvalues（'Zhang1',26）
insertintoempvalues（'Zhang2',27）
insertintoempvalues（'Zhang3',29）
insertintoempvalues（'Zhang1',26）
insertintoempvalues（'Zhang2',27）
insertintoempvalues（'Zhang3',28）
insertintoempvalues（'Zhang1',26）
insertintoempvalues（'Zhang4',22）
insertintoempvalues（'Wang1',27）
insertintoempvalues（'wang2',28）
insertintoempvalues（'Wang2',26）
insertintoempvalues（'Wang1',22）
--列出所有名字重复的人的记录?
--解法一：
要知道所有名字有重复人资料，首先必须知道哪个名字重复了：
selectid,name,agefromempwherenamein（selectnamefromempgroupbynamehavingcount（*）>1）
--解法二：
如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的，就有：
select*fromempwhere（selectcount（*）fromempewheree.name=emp.name）>1
--解法三：
如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求：
select*fromempwhereexists（select*fromempewheree.name=emp.nameande.id<>emp.id）
--或：
selectdistinctemp.*fromempinnerjoinempeonemp.name=e.nameandemp.id<>e.id
第八题：
有例表：
emp（name,age）
Tom16
Sun14
Tom16
Tom16
要求：
过滤掉所有多余的重复记录
createtableemp（
namevarchar（20）,
ageint
）
insertintoempvalues（'Tom',16）
insertintoempvalues（'Sun',14）
insertintoempvalues（'Tom',16）
insertintoempvalues（'Tom',16）
--解法一：
通过distinct、groupby过滤重复：
selectdistinct*fromemp
或
selectname,agefromempgroupbyname,age
--获得需要的数据，如果可以使用临时表就有解法：
selectdistinct*into#tmpfromemp
deletefromemp
insertintoempselect*from#tmp
--但是如果不可以使用临时表，那该怎么办？
altertableempaddchkintidentity（1,1）
--重复记录可以表示为：
select*fromempwhere（selectcount（*）fromempewheree.name=emp.name）>1
--要删除的是：
deletefromempwhere（selectcount（*）fromempewheree.name=emp.nameande.chk>=emp.chk）>1
--再把添加的列删掉，出现结果。
altertableempdropcolumnchk
--）另一个思路：
视图
selectmin（chk）fromempgroupbynamehavingcount（*）>1
--获得有重复的记录chk最小的值,于是可以
deletefromempwherechknotin（selectmin（chk）fromempgroupbyname）
第九题：
有列表：
emp（emp_no,name,age）
001Tom17
002Sun14
003Tom15
004Tom16
要求生成序列号
createtableemp（
emp_noint,
namevarchar（20）,
ageint
）
insertintoempvalues（001,'Tom',17）
insertintoempvalues（002,'Sun',14）
insertintoempvalues（003,'Tom',15）
insertintoempvalues（004,'Tom',16）
--
（1）最简单的方法：
altertableempaddchkintidentity（1,1）
--或
select*,identity（int,1,1）chkinto#tmpfromemp
select*fromemp
altertableempdropcolumnchk
--如果需要控制顺序怎么办？
select*,identity（int,1,1）chkinto#tmpfromemporderbyage
deletefromemp
altertableempaddchkint
insertintoempselect*from#tmp
select*from#tmp
droptable#tmp
--
（2）假如不可以更改表结构，怎么办？
如果不可以唯一区分每条记录是没有办法的，
selectemp.*,（selectcount（*）fromempewheree.emp_no<=emp.emp_no）fromemporderby（selectcount（*）fromempewheree.emp_no<=emp.emp_no）
第十题：
学科表:
姓名选课
---------------------
张三数学
张三物理
张三语文
张三化学
李四数学
李四化学
李四语文
王五数学
王五物理
王五语文
赵六数学
赵六物理
赵六语文
周七数学
周七物理
问题一：
只选数学，物理，语文的学生,查询结果如下,写出相应SQL语句
姓名选课
---------------------
王五数学
王五物理
王五语文
赵六数学
赵六物理
赵六语文
问题二：
同时选了数学，物理，语文的学生,查询结果如下,写出相应SQL语句
姓名选课
---------------------
张三数学
张三物理
张三语文
王五数学
王五物理
王五语文
赵六数学
赵六物理
赵六语文
createtablecourse（
升级会员 