SQL试题

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 1else 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.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.rq

2.请教一个面试中遇到的SQL语句的查询问题
表中有A BC三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
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 #tmp
select (case when a>b then a else b end),(case when b>c then b else c end) from #tmp

3.面试题：一个日期判断的sql语句？
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
------------------------------------------
select * from #tmp where datediff(dd,rq,getdate())=0
select * from #tmp where rq=rtrim(convert(varchar,getdate(),23))

4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记

录并按以下条件显示出来（并写出您的思路）： 
   大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。 
       显示格式： 
       语文       数学               英语 
       及格       优秀               不及格   
------------------------------------------

CREATE TABLE A (语文 int, 数学 int, 英语 int);
insert into A values(70,80,58);
insert into A values(100,50,60);

select * from A;

select (case when 语文 >=80 then ‘优秀’
       when 语文 >=60 then ‘及格’else ‘不及格’end) 语文,
    (casewhen 数学 >=80then‘优秀’
       when 数学 >=60 then‘及格’ else ‘不及格’end) 数学,
    (casewhen 英语 >=80then ‘优秀’
       when 英语 >=60 then ‘及格’else ‘不及格’end) 英语

from A;

5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
------------------------------------------
用户临时表:createtable #xx(ID int, IDValues int)
系统临时表:createtable ##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      8

table2

部门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

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

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 #A

create 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 #B

2)select b.dep,dname,
       sum(case when a.mon='一月份' then a.yj else 0 end ) as'一月份',
       sum(case when a.mon='二月份' then a.yj else 0 end ) as'一月份',
       sum(case when a.mon='三月份' then a.yj else 0 end ) as'一月份'
from  #B b left join  #A a on a.dep=b.dep group by b.dep ,dname--order by b.dep

 

??)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2b,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

8.华为一道面试题
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
create 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','国际业务部')
insert into #B values('01','国内业务一部')
insert into #B values('02','国内业务二部')
insert into #B values('01','国内业务三部')
insert into #B values('03','国际业务部')
select * from #b

select dep,count(dep) from #B  group by dep having count(*)>1
select count(*) from #B  group by dep having count(*)>1
select * from(select count(dep) as count from #B group by dep)T whereT.count>1

 

题目1:

问题描述:

　　为管理岗位业务培训信息，建立3个表:

 S(S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

 C(C#,CN ) C#,CN 分别代表课程编号、课程名称

 SC( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

 1.使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

 --实现代码:

 Select SN,SD FROM S Where [S#] IN( Select[S#] FROM C,SC Where C.[C#]=SC.[C#] AND CN=N'税收基础')

 2.使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

 --实现代码:

 Select S.SN,S.SD FROM S,SC WhereS.[S#]=SC.[S#] AND SC.[C#]='C2'

 3.使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位

 --实现代码:

 Select SN,SD FROM S Where [S#] NOTIN( Select [S#] FROM SC Where [C#]='C5')

 4.使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

 --实现代码:

 Select SN,SD FROM S Where [S#] IN( Select[S#] FROM SC RIGHT JOIN C ON SC.[C#]=C.[C#] GROUP BY [S#] HAVINGCOUNT(*)=COUNT([S#]))

 5.查询选修了课程的学员人数

 --实现代码:

 Select学员人数=COUNT(DISTINCT[S#]) FROM SC

 6.查询选修课程超过5门的学员学号和所属单位

 --实现代码:

 Select SN,SD FROM S Where [S#] IN( Select[S#] FROM SC GROUP BY [S#] HAVING COUNT(DISTINCT [C#])>5)

题目2:

问题描述:

 

 S(SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

 C(CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER为任课教师

 SC(SNO,CNO,SCGRADE)选课关系。SCGRADE 为成绩

 1.找出没有选修过“李明”老师讲授课程的所有学生姓名

 --实现代码:

 Select SNAME FROM S Where NOT EXISTS( Select * FROM SC,CWhere SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO)

 

 2.列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

 --实现代码:

 Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)FROM S,SC,( Select SNO FROM SC Where SCGRADE<60 GROUP BY SNO HAVINGCOUNT(DISTINCT CNO)>=2 )A Where S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BYS.SNO,S.SNAME

 

 3.列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

 --实现代码:

 Select S.SNO,S.SNAME FROM S,( Select SC.SNO FROMSC,C Where SC.CNO=C.CNO AND C.CNAME IN('1','2') GROUP BY SNO HAVINGCOUNT(DISTINCT CNO)=2 )SC Where S.SNO=SC.SNO

 

 4.列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

 --实现代码:

 Select S.SNO,S.SNAME FROM S,( Select SC1.SNO FROMSC SC1,C C1,SC SC2,C C2 Where SC1.CNO=C1.CNO AND C1.NAME='1' AND SC2.CNO=C2.CNOAND C2.NAME='2' AND SC1.SCGRADE>SC2.SCGRADE )SC Where S.SNO=SC.SNO

 

 5.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

 --实现代码:

 

 Select S.SNO,S.SNAME,SC.[1号课成绩],SC.[2号课成绩] FROM S,( Select SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE FROM SC SC1,C C1,SC SC2,C C2 Where SC1.CNO=C1.CNO ANDC1.NAME='1' AND SC2.CNO=C2.CNO AND C2.NAME='2' AND SC1.SCGRADE>SC2.SCGRADE)SC Where S.SNO=SC.SNO

 

续篇

十几道sql语句面试题

第1部分：

题目：

Student(S#,Sname,Sage,Ssex) 学生表

 Course(C#,Cname,T#) 课程表

 SC(S#,C#,score) 成绩表

 Teacher(T#,Tname) 教师表

问题：

 1、查询“001”课程比“002”课程成绩高的所有学生的学号；

 select a.S#

 from (select s#,score from SC where C#=’001′) a,

 (select s#,score from SC where C#=’002′) b

 where a.score>b.score and a.s#=b.s#;

 

2、查询平均成绩大于60分的同学的学号和平均成绩；

 select S#,avg(score)

 from sc

 group by S# having avg(score) >60;

 

3、查询所有同学的学号、姓名、选课数、总成绩；

 selectStudent.S#,Student.Sname,count(SC.C#),sum(score)

 from Student left Outer join SC onStudent.S#=SC.S#

 group by Student.S#,Sname

 

4、查询姓“李”的老师的个数；

 select count(distinct(Tname))

 from Teacher

 where Tname like ‘李%’;

 

5、查询没学过“叶平”老师课的同学的学号、姓名；

 select Student.S#,Student.Sname

 from Student

 where S# not in (select distinct( SC.S#) fromSC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# andTeacher.Tname=’叶平’);

 

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

 select Student.S#,Student.Sname

 from Student,SC

 where Student.S#=SC.S# and SC.C#=’001′and exists(Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);

 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

 select S#,Sname

 from Student

 where S# in

 (select S#

 from SC ,Course ,Teacher

 where SC.C#=Course.C# and Teacher.T#=Course.T#and Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select count(C#) fromCourse,Teacher where Teacher.T#=Course.T# and Tname=’叶平’));

 

8、查询所有课程成绩小于60分的同学的学号、姓名；

 select S#,Sname

 from Student

 where S# not in (select Student.S# fromStudent,SC where S.S#=SC.S# and score>60);

 

9、查询没有学全所有课的同学的学号、姓名；

 select Student.S#,Student.Sname

 from Student,SC

 where Student.S#=SC.S#

 group by Student.S#,Student.Sname havingcount(C#) <(select count(C#) from Course);

 

10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；

 select S#,Sname

 fromStudent,SC

 where Student.S#=SC.S# and C# in （select C#from SC where S#='1001'）;

 

11、删除学习“叶平”老师课的SC表记录；

 Delect SC

 from course ,Teacher

 where Course.C#=SC.C# and Course.T#=Teacher.T# and Tname='叶平';

 

12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

 SELECT L.C# 课程ID,L.score 最高分,R.score 最低分

 FROM SC L ,SC R

 WHERE L.C# = R.C#

 and

 L.score = (SELECT MAX(IL.score)

 FROM SC IL,Student IM

 WHERE IL.C# = L.C# and IM.S#=IL.S#

 GROUP BY IL.C#)

 and

 R.Score = (SELECT MIN(IR.score)

 FROM SC IR

 WHERE IR.C# = R.C#

 GROUPBY IR.C# );

 

13、查询学生平均成绩及其名次

 SELECT 1+(SELECT COUNT( distinct 平均成绩)

FROM (SELECT S#,AVG(score) 平均成绩

FROM SC

 GROUP BY S# ) T1

 WHERE 平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩

 FROM (SELECT S#,AVG(score) 平均成绩 FROM SCGROUP BY S# ) T2

 ORDER BY 平均成绩 desc;

 

14、查询各科成绩前三名的记录:(不考虑成绩并列情况)

 SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as分数

 FROM SC t1

 WHERE score IN (SELECT TOP 3 score

 FROM SC

 WHERE t1.C#= C#

 ORDER BY score DESC)

 ORDER BY t1.C#;

 

15、查询每门功成绩最好的前两名

 SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as分数

 FROM SC t1

 WHERE score IN (SELECT TOP 2 score

 FROM SC

 WHERE t1.C#= C#

 ORDER BY score DESC )

 ORDER BY t1.C#;

补充：

 已经知道原表

 year salary

 ——————

 2000 1000

 2001 2000

 2002 3000

 2003 4000

 

解：

 select b.year,sum(a.salary)

 from salary a,salary b

 where a.year<=b.year

 group by b.year

 order by b.year;

在面试过程中多次碰到一道SQL查询的题目，查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：

 方法一：

 select top 10 *

 from A

 where ID >(select max(ID) from (select top30 ID from A order by ID ) T) order by ID

 方法二：

 select top 10 *

 from A

 where ID not In (select top 30 ID from A orderby ID)

 order by ID

 

第2部分：

数据库及SQL部分：（共4题：基础3道，中等难度1道）

106、有3个表（15分钟）：【基础】

Student 学生表 (学号，姓名，性别，年龄，组织部门)

Course 课程表 (编号，课程名称)

Sc 选课表 (学号，课程编号，成绩)

表结构如下：

1）写一个SQL语句，查询选修了’计算机原理’的学生学号和姓名（3分钟）

2）写一个SQL语句，查询’周星驰’同学选修了的课程名字（3分钟）

3）写一个SQL语句，查询选修了5门课程的学生学号和姓名（9分钟）

答：1）SQL语句如下：

select stu.sno, stu.sname from Studentstu

where (select count(*) from sc wheresno=stu.sno and cno =

(select cno from Course where cname='计算机原理')) !=0;

2）SQL语句如下：

select cname from Course

where cno in ( select cno from sc wheresno =

(select sno from Student where sname='周星驰'));

3）SQL语句如下：

select stu.sno, stu.sname from studentstu

where (select count(*) from sc wheresno=stu.sno) = 5;

107、有三张表,学生表S,课程C,学生课程表SC,学生可以选修多门课程,一门课程可以被多个学生选修,通过SC表关联。【基础】

1）写出建表语句；

2）写出SQL语句,查询选修了所有选修课程的学生；

3）写出SQL语句,查询选修了至少5门以上的课程的学生。

答：1）建表语句如下（mysql数据库）：

create 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)

);

2）SQL语句如下：

select stu.id, stu.name from s stu

where (select count(*) from sc wheresid=stu.id)

= (select count(*) from c);

3）SQL语句如下：

select stu.id, stu.name from s stu

where (select count(*) from sc wheresid=stu.id)>=5;

108、数据库表(Test)结构如下：【基础】

ID NAME AGE MANAGER(所属主管人ID)

106 A 30 104

109 B 19 104

104 C 20 111

107 D 35 109

112 E 25 120

119 F 45 NULL

要求:列出所有年龄比所属主管年龄大的人的ID和名字?

答：SQL语句如下：

select employee.name from test employee

where employee.age > (selectmanager.age from test manager

where manager.id=employee.manager);

109、有如下两张表

表city：表state：

CityNo

CityName

StateNo

BJ

北京

（Null）

SH

上海

（Null）

GZ

广州

GD

DL

大连

LN

 

State No

State Name

GD

广东

LN

辽宁

SD

山东

NMG

内蒙古

欲得到如下结果：

City No  City Name  State No  State Name

BJ       北京      （Null） （Null）

DL      大连        LN    辽宁

GZ      广州       GD    广东

SH       上海     （Null）（Null）

写相应的SQL语句。

答：SQL语句为：

SELECT C.CITYNO, C.CITYNAME, C.STATENO,S.STATENAME

FROM CITY C, STATE S

WHERE C.STATENO=S.STATENO(+)

ORDER BY(C.CITYNO);            或者

1

select  c.*, s.stateName from city c left join state s on c.stateNo=s.stateNo;

2、select c.cityName from city c, salary s where c.cityNo=s.cityNo and s.salary<(select avg(salary) from salary);

括号中子查询是先求出工资标准平均值，外面查询第一个条件是找出城市中文名，第二个是找出低于平均值的

 

 

第3部分：

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','负');

Select * from #tmp;

1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case whenshengfu='负' then 1 else 0end)'负' from #tmpgroup by rq;
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)Ninner join
(select rq,負=count(*) from #tmp where shengfu='负'group by rq)Mon N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group bycol001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group bycol001) b
where a.col001=b.col001

2.请教一个面试中遇到的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_name

3.面试题：一个日期判断的sql语句？
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0

4.有一张表，里面有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 A;

5.在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 8

table2

部门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.dep

8.华为一道面试题
一个表中的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 whereT.count>1

 

第4部分：

SQL面试题（1）

create tabletesttable1
(
id int IDENTITY,
department varchar(12)
)

select * fromtesttable1
insert into testtable1 values('设计')
insert into testtable1 values('市场')
insert into testtable1 values('售后')
/*
结果
id department
1 设计
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,'陈七')
/*
用一条SQL语句，怎么显示如下结果
id dptID department name
1 1 设计张三
2 1 设计李四
3 2 市场王五
4 3 售后彭六
5 4 黑人陈七
*/

答案：

SELECTtesttable2.* , ISNULL(department,'黑人')
FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID

也做出来了可比这方法稍复杂。

sql面试题（2）

有表A，结构如下：
A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为：
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。

结果：

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_id
from myPro group by p_id

 

SQL面试题（3）

为管理业务培训信息，建立3个表：

S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄

C(C#,CN)C#,CN分别代表课程编号，课程名称

SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩

（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?

答案：select s# ,snfrom s where S# in(select S# from c,sc where c.c#=sc.c# and cn=’税收基础’)

(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?

答：select sn,sdfrom s,sc where s.s#=sc.s# and sc.c#=’c2’

(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?

答：select sn,sdfrom s where s# not in(select s# from sc where c#=’c5’)

(4)查询选修了课程的学员人数

答：select学员人数=count(distinct s#) from sc

(5) 查询选修课程超过5门的学员学号和所属单位?

答：select sn,sdfrom s where s# in(select s# from sc group by s# having count(distinctc#)>5)

SQL面试题（4）

1.       查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：
select top 10 * from A where ID >(select max(ID) from (select top 30 ID fromA order by A ) T) order by A

2.查询表A中存在ID重复三次以上的记录,完整的查询语句如下：
select * from(select count(ID) as count from table group by ID)T whereT.count>3

一. 创建一个学生档案表(表名为xsda)，其表结构如下：

列名

类型

长度

允许空

备注

Xh

Char

10

 

学号

Xm

Char

8

√

姓名

csny

Datetime

 

√

出生年月

Gz

Decimal

6

√

工资

Zy

Char

10

√

专业

Create Table xsda

(

xh    Char (10)not null  unique,

xm       Char(8),

csny     Datetime ,

gz      Decimal(6,1),

zy      Char      (10)

)

1.        在xsda表中插入一个学生记录：（2000jsj008,李平）

    insert intoxsda(xh,xm)     values(‘2000jsj008’,’李平’)

2.        把xsda表中80-01-01前出生的人的工资增加20%

Updatexsda set gz=gz*1.2 where csny<’ 80-01-01’

3.        查询xsda表中不同专业的人数

Select zy ,count(xh)  from xsda group  by  zy

4.        假如另外还有一个学生成绩表xscj(xh,kch,kccj), xh,kch,kccj分别指学号、课程号、成绩，要求查询姓名为李平的同学的各门课程的成绩。

Selectkch,kccj  from xsda,xscj wherexsda.xh=xscj.xh and xm=’ 李平’

5.        xsda、xscj表同上，查询选修了kch为Yy2的学生的xh和Xm

Selectxh,xm  from xsda,xscj wherexsda.xh=xscj.xh and kch=’ Yy2’

 

 

二、使用SQL语句创建一个班级表CLASS，属性如下：CLASSNO，DEPARTNO，CLASSNAME；类型均为字符型；长度分别为8、2、20且均不允许为空。

CREATE ___table_____ CLASS

(CLASSNO __char____ (8)NOT NULL,

 DEPARTNO CHAR (2) NOT NULL,

 CLASSNAME　CHAR (__20__) NOT NULL

）

 

三、有一个[学生课程]数据库，数据库中包括三个表：

学生表:Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为：Student(Sno,Sname,Ssex,Sage,Sdept)，Sno 为关键字。

课程表:Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记为： Course(Cno,Cname,Cpno,Ccredit) Cno为关键字。

成绩表:SG由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记为： SG(Sno,Cno,Grade) (SNO,CNO) 为关键字。

用SQL语言实现下列功能：

1．建立学生表[Student]，其中学号属性不能为空，并且其值是唯一的。

2．查考试成绩有不及格的学生的学号。

3．将学号为05001学生的年龄改为22岁。

4．计算1号课程的学生平均成绩。

5. 查计算机系姓赵的男同学的姓名(Sname)、性别(Ssex)、年龄(Sage)。

6．将一个新学生记录(学号：05020；姓名：丁莉；性别：女；年龄：17岁；所在系：计算机；)插入Student表中。

 

1．

createtable student

(Snochar(5) primary key,

 Sname char(20),

 Ssex char(2),

 Sage int,

 Sdept char(15))

2．

selectdistinct sno
from sg
where grade < 60

3．

updatestudent
set sage=22
where sno='05001'

4．

select avg(grade)

from sg

where cno='1'

5.   

selectsname，ssex，sage

fromsdudent

wheresdept=’计算机系’and sname like '赵 %' and ssex ='女'

 

6．

insertinto student
values ('05020', '丁莉', '女', 17, '计算机系')

 

四、有一个[学生课程]数据库，数据库中包括三个表：

学生表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为： Student(Sno,Sname,Ssex,Sage,Sdept) ，Sno 为关键字。

课程表Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记为：Course(Cno,Cname,Cpno,Ccredit) Cno为关键字。

成绩表SG由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记为： SG(Sno,Cno,Grade) (SNO,CNO)为关键字。

用SQL语言实现下列功能：

1．建立学生表Student，其中学号属性不能为空，并且其值是唯一的。

2．向Student表增加“入学时间(Scome)”列，其数据类型为日期型。

3．查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

4．查询学习1号课程的学生最高分数、平均成绩。

5．查询与“李洋”在同一个系学习的学生。

6．将计算机系全体学生的成绩置零。

7．删除学号为05019的学生记录。

8．删除计算机系所有学生的成绩记录。

1．

CREATETABLEStudent

(SnoCHAR(5) NOT NULL UNIQUE,

SnameCHAR(20),

SsexCHAR(2),

SageINT,

SdeptCHAR(15))

2．

ALTERTABLE Student ADD Scome DATETIME

3．

SELECTSno, Grade

FROM SG

WHERECno='3'

ORDERBY Grade DESC

4．

SELECTMAX(Grade), AVG(Grade)

FROM SC

WHERECno='1'

5．

SELECTSno, Sname, Sdept

FROMStudent

WHERESdept IN

 (SELECT Sdept FROM Student

   WHERE Sname='李洋')

6．

UPDATESG

SETGrade=0

WHERESno in

(SELECT Sno FROM Student

WHERESdept = '计算机系')

7．

DELETEFROM Student

WHERESno='05019'

8．

DELETEFROM SG

WHERESno in

(SELECT Sno FROM Student

WHERESdept = '计算机系')

 

 

五、有一个[学生课程]数据库，数据库中包括三个表：

学生表由学号、姓名、性别、年龄、所在系五个属性组成，

    可记为：学生表(学号，姓名，性别，年龄，所在系)  [学号]为关键字。

课程表由课程号、课程名、先修课号、学分四个属性组成，

    可记为：课程表(课程号，课程名，先修课号，学分) 课程表为关键字。

学生选课表由学号、课程号、成绩三个属性组成，

    可记为： 成绩表(学号，课程号，成绩) (学号、课程号)为关键字。

编写用SQL语言实现下列功能的sql语句代码：

1、建立一个[学生表]，其中学号属性不能为空，并且其值是唯一的。

2、查询全体学生的详细记录。

3、查询年龄在20至23岁之间的学生的姓名、系别、和年龄。

4、计算1号课程的学生平均成绩。

5、将计算机科学系全体学生的成绩置零。

 

1、建立一个学生表。

CREATE TABLE 学生表

(学号 CHAR(5)NOT NULL UNIQUE,

 姓名CHAR(20),

 性别 CHAR(2),

 年龄 INT,

 所在系 CHAR(15))

2、查询全体学生的详细记录

SELECT *

FROM 学生表

3、查询年龄在20至23岁之间的学生的姓名、系别、和年龄

SELECT 姓名, 所在系, 年龄

FROM 学生表

WHERE 年龄 BETWEEN 20AND 23

4、计算1号课程的学生平均成绩

SELECT AVG(成绩)

FROM 成绩表

WHERE 课程号='1'

5、将计算机科学系全体学生的成绩置零

UPDATE 成绩表

SET 成绩=0

WHERE 学号 in

( SELECT 学号 FROM 学生表

  WHERE Sdept ='计算机科学系')

现有关系数据库如下：

数据库名：学生成绩数据库

学生表(班级编号，学号，姓名，性别，民族，身份证号，出生日期)

课程表(课程号，课程名，开课学期，学时)

成绩表(ID,学号，课程号，分数)

用SQL语言实现下列功能的sql语句代码：

 

1．查询学生信息表中的年龄(重复年龄只显示一次)

2．从学生信息表和教学成绩表中查询查询学生的学号、姓名、课程名和分数

3．从课程表中统计第二学期的总学时。

执行结果为：

课程好   课程名  开课学期   学时

…       …      …         …

…       …      …         …

                  sum

                  ============ 

                            …

4．编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的姓名、课程名、分数。

 

5．创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。

 

1．查询学生信息表中的年龄(重复年龄只显示一次)

use 教学成绩管理数据库

select distinct 年龄=datediff(year,出生日期,getdate())

from 学生信息表

2．从学生信息表和教学成绩表中查询查询学生的学号、姓名、课程名和分数

use 学生成绩数据库

select 成绩表.学号,姓名,课程名,分数

from 教学成绩表,学生信息表

where成绩表.学号 = 学生表.学号 AND 成绩表.课程号=课程表.课程号

3.       从课程表中统计第二学期的总学时。

Select课程号, 课程名, 开课学期, 学时

From 课程表

Where开课学期=2

compute avg(学时)

4．编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的姓名、课程名、分数。

CREATE PROCEDURE 成绩1  @xh char (6)

as

select 姓名,课程名称,分数 from 教学成绩表视图

where 学号=@xh

5．创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。

Create Trigger 触发器7  On  学生表

For updata

As

Print “学生姓名已被修改”

Go

现有关系数据库如下：

数据库名：学生成绩数据库

学生表(班级编号，学号，姓名，性别，民族，身份证号，出生日期)

课程表(课程号，课程名)

成绩表(ID,学号，课号，分数)

用SQL语言实现下列功能的sql语句代码：

1．在[学生成绩数据库]的[学生表]中查询年龄为20岁或22岁的学生。

2．在[学生成绩数据库]中查询每个学生的班级编号、学号、姓名、平均分，结果按平均分降序排列，均分相同者按班级排列。

3．编写一个自定义函数，根据[学生表]中的[出生日期]列，计算年龄。

4．创建一个视图[教学成绩表视图]显示学生的学号、姓名、课程名、分数。

5．编写一个存储过程，输入学号，从[教学成绩表视图]显示该学生的姓名、课程名、分数。

6．把[学生表]、[成绩表]通过[学号]建立约束关系。

7．把[学生表]、[成绩表]通过[学号]建立约束关系。

8．在学生表中插入记录：

班级编号  学号      姓名    性别  民族  身份证号            出生日期

050201   050201001  王莉欣  女    汉     53010219790625224  1979-06-25

050202   050202001  张晶    男    NULL  01020319801224121  NULL

把张晶的民族改为“汉”、出生日期改为“1980-12-24”

1．在学生成绩数据库的学生表中查询年龄为20岁或22岁的学生

use 教学成绩管理数据库

select 姓名, 性别, 年龄=datediff(year,出生日期,getdate())

from 学生表

where(datediff(year,出生日期,getdate())=20) or(datediff(year,出生日期,getdate())=22)

 

2．在学生成绩数据库中查询每个学生的班级编号、学号、姓名、平均分，结果按平均分降序排列，均分相同者按班级排列

use 教学成绩管理数据库

select 班级编号,a.学号,a.姓名, avg(分数) 平均分

from 学生表 as ajoin成绩表 as b

on a.学号 = b.学号

group by 班级编号, a.学号,a.姓名

order by avg(分数) desc, 班级编号 asc

 

3．编写一个自定义函数，根据[学生表]中的[出生日期]列，计算年龄。

CREATE FUNCTION dbo.计算年龄(@vardatedatetime,@Curdate  datetime)

RETURNS  tinyint

AS 

BEGIN

   return datediff(yyyy, @vardate, @Curdate)

END

 

4．创建一个视图“教学成绩表视图” 显示学生的学号、姓名、课程名、分数。

CREATEVIEW [教学成绩表视图]

AS

SELECT 学号, 姓名, 课程名, 分数

FROM 学生表, 成绩表

WHERE 学生表.学号=成绩表.学号

 

5．编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的姓名、课程名、分数。

CREATE PROCEDURE [成绩1]  @xh char (6)

as

select 姓名,课程名称,分数 from 教学成绩表视图

where 学号=@xh

 

6．把学生表、成绩表通过“学号”建立约束关系。

alter table 学生表

add constraint  约束1 foreign key (学号) references  成绩表 (学号)

 

7．创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。

Create  Trigger 触发器7  On  学生表

For updata

As

Print '学生姓名已被修改'

Go

 

8．

insert 学生表 values

('050201','050201001','王莉欣','女','汉','53010219790625224',

'1979-06-25')

insert 学生表 values

('050201','050201001','张晶', '男', null,null, null)

update 学生表

set 民族='汉',

    身份证号='01020319800226121',

    出生日期='1980-02-26'

where 姓名='张晶'

 

1、 用SQL语句创建表1和表2。

departments(Depid,Depname,Depnote)

employees(Empid,Empname,Birthdate,Depart,Salary,Position)

表1    departments

字段名

数据类型

说明

Depid

Tinyint

部门编号（主键）

Depname

Char(12)

部门名称

Depnote

Varchar(100)

有关说明

表2 employees

字段名

数据类型

说明

Empid

Char(6)

员工编号（主键）

Empname

Char(20)

员工姓名（非空）

Birthdate

Smalldatetime

出生日期

Depart

Tinyint

所在部门（外键）（非空）

Salary

Float

月薪

Position

Char(8)

职务

 

2、 向表1中添加如下数据

 

Depid

Depname

Denote

1

软件开发部

 

2

系统集成部

 

 

3、 向表2中添加如下数据

 

empid

empname

birthdate

depart

salary

Position

A00001

王晓丽

1970/4/27

2

2400．00

 

A00004

马明

1962/3/14

1

4600．00

副经理

4、 查询所有1970年以后出生的员工的信息。

5、 查询工资高于2000元的员工的信息。

6、 查询系统集成部的所有员工的信息。

7、 统计软件开发部的人均工资。

8、 查询所有员工中工资最高和最低的人。

9、 统计软件开发部的人数。

10、 将所有员工的工资上调10%。

11、 将工资收入低于2500元的员工每人加薪200元。

12、 对所有“岗位”一栏为空的记录，将其“岗位”改为“职员”。

13、 删除年龄大于50岁的员工的信息。

14、删除所有1970年以前出生的员工的信息。

1、 用SQL语句创建表1和表2。

departments(Depid,Depname,Depnote)

employees(Empid,Empname,Birthdate,Depart,Salary,Position)

表1    departments

字段名

数据类型

说明

Depid

Tinyint

部门编号（主键）

Depname

Char(12)

部门名称

Depnote

Varchar(100)

有关说明

表2 employees

字段名

数据类型

说明

Empid

Char(6)

员工编号（主键）

Empname

Char(20)

员工姓名（非空）

Birthdate

Smalldatetime

出生日期

Depart

Tinyint

所在部门（外键）（非空）

Salary

Float

月薪

Position

Char(8)

职务

 

createtable departsment

(

Depid   Tinyint not null primary key,

Depname     Char(12),

Depnote Varchar(100),

)

go

createtable employee

(

Empid   Char(6) not null primary key,

Empname    Char(20) not null,

Birthdate     Smalldatetime

Depart  Tinyint foreign key referencesdepartments(Depdid),

Salary   Float,

Position Char(8)

)

2、 向表1中添加如下数据

Depid

Depname

Denote

1

软件开发部

 

2

系统集成部

 

insertinto departments(Depid,Depname) values('1','软件开发部')

go

insertinto departments(Depid,Depname) values('2','系统集成部')

3、 向表2中添加如下数据

empid

empname

birthdate

depart

salary

Position

A00001

王晓丽

1970/4/27

2

2400．00

 

A00004

马明

1962/3/14

1

4600．00

副经理

 

insert into employeevalues('A00001','王晓丽','1970/4/27',2,2400．00,'')

go

insert into employeevalues('A00004','马明' '1962/3/14',1,4600．00,'副经理')

4、 查询所有1970年以后出生的员工的信息。

Select *  from employee where Birthdate>’1970-01-01’

5、 查询工资高于2000元的员工的信息。

Select *  from employee where Salary>2000

6、 查询系统集成部的所有员工的信息。

Selectemployee.*  fromemployee,departments  where employee.Depart=departments. Depdid  and Depname=’系统集成部’

7、 统计软件开发部的人均工资

select avg(salary)from  employee, departments whereemployee. Depart=departments. Depdid  andDepname=’ 软件开发部’

8、 查询所有员工中工资最高和最低的人。

Select * fromemployee where saraly=(select max(saraly) from employee)

go

Select * fromemployee where saraly=(select min(saraly) from employee)

9、 统计软件开发部的人数。

select count(*)from  employee, departments whereemployee. Depart=departments. Depdid  andDepname=’ 软件开发部’

10、 将所有员工的工资上调10%。

Update  employee set gz=gz*1.2

11、 将工资收入低于2500元的员工每人加薪200元。

Update  employee set gz=gz+200 where saray<2500

12、 对所有“岗位”一栏为空的记录，将其“岗位”改为“职员”。

Update employee set Position=’职员’ where  Position is null

13、 删除年龄大于50岁的员工的信息。

Delete from employeewhere year(getdate())-year(birthdate)>50

14、删除所有1970年以前出生的员工的信息。

  Delete from employee where birthdate<’1970-01-01’