SQL经典面试题集(二).doc

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 values (张三,数学,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 students 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 name 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 gradet2.x and = 第十五题：-查询学生表中，选修课超过5门的名字!create table students ( id int IDENTITY, name varchar(20), elective_course varchar(20)insert into students values(student1,course1)insert into students values(student1,course2)insert into students values(student1,course3)insert into students values(student1,course4)insert into students values(student1,course6)insert into students values(student1,course6)insert into students values(student2,course1)insert into students values(student2,course2)insert into students values(student2,course3)insert into students values(student2,course4)insert into students values(student2,course5)insert into students values(student3,course1)insert into students values(student3,course2)insert into students values(student3,course3)insert into students values(student3,course4)insert into students values(student4,course1)insert into students values(student4,course2)insert into students values(student4,course3)insert into students values(student4,course4)insert into students values(student4,course5)insert into students values(student4,course6)insert into students values(student4,course7)insert into students values(student5,course2)insert into students values(student5,course3)insert into students values(student5,course4)insert into students values(student5,course5)insert into students values(student5,course6)insert into students values(student5,course7)insert into students values(student5,course8)insert into students values(student5,course9)insert into students values(student6,course7)insert into students values(student6,course8)insert into students values(student6,course9)-解答: select name from students group by name having count(elective_course)=5第十六题：DbTable表有三列，id，name，data，其中name列里每行都含有data,如第一行里为aadatabb，第二行为abcdatacd，要求用对应data列的数据替换掉data，sql怎么写？create table DbTable ( ID int IDENTITY, name varchar(20), data varchar(10)insert into DbTable values (a1databb,1)insert into DbTable values (a2databb,2)insert into DbTable values (a3databb,3)insert into DbTable values (a4databb,4)insert into DbTable values (a5databb,5)-解答:update DbTable set name=replace(name,data,data)select * from DbTable第十七题：存在表table（FID，FCLASS，FSSCORE），三字段分别代表姓名、班级、成绩。用最高效、最简单的SQL语句列出人数大于30的各班最高成绩的列表，显示班级、成绩两个字段。create table F3 ( FID varchar(20), FLASS varchar(20), FSSCORE int)insert into F3 values (S_Name1,Class1,67)insert into F3 values (S_Name2,Class1,57)insert into F3 values (S_Name3,Class1,27)insert into F3 values (S_Name4,Class1,37)insert into F3 values (S_Name5,Class1,97)insert into F3 values (S_Name6,Class2,67)insert into F3 values (S_Name7,Class2,57)insert into F3 values (S_Name8,Class2,27)insert into F3 values (S_Name9,Class2,37)insert into F3 values (S_Name10,Class2,97)insert into F3 values (S_Name11,Class2,37)insert into F3 values (S_Name112,Class2,97)insert into F3 values (S_Name17,Class3,57)insert into F3 values (S_Name18,Class3,27)insert into F3 values (S_Name19,Class3,37)insert into F3 values (S_Name110,Class3,88)insert into F3 values (S_Name111,Class3,37)insert into F3 values (S_Name1112,Class3,67)insert into F3 values (S_Name117,Class4,57)insert into F3 values (S_Name118,Class4,27)insert into F3 values (S_Name119,Class4,37)insert into F3 values (S_Name1110,Class4,82)insert into F3 values (S_Name1111,Class4,37)insert into F3 values (S_Name11112,Class4,67)insert into F3 values (S_Name11111,Class5,37)insert into F3 values (S_Name111112,Class5,67)-解答：为了便于组装测试数据，这里一以5为人数-解法一：select F3.FLASS, Max(FSSCORE) from F3 group by FLASS having count(*) =5-解法二：-第一步：查询出人数大于5的班级-select FLASS ,count(*) as Total from F3 group by FLASS having count(*) = 5-第二步：查询出所有人数大于5的班级的所有学生记录-select * from F3 where FLASS in (select FLASS from F3 group by FLASS having count(*) = 5 )-第三步：通过对第二步的记录根据FCLASS分组查询-select FLASS, Max(FSSCORE) from F3 where FLASS in (select FLASS from F3 group by FLASS having count(*) = 5 ) group by FLASS-解法三：select FLASS,max(fsscore) from(select * from F3where FLASS in (select FLASS from F3 group by FLASS having count(*)=5) T group by FLASS 第十八题：有一张老师表Teachers，字段是T_ID,T_NAME；有一张学生表Students，字段是S_ID,S_NAME；还有一张班级表Classes，字段是T_ID,S_ID,C_NAME，其中C_NAME的取值只有大班和小班，请查询出符合条件的老师的名字，条件是老师在大班中带的学生数大于此老师在小班中带的学生数。create table Teachers ( T_ID int, T_NAME varchar(20)create table Students ( S_ID int, S_NAME varchar(20)create table Classes ( T_ID int, S_ID int, C_NAME varchar(20)insert into Teachers values(1,T1)insert into Teachers values(2,T2)insert into Teachers values(3,T3)insert into Teachers values(4,T4)insert into Teachers values(5,T5)insert into Students values(1,S1)insert into Students values(2,S1)insert into Students values(3,S1)insert into Students values(4,S1)insert into Students values(5,S1)insert into Students values(6,S1)insert into Students values(7,S1)insert into Students values(8,S1)insert into Students values(9,S1)insert into Students values(10,S1)insert into Students values(11,S1)insert into Students values(12,S1)insert into Students values(13,S1)insert into Students values(14,S1)insert into Students values(15,S1)insert into Students values(16,S1)insert into Classes values(1,1,大班)insert into Classes values(1,2,大班)insert into Classes values(1,3,小班)insert into Classes values(1,4,大班)insert into Classes values(1,13,大班)insert into Classes values(1,14,大班)insert into Classes values(1,15,小班)insert into Classes values(1,16,大班)insert into Classes values(2,1,大班)insert into Classes values(2,2,小班)insert into Classes values(2,3,大班)insert into Classes values(2,4,大班)insert into Classes values(2,16,小班)insert into Classes values(2,15,小班)insert into Classes values(2,14,小班)insert into Classes values(3,5,大班)insert into Classes values(3,6,小班)insert into Classes values(3,7,大班)insert into Classes values(4,4,大班)insert into Classes values(4,5,大班)insert into Classes values(4,6,小班)insert into Classes values(4,7,小班)insert into Classes values(4,8,小班)insert into Classes values(5,9,大班)insert into Classes values(5,10,小班)insert into Classes values(5,11,小班)insert into Classes values(5,12,小班)-第一步：查询出每个老师所带的小班的人数-select T_ID,count(*) as x from Classes where C_Name=小班 group by T_ID-第二步：查询出每个老师所带的大班的人数-select T_ID,count(*) as x from Classes where C_Name=大班 group by T_ID-第三步：在上面一二步的基础上查询出大班人数大于小班人数的老师-select T_NAMEfrom Teachers t, (select T_ID,count(*) as x from Classes where C_Name=小班 group by T_ID) T1, (select T_ID,count(*) as x from Classes where C_Name=大班 group by T_ID) T2where T1.xT2.x and T1.T_ID=T2.T_ID and t.T_ID=T1.T_ID-考察要点：1.分组查询. 2.把查询出来的某些结果作为表来连接查询出相关结果. 第十九题：前提:a 部门表 b 员工表a表字段(id -部门编号departmentName-部门名称)b表字段(id-部门编号employee- 员工名称)问题:如何一条sql语句查询出每个部门共有多少人?*/create table departments( ID int IDENTITY, Name varchar (20),)create table employees( ID int, Name varchar (20)insert into departments values (DeparmentA)insert into departments values (DeparmentB)insert into departments values (DeparmentC)insert into departments values (DeparmentD)insert into departments values (DeparmentE)insert into employees values (1,Zhang3)insert into employees values (1,Zhang4)insert into employees values (1,Zhang5)insert into employees values (2,Li3)insert into employees values (2,Li4)insert into employees values (2,Li5)insert into employees values (2,Li6)insert into employees values (3,Zhao3)insert into employees values (3,Zhao4)insert into employees values (3,Zhao5)insert into employees values (4,Chen4)insert into employees values (4,Chen5)insert into employees values (5,Zhu4)-解法一-select b.id,a.Name,count(b.id)as employeecount from departments a left join employees b on a.id=b.id group by b.id,a.Name-或select b.id,a.Name,count(*)as employeecount from departments a left join employees b on a.id=b.id group by b.id,a.Name-解法二-select t.id as Id, as Na