SQL经典面试题集.docx

SQL经典面试题集.docx

《SQL经典面试题集.docx》由会员分享，可在线阅读，更多相关《SQL经典面试题集.docx（11页珍藏版）》请在冰豆网上搜索。

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）,

 SN  varchar（25）,

 SD  varchar（25）,

 SA  int

）

createtableC

（

 C#  varchar（10）,

 CN  varchar（25）

）

createtableSC

（

 S#  varchar（10）,

 C#  varchar（10）,

 G   int

 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#fromCleftjoinSC  on  C.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.namefromtesttable2t2  leftjoin  testtable1t1ont1.id=t2.dptID

select*fromtesttable2

用一条SQL语句，怎么显示如下结果

iddptIDdepartmentname

1   1      设计        张三

2   1      设计        李四

3   2      市场        王五

4   3      售后        彭六

5   4      黑人        陈七

--解答：

  --解法一:

  selectt2.id,t2.dptID,t1.department,t2.namefromtesttable2t2  leftjoin  testtable1t1ont1.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.namefromtesttable2t2  innerjoin  testtable1t1ont1.id=t2.dptID

第三题：

有表A，结构如下：

A:

p_ID，p_Num，s_id

1，10，01

1，12，2

2，8，01

3，11，01

3，8，03

其中：

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_ID  int,

 p_Num  int,

 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

（

 id  intIDENTITY,

 Name  varchar（25）

）

--1.查询A（ID,Name）表中第31至40条记录，ID作为主键可能是不是连续增长的列？

--解答：

  selecttop10*fromAwhereID>（selectmax（ID）from（selecttop30IDfromAorderbyid）T）orderbyid

第五题：

--查询A（ID,Name）表中存在ID重复三次以上的记

droptableA

createtableA

（

 id  int,

 Name  varchar（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*fromempe  wheree.name=emp.nameande.id<>emp.id）

--或：

selectdistinctemp.*fromempinnerjoinempeonemp.name=e.nameandemp.id<>e.id

第八题：

有例表：

emp（name,age）

 Tom   16

 Sun   14    

 Tom   16    

 Tom   16

要求：

过滤掉所有多余的重复记录

createtableemp（

 namevarchar（20）,

 ageint

）

insertintoempvalues（'Tom',16）

insertintoempvalues（'Sun',14）

insertintoempvalues（'Tom',16）

insertintoempvalues（'Tom',16）

--解法一：

通过distinct、groupby过滤重复：

selectdistinct*from  emp    

或 

selectname,agefromempgroupbyname,age

--获得需要的数据，如果可以使用临时表就有解法：

selectdistinct*into#tmpfromemp

deletefromemp

insertintoempselect*  from#tmp

--但是如果不可以使用临时表，那该怎么办？

altertableempaddchkintidentity（1,1）

--重复记录可以表示为：

select*from  emp  where（selectcount（*）fromempewheree.name=emp.name）>1

--要删除的是：

deletefromempwhere（selectcount（*）fromempewheree.name=emp.nameande.chk>=emp.chk）>1

--再把添加的列删掉，出现结果。

altertableempdropcolumnchk

--）另一个思路：

视图

selectmin（chk）fromempgroupbynamehaving   count（*）   >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）chk  into#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）

第十题：

学科表:

姓名选课

---------------------

张三数学

张三物理

张三语文

张三化学

李四数学

李四化学

李四语文

王五数学

王五物理

王五语文

赵