标准SQL嵌套语句题集.docx

标准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（