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 门的学员学号和所属单位?drop table S;drop table C;drop table SC;create table S(S# varchar(10),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 intPrimary Key(S#, C#)insert into S values (10001,Students1,department1,23)insert into S values (10002,Students2,department1,24)insert into S values (10003,Students3,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 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 values (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)insert 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 inner 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,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 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(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 from 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.id , 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.dptIDselect t2.id,t2.dptID,t1.department,t2.name from testtable2 t2 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(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,sum(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 order by id ) T) order by id第五题：-查询 A(ID,Name)表中存在 ID 重