练习题一create database mydb go use mydb create table student ( --学号 sno varchar(3) not null primary key, --姓名 sname varchar(4) not null, --性别 ssex varchar(2) not null, --出生年月 sbirthday datetime, --所在班级 class varchar(5) ) create table teacher ( --教工编号 tno varchar(3) not null primary key, --教工姓名 tname varchar(4) not null, --教工性别 tsex varchar(2) not null, --教工出生日期 tbirthday datetime, --职称 prof varchar(6), --所在部门 depart varchar(10) )create table course ( --课程号 cno varchar(5) not null primary key, --课程名称 cname varchar(10) not null, --教工编号 tno varchar(3) references teacher(tno) )create table score ( --学号 sno varchar(3) not null references student(sno), --课程号 cno varchar(5) not null references course(cno), --成绩 degree decimal(4,1) ) insert into student values('108','曾华','男','1977-09-01','95033')insert into student values('105','匡明','男','1975-10-02','95031')insert into student values('107','王丽','女','1976-01-23','95033')insert into student values('101','李军','男','1976-02-20','95033')insert into student values('109','王芳','女','1975-02-10','95031')insert into student values('103','陆君','男','1974-06-03','95031')insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系') insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系') insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系') insert into teacher values('831','刘冰','女','1958-08-14','助教','电子工程系')insert into course values('3-105','计算机导论','825') insert into course values('3-245','操作系统','804') insert into course values('6-166','数字电路','856') insert into course values('9-888','高等数学','831') insert into score values('103','3-245','86') insert into score values('105','3-245','75') insert into score values('109','3-245','68') insert into score values('103','3-105','92') insert into score values('105','3-105','88') insert into score values('109','3-105','76') insert into score values('101','3-105','64') insert into score values('107','3-105','91') insert into score values('108','3-105','78') insert into score values('101','6-166','85') insert into score values('107','6-166','79') insert into score values('108','6-166','81')select * from student select * from teacher select * from course select * from score--1、 查询Student表中的所有记录的Sname、Ssex和Class列。 --2、 查询教师所有的单位即不重复的Depart列。 --3、 查询Student表的所有记录。 --4、 查询Score表中成绩在60到80之间的所有记录。 --5、 查询Score表中成绩为85,86或88的记录。 --6、 查询Student表中“95031”班或性别为“女”的同学记录。 --7、 以Class降序查询Student表的所有记录。 --8、 以Cno升序、Degree降序查询Score表的所有记录。 --9、 查询“95031”班的学生人数。 --10、查询Score表中的最高分的学生学号和课程号。 --11、查询‘3-105’号课程的平均分。 --12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 --13、查询最低分大于70,最高分小于90的Sno列。 --14、查询所有学生的Sname、Cno和Degree列。 --15、查询所有学生的Sno、Cname和Degree列。 --16、查询所有学生的Sname、Cname和Degree列。 --17、查询“95033”班所选课程的平均分。 --18、假设使用如下命令建立了一个grade表: --create table grade(low int,upp int,rank varchar(1)) --insert into grade values(90,100,'A') --insert into grade values(80,89,'B') --insert into grade values(70,79,'C') --insert into grade values(60,69,'D') --insert into grade values(0,59,'E') --现查询所有同学的Sno、Cno和rank列。 --19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。//无关子查询 --20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。 --21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。 --22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 --23、查询“张旭“教师任课的学生成绩。 --24、查询选修某课程的同学人数多于5人的教师姓名。 --25、查询95033班和95031班全体学生的记录。 --26、查询存在有85分以上成绩的课程Cno. --27、查询出“计算机系“教师所教课程的成绩表。 --28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。 --29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。 --30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree. --31、查询所有教师和同学的name、sex和birthday. --32、查询所有“女”教师和“女”同学的name、sex和birthday. --33、查询成绩比该课程平均成绩低的同学的成绩表。 --34、查询所有任课教师的Tname和Depart. --35 查询所有未讲课的教师的Tname和Depart. --36、查询至少有2名男生的班号。 --37、查询Student表中不姓“王”的同学记录。 --38、查询Student表中每个学生的姓名和年龄。 --39、查询Student表中最大和最小的Sbirthday日期值。 --40、以班号和年龄从大到小的顺序查询Student表中的全部记录。 --41、查询“男”教师及其所上的课程。 --42、查询最高分同学的Sno、Cno和Degree列。 --43、查询和“李军”同性别的所有同学的Sname. --44、查询和“李军”同性别并同班的同学Sname. --45、查询所有选修“计算机导论”课程的“男”同学的成绩表。 --46、查询score表中分数最高的学生的信息。//多层嵌套 --47、查询score表中的平均分在80分以上的学生信息。//相关查询。无关查询练习题二创建以下三个表: 学生表: Student(Sno,Sname,Ssex,Sbirthday,Sage,Sdept) Student 由学号 (Sno)、姓名 (Sname)、性别 (Ssex)、出生日期 (Sbirthday)、年龄 (Sage), 所在系 (Sdept) 五个属性组成,其中 Sno 为主键. 课程表: Coures(Cno,Cname,Cpno,Ccredit) Coures 由课程号 (Cno)、课程名 (Cname)、选修课号 (Cpno)、学分 (Ccredit) 四个属性组成, 其中 Cno 为主键. 学生选课表: SC(Sno,Cno,Grade) SC 由学号 (Sno)、 课程号 (Cno)、 成绩 (Grade) 三个属性组成, 主键为 (Sno,Cno).按照以上三个表来做如下的习题:1.查找全体学生的学号与姓名2.查找全体学生的姓名、学号与所在系3.查找全体学生的详细纪录4.查找全体学生的姓名和其出生日期 5.查找全体学生的姓名、出生日期和所有系,要求用小写字母表示所有系名6.查找全体学生的姓名、出生日期和所有系,要求给这几列起别名7.查找选修了课程的学生学号(不能重复)8.查找计算系全体学生的名单9.查找所有年龄在20岁以下的学生姓名和其年龄(2种做法)10.查找考试成绩有不及格的学生的学号(不能重复)11.查找年龄在20--30岁(包括20岁和23岁)之间的学生的姓名、系别和年龄12.查找年龄不在20--30岁(包括20岁和23岁)之间的学生的姓名、系别和年龄13.查找信息系、数学系和计算机系学生的姓名与性别(用in做)14.查找不是信息系、数学系,也不是计算机系的学生的姓名与性别(用not in做)15.查找学号以为95001开头的学生的详细情况(用2种方法做)16.查找所有以“刘”开头的学生的姓名、学号和性别17.查找以“欧阳”开头的且三个汉字的学生的姓名18.查找名字中第2个字为“阳”字的学生的姓名和学号19.查找所有不以“刘”开头的学生姓名20.查找以“C”开头的课程的课程号和学分21.查找以“H”开头,且倒数第三个字符为“T”的课程的课程号和学分22.某些学生选修课程后没有参加考试,所以有选课纪录,但没有考试成绩。查找缺少成绩的学生的学号和相应 的课程号23.查找除了无成绩的学生的学号和课程号24.查找计算机系年龄在20岁以下的学生姓名25.查找是计算机系 或者是 数学系 或者是 信息系的学生姓名和性别26.查找选修了3号课程的学生的学号与其成绩,其查找结果按分数的降序排列27.查找全体学生情况,查找结果按所在系的系号升序排列,同一系中的学生按年龄降序排列28.查找学生总人数29.查找选修了课程的学生人数30.计算1号课程的学生平均成绩
SQL语句练习题参考答案 1、 select Sname,Ssex,Class from Student; 2、 select distinct depart from teacher; 3、 select Sno as '学号',Sname as '姓名',Ssex as '性别',Sbirthday as'出生日期',Class as'班号'from student; 或 select Sno as 学号,Sname as 姓名,Ssex as 性别,Sbirthday as 出生日期,Class as 班号 from student; 4、 select * from score where degree between 60 and 80; 或select * from score where degree>=60 and degree<=80; 5、 select * from score where degree in (85,86,88); 6、 select * from student where class='95031'or Ssex='女'; 7、 select * from student order by class desc; 8、 select * from score order by cno asc ,degree desc; 或select * from score order by cno ,degree desc; 9、 select count(*) as CNT from student where class='95031'; 10、select Sno as '学号',cno as '课程号', degree as '最高分' from score where degree=(select max(degree) from score) 11、select avg(degree)as 课程平均分 from score where cno='3-105'; 12、select cno,avg(degree) from score where cno like'3%'group by cno having count(*) >5; 13、select Sno from score group by Sno having min(degree)>70 and max(degree)<90; 14、select student.Sname,score.Cno,score.degree from student,score where student.Sno=score.Sno; 15、select x.Sno,y.Cname,x.degree from score x,course y where x.Cno=y.Cno; 16、select x.Sname,y.Cname,z.degree from student x,course y,score z where x.Sno=z.Sno and z.Cno=y.Cno; 17、select y.Cno,avg(y.degree) from student x,score y where x.Sno=y.Sno and x.class='95033'group by y.cno; 18、select Sno,Cno,rank from score,grade where degree between low and upp order by rank; 19、select x.Cno,x.Sno,x.degree from score x,score y where x.cno='3-105' and x.degree>y.degree and y.sno='109'and y.cno='3-105'; 20、 1,查询成绩非本科最高 select * from score b where degree <(select max(degree) from score a where a.cno=b.cno); 2,查询成绩非本科最高并且选2门以上的学生的成绩: 21、select x.cno,x.Sno,x.degree from score x,score y where x.degree>y.degree and y.sno='109'and y.cno='3-105'; select cno,sno,degree from score where degree >(select degree from score where sno='109' and cno='3-105') 22、select sno,sname,sbirthday from student where to_char(sbirthday,'yyyy')=(select to_char(sbirthday,'yyyy') from student where sno='108'); 23、select cno,sno,degree from score where cno=(select x.cno from course x,teacher y where x.tno=y.tno and y.tname='张旭'); 24、select tname from teacher where tno in(select x.tno from course x,score y where x.cno=y.cno group by x.tno having count(x.tno)>5); 25、select * from student where class in('95033','95031'); 26、select distinct cno from score where degree in (select degree from score where degree>85); 27、select * from score where cno in(select x.cno from course x,teacher y where y.tno=x.tno and y.depart='计算机系'); 28、select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系'); 29、select * from score where cno='3-105' and degree>any (select degree from score where cno='3-245')order by degree desc; 30、select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245'); 31、select tname,tsex,tbirthday from teacher union select sname,ssex,sbirthday from student; 32、select tname,tsex,tbirthday from teacher where tsex='女' union select sname,ssex,sbirthday from student where ssex='女'; 33、select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno); 34、select tname,depart from teacher a where exists (select * from course b where a.tno=b.tno); 35、select tname,depart from teacher a where not exists (select * from course b where a.tno=b.tno); 36、select class from student where ssex='男'group by class having count(*)>=2; 37、select * from student where sname not like'王_'; 38、select sname as 姓名,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')) as 年龄 from student 39、select sname,sbirthday as 最大 from student where sbirthday =(select min (sbirthday) from student) union select sname,sbirthday as 最小 from student where sbirthday =(select max(sbirthday) from student) 40、select class,sname,sbirthday from student order by class desc,sbirthday; 41、select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex='男'; 42、select * from score where degree=(select max(degree)from score); 43、select sname from student where ssex=(select ssex from student where sname='李军'); 44、select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军'); 45、select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course where cname='计算机导论');
eg:1.What's the difference between DBCC DBREINDEX and DBCC INDEXDEFRAG?2.What’s the difference between a primary key and a unique key?
1,现有表bill和表payment结构如下,两表通过pay_id关联:(简单) bill表――bill_id是帐单标识,charge为帐单金额,pay_id为付款编号,PK:bill_id bill_id INTEGER charge INTEGER pay_id INTEGER payment表――pay_id为付款编号,charge为付款金额,PK:pay_id pay_id INTEGER charge INTEGER 要求更新payment使charge=bill表中同一pay_id的charge之和。 /* update a set a.charge=b.charge from payment a,(select pay_id,sum(charge) as charge from bill group by pay_id) b where a.pay_id=b.pay_id */ 2、有如下信息: 起始地 目的地 距离(公里) A B 1000 A C 1100 A D 900 A E 400 B D 300 D F 600 E A 400 F G 1000 C B 600 请用SQL语句或一段代码写出从A出发,可达到的目的地(包括间接方式)。 /* 第二道题目: create table t (st varchar(20),ed varchar(20),km int) insert t values ( 'A ', 'B ',1000) insert t values ( 'A ', 'C ',1100) insert t values ( 'A ', 'D ',900) insert t values ( 'A ', 'E ',400) insert t values ( 'B ', 'D ',300) insert t values ( 'D ', 'F ',600) insert t values ( 'E ', 'A ',400) insert t values ( 'F ', 'G ',1000) insert t values ( 'C ', 'B ',600) go create function f_go(@col varchar(10)) returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int) as begin declare @i int set @i=1 insert @t select st+ '- '+ed,*,@i from t where st=@col while exists (select * from t a,@t b where b.ed=a.st and b.level=@i and b.ed <> @col ) begin set @i=@i+1 insert @t select b.col+ '- '+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t b where b.level=@i-1 and b.ed=a.st and b.ed <> @col end return end go select col,km from dbo.f_go( 'A ') drop function f_go drop table t */ 3.表A定义如下: 属性 类型 备注 Id Integer PK Name Varchar(30) State char(3) 表A中现有1000万条记录,如果客户端要通过分页的方式从表A中取数据,其中每页20条 记录,有几种方法?描述每种方法如何处理,有什么优缺点?用SQL写出实现代码。 表A定义如下: 属性 类型 备注 Id Integer PK Name Varchar(30) State char(3) 表A中现有1000万条记录,如果客户端要通过分页的方式从表A中取数据,其中每页20条 记录,有几种方法?描述每种方法如何处理,有什么优缺点?用SQL写出实现代码。 /* ------用老大的方法 查询 X页第y行记录: 1.select top y * from 表a where id not in(select top (x-1)*y id from 表a) 2.select id2=identity(int,1,1),* into #tb from 表a select * from #tb where id2 between (x-1)*y and (x*y-1) */ 4、表A定义如下: 属性 类型 Year Integer Quarter Varchar(30) Amount float Year Quarter Amount 2000 1 1.1 2000 2 1.2 2000 3 1.3 2000 4 1.4 2001 1 2.1 2001 2 2.2 2001 3 2.3 2001 4 2.4 其中每行表表示一个季度的数据。 如果处理表A中的数据,得到如下的结果。 Year Quarter1 Quarter2 Quarter3 Quarter4 2000 1.1 1.2 1.3 1.4 2001 2.1 2.2 2.3 2.4 请用SQL写一段代码实现 select year,(case when quarter=1 then amount end) as 'quarter1 ', (case when quarter=2 then amount end) as 'quarter2 ', (case when quarter=3 then amount end) as 'quarter3 ', (case when quarter=4 then amount end) as 'quarter4 ' from 表A group by year 代码 编号 a F001 a F002 a F003 a F005 a F008 a F009 a F010 a F030 变成下列方式 代码 起始号 截止号 a F001 F003 a F005 F005 a F008 F010 a F030 F030 declare @a table(代码 varchar(2),编号 varchar(8)) insert into @a select 'a ', 'F001 ' insert into @a select 'a ', 'F002 ' insert into @a select 'a ', 'F003 ' insert into @a select 'a ', 'F005 ' insert into @a select 'a ', 'F008 ' insert into @a select 'a ', 'F009 ' insert into @a select 'a ', 'F010 ' insert into @a select 'a ', 'F030 ' --select t.* from @a t where not exists(select 1 from @a where 代码=t.代码 and right(编号,3)=right(t.编号,3)-1) --select t.* from @a t where not exists(select 1 from @a where 代码=t.代码 and right(编号,3)=right(t.编号,3)+1) select a.代码, 起始号=a.编号, 截止号=min(b.编号) from (select t.* from @a t where not exists(select 1 from @a where 代码=t.代码 and right(编号,3)=right(t.编号,3)-1)) a, (select t.* from @a t where not exists(select 1 from @a where 代码=t.代码 and right(编号,3)=right(t.编号,3)+1)) b where a.代码=b.代码 and a.编号 <=b.编号 group by a.代码,a.编号 /* 代码 起始号 截止号 ---- -------- -------- a F001 F003 a F005 F005 a F008 F010 a F030 F030 */ --设置系统时间 exec master..xp_cmdshell 'date 2006-2-13 ' exec master..xp_cmdshell 'time 14:44:00 '
--1. pk 主键 fk 外键,那个关系图乱了,这个也没啥说的--2. 信息ID、信息标题、信息发布时间、信息发布人姓名、信息评论总数和最后评论时间,并且按最后评论时间排序,请给出该查询的SQL语句?select * from ( select top 20 a.infoid,a.infotitle,a.infopubdate,c.username, count(1) as 信息评论总数,max(b.inforeplydate) as 最后评论时间 from info a inner join InfoReply b on a.infoid = b.infoid inner join User c on a.infopubuser = c.userno group by a.infoid,a.infotitle,a.infopubdate,c.username order by a.infopubdate desc )tab order by 最后评论时间--3. select * from ( select top 20 a.infoid,a.infotitle,a.infopubdate,isnull(c.username,'用户被删除') as 用户名, count(1) as 信息评论总数,max(b.inforeplydate) as 最后评论时间 from info a inner join InfoReply b on a.infoid = b.infoid left join User c on a.infopubuser = c.userno group by a.infoid,a.infotitle,a.infopubdate,c.username order by a.infopubdate desc )tab order by 最后评论时间--4. 可以用触发器实现,快下班了,来不及写了--5. select a.infoid,a.infotitle,a.infopubdate,c.username, count(1) as 信息评论总数 from info a inner join InfoReply b on a.infoid = b.infoid inner join User c on a.infopubuser = c.userno group by a.infoid,a.infotitle,a.infopubdate,c.username--6.这个题目出的有点2了估计出题目的人的用意是要根据自增列找出丢失的纪录 select count(1) as 丢失的纪录数 from into a where not exists(select 1 from info where infoID = a.infoID - 1)--7. 找到丢失的所有纪录(如果最后一条纪录丢失,则找不回来) select infoID - 1 as 丢失的infoID from info a where not exists(select 1 from info where infoID = a.infoID - 1)--8. select a.infoID from InfoReply a left join info b on a.infoid = b.infoid where b.infoid is null--9. 这个最简捷不敢说,再想想,不过这样写的效率应该不差 select '热门' as 类别,数量=(select count(1) from info where infoReplyCount>=20) union all select '非热门' as 类别,数量=(select count(1) from info where infoReplyCount<20)
已经知道原表 year salary ------------------ --------------------- 2000 1000 2001 2000 2002 3000 2003 4000 显示查询结果 year salary ------------------ --------------------- 2000 1000 2001 3000 2002 6000 2003 10000即salary为以前年的工资的和; 我这里提供的答案有两种 第一种: select b.year,sum(a.salary) from salary a,salary b where a.year<=b.year group by b.year order by b.year;其中salary 为工资表 第二种: select s1.year "year",(select sum(s2.salary) from salary s2 where s2.year<=s1.year) "salary" from salary s1;现在我们假设只有一个table,名为pages,有四个字段,id, url,title,body。里面储存了很多网页,网页的url地址,title和网页的内容,然后你用一个sql查询将url匹配的排在最前, title匹配的其次,body匹配最后,没有任何字段匹配的,不返回。就是上面这道面试题,让我想了一个下午,在网上找资料,最后用下面方法实现 SELECT * FROM page where url like '%baidu%' or title like '%baidu%' or like '' ORDER BY CHARINDEX('baidu', url) DESC, CHARINDEX('baidu', title) DESC, CHARINDEX('baidu', body) DESC 1.一道SQL语句面试题,关于group by 表内容: 2005-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 2 2005-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 #tmp group by rq 2) 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.rq 3)select a.col001,a.a1 胜,b.b1 负 from (select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a, (select col001,count(col001) b1 from temp1 where col002='负' group by col001) b where a.col001=b.col0012.请教一个面试中遇到的SQL语句的查询问题 表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。 ------------------------------------------ select (case when a>b then a else b end ), (case when b>c then b esle c end) from table_name3.面试题:一个日期判断的sql语句? 请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型,包含日期与时间) ------------------------------------------ select * from tb where datediff(dd,SendTime,getdate())=04.有一张表,里面有3个字段:语文,数学,英语。其中有3条记录分别表示语文70分,数学80分,英语58分,请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路): 大于或等于80表示优秀,大于或等于60表示及格,小于60分表示不及格。 显示格式: 语文 数学 英语 及格 优秀 不及格 ------------------------------------------ select (case when 语文>=80 then '优秀' when 语文>=60 then '及格' else '不及格') as 语文, (case when 数学>=80 then '优秀' when 数学>=60 then '及格' else '不及格') as 数学, (case when 英语>=80 then '优秀' when 英语>=60 then '及格' else '不及格') as 英语, from table5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表,里面包含两个字段ID和IDValues,类型都是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。日志文件 日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件,但可以不止一个。日志文件的推荐文件扩展名是 .ldf。7.请用一个sql语句得出结果 从table1,table2中取出如table3所列格式数据,注意提供的数据及结果不准确,只是作为一个格式向大家请教。 如使用存储过程也可以。table1月份mon 部门dep 业绩yj ------------------------------- 一月份 01 10 一月份 02 10 一月份 03 5 二月份 02 8 二月份 04 9 三月份 03 8table2部门dep 部门名称dname -------------------------------- 01 国内业务一部 02 国内业务二部 03 国内业务三部 04 国际业务部table3 (result)部门dep 一月份 二月份 三月份 -------------------------------------- 01 10 null null 02 10 8 null 03 null 5 8 04 null null 9------------------------------------------ 1) select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份' from table1 a,table2 b,table2 c,table2 d where a.部门dep = b.部门dep and b.月份mon = '一月份' and a.部门dep = c.部门dep and c.月份mon = '二月份' and a.部门dep = d.部门dep and d.月份mon = '三月份' and 2) select a.dep, sum(case when b.mon=1 then b.yj else 0 end) as '一月份', sum(case when b.mon=2 then b.yj else 0 end) as '二月份', sum(case when b.mon=3 then b.yj else 0 end) as '三月份', sum(case when b.mon=4 then b.yj else 0 end) as '四月份', sum(case when b.mon=5 then b.yj else 0 end) as '五月份', sum(case when b.mon=6 then b.yj else 0 end) as '六月份', sum(case when b.mon=7 then b.yj else 0 end) as '七月份', sum(case when b.mon=8 then b.yj else 0 end) as '八月份', sum(case when b.mon=9 then b.yj else 0 end) as '九月份', sum(case when b.mon=10 then b.yj else 0 end) as '十月份', sum(case when b.mon=11 then b.yj else 0 end) as '十一月份', sum(case when b.mon=12 then b.yj else 0 end) as '十二月份', from table2 a left join table1 b on a.dep=b.dep8.华为一道面试题 一个表中的Id有多个记录,把所有这个id的记录查出来,并显示共有多少条记录数。 ------------------------------------------ select id, Count(*) from tb group by id having count(*)>1 select * from(select count(ID) as count from table group by ID)T where T.count>1 9。表结构以及数据如下:CREATE TABLE 表 (ID int, 日期 varchar(11), 单据 char(3))INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 1 , '2004-08-02' , '001' ); INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 2 , '2004-09-02' , '001' ); INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 3 , '2004-10-02' , '002' ); INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 4 , '2004-09-02' , '002' );要求:设计一个查询,返回结果如下:ID 日期 单据 ---------- ----------- --- 1 2004-08-02 001 4 2004-09-02 002即对于每个单据号,返回日期最小的行。--解答: --相关子查询 select a.* from 表 a where 日期= (select min(日期) from 表 where 单据=a.单据)--用JOIN的连接 select a.* from 表 a, (select min(日期) 日期,单据 from 表 group by 单据) b where a.单据=b.单据 and a.日期=b.日期--不用JOIN的连接 select a.* from 表 a JOIN (select min(日期) 日期,单据 from 表 group by 单据) b ON a.单据=b.单据 and a.日期=b.日期--用谓词Exists select * from 表 a where not exists(select 1 from 表 where 单据=a.单据 and 日期<a.日期) 但我感觉这种方法并不是最简单的,后来把这个方法发给面试的人,他给我了一种更简单方法,只要用基本的Sql语句就可以实现。代码如下select a.[id],a. from ( select [page].[id],100 as from [page] where [page].[url] like '%baidu%' union select [page].[id],50 as from [page] where [page].[title] like '%baidu%' union select [page].[id],10 as from [page] where [page].[body] like '%baidu%' ) as a order by desc 用union 实现联合查询,在每个查询语句中定义一个临时变量 并给赋值,在最后的输出时采用来排序,这样实现,非常简单,我感觉这题更多考研我们的编程思想。 一道sql面试题解法 id strvalue type 1 how 1 2 are 1 3 you 1 4 fine 2 5 thank 2 6 you 2要求用sql把它们搜索出来成为这样的 #how are you#fine thank you# 解答:select (select '#'+replace( replace((SELECT strvalue FROM tb_test t where type = 1 FOR XML AUTO),'<t strvalue="',' ') ,'"/>', ' ') +'#' ) + (select replace(replace((SELECT strvalue FROM tb_test t where type = 2 FOR XML AUTO),'<t strvalue="',' '),'"/>', ' ')+'#')
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html
go
use mydb
create table student
(
--学号
sno varchar(3) not null primary key,
--姓名
sname varchar(4) not null,
--性别
ssex varchar(2) not null,
--出生年月
sbirthday datetime,
--所在班级
class varchar(5)
)
create table teacher
(
--教工编号
tno varchar(3) not null primary key,
--教工姓名
tname varchar(4) not null,
--教工性别
tsex varchar(2) not null,
--教工出生日期
tbirthday datetime,
--职称
prof varchar(6),
--所在部门
depart varchar(10)
)create table course
(
--课程号
cno varchar(5) not null primary key,
--课程名称
cname varchar(10) not null,
--教工编号
tno varchar(3) references teacher(tno)
)create table score
(
--学号
sno varchar(3) not null references student(sno),
--课程号
cno varchar(5) not null references course(cno),
--成绩
degree decimal(4,1)
)
insert into student
values('108','曾华','男','1977-09-01','95033')insert into student
values('105','匡明','男','1975-10-02','95031')insert into student
values('107','王丽','女','1976-01-23','95033')insert into student
values('101','李军','男','1976-02-20','95033')insert into student
values('109','王芳','女','1975-02-10','95031')insert into student
values('103','陆君','男','1974-06-03','95031')insert into teacher
values('804','李诚','男','1958-12-02','副教授','计算机系')
insert into teacher
values('856','张旭','男','1969-03-12','讲师','电子工程系')
insert into teacher
values('825','王萍','女','1972-05-05','助教','计算机系')
insert into teacher
values('831','刘冰','女','1958-08-14','助教','电子工程系')insert into course
values('3-105','计算机导论','825')
insert into course
values('3-245','操作系统','804')
insert into course
values('6-166','数字电路','856')
insert into course
values('9-888','高等数学','831')
insert into score
values('103','3-245','86')
insert into score
values('105','3-245','75')
insert into score
values('109','3-245','68')
insert into score
values('103','3-105','92')
insert into score
values('105','3-105','88')
insert into score
values('109','3-105','76')
insert into score
values('101','3-105','64')
insert into score
values('107','3-105','91')
insert into score
values('108','3-105','78')
insert into score
values('101','6-166','85')
insert into score
values('107','6-166','79')
insert into score
values('108','6-166','81')select * from student
select * from teacher
select * from course
select * from score--1、 查询Student表中的所有记录的Sname、Ssex和Class列。
--2、 查询教师所有的单位即不重复的Depart列。
--3、 查询Student表的所有记录。
--4、 查询Score表中成绩在60到80之间的所有记录。
--5、 查询Score表中成绩为85,86或88的记录。
--6、 查询Student表中“95031”班或性别为“女”的同学记录。
--7、 以Class降序查询Student表的所有记录。
--8、 以Cno升序、Degree降序查询Score表的所有记录。
--9、 查询“95031”班的学生人数。
--10、查询Score表中的最高分的学生学号和课程号。
--11、查询‘3-105’号课程的平均分。
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
--13、查询最低分大于70,最高分小于90的Sno列。
--14、查询所有学生的Sname、Cno和Degree列。
--15、查询所有学生的Sno、Cname和Degree列。
--16、查询所有学生的Sname、Cname和Degree列。
--17、查询“95033”班所选课程的平均分。
--18、假设使用如下命令建立了一个grade表:
--create table grade(low int,upp int,rank varchar(1))
--insert into grade values(90,100,'A')
--insert into grade values(80,89,'B')
--insert into grade values(70,79,'C')
--insert into grade values(60,69,'D')
--insert into grade values(0,59,'E')
--现查询所有同学的Sno、Cno和rank列。
--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。//无关子查询
--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
--23、查询“张旭“教师任课的学生成绩。
--24、查询选修某课程的同学人数多于5人的教师姓名。
--25、查询95033班和95031班全体学生的记录。
--26、查询存在有85分以上成绩的课程Cno.
--27、查询出“计算机系“教师所教课程的成绩表。
--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
--31、查询所有教师和同学的name、sex和birthday.
--32、查询所有“女”教师和“女”同学的name、sex和birthday.
--33、查询成绩比该课程平均成绩低的同学的成绩表。
--34、查询所有任课教师的Tname和Depart.
--35 查询所有未讲课的教师的Tname和Depart.
--36、查询至少有2名男生的班号。
--37、查询Student表中不姓“王”的同学记录。
--38、查询Student表中每个学生的姓名和年龄。
--39、查询Student表中最大和最小的Sbirthday日期值。
--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
--41、查询“男”教师及其所上的课程。
--42、查询最高分同学的Sno、Cno和Degree列。
--43、查询和“李军”同性别的所有同学的Sname.
--44、查询和“李军”同性别并同班的同学Sname.
--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
--46、查询score表中分数最高的学生的信息。//多层嵌套
--47、查询score表中的平均分在80分以上的学生信息。//相关查询。无关查询练习题二创建以下三个表: 学生表: Student(Sno,Sname,Ssex,Sbirthday,Sage,Sdept)
Student 由学号 (Sno)、姓名 (Sname)、性别 (Ssex)、出生日期 (Sbirthday)、年龄 (Sage),
所在系 (Sdept) 五个属性组成,其中 Sno 为主键. 课程表: Coures(Cno,Cname,Cpno,Ccredit)
Coures 由课程号 (Cno)、课程名 (Cname)、选修课号 (Cpno)、学分 (Ccredit) 四个属性组成,
其中 Cno 为主键. 学生选课表: SC(Sno,Cno,Grade)
SC 由学号 (Sno)、 课程号 (Cno)、 成绩 (Grade) 三个属性组成, 主键为 (Sno,Cno).按照以上三个表来做如下的习题:1.查找全体学生的学号与姓名2.查找全体学生的姓名、学号与所在系3.查找全体学生的详细纪录4.查找全体学生的姓名和其出生日期 5.查找全体学生的姓名、出生日期和所有系,要求用小写字母表示所有系名6.查找全体学生的姓名、出生日期和所有系,要求给这几列起别名7.查找选修了课程的学生学号(不能重复)8.查找计算系全体学生的名单9.查找所有年龄在20岁以下的学生姓名和其年龄(2种做法)10.查找考试成绩有不及格的学生的学号(不能重复)11.查找年龄在20--30岁(包括20岁和23岁)之间的学生的姓名、系别和年龄12.查找年龄不在20--30岁(包括20岁和23岁)之间的学生的姓名、系别和年龄13.查找信息系、数学系和计算机系学生的姓名与性别(用in做)14.查找不是信息系、数学系,也不是计算机系的学生的姓名与性别(用not in做)15.查找学号以为95001开头的学生的详细情况(用2种方法做)16.查找所有以“刘”开头的学生的姓名、学号和性别17.查找以“欧阳”开头的且三个汉字的学生的姓名18.查找名字中第2个字为“阳”字的学生的姓名和学号19.查找所有不以“刘”开头的学生姓名20.查找以“C”开头的课程的课程号和学分21.查找以“H”开头,且倒数第三个字符为“T”的课程的课程号和学分22.某些学生选修课程后没有参加考试,所以有选课纪录,但没有考试成绩。查找缺少成绩的学生的学号和相应
的课程号23.查找除了无成绩的学生的学号和课程号24.查找计算机系年龄在20岁以下的学生姓名25.查找是计算机系 或者是 数学系 或者是 信息系的学生姓名和性别26.查找选修了3号课程的学生的学号与其成绩,其查找结果按分数的降序排列27.查找全体学生情况,查找结果按所在系的系号升序排列,同一系中的学生按年龄降序排列28.查找学生总人数29.查找选修了课程的学生人数30.计算1号课程的学生平均成绩
1、 select Sname,Ssex,Class from Student;
2、 select distinct depart from teacher;
3、 select Sno as '学号',Sname as '姓名',Ssex as '性别',Sbirthday as'出生日期',Class as'班号'from student;
或
select Sno as 学号,Sname as 姓名,Ssex as 性别,Sbirthday as 出生日期,Class as 班号 from student;
4、 select * from score where degree between 60 and 80;
或select * from score where degree>=60 and degree<=80;
5、 select * from score where degree in (85,86,88);
6、 select * from student where class='95031'or Ssex='女';
7、 select * from student order by class desc;
8、 select * from score order by cno asc ,degree desc;
或select * from score order by cno ,degree desc;
9、 select count(*) as CNT from student where class='95031';
10、select Sno as '学号',cno as '课程号', degree as '最高分' from score
where degree=(select max(degree) from score)
11、select avg(degree)as 课程平均分 from score where cno='3-105';
12、select cno,avg(degree) from score where cno like'3%'group by cno having count(*) >5;
13、select Sno from score group by Sno having min(degree)>70 and max(degree)<90;
14、select student.Sname,score.Cno,score.degree from student,score where student.Sno=score.Sno;
15、select x.Sno,y.Cname,x.degree from score x,course y where x.Cno=y.Cno;
16、select x.Sname,y.Cname,z.degree from student x,course y,score z where x.Sno=z.Sno and z.Cno=y.Cno;
17、select y.Cno,avg(y.degree) from student x,score y where x.Sno=y.Sno and x.class='95033'group by y.cno;
18、select Sno,Cno,rank from score,grade where degree between low and upp order by rank;
19、select x.Cno,x.Sno,x.degree from score x,score y
where x.cno='3-105' and x.degree>y.degree and y.sno='109'and y.cno='3-105';
20、
1,查询成绩非本科最高 select * from score b where degree <(select max(degree) from score a where a.cno=b.cno);
2,查询成绩非本科最高并且选2门以上的学生的成绩:
21、select x.cno,x.Sno,x.degree from score x,score y where x.degree>y.degree and y.sno='109'and y.cno='3-105';
select cno,sno,degree from score where degree >(select degree from score where sno='109' and cno='3-105')
22、select sno,sname,sbirthday from student where to_char(sbirthday,'yyyy')=(select to_char(sbirthday,'yyyy') from student where sno='108');
23、select cno,sno,degree from score where cno=(select x.cno from course x,teacher y where x.tno=y.tno and y.tname='张旭');
24、select tname from teacher where tno in(select x.tno from course x,score y where x.cno=y.cno group by x.tno having count(x.tno)>5);
25、select * from student where class in('95033','95031');
26、select distinct cno from score where degree in (select degree from score where degree>85);
27、select * from score where cno in(select x.cno from course x,teacher y where y.tno=x.tno and y.depart='计算机系');
28、select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
29、select * from score where cno='3-105' and degree>any (select degree from score where cno='3-245')order by degree desc;
30、select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');
31、select tname,tsex,tbirthday from teacher
union select sname,ssex,sbirthday from student;
32、select tname,tsex,tbirthday from teacher where tsex='女'
union select sname,ssex,sbirthday from student where ssex='女';
33、select * from score a where degree<(select avg(degree)
from score b where a.cno=b.cno);
34、select tname,depart from teacher a where exists
(select * from course b where a.tno=b.tno);
35、select tname,depart from teacher a where not exists
(select * from course b where a.tno=b.tno);
36、select class from student where ssex='男'group by class having count(*)>=2;
37、select * from student where sname not like'王_';
38、select sname as 姓名,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')) as 年龄 from student
39、select sname,sbirthday as 最大 from student where sbirthday =(select min (sbirthday) from student)
union select sname,sbirthday as 最小 from student where sbirthday =(select max(sbirthday) from student)
40、select class,sname,sbirthday from student order by class desc,sbirthday;
41、select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex='男';
42、select * from score where degree=(select max(degree)from score);
43、select sname from student where ssex=(select ssex from student where sname='李军');
44、select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');
45、select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course
where cname='计算机导论');
bill表――bill_id是帐单标识,charge为帐单金额,pay_id为付款编号,PK:bill_id
bill_id INTEGER
charge INTEGER
pay_id INTEGER
payment表――pay_id为付款编号,charge为付款金额,PK:pay_id
pay_id INTEGER
charge INTEGER
要求更新payment使charge=bill表中同一pay_id的charge之和。 /*
update a set a.charge=b.charge
from payment a,(select pay_id,sum(charge) as charge from bill group by pay_id) b
where a.pay_id=b.pay_id
*/
2、有如下信息:
起始地 目的地 距离(公里)
A B 1000
A C 1100
A D 900
A E 400
B D 300
D F 600
E A 400
F G 1000
C B 600
请用SQL语句或一段代码写出从A出发,可达到的目的地(包括间接方式)。 /*
第二道题目: create table t
(st varchar(20),ed varchar(20),km int) insert t values ( 'A ', 'B ',1000)
insert t values ( 'A ', 'C ',1100)
insert t values ( 'A ', 'D ',900)
insert t values ( 'A ', 'E ',400)
insert t values ( 'B ', 'D ',300)
insert t values ( 'D ', 'F ',600)
insert t values ( 'E ', 'A ',400)
insert t values ( 'F ', 'G ',1000)
insert t values ( 'C ', 'B ',600)
go
create function f_go(@col varchar(10))
returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int)
as
begin
declare @i int
set @i=1
insert @t select st+ '- '+ed,*,@i from t where st=@col
while exists (select * from t a,@t b where
b.ed=a.st and b.level=@i and b.ed <> @col )
begin
set @i=@i+1
insert @t
select b.col+ '- '+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t b
where b.level=@i-1 and b.ed=a.st and b.ed <> @col
end
return
end
go select col,km from dbo.f_go( 'A ')
drop function f_go
drop table t
*/
3.表A定义如下:
属性 类型 备注
Id Integer PK
Name Varchar(30)
State char(3)
表A中现有1000万条记录,如果客户端要通过分页的方式从表A中取数据,其中每页20条
记录,有几种方法?描述每种方法如何处理,有什么优缺点?用SQL写出实现代码。
表A定义如下:
属性 类型 备注
Id Integer PK
Name Varchar(30)
State char(3)
表A中现有1000万条记录,如果客户端要通过分页的方式从表A中取数据,其中每页20条
记录,有几种方法?描述每种方法如何处理,有什么优缺点?用SQL写出实现代码。
/*
------用老大的方法
查询 X页第y行记录:
1.select top y * from 表a where id not in(select top (x-1)*y id from 表a)
2.select id2=identity(int,1,1),* into #tb from 表a
select * from #tb where id2 between (x-1)*y and (x*y-1)
*/
4、表A定义如下:
属性 类型
Year Integer
Quarter Varchar(30)
Amount float Year Quarter Amount
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2001 1 2.1
2001 2 2.2
2001 3 2.3
2001 4 2.4
其中每行表表示一个季度的数据。
如果处理表A中的数据,得到如下的结果。
Year Quarter1 Quarter2 Quarter3 Quarter4
2000 1.1 1.2 1.3 1.4
2001 2.1 2.2 2.3 2.4
请用SQL写一段代码实现 select year,(case when quarter=1 then amount end) as 'quarter1 ',
(case when quarter=2 then amount end) as 'quarter2 ',
(case when quarter=3 then amount end) as 'quarter3 ',
(case when quarter=4 then amount end) as 'quarter4 ' from 表A
group by year
代码 编号
a F001
a F002
a F003
a F005
a F008
a F009
a F010
a F030
变成下列方式
代码 起始号 截止号
a F001 F003
a F005 F005
a F008 F010
a F030 F030
declare @a table(代码 varchar(2),编号 varchar(8))
insert into @a select 'a ', 'F001 '
insert into @a select 'a ', 'F002 '
insert into @a select 'a ', 'F003 '
insert into @a select 'a ', 'F005 '
insert into @a select 'a ', 'F008 '
insert into @a select 'a ', 'F009 '
insert into @a select 'a ', 'F010 '
insert into @a select 'a ', 'F030 ' --select t.* from @a t where not exists(select 1 from @a where 代码=t.代码 and right(编号,3)=right(t.编号,3)-1)
--select t.* from @a t where not exists(select 1 from @a where 代码=t.代码 and right(编号,3)=right(t.编号,3)+1) select
a.代码,
起始号=a.编号,
截止号=min(b.编号)
from
(select t.* from @a t where not exists(select 1 from @a where 代码=t.代码 and right(编号,3)=right(t.编号,3)-1)) a,
(select t.* from @a t where not exists(select 1 from @a where 代码=t.代码 and right(编号,3)=right(t.编号,3)+1)) b
where
a.代码=b.代码 and a.编号 <=b.编号
group by
a.代码,a.编号 /*
代码 起始号 截止号
---- -------- --------
a F001 F003
a F005 F005
a F008 F010
a F030 F030
*/
--设置系统时间
exec master..xp_cmdshell 'date 2006-2-13 '
exec master..xp_cmdshell 'time 14:44:00 '
infoID INT 信息ID(自增列)
infoTitle NVARCHAR(100) 信息标题
infoContent NVARCHAR(2000) 信息正文
infoPubDate DATETIME 信息发布日期
infoPubUser VARCHAR(30) 信息发布用户帐号InfoReply:
infoReplyID INT 评论ID(自增列)
infoID INT 信息ID
infoReplyContent NVARCHAR(2000) 评论正文
infoReplyDate DATETIME 评论日期
infoReplyUser VARCHAR(30) 评论用户帐号User
userNo VARCHAR(30) 用户帐号
userName NVARCHAR(30) 用户姓名
表的关系可以通过以下模型图来表示:info InfoReply
PK infoID ----------------------------------------------->PK infoReplyID
FK infoPubUser FK2 infoID
| FK1 infoReplyUser
| |
| |
| User |
|--------------------->PK userNo <--------------------------|
userName现有下列问题:
(1) 请说明在上述模型图中,PK和FK1、FK2的含义?(2) 现在有一个查询,需要返回一个最新发布的20条信息列表,要求包含:信息ID、信息标题、信息发布时间、信息发布人姓名、信息评论总数和最后评论时间,并且按最后评论时间排序,请给出该查询的SQL语句?(3) 仍是第(2)中的查询要求,现在假设Info表和User表之间不存在任何外键约束,即infoPubUser表示的用户帐号可能在User表中已经被删除,这时如果要继续返回最新的20条信息列表,包含(2)中相同的列,该SQL语句应该怎么写?
(4) 假设Info表有一个字段infoReplyCount(信息评论总数),那我们需要怎么做才能让用户每增加一条评论,该字段自动+1,说出大概思路即刻,能写出SQL语句最好
(5) 写出返回每一条信息的评论总数的SQL语句
(6) 假设发现Info表有多条记录丢失了,如何最大程度地找出有多少条记录丢失了,写出SQL语句。(不使用InfoReply表)
(7) 假设发现Info表只有1条记录丢失了,如何知道丢失的记录的ID,写出SQL语句(不使用InfoReply表)
(8) 仍然是(6)的问题,可以使用InfoReply表,写出SQL语句。
(9) 假设Info表有一个字段infoReplyCount(信息评论总数),infoReplyCount>=20的为热门帖子,小于20的为非热门帖子,请根据Info表的内容输出以下2行数据,要求SQL语句最简。类别 数量
热门 30
非热门 18
--1. pk 主键 fk 外键,那个关系图乱了,这个也没啥说的--2. 信息ID、信息标题、信息发布时间、信息发布人姓名、信息评论总数和最后评论时间,并且按最后评论时间排序,请给出该查询的SQL语句?select * from
(
select top 20 a.infoid,a.infotitle,a.infopubdate,c.username,
count(1) as 信息评论总数,max(b.inforeplydate) as 最后评论时间
from info a inner join InfoReply b on a.infoid = b.infoid
inner join User c on a.infopubuser = c.userno
group by a.infoid,a.infotitle,a.infopubdate,c.username
order by a.infopubdate desc
)tab
order by 最后评论时间--3.
select * from
(
select top 20 a.infoid,a.infotitle,a.infopubdate,isnull(c.username,'用户被删除') as 用户名,
count(1) as 信息评论总数,max(b.inforeplydate) as 最后评论时间
from info a inner join InfoReply b on a.infoid = b.infoid
left join User c on a.infopubuser = c.userno
group by a.infoid,a.infotitle,a.infopubdate,c.username
order by a.infopubdate desc
)tab
order by 最后评论时间--4. 可以用触发器实现,快下班了,来不及写了--5.
select a.infoid,a.infotitle,a.infopubdate,c.username,
count(1) as 信息评论总数
from info a inner join InfoReply b on a.infoid = b.infoid
inner join User c on a.infopubuser = c.userno
group by a.infoid,a.infotitle,a.infopubdate,c.username--6.这个题目出的有点2了估计出题目的人的用意是要根据自增列找出丢失的纪录
select count(1) as 丢失的纪录数
from into a
where not exists(select 1 from info where infoID = a.infoID - 1)--7. 找到丢失的所有纪录(如果最后一条纪录丢失,则找不回来)
select infoID - 1 as 丢失的infoID
from info a
where not exists(select 1 from info where infoID = a.infoID - 1)--8.
select a.infoID
from InfoReply a left join info b on a.infoid = b.infoid
where b.infoid is null--9. 这个最简捷不敢说,再想想,不过这样写的效率应该不差
select '热门' as 类别,数量=(select count(1) from info where infoReplyCount>=20)
union all
select '非热门' as 类别,数量=(select count(1) from info where infoReplyCount<20)
year salary
------------------ ---------------------
2000 1000
2001 2000
2002 3000
2003 4000
显示查询结果
year salary
------------------ ---------------------
2000 1000
2001 3000
2002 6000
2003 10000即salary为以前年的工资的和; 我这里提供的答案有两种
第一种:
select b.year,sum(a.salary)
from salary a,salary b
where a.year<=b.year group by b.year
order by b.year;其中salary 为工资表 第二种:
select s1.year "year",(select sum(s2.salary) from salary s2 where s2.year<=s1.year) "salary" from salary s1;现在我们假设只有一个table,名为pages,有四个字段,id, url,title,body。里面储存了很多网页,网页的url地址,title和网页的内容,然后你用一个sql查询将url匹配的排在最前, title匹配的其次,body匹配最后,没有任何字段匹配的,不返回。就是上面这道面试题,让我想了一个下午,在网上找资料,最后用下面方法实现
SELECT *
FROM page where url like '%baidu%' or title like '%baidu%' or like ''
ORDER BY CHARINDEX('baidu', url) DESC, CHARINDEX('baidu', title) DESC,
CHARINDEX('baidu', body) DESC
1.一道SQL语句面试题,关于group by
表内容:
2005-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 2
2005-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 #tmp group by rq
2) 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.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col0012.请教一个面试中遇到的SQL语句的查询问题
表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name3.面试题:一个日期判断的sql语句?
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型,包含日期与时间)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=04.有一张表,里面有3个字段:语文,数学,英语。其中有3条记录分别表示语文70分,数学80分,英语58分,请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路):
大于或等于80表示优秀,大于或等于60表示及格,小于60分表示不及格。
显示格式:
语文 数学 英语
及格 优秀 不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表,里面包含两个字段ID和IDValues,类型都是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。日志文件
日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件,但可以不止一个。日志文件的推荐文件扩展名是 .ldf。7.请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据,注意提供的数据及结果不准确,只是作为一个格式向大家请教。
如使用存储过程也可以。table1月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8table2部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部table3 (result)部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9------------------------------------------
1)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部门dep = b.部门dep and b.月份mon = '一月份' and
a.部门dep = c.部门dep and c.月份mon = '二月份' and
a.部门dep = d.部门dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep8.华为一道面试题
一个表中的Id有多个记录,把所有这个id的记录查出来,并显示共有多少条记录数。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
9。表结构以及数据如下:CREATE TABLE 表
(ID int, 日期 varchar(11), 单据 char(3))INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 1 , '2004-08-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 2 , '2004-09-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 3 , '2004-10-02' , '002' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 4 , '2004-09-02' , '002' );要求:设计一个查询,返回结果如下:ID 日期 单据
---------- ----------- ---
1 2004-08-02 001
4 2004-09-02 002即对于每个单据号,返回日期最小的行。--解答:
--相关子查询
select a.* from 表 a
where 日期=
(select min(日期) from 表 where 单据=a.单据)--用JOIN的连接
select a.* from 表 a,
(select min(日期) 日期,单据 from 表 group by 单据) b
where a.单据=b.单据 and a.日期=b.日期--不用JOIN的连接
select a.* from 表 a JOIN
(select min(日期) 日期,单据 from 表 group by 单据) b
ON a.单据=b.单据 and a.日期=b.日期--用谓词Exists
select * from 表 a
where not exists(select 1 from 表 where 单据=a.单据 and 日期<a.日期) 但我感觉这种方法并不是最简单的,后来把这个方法发给面试的人,他给我了一种更简单方法,只要用基本的Sql语句就可以实现。代码如下select a.[id],a. from
(
select [page].[id],100 as from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as from [page] where [page].[title] like '%baidu%'
union
select [page].[id],10 as from [page] where [page].[body] like '%baidu%'
) as a order by desc 用union 实现联合查询,在每个查询语句中定义一个临时变量 并给赋值,在最后的输出时采用来排序,这样实现,非常简单,我感觉这题更多考研我们的编程思想。 一道sql面试题解法
id strvalue type
1 how 1
2 are 1
3 you 1
4 fine 2
5 thank 2
6 you 2要求用sql把它们搜索出来成为这样的
#how are you#fine thank you# 解答:select
(select '#'+replace(
replace((SELECT strvalue FROM tb_test t where type = 1 FOR XML AUTO),'<t strvalue="',' ')
,'"/>', ' ')
+'#'
)
+
(select replace(replace((SELECT strvalue FROM tb_test t where type = 2 FOR XML AUTO),'<t strvalue="',' '),'"/>', ' ')+'#')