SQL语句简单面试题.doc

1. sqlgroup by2005-05-09 2005-05-09 2005-05-09 2005-05-09 2005-05-10 2005-05-10 2005-05-10 , sql? 2005-05-09 2 22005-05-10 1 2-create table #tmp(rq varchar(10),shengfu nchar(1)insert into #tmp values(2005-05-09,)insert into #tmp values(2005-05-09,)insert into #tmp values(2005-05-09,)insert into #tmp values(2005-05-09,)insert into #tmp values(2005-05-10,)insert into #tmp values(2005-05-10,)insert into #tmp values(2005-05-10,)1) select rq, sum(case when shengfu= then 1 else 0 end),sum(case when shengfu= then 1 else 0 end) from #tmpgroup by rq2) select n.rq,n.,m.“ from (select rq,=count(*) from #tmp where shengfu=group by rq)n inner join (select rq,“=count(*) from #tmp where shengfu=group by rq)m on n.rq=m.rq3) select a.rq,a.a1 ,b.b1 from (select rq,count(rq) a1 from #tmp whereshengfu= group by rq) a,(select rq,count(rq) b1 from #tmp where shengfu= group by rq) b where a.rq=b.rq2.sqla b c,sqlababbcbc-create table #tmp(a int,b int,c int)insert into #tmp values(10,20,30)-insert into #tmp values(10,30,20)-insert into #tmp values(40,10,20)select * from #tmpselect (case when a>b then a else b end),(case when b>c then b else c end ) from #tmp3.sqltb_send(sendtime)?(sendtimedatetime)-select * from #tmp where datediff(dd,rq,getdate()=0select * from #tmp where rq=rtrim(convert(varchar,getdate(),23)4.33708058sql806060 -create table #tmp( int, int , int)insert into #tmp values(70,80,58)-insert into #tmp values(100,50,60)select * from #tmpselect (case when >=80 then when >=60 then else end ) ,(case when >=80 then when >=60 then else end ) ,(case when >=80 then when >=60 then else end ) from #tmp5.sqlserver2000sqlididvalues,int?-:create table #xx(id int, idvalues int):create table #xx(id int, idvalues int):session,.sql server,session,.6.sqlserver2000-(*.dbf),sql server.sql server 2000 .mdf .ndf .ldf7.sqltable1,table2table3table1mon dep yj-01100210035028049038table2depdname-01020304table3 resultdep -0110 nullnull02108 null03null 5 804nullnull9-create table #a(mon varchar(10),dep varchar(10) ,yj int)insert into #a values(,01,10)insert into #a values(,02,10)insert into #a values(,03,5)insert into #a values(,02,8)insert into #a values(,04,9)insert into #a values(,03,8)select * from #acreate table #b(dep varchar(10),dname varchar(20)insert into #b values(01,)insert into #b values(02,)insert into #b values(03,)insert into #b values(04,)select * from #b-1) select dep,(select sum(yj) from #a where mon= and #a.dep=#b.dep) ,(select sum(yj) from #a where mon= and #a.dep=#b.dep) ,(select sum(yj) from #a where mon= and #a.dep=#b.dep) from #b2) select b.dep,dname,sum(case when a.mon= then a.yj else 0 end ) as ,sqlsql1student(s#,sname,sage,ssex) course(c#,cname,t#) sc(s#,c#,score) teacher(t#,tname) 1001002selecta.s#from (select s#,score from sc where c#=001) a,(select s#,score from sc where c#=002) bwherea.score>b.score and a.s#=b.s#;260select s#,avg(score)fromscgroup by s# having avg(score) >60;3select student.s#,student.sname,count(sc.c#),sum(score)from student left outer join sc on student.s#=sc.s#group by student.s#,sname4select count(distinct(tname)from teacherwhere tname like %;5select student.s#,student.snamefrom studentwhere s# not in (select distinct( sc.s#) from sc,course,teacher where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname=);6001002select student.s#,student.snamefromstudent,scwhere student.s#=sc.s# and sc.c#=001and exists( select * from sc as sc_2 where sc_2.s#=sc.s# and sc_2.c#=002);7select s#,snamefrom studentwhere s# in(select s#from sc ,course ,teacherwhere sc.c#=course.c# and teacher.t#=course.t# and teacher.tname= group by s# having count(sc.c#)=(select count(c#) from course,teacher where teacher.t#=course.t# and tname=);860select s#,snamefrom studentwhere s# not in (select student.s# from student,sc where s.s#=sc.s# and score>60);9select student.s#,student.snamefromstudent,scwherestudent.s#=sc.s#group by student.s#,student.sname having count(c#) <(select count(c#) from course);101001 select s#,snamefromstudent,scwhere student.s#=sc.s# and c# in select c# from sc where s#=1001;11scdelect scfrom course ,teacherwhere course.c#=sc.c# and course.t#= teacher.t# and tname=;from sc l ,sc rwhere l.c# = r.c#andl.score = (select max(il.score)from sc il,student imwhere il.c# = l.c# and im.s#=il.s#group by il.c#)andr.score = (select min(ir.score)from sc irwhere ir.c# = r.c#group by ir.c# );13select 1+(select count( distinct )from (select s#,avg(score) from scgroup by s# ) t1where > t2.) , s# ,from (select s#,avg(score) from sc group by s# ) t2order by desc;14:()select t1.s# as id,t1.c# as id,score as from sc t1where score in (select top 3 scorefrom scwhere t1.c#= c#order by score desc)order by t1.c#;15select t1.s# as id,t1.c# as id,score as from sc t1where score in (select top 2 scorefrom scwhere t1.c#= c#order by score desc )order by t1.c#;year salary2000 10002001 20002002 30002003 4000selectb.year,sum(a.salary)from salary a,salary bwherea.year<=b.yeargroup by b.yearorder by b.year;sqla(id,name)3140idselect top 10 *from awhere id >(select max(id) from (select top 30 id from a order by id ) t) order by idselect top 10 *from awhere id not in (select top 30 id from a order by id)order by id2sql431106315student ()course ()sc ()1sql32sql33sql591sqlselectstu.sno, stu.sname from student stuwhere (select count(*) from sc where sno=stu.sno and cno =(select cno from course where cname=) != 0;2sqlselectcname from coursewherecno in ( select cno from sc where sno =(select sno from student where sname=);3sqlselectstu.sno, stu.sname from student stuwhere (select count(*) from sc where sno=stu.sno) = 5;107,s,c,sc,sc12sql,3sql,51mysqlcreate table s(id integer primary key, name varchar(20);create table c(id integer primary key, name varchar(20);create table sc(sid integer references s(id),cid integer references c(id),primary key(sid,cid);2sqlselect stu.id, from s stuwhere (select count(*) from sc where sid=stu.id) = (select count(*) from c);3sqlselect stu.id, from s stuwhere (select count(*) from sc where sid=stu.id)>=5; 108(test)id name age manager(id)106 a 30 104109 b 19 104104 c 20 111107 d 35 109112 e 25 120119 f 45 null:id? sqlselect from test employeewhereemployee.age> (select manager.age from test manager where manager.id=employee.manager);109citystatecityno cityname statenobj nullsh nullgz gd dl ln state no state namegd ln sd nmg city no city name state no state namebj nullnulldl ln gz gd sh nullnullsqlsqlselect c.cityno, c.cityname, c.stateno, s.statename from city c, state swhere c.stateno=s.stateno(+)order by(c.cityno);sql1. sqlgroup by2005-05-09 2005-05-09 2005-05-09 2005-05-09 2005-05-10 2005-05-10 2005-05-10 , sql? 2005-05-09 2 22005-05-10 1 2-create table #tmp(rq varchar(10),shengfu nchar(1)insert into #tmp values(2005-05-09,)insert into #tmp values(2005-05-09,) insert into #tmp values(2005-05-09,) insert into #tmp values(2005-05-09,) insert into #tmp values(2005-05-10,) insert into #tmp values(2005-05-10,) insert into #tmp values(2005-05-10,)1) select rq, sum(case when shengfu= then 1 else 0 end),sum(case when shengfu= then 1 else 0 end) from #tmpgr