sql基础题目测试及正确答案
use test
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values(‘01‘ , N‘赵雷‘ , ‘1990-01-01‘ , N‘男‘)
insert into Student values(‘02‘ , N‘钱电‘ , ‘1990-12-21‘ , N‘男‘)
insert into Student values(‘03‘ , N‘孙风‘ , ‘1990-05-20‘ , N‘男‘)
insert into Student values(‘04‘ , N‘李云‘ , ‘1990-08-06‘ , N‘男‘)
insert into Student values(‘05‘ , N‘周梅‘ , ‘1991-12-01‘ , N‘女‘)
insert into Student values(‘06‘ , N‘吴兰‘ , ‘1992-03-01‘ , N‘女‘)
insert into Student values(‘07‘ , N‘郑竹‘ , ‘1989-07-01‘ , N‘女‘)
insert into Student values(‘08‘ , N‘王菊‘ , ‘1990-01-20‘ , N‘女‘)
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values(‘01‘ , N‘语文‘ , ‘02‘)
insert into Course values(‘02‘ , N‘数学‘ , ‘01‘)
insert into Course values(‘03‘ , N‘英语‘ , ‘03‘)
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values(‘01‘ , N‘张三‘)
insert into Teacher values(‘02‘ , N‘李四‘)
insert into Teacher values(‘03‘ , N‘王五‘)
create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))
insert into SC values(‘01‘ , ‘01‘ , 80)
insert into SC values(‘01‘ , ‘02‘ , 90)
insert into SC values(‘01‘ , ‘03‘ , 99)
insert into SC values(‘02‘ , ‘01‘ , 70)
insert into SC values(‘02‘ , ‘02‘ , 60)
insert into SC values(‘02‘ , ‘03‘ , 80)
insert into SC values(‘03‘ , ‘01‘ , 80)
insert into SC values(‘03‘ , ‘02‘ , 80)
insert into SC values(‘03‘ , ‘03‘ , 80)
insert into SC values(‘04‘ , ‘01‘ , 50)
insert into SC values(‘04‘ , ‘02‘ , 30)
insert into SC values(‘04‘ , ‘03‘ , 20)
insert into SC values(‘05‘ , ‘01‘ , 76)
insert into SC values(‘05‘ , ‘02‘ , 87)
insert into SC values(‘06‘ , ‘01‘ , 31)
insert into SC values(‘06‘ , ‘03‘ , 34)
insert into SC values(‘07‘ , ‘02‘ , 89)
insert into SC values(‘07‘ , ‘03‘ , 98)
go
注意：有些题目为了方便测试，可以自行修改表中的数据
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select s.* ,a.score as ‘01‘ ,b.score as ‘02‘ from Student s
left join sc a on a.S#=s.S# and a.C#=‘01‘
left join sc b on b.S#=s.S# and b.C#=‘02‘
where a.score>b.score
select a.* ,b.C# , b.score from Student a,SC b where a.S#=b.S#
and a.S# in ( select S# from SC c1 where C#=‘01‘ and score>
( select score from SC c2 where C#=‘02‘ and c2.S#=c1.S# )
);
--1.1、查询同时存在"01"课程和"02"课程的情况
select a.S#,a.Sname,b.score,c.score from Student a
left join sc b on b.s#=a.s# and b.c#=‘01‘
left join sc c on c.s#=a.s# and c.c#=‘02‘
where b.score>0 and c.score>0;
--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)
select a.S#,a.Sname,b.score as ‘01‘,c.score as ‘02‘ from Student a
left join sc b on b.s#=a.s# and b.c#=‘01‘
left join sc c on c.s#=a.s# and c.c#=‘02‘
Where b.score>isnull(c.score,0);
--1.2.1存在01，但是可能不存在02
--这个表示左连接后c#编号为null的也包含进去
select * from student s left join sc a on s.S#=a.S# and a.C#=‘02‘
--这个表示左连接后c#编号必须为‘02‘，不包含null
select * from student s left join sc a on s.S#=a.S# where a.C#=‘02‘
--这两个结起来可以是01必须有，02可以不要
select s.*,a.score as ‘01‘,b.score as ‘02‘ from student s
left join sc a on a.S#=s.S#
left join sc b on b.s#=s.s# and b.C#=‘02‘
where a.C#=‘01‘
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.S#,a.Sname,b.score ,c.score from Student a
left join sc b on b.s#=a.s# and b.c#=’01’
left join sc c on c.s#=a.s# and c.c#=’02’
Where b.score<c.score;
--2.1、查询同时存在"01"课程和"02"课程的情况
select a.S#,a.Sname,b.score as ‘01‘,c.score as ‘02‘ from Student a
inner join sc b on b.s#=a.s# and b.c#=‘01‘
inner join sc c on c.s#=a.s# and c.c#=‘02‘;
--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
select a.S#,a.Sname,b.score as ‘01‘,c.score as ‘02‘ from Student a
left join sc b on b.s#=a.s# and b.c#=‘01‘
left join sc c on c.s#=a.s# and c.c#=‘02‘
Where c.score>isnull(b.score,0);
2.2.1存在01，但是不存在02课程的人
select distinct s.* from student s
left join sc a on a.s#=s.s#
left join sc b on b.s#=s.s#
--"<>"是指不等于的意思
where a.c#=‘02‘ and b.C#<>‘01‘
--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.S# ,s.Sname ,avgs from Student s ,
(select s# ,cast(avg(score) as decimal(18,2)) as avgs from sc group by s# having AVG(score)>=60) c
where c.S#=s.S#
--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select s.S# ,s.Sname ,avgs from Student s ,
(select s# ,cast(avg(score) as decimal(18,2)) as avgs from sc group by s# having AVG(score)<60) c
where c.S#=s.S#
--4.1、查询在sc表存在成绩的学生信息的SQL语句。
select * from Student where s# in (select s# from sc);
--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
select * from Student where s# not in (select s# from sc);
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s# ,s.Sname ,b.c as 课程数 ,b.d as 总分数 from Student s
left join (select s# ,count(c#) as c,sum(score) as d from sc group by s# ) b
on s.s#=b.S#
--5.1、查询所有有成绩的SQL。
select s.* ,sc.C# ,sc.score from Student s ,sc
where s.S#=sc.S#
--5.2、查询所有(包括有成绩和无成绩)的SQL。
select s.* ,sc.C# ,sc.score from Student s left join
sc on s.S#=sc.S#
--6、查询"李"姓老师的数量
select count(1) as 数量 from Teacher t where t.Tname=‘李%‘
--7、查询学过"张三"老师授课的同学的信息
Select s.s#, s.sname from student s,sc where s.s#=sc.s# and
Sc.c# in(select c.c# from course c,teacher t where c.t#=t.t# and t.tname=‘张三‘);
--8、查询没学过"张三"老师授课的同学的信息
Select distinct(s.s#) , s.sname from student s,sc where s.s#=sc.s# and
Sc.c# not in(select c.c# from course c,teacher t where c.t#=t.t# and t.tname=‘张三‘);
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.S#,a.Sname,b.score ,c.score from Student a
inner join sc b on b.s#=a.s# and b.c#=‘01‘
inner join sc c on c.s#=a.s# and c.c#=‘02‘
--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.S#,a.Sname,b.score,c.score from Student a
left join sc b on b.s#=a.s# and b.c#=‘01‘
left join sc c on c.s#=a.s# and c.c#=‘02‘
where c.score is null and b.score>0;
--11、查询没有学全所有课程的同学的信息
select a.S#,a.Sname,b.score,c.score ,d.score from Student a
left join sc b on b.s#=a.s# and b.c#=‘01‘
left join sc c on c.s#=a.s# and c.c#=‘02‘
left join sc d on d.S#=a.S# and d.C#=‘03‘
where c.score is null or b.score is null or d.score is null;
select s.* from Student s
where s# not in (select s# from sc group by s# having count(1)=3)
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct(a.S#),a.Sname from Student a,sc
where a.S#=sc.S# and sc.C#=any(select C# from Student a,sc
where a.S#=sc.S# and a.S#=‘01‘) and sc.S#!=‘01‘;
select s.* from Student s
where s# in
( select s# from sc where c# in (select C# from sc where S#=‘01‘and s#!=‘01‘))
select distinct s.* from student s
left join sc on s.s# = sc.s#
--先将学号为01 的去处掉，然后再将查找有这几门课程的学生
where sc.c# in ( select c# from sc where sc.s#=‘01‘) and sc.s# <> ‘01‘
--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select S# from SC where S# not in
(select distinct(S#) from SC where C# not in (select C# from SC where S#=‘01‘))
group by S# having count(*)=(select count(*) from SC where S#=‘01‘);
select s.* from Student s
where s# in
( select distinct(s#) from sc
where c# in (select C# from sc where S#=‘01‘) and s#!=‘01‘
group by s# having count(1)=3
)
--更全面些
select * from student s
where s# in (
select s# from sc
where c# in (select c# from sc where s#=‘01‘)
group by s# having count(1)=(select count(1) from sc where s#=‘01‘))
--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
Select distinct(s.s#) , s.sname from student s,sc where s.s#=sc.s# and
Sc.c# not in(select c.c# from course c,teacher t where c.t#=t.t# and t.tname=‘张三‘);
--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.S#,a.Sname,avg(sc.score) from Student a,sc
where a.S#=sc.S# and sc.S# in(
select s# from sc where score>=60
group by s# having count(*)>=2 )
group by a.S#,a.Sname;
--16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.S#,a.Sname,sc.score from Student a,sc
where a.S#=sc.S# and sc.score<60 and sc.c#=‘01‘
order by sc.score desc;
--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select *from
(select s.S#, avg(f.score) as avg from Student s,sc f
where s.S#=f.s#
group by s.S#) a left join
(select a.S#,a.Sname,b.score as bs ,c.score as cs ,d.score as ds from Student a
left join sc b on b.s#=a.s# and b.c#=‘01‘
left join sc c on c.s#=a.s# and c.c#=‘02‘
left join sc d on d.S#=a.S# and d.C#=‘03‘)b on a.S#=b.S#;
--用左连接将一个一个数连接起来，然后再显示出来
select s.*,b.score as ‘01‘,c.score as ‘02‘ , d.score as ‘03‘,a.avg_score from student s
--求出平均分，不计算null值
left join (select s# ,avg (score) as avg_score from sc group by s# ) a on s.S# =a.S#
left join sc b on b.S#=s.s# and b.c#=‘01‘
left join sc c on c.s#=s.s# and c.c#=‘02‘
left join sc d on d.s#= s.s# and d.c#=‘03‘
order by a.avg_score desc
--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select m.C# [课程编号], m.Cname [课程名称],
max(n.score) [最高分],
min(n.score) [最低分],
cast(avg(n.score) as decimal(18,2)) [平均分]--,
,cast((select count(1) from SC where C# = m.C# and score >= 60)*100.00 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) as 合格率
,cast((select count(1) from SC where C# = m.C# and score >= 70 and score < 80 )*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) as 中等率
,cast((select count(1) from SC where C# = m.C# and score >= 80 and score < 90 )*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) as 优良率
,cast((select count(1) from SC where C# = m.C# and score >= 90)*100.00/ (select count(1) from SC where C# = m.C#) as decimal(18,2)) as 优秀率
from Course m , SC n
where m.C# = n.C#
group by m.C# , m.Cname
--19、按各科成绩进行排序，并显示排名
select c#,a.Sname ,score ,rank() over(partition by c# order by score) as ‘排名‘ from sc,student a
where a.S#=sc.S#;
--20、查询学生的总成绩并进行排名
select sc.s#,a.Sname,sum(sc.score) as ‘总成绩‘ from sc,Student a
where sc.S#=a.S#
group by sc.s#,a.Sname order by sum(sc.score)desc;
--20.1 查询学生的总成绩
select sc.s#,a.Sname,sum(sc.score) as ‘总成绩‘ from sc,Student a
where sc.S#=a.S#
group by sc.s#,a.Sname;
--20.2 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。
--20.3 查询学生的总成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select sc.s#,a.Sname,sum(sc.score) as ‘sscore‘, rank() over(order by sum(sc.score))
from sc,Student a
where sc.S#=a.S#
group by sc.s#,a.Sname
select sc.s#,a.Sname,sum(sc.score) as ‘sscore‘, dense_rank() over(order by sum(sc.score))
from sc,Student a
where sc.S#=a.S#
group by sc.s#,a.Sname
--21、查询不同老师所教不同课程平均分从高到低显示
select a.C#,a.Cname,b.Tname ,avg(sc.score) as ‘avg‘ from sc,Course a,Teacher b
where a.T#=b.T# and sc.C#=a.C#
group by a.C#,a.Cname,b.Tname
order by avg(score) desc
--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select *from (
select c#,a.Sname ,score ,DENSE_RANK() over(partition by c# order by score) as ‘srank‘ from sc,student a
where a.S#=sc.S#) a
where a.srank between 2 and 3;
--23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select t1.*,round(t1.num1*100/t2.num,2) as ‘百分比‘ from
( select s.C#,c.Cname,
(case when s.score>=85 then ‘100-85‘
when s.score>=70 and s.score<85 then ‘85-70‘
when s.score>=60 and s.score<70 then ‘70-60‘
else ‘0-60‘
end) as px,count(1) as num1
from sc s,Course c
where c.C#=s.C#
group by s.C#,c.Cname,(case when s.score>=85 then ‘100-85‘
when s.score>=70 and s.score<85 then ‘85-70‘
when s.score>=60 and s.score<70 then ‘70-60‘
else ‘0-60‘
end)) t1,
(select s.C#,c.Cname,count(1)as num from sc s,Course c
where s.C#=c.C#
group by s.C#,c.Cname
) t2
where t1.C#=t2.C#;
select
s.c#,c.cname,(select count(1) from sc where C#=s.c# and score between 85 and 100) as [85-100],
(select count(1) from sc where c# = s.c# and score between 70 and 85) as [70-85],
(select count(1) from sc where c# = s.c# and score between 60 and 70) as [60-70],
(select count(1) from sc where c# = s.c# and score<60) as [0-60],
cast(cast((select count(1) from sc where c# = s.c# and score between 85 and 100) as decimal(18,2))*100/
cast((select count(1) from sc where c# = s.c#) as decimal(18,2)) as decimal(18,2)) as[85-100百分比],
cast(cast((select count(1) from sc where c# = s.c# and score between 70 and 85) as decimal(18,2))*100/
cast((select count(1) from sc where c# = s.c#) as decimal(18,2)) as decimal(18,2)) as[70-85百分比],
cast(cast((select count(1) from sc where c# = s.c# and score between 60 and 70) as decimal(18,2))*100/
cast((select count(1) from sc where c# = s.c#) as decimal(18,2)) as decimal(18,2)) as[60-70百分比],
cast(cast((select count(1) from sc where c# = s.c# and score<60) as decimal(18,2))*100/
cast((select count(1) from sc where c# = s.c#) as decimal(18,2)) as decimal(18,2)) as[0-60百分比]
from sc s inner join course c on c.c#=s.c#
group by s.c#,c.cname
--23.1 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]
select s.C#,c.Cname,
( case when s.score>=85 then ‘100-85‘
when s.score>=70 and s.score<85 then ‘85-70‘
when s.score>=60 and s.score<70 then ‘70-60‘
else ‘0-60‘
end
) as px,count(1) as num from sc s,Course c
where s.C#=c.C#
group by s.C#,c.Cname, ( case when s.score>=85 then ‘100-85‘
when s.score>=70 and s.score<85 then ‘85-70‘
when s.score>=60 and s.score<70 then ‘70-60‘
else ‘0-60‘
end
)
order by s.C#;
--23.2 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[<60]及所占百分比
select t1.*,round(t1.num1*100/t2.num,2) as ‘百分比‘ from
( select s.C#,c.Cname,
(case when s.score>=85 then ‘100-85‘
when s.score>=70 and s.score<85 then ‘85-70‘
when s.score>=60 and s.score<70 then ‘70-60‘
else ‘0-60‘
end) as px,count(1) as num1
from sc s,Course c
where c.C#=s.C#
group by s.C#,c.Cname,(case when s.score>=85 then ‘100-85‘
when s.score>=70 and s.score<85 then ‘85-70‘
when s.score>=60 and s.score<70 then ‘70-60‘
else ‘0-60‘
end)) t1,
(select s.C#,c.Cname,count(1)as num from sc s,Course c
where s.C#=c.C#
group by s.C#,c.Cname
) t2
where t1.C#=t2.C#;
--简化版
select s.c#,c.cname,
(case when s.score between 85 and 100 then ‘85-100‘
when s.score between 70 and 85 then ‘70-85‘
when s.score between 60 and 70 then ‘60-70‘
else ‘0-60‘ end) as fshu,count(1) as num,
cast(cast(count(1)*100 as decimal(18,2))
/cast((select count(1) from sc where c#=s.c#) as decimal(18,2))as decimal(18,2)) as ‘百分比‘
from sc s
inner join course c on s.C# = c.C#
group by s.c#,c.cname,
(case when s.score between 85 and 100 then ‘85-100‘
when s.score between 70 and 85 then ‘70-85‘
when s.score between 60 and 70 then ‘60-70‘
else ‘0-60‘ end)
--24、查询学生平均成绩及其名次
select sc.S#,s.Sname,cast(avg(sc.score) as decimal(18,2)) as 平均分,rank() over(order by avg(sc.score)) as 排名 from sc,Student s
where sc.S#=s.S#
group by sc.S#,s.Sname;
select s.s#,s.sname,convert(decimal(18,2),avg(sc.score)) as [分数],
rank() over(order by avg(sc.score)desc ) as 排名 from student s
left join sc on s.s#=sc.s#
group by s.s#,s.sname
--24.1 查询学生的平均成绩并进行排名，sql 2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
--24.2 查询学生的平均成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select sc.S#,s.Sname,cast(avg(sc.score) as decimal(18,2)) as 平均分, isnull (rank() over(order by avg(sc.score)),null) as 排名 from sc,Student s
where sc.S#=s.S#
group by sc.S#,s.Sname;
select sc.S#,s.Sname,cast(avg(sc.score) as decimal(18,2)) as 平均分,rank() over(order by avg(sc.score)) as 排名 from sc,Student s
where sc.S#=s.S#
group by sc.S#,s.Sname;
--不保留名次空缺
select s.s#,s.sname,convert(decimal(18,2),avg(sc.score)) as [平均成绩],
dense_rank() over(order by avg(sc.score) desc) as [排名] from student s
left join sc on s.s#=sc.s#
group by s.s#,s.sname
--25、查询各科成绩前三名的记录
--25.1 分数重复时保留名次空缺 ???????????????????????????????
select sc.c# ,sc.s# ,s.Sname , sc.score ,rank() over(partition by sc.c# order by sc.score desc)
from sc ,Student s
where sc.S#=s.S#
--25.2 分数重复时不保留名次空缺，合并名次
select * from(
select c.c#,c.cname,sc.score,dense_rank() over(partition by c.c# order by sc.score desc) as [排名]
from sc left join course c on c.c#=sc.c#
left join student s on sc.s#=s.s# ) t
where t.排名<4
--26、查询每门课程被选修的学生数
select c.C#,c.Cname,count(1) as 数量
from Course c,sc
where c.C#=sc.C#
group by c.C#,c.Cname;
--27、查询出只有两门课程的全部学生的学号和姓名
select s.S#,s.Sname,count(1) as 课程数 from Student s,sc
where s.S#=sc.S#
group by s.S#,s.Sname
having count(1)=2
--28、查询男生、女生人数
select Ssex, count(1) from Student
group by Ssex;
--29、查询名字中含有"风"字的学生信息
select *from student
where sname like ‘%风%‘;
--30、查询同名同性学生名单，并统计同名人数
select Sname, count(1) as 数量 from Student
group by Sname
having COUNT(1)>1;
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from Student where year(sage)=1990;
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c#, cast(avg(score ) as decimal(18,2)) as avgscore from sc
group by c#
order by avgscore desc,C#
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.S# ,s.Sname ,cast(AVG(sc.score) as decimal(18,2)) as avgscore from Student s, sc
where s.S#=sc.S#
group by s.S#,s.Sname
having avg(score)>=85
--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.Sname ,sc.score from Student s ,Course c ,sc
where s.S#=sc.S# and c.C#=sc.C# and sc.score<60 and c.Cname=‘数学‘;
--35、查询所有学生的课程及分数情况；
select s.Sname ,c.Cname ,sc.score from Student s ,Course c ,sc
where s.S#=sc.S# and sc.C#=c.C#
order by score
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select s.S# ,s.Sname ,c.Cname ,sc.score from sc ,Course c ,Student s
where sc.S#=s.S# and c.C#=sc.C# and s.S# in (
select s# from sc a where score>70 group by s#
having count(1)=(select count(1) from sc where s#=a.S# group by s#))
--37、查询不及格的课程
select s.S# ,s.Sname ,c.Cname ,sc.score from sc ,Course c ,Student s
where sc.S#=s.S# and c.C#=sc.C# and sc.score<60
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select s.S# ,s.Sname ,sc.score from Student s ,sc
where s.S#=sc.S# and sc.C#=‘01‘ and sc.score>=80
--39、求每门课程的学生人数
select sc.C# ,count(1) as 人数 from sc
group by sc.C#
--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select sc.C# ,s.* ,sc.score from Student s ,sc ,(
select sc.C# ,max(sc.score) as maxscore from Teacher t ,Course c ,sc
where t.T#=c.T# and t.Tname=‘张三‘ and sc.C#=c.C#
group by sc.C# ) t
where s.S#=sc.S# and t.C#=sc.C# and sc.score=t.maxscore
order by sc.C#
--将所有的表连接起来，排列出该老所教课程的所有学生的分数，根据分数从高到低进行排序
--取第一条数据，便是成绩最高的学生了
select top 1 s.s#, s.sname, sc.score from student s inner join sc on s.s#=sc.s#
inner join course c on c.c#=sc.c#
inner join teacher t on c.t#=t.t#
where t.tname=‘张三‘
order by sc.score desc
--40.1 当最高分只有一个时
select s.* ,sc.score from student s ,sc ,
(select sc.C#,t.maxscore ,count(1) as aount from sc , (
select sc.C# ,max(sc.score) as maxscore from Teacher t ,Course c ,sc
where t.T#=c.T# and t.Tname=‘张三‘ and sc.C#=c.C#
group by sc.C# ) t
where t.C#=sc.C# and sc.score=t.maxscore
group by sc.C# ,t.maxscore
having count(1)<2 ) a
where s.S#=sc.S# and a.C#=sc.C# and a.maxscore=sc.score
--将所有的表连接起来，排列出该老所教课程的所有学生的分数，根据分数从高到低进行排序
--取第一条数据，便是成绩最高的学生了
select top 1 s.s#, s.sname, sc.score from student s inner join sc on s.s#=sc.s#
inner join course c on c.c#=sc.c#
inner join teacher t on c.t#=t.t#
where t.tname=‘张三‘
order by sc.score desc
--40.2 当最高分出现多个时
select sc.C# ,s.* ,sc.score from student s ,sc ,
(select sc.C#,t.maxscore ,count(1) as aount from sc , (
select sc.C# ,max(sc.score) as maxscore from Teacher t ,Course c ,sc
where t.T#=c.T# and t.Tname=‘张三‘ and sc.C#=c.C#
group by sc.C# ) t --算出每一门科目的最高分及科目号
where t.C#=sc.C# and sc.score=t.maxscore
group by sc.C# ,t.maxscore
having count(1)>1 ) a --算出多人得分的科目及最高分
where s.S#=sc.S# and a.C#=sc.C# and a.maxscore=sc.score --根据科目号和最高分匹配学生
--先将最高分和课程取出来，然后再将将表进行连接,group by
select t.s#,t.sname,sc.score from student t inner join sc on t.S#=sc.S#
inner join
(
select c.c#, max(sc.score) as maxscore from sc inner join course c on c.C#=sc.C#
inner join teacher t on t.t#=c.t#
where t.tname=‘张三‘
group by c.C#
)a on a.C#=sc.C# and a.maxscore = sc.score
--先将最高分和课程取出来，然后再将将表进行连接,order by
select t.s#,t.sname,sc.score from student t inner join sc on t.S#=sc.S#
inner join
(
select top 1 sc.c#, sc.score as maxscore from sc inner join course c on c.C#=sc.C#
inner join teacher t on t.t#=c.t#
where t.tname=‘张三‘
order by sc.score desc
)a on a.C#=sc.C# and a.maxscore = sc.score
--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
--查询某个学生的课程不同，但是成绩同的情况
select s.* ,a.score as ‘01‘ ,b.score as ‘02‘,c.score as ‘03‘ from Student s
left join sc a on s.S#=a.S# and a.C#=‘01‘
left join sc b on s.S#=b.S# and b.C#=‘02‘
left join sc c on s.S#=c.S# and c.C#=‘03‘
where a.score=b.score or a.score=c.score or b.score=c.score
--42、查询每门功成绩最好的前两名
select * from (
select sc.C# ,s.Sname , sc.score ,
rank() over( partition by sc.c# order by sc.score desc) as scorank
from sc,Student s
where s.S#=sc.S# ) t
where t.scorank<3;
--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c# ,count(1) as acount from sc
group by c#
having count(1)>5
order by acount desc ,C#
--44、检索至少选修两门课程的学生学号
select s# as 学生学号,count(1) as 课程数 from sc
group by s#
having count(1)>1
--45、查询选修了全部课程的学生信息
select sc.s# ,count(1) as 课程数 from sc
group by sc.S#
having count(1)=(select count(1) from Course )
select distinct(s.S#), s.* from sc,Student s
where sc.S#=s.S# and s.S#=(
select sc.s# as 课程数 from sc
where sc.S#=s.S#
group by sc.S#
having count(1)=(select count(1) from Course ) )
--46、查询各学生的年龄
select s# ,sname ,( year(GETDATE())-year(Sage)) as 年龄 ,Ssex from Student
--46.1 只按照年份来算
select s# ,sname ,( year(GETDATE())-year(Sage)) as 年龄 ,Ssex from Student
或
select s# ,sname ,DATEDIFF(yy ,sage ,getdate()) as 年龄 from student
--46.2 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
--dateadd(month,2,getdate())--日期相加 ：比如getdate是2017-12-27 ，显示是：2018-02-27
--datediff(month,sage, getdate()) --日期相减
- 日期指定的部分：月month(这年的第几个月) ，年year, 天打day(这个月的第几天)
- 开始的日期
- 结束的日期
--datename(week,getdate()) --返回日期的指定部分，放回该日期的第几个星期，nvarchar
注释：这个返回的是这个年的第几个星期
--datepart(week,getdate()) --同上，结果返回int型
--convert(varchar(10),getdate(), 120) --以varchar的形式显示前10个字符，显示格式是120
即：2017-12-27
--cast(getdate() as varchar(10)) --与上相同，但是没有显示格式，所以显示出来不好看
select s#, sname,
case when month(getdate())<month(sage) then datediff(year,sage,getdate())-1
when month(getdate())>month(sage) then datediff(year,sage,getdate())
else (case when day(getdate())<=day(sage) then datediff(year,sage,getdate())-1
else datediff(year,sage,getdate())
end)
end
from student
--47、查询本周过生日的学生
--datename(week,getdate()) --返回日期的指定部分，放回该日期的第几个星期，nvarchar
注释：这个返回的是这个年的第几个星期
--datepart(week,getdate()) --同上，结果返回int型
本周的星期一
- SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--***wk == week ,从系统指定时间一共过了多少个礼拜
--***datediff只会显示数量，不会显示日期
--显示系统判定时间到现在一共有多少个礼拜了
select datediff(wk,0,getdate())--wk代表星期，getdate()代表现在日期
--***dateadd( [指定格式(wk,day,year)] , [要加入的数量]，[从哪天开始加 (getdate())] )
--***dateadd只会显示日期，不会显示数量
--这周星期一的日期，从日期是0开始加入礼拜数量
select dateadd(wk,datediff(wk,0,getdate()),0)
select day(dateadd(wk,datediff(wk,0,getdate()),0))--这天是几号
--这周星期日的日期
select dateadd(wk,datediff(wk,0,getdate())+1,0)-1
--计算是这周过生日的
--流程：1、计算这周的第一天和最后一天，取这两天的几月几号，下面都是这种情况
--2、判断是否存在跨年的，即判断第一天和最后一天（12-25,1-1)的大小，可能存在2017-12-25到2018-01-01这种情况
--如果第一天比最后一天小，则生日就是大于第一天，小于最后一天，否则生日大于最后一天，小于第一天。
select s# , sname,convert(varchar(10),sage,120) as [生日] from student
where convert(varchar(5),sage,110) >=
case when convert(varchar(5),dateadd(wk,datediff(wk,0,getdate()),0),110) < convert(varchar(5),dateadd(wk,datediff(wk,0,getdate())+1,0)-1,110)
then convert(varchar(5),dateadd(wk,datediff(wk,0,getdate()),0),110)
else convert(varchar(5),dateadd(wk,datediff(wk,0,getdate())+1,0)-1,110)
end
and convert(varchar(5),sage,110) <=
case when convert(varchar(5),dateadd(wk,datediff(wk,0,getdate()),0),110) > convert(varchar(5),dateadd(wk,datediff(wk,0,getdate())+1,0)-1,110)
then convert(varchar(5),dateadd(wk,datediff(wk,0,getdate()),0),110)
else convert(varchar(5),dateadd(wk,datediff(wk,0,getdate())+1,0)-1,110)
end
--48、查询下周过生日的学生
select s.* from Student s
where datename(week,day(sage))=DateName(week,day(Getdate()))+1
--这周是月底怎么搞，还有这周是指第1-7天 为第一周，所以有问题
--49、查询本月过生日的学生
select s.* from Student s
where month(sage)=month(getdate())
--50、查询下月过生日的学生
select s.* from Student s
where month(sage)=month(getdate())+1
但是这个月是12月怎么去算
所以，修改下
--**dateadd加上一个月的日期，就能跳到下个月了，然后下个月的月数
select s#,sname, convert(varchar(10), sage, 120) as [生日] from student
where month(sage) = month(dateadd(month , 1, getdate()))
--用case...when 函数，当这个月为12月时就将他改为1
select s# , sname, convert(varchar(10) ,sage, 120) from student s
where datepart(month,sage) =
case when datepart(month,getdate()) = 12 then 1
else datepart(month,getdate())+1 end
1、以下纯取值，不关联表了
- 月初
--datediff从初始到现在一共有多少个月，然后将这些月数相加换成日期
select dateadd(month,datediff(month,0,getdate()),0);
- 月末
--datediff从初始日期到现在一共有多少个月，然后+1就多一个月，最后—1就是下个月的月初变为月末
select dateadd(month,datediff(month, 0, getdate())+1,0)-1
- 下个月的月初
select dateadd(month,datediff(month,0,getdate())+1,0)
- 下个月的月末
select dateadd(month,datediff(month, 0, getdate())+2,0)-1
sql基础题目测试及正确答案
标签：sel com value table mon mic arc 同时存在 into
小编还为您整理了以下内容，可能对您也有帮助：
SQL数据库（简单）简答题
Microsoft SQL Server 2000 简答题及其答案
1．在安装SQL Server 2000时，“仅客户端工具”、“服务器和客户端工具”及“仅连接”等安装定义有什么差别？
答：仅客户端工具：仅安装客户端工具。服务器和客户端工具：同时安装服务器和客户端工具。仅连接：仅安装客户端连接。
2．一个数据库中包含哪几种文件？
答：主数据库文件，辅助数据文件，事务日志文件。
3．事务文件和数据文件分开存放有什么好处？
答：因为利用事务日志备份可以将数据库恢复到特定的即时点(如输入不想要的数据之前的那一点)或故障发生点。在媒体恢复策略中应考虑利用事务日志备份。
4．什么是批处理？使用批处理有何？
答：包含一个或多个T-SQL语句的组，从应用程序将一次性发送到SQL Server执行，SQL Server批处理语句作为整体编译成一个可执行单元。
：如不能够在同一个批处理中更改表，然后引用新列。如在一个批处理程序中，有两条语句。第一条语句用来在一张表中插入一个字段，如在User表中插入员工的出生年月;第二条语句则用来引用这个列，如根据身份证号码来推算出这个员工的出身年月并把这个值赋值给这个第一条语句创建的出身年月字段。
5．数据检索时，COMPUTE和COMPUTE BY产生的结果有何不同？
答：Compute:第一个为明细行 第二个为聚合函数的合计 Compute by:第一个为明细行第二个为聚合函数的小计
6．什么是索引？索引分为哪两种？各有什么特点？
答：1、数据库中的索引与书籍中的目录类似。在一本书中，利用目录可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。 2、聚集索引和非聚集索引。
3、聚集索引的特点：行的物理位置和行在索引中的位置是相同的，每个表只能有一个聚集索引，聚集索引的平均大小大约为表大小的5%左右。
非聚集索引特点：若未指定索引类型，则默认为非聚集索引，叶节点页的次序和表的物理存储次序不同，每个表最多可以有249个非聚集索引，在非聚集索引创建之前创建聚集索引。
7．创建索引有什么优点和缺点？
答：优点：索引在数据库中的作用类似于目录在书籍中的作用，用来提高查找信息的速度。
使用索引查找数据，无需对整表进行扫描，可以快速找到所需数据。
缺点：索引需要占用数据表以外的物理存储空间。创建索引和维护索引要花费一定的时间。当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。
8．什么是视图？使用视图的优点和缺点是什么？
答：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
优点：1隐蔽数据库的复杂性2控制用户提取数据3简化数据库用户管理 4简化查询
缺点：1性能：SQL Server必须把视图的查询转化成对基本表的查询，如果这个视图是由，一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，SQL Server也，把它变成一个复杂的结合体，需要花费一定的时间。2修改：当用户试图修改视图的某些行时，SQL Server必须把它转化为对基本，表的某些行的修改。对于简单视图来说，这是很方便的，但是，对于比较复杂的，视图，可能是不可修改的。
9. 修改视图中的数据会受到哪些？
答：1、必须有权限 2、单表视图直接改
3、多表可在视图中定义 instead of 触发器.
4、对由多表连接成的视图修改数据时，不能同时影响一个以上的基表，也不能删除视图中的数据
5、对视图上的某些列不能修改，这些列是:计算列，内置函数列和行集合函数列
6、对具有NOT NULL 的列进行修改时可能会出错。通过视图修改或者插入数据时，必须保证未显示的具有NOT NULL 属性的列有值，可以是默认值，IDENTITY 等，否则不能像视图中插入数据行。
7、如果某些列因为规则或者约束的不能接受从视图插入的数据时，则插入数据可能会失败
8、删除基表并不删除视图，建议采用与表明显不同的名字命名规则
10. 什么是数据完整性？如果数据库不实施数据完整性会产生什么结果？
答：数据完整性是指数据的精确性和可靠性。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
11．数据完整性有哪几类？如何实施？
答：1、数据完整性分为四类：实体完整性、域完整性、参照完整性、用户定义的完整性。
2、数据库采用多种方法来保证数据完整性，包括外键、束约、规则和触发器。系统很好地处理了这四者的关系，并针对不同的具体情况用不同的方法进行，相互交叉使用，相补缺点。
12. 什么是主键约束？什么是惟一约束？两者有什么区别？
答：主键约束：有一列或列的组合，其值能唯一的标识表中的每一行。
唯一约束：不允许数据库表在指定列上具有相同的值，但允许有空值。
区别：一个表可以定义多个唯一约束，只能定义一个主键约束；空值得列上可以定义唯一约束，不能定义主键约束。
13．什么是外键，怎样建立外键约束？
答：外键是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。
14．什么是存储过程？使用存储过程有什么好处？
答：1、是存储在服务器上的 Transact-SQL 语句的命名集合，是封装重复性任务的方法，支持用户声明变量、条件执行以及其他强有力的编程特性。
2、存储过程封装了商务逻辑，确保一致的数据访问和修改。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用
屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象
提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限
改善性能。预编译的 Transact-SQL 语句，可以根据条件决定执行哪一部分
减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包
15．什么是触发器？触发器分有什么作用？
答：触发器是一类特殊的存储过程，不能被直接调用，也不传递或接受参数
作用：1、维护不可能在表创建时刻通过声明性约束进行的复杂的完整性约束。
2、通过记录所进行的修改以及谁进行了修改来审计表中的信息。
3、当表被修改的时候，自动给其他需要执行操作的程序发信号。
SQL经典50题题解
本篇文章主要是对SQL经典50题进行详细解析。
解析包含：1、解题思路，2、考核知识点，3、答案；
首先，表结构用脑图输出出来，如下所示：
先进行数据准备，建表以及插入数据。
1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
解题思路：
第一步：关键词有“课程编号”、“课程成绩”、“学生信息”，锁定使用表：学生表、成绩表。
第二步：给出学生信息及课程分数，通过主键sid关联学生表和课程表。
第三步：比较同一个学生不同课程的成绩，再关联一次课程表，利用sid、cid进行关联。
第四步：根据题目，用where比较分数筛选结果。
考核知识点： join，where
答案：
1.1 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
解题思路：
第一步：关键词有“课程编号”、“学生编号”，锁定使用表：成绩表。
第二步：分别查询出存在" 01 "课程的学生和存在" 02 "课程的学生。
第三步：对两个子查询进行关联，用sid进行左联接。
考核知识点： where, 子查询，left join
答案：
1.2 查询同时存在01和02课程的情况
解题思路： 同1.1，把left join改为join
考核知识点： where, 子查询，join
答案：
1.3 查询选择了02课程但没有01课程的情况
解题思路： 类似1.1，把left join改为right join
考核知识点： where, 子查询，right join
答案：
小结： 上面的题主要考察join、left join、right join。
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
解题思路：
第一步：关键词有“平均成绩”、“学生编号”、“学生姓名”，锁定使用表：成绩表、学生表。
第二步：给出学生信息及课程分数，通过主键sid关联学生表和课程表。。
第三步：根据题目，用group by聚合计算出平均成绩，然后筛选出大于等于60分的学生。
考核知识点： join、group by、avg()
答案：
3.查询在 SC 表存在成绩的学生信息
解题思路：
第一步：关键词有“SC”、“学生信息”，锁定使用表：成绩表、学生表。
第二步：用EXISTS判断在SC表存在成绩的学生信息
考核知识点： EXISTS语句
答案：
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
解题思路：
第一步：关键词有“学生编号”、“学生姓名”、“选课总数”、“课程成绩”，锁定使用表：成绩表、学生表。
第二步：通过主键sid关联学生表成绩表，得到学生信息、学生成绩的宽表
第三步：根据题目，用group by聚合计算选课总数和总成绩
考核知识点： left join、group by、count()、sum()、ifnull()
答案：
5.查询「李」姓老师的数量
解题思路：
第一步：关键词有“老师的数量”，锁定使用表：教师表。
第二步：先筛选出「李」姓老师，再汇总统计「李」姓老师的数量
考核知识点： like、where、%、count()
答案：
6.查询学过「张三」老师授课的同学的信息
解题思路：
第一步：关键词有“老师”、“学生信息”，锁定使用表：教师表、学生表、成绩表、课程表。
第二步：通过sid关联学生表、成绩表，再通过cid关联课程表，最后通过tid关联教师表。
第三步：用where筛选出「张三」老师授课的同学的信息。
考核知识点： 多重连接join
答案：
7.查询没有学全所有课程的同学的信息。
解题思路：
第一步：关键词有“课程”、“学生信息”，锁定使用表：学生表、成绩表、课程表。
第二步：先统计学生的课程数量，再筛选出小于所有课程数量的学生。
考核知识点： left join、group by、count()
答案：
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息。
解题思路：
第一步：关键词有“课程”、“学生信息”，锁定使用表：学生表、成绩表。
第二步：先查询学号“01”的同学学习的课程。
第三步：通过sid关联学生表和成绩表，获取所有学生信息、课程信息。
第四步：用EXISTS筛选出至少1门课相同的同学信息。
考核知识点： left join、子查询、EXISTS
答案：
9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
解题思路：
第一步：关键词有“课程”、“学生信息”，锁定使用表：学生表、成绩表。
第二步：用“01”号的同学学习的课程左关联学生课程表，筛选出关链课程数一致的其他同学的sid
第三步：通过sid关联学生表和成绩表，获取完整的学生信息。
考核知识点： left join、子查询、group by
答案：
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
解题思路：
第一步：关键词有“老师”、“学生姓名”，锁定使用表：学生表、成绩表、课程表、教师表。
第二步：反向求解。先查询至少学过“张三”老师讲授的课程的学生sid
第三步：用NOT EXISTS筛选出不在第二步查询结果的学生信息,。
考核知识点： 多重连接join、NOT EXISTS、子查询
答案：
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
解题思路：
第一步：关键词有“姓名”、“平均成绩”，锁定使用表：学生表、成绩表。
第二步：通过sid关联学生表和成绩表，得到学生成绩信息宽表
第三步：用group by聚合统计，having对聚合的结果进行筛选。
考核知识点： join、group by、having、case when语句、avg()
答案：
12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
解题思路：
第一步：关键词有“课程分数”、“学生信息”，锁定使用表：学生表、成绩表。
第二步：通过sid关联学生表和成绩表，得到学生成绩信息宽表
第三步：用where筛选" 01 "课程分数小于 60的记录，并按照分数降序排列。
考核知识点： join、where、order by
答案：
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
解题思路：
第一步：关键词有“平均成绩”，锁定使用表：成绩表。
第二步：用group by将学生课程的成绩由行转换为列并计算平均成绩。
第三步：按照平均成绩降序显示学生的所有课程的成绩以及平均成绩。
考核知识点： join
答案：
14.查询各科成绩最高分、最低分和平均分,以如下形式显示：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，
优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
解题思路：
第一步：关键词有“课程name”、“最高分”，锁定使用表：课程表、成绩表。
第二步：用group by聚合计算课程最高分、最低分、平均分。
第三步：用case语句判断及格、中等、优良、优秀，并结合group by计算。
考核知识点： join、group by、max()、min()、avg()、sum()、case when语句
答案：
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
解题思路：
第一步：关键词有“各科成绩”，锁定使用表：成绩表。
第二步：用rank()排名。
考核知识点： rank() over(partition by)
答案：
15.1 按各科成绩进行行排序，并显示排名， Score 重复时合并名次
解题思路：
第一步：关键词有“各科成绩”，锁定使用表：成绩表。
第二步：用dense_rank()排名。
考核知识点： dense_rank() over(partition by)
答案：
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
解题思路：
第一步：关键词有“总成绩”，锁定使用表：成绩表。
第二步：用group by统计学生的总成绩。
第三步：用left join自关联进行排名。
考核知识点： group by、 left join
答案：
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
解题思路：
第一步：关键词有“总成绩”，锁定使用表：成绩表。
第二步：用group by统计学生的总成绩。
第三步：用变量进行排名。
考核知识点： group by、变量
答案：
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
解题思路：
第一步：关键词有“各科成绩”、“课程名称”，锁定使用表：成绩表、课程表。
第二步：通过cid关联课程表和成绩表，得到课程、成绩信息宽表。
第三步：用group by聚合统计各分段的人数及百分比。
考核知识点： join、group by、case when条件语句
答案：
18.查询各科成绩前三名的记录
解题思路：
第一步：关键词有“各科成绩”，锁定使用表：成绩表。
第二步：筛选出各科比当前成绩高的人数小于3的学生记为各科的前三名。
考核知识点： 子查询
答案：
19.查询每门课程被选修的学生数
解题思路：
第一步：关键词有“每门课程”、“学生数”，锁定使用表：课程表、成绩表。
第二步：用left join关联课程表和成绩表，再用group by分组汇总各科的学生数。
考核知识点： left join、group by
答案：
20.查询出只选修两门课程的学生学号和姓名
解题思路：
第一步：关键词有“选修课程”、“学生姓名”，锁定使用表：学生表、成绩表。
第二步：用join关联学生表和成绩表，再用group by分组汇总每个学生的选修课程数，最后用having对分组汇总结果筛选出选修两门课程的学生。
考核知识点： join、group by、having
答案：
21. 查询男生、女生人数
解题思路：
第一步：关键词有“男生、女生”，锁定使用表：学生表。
第二步：通过ssex学生表用group by分组汇总男生、女生人数。
考核知识点： group by
答案：
22. 查询名字中含有「风」字的学生信息
解题思路：
第一步：关键词有“学生信息”，锁定使用表：学生表。
第二步：用like匹配姓名中含有风」字的学生。
考核知识点： like、%
答案：
23查询同名同性学生名单，并统计同名人数
解题思路：
第一步：关键词有“学生名单”，锁定使用表：学生表。
第二步：使用group by，汇总同名同性人数，再用having筛选出大于1的记录
考核知识点： group by、having
答案：
24.查询 1990 年出生的学生名单
解题思路：
第一步：关键词有“学生名单”，锁定使用表：学生表。
第二步：用where筛选出1990年出生的学生名单
考核知识点： where、year
答案：
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编
号升序排列。
解题思路：
第一步：关键词有“平均成绩”，锁定使用表：成绩表。
第二步：用group by分组计算各科平均成绩，再用order by完成多列排序
考核知识点： group by、order by
答案：
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
解题思路：
第一步：关键词有“平均成绩”、“学生姓名”，锁定使用表：成绩表、学生表。
第二步：用join关联学生表和成绩表
第三步：用group by分组汇总计算每个学生的平均成绩，再用having筛选平均成绩>=85的记录
考核知识点： join、group by、having
答案：
27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
解题思路：
第一步：关键词有“课程名称”、“分数”、“学生姓名”，锁定使用表：课程表、成绩表、学生表。
第二步：用join关联学生表、成绩表、课程表，再用where筛选
考核知识点： 多重join、where
答案：
28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
解题思路：
第一步：关键词有“所有学生”、“分数”，锁定使用表：学生表、成绩表
第二步：用left join关联学生表、成绩表
考核知识点： left join
答案：
29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
解题思路：
第一步：关键词有“课程成绩”、“姓名”、“课程名称”，锁定使用表：学生表、成绩表、课程表
第二步：用join关联学生表、成绩表、课程表，再筛选出课程成绩在70分以上的。
考核知识点： 多重join
答案：
30.查询不及格的课程
解题思路：
第一步：关键词有“不及格的课程”，锁定使用表：成绩表、课程表
第二步：关联课程表和成绩表，再条件筛选出不及格的课程信息。
考核知识点： join、where、去重
答案：
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
解题思路：
第一步：关键词有“课程编号”、“课程成绩”、“姓名”，锁定使用表：成绩表、学生表
第二步：关联成绩表和学生表，再条件筛选出结果。
考核知识点： join、where
答案：
32.求每门课程的学生人数
解题思路：
第一步：关键词有“课程”、“学生人数”，锁定使用表：成绩表
第二步：用group by分组汇总各科的学生人数。
考核知识点： group by
答案：
33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
解题思路：
第一步：关键词有“成绩”、“「张三」老师”、“学生信息”，锁定使用表：成绩表、课程表、学生表、教师表
第二步：关联所有表，筛选出选修「张三」老师所授课程的学生。
第三步：因为成绩不重复，对学生成绩由高到低排序，筛选出第一行记录。
考核知识点： 多重join、order by、limit
答案：
34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生
信息及其成绩
解题思路：
第一步：关键词有“成绩”、“「张三」老师”、“学生信息”，锁定使用表：成绩表、课程表、学生表、教师表
第二步：关联所有表，筛选出选修「张三」老师所授课程的学生。
第三步：因为成绩有重复，先求出最高成绩，再匹配最高成绩对应的学生信息。
考核知识点： 多重join、max()
答案：
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
解题思路：
第一步：关键词有“成绩”，锁定使用表：成绩表
第二步：自联接，筛选出不同课程成绩相同的记录
考核知识点： 自联接join
答案：
36. 查询每门成绩最好的前两名
解题思路：
第一步：关键词有“成绩”，锁定使用表：成绩表
第二步：自联接，筛选出各科低于自身成绩的人数为2的。
考核知识点： left join
答案：
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
解题思路：
第一步：关键词有“选修人数”，锁定使用表：成绩表
第二步：先用group by分组汇总各科的选修人数，再条件筛选出超过5人的课程。
考核知识点： group by、having
答案：
38.检索至少选修两门课程的学生学号
解题思路：
第一步：关键词有“两门课程”，锁定使用表：成绩表
第二步：先用group by分组汇总每个学生的选修课程数，再用having筛选出至少2门课程的学生学号
考核知识点： group by、having
答案：
39.查询选修了全部课程的学生信息
解题思路：
第一步：关键词有“全部课程”、“学生信息”，锁定使用表：成绩表、课程表、学生表
第二步：关联学生表和成绩表，再用group by分组统计每个学生的选修课程数
第三步：最后用having筛选出等于全部课程数的学生信息。
考核知识点： join、 group by、having、子查询
答案：
40.查询各学生的年龄，只按年份来算
解题思路：
第一步：关键词有“学生的年龄”，锁定使用表：学生表
第二步：用year和now来统计
考核知识点： year、now
答案：
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
解题思路：
第一步：关键词有“出生日期”，锁定使用表：学生表
第二步：用timestampdiff()统计年龄
考核知识点： timestampdiff()
答案：
42.查询本周过生日的学生
解题思路：
第一步：关键词有“过生日”，锁定使用表：学生表
第二步：用week函数
考核知识点： week()
答案：
43. 查询下周过生日的学生
解题思路：
第一步：关键词有“过生日”，锁定使用表：学生表
第二步：用week函数
考核知识点： week()
答案：
44.查询本月过生日的学生
解题思路：
第一步：关键词有“过生日”，锁定使用表：学生表
第二步：用month函数
考核知识点： month()
答案：
45.查询下月过生日的学生
解题思路：
第一步：关键词有“过生日”，锁定使用表：学生表
第二步：用month函数
考核知识点： month()
答案：
sql语句相关测试
sql语句相关测试
篇一：SQL语句测试
1、（10分）要求:
选择受理时间在2008-5-1 到 2008-6-1之间的所有申请人姓氏为“刘”的数据，并把“新受理编号”列表示成当前机器时间的年月和原受理编号年月后的数值组合
格式如下：
受理编号，新受理编号，受理时间， 申请人
200801112 201008112 2008-01-13刘XX
需要的表
I_Optinst 业务实例表
REGDATE（受理日期）
Regnum(受理编号)
Proposer（申请人）
解答：select Regnum as 受理编号, as 新受理编号,REGDATE as 受理时间,Proposer as申请人 from Optinst 2、（15分）要求：
前提：只统计业务小类“存量房买卖”
①按照月份分12月列出2008年每个月份的月份对应月份的交易总面积
②按照月份分12月列出2008年每个月份的月份对应月份的交易均价（申报价格/建筑面积）
格式
年度月份 交易总面积 年度月份交易均价（元/平方米）
2008-01 23232 2008-01 2323
2008-02 23232008-02 232
2008-03 232323 2008-03 7656
2008-04 232323 2008-03 565
2008-05 232323 2008-03 5656
2008-06 232323 2008-03 565
2008-07 232323 2008-03 67
2008-08 232323 2008-03 676
2008-09 232323 2008-03 6767
2008-10 232323 2008-03 8686
2008-11 232323 2008-03 867
2008-12 232323 2008-03 454
需要的表：
Fc_room 房间表
BAREA（建筑面积）
I_Optinst业务实例表
regdate（受理时间）
fc_owner 产权表
COSTVAL(申报价格)
EVLVAL（评估价格）
fc_owoom 房间明细表
1.select regdate as年度月份,BAREA as 交易总面积 from Fc_room,Optinst where
3、（20分）要求：
①：按照时间统计收费明细 统计格式如下
受理编号缴费人收费日期 收费名称收费金额核费人收费人
②：按照时间汇总（2008年度）统计收费项目分类 统计格式如下
收费名称 总金额
需要的表:
I_OptInst（业务实例表）
Regnum 受理编号
Proposer 申请人
I_Charge（收费实例表）
HEFEIMAN 核费人
PAYEE 收款人
CDATE 收费日期
I_ChrDtl（收费实例明细表
CNAME 收费名称
MONEY 收费金额
4、（15分）要求：用途是住宅并且建筑面积<=140>140 定义为 “非普通住宅”
用途是商业并且建筑面积>140 定义为 “商业*”
其他情况定义为“非住宅”
根据用途和面积列表出所有数据
格式
受理编号， 用途
200406000386 普通住宅
200406004631 非普通住宅
200406004633 普通住宅
200406004638 普通住宅
200406004641 非住宅
200501000004 普通住宅
200406004568 非住宅
200406005677 商业*
表：
fc_room
barea 建筑面积
BUse 用途
i_optinst
regnum 受理编号
5、（30分）工作量统计
① 选择出以下格式的数据；并创建视图名称为view_AAAA
业务小类业务实例 交易价格建筑面积 登记时间
1 存量房买卖 14100 19400.0029.98 2005-11-10 11:32:50 2 新建商品房 15041 229530.00 124.07 2005-11-21 08:59:36 3 新建商品房 15043 177363.00 101.35 2005-11-21 09:15:59 4 新建商品房 13046 71130.0023.71 2005-11-02 10:15:37 5 新建商品房 11280 148563.00 87.39 2005-10-11 09:50:48 6 新建商品房 11353 267369.00 116.04 2005-10-11 15:34:53 7 房改售房 2689 35.22 2004-06-17 08:43:00 8 产权人名称变更 11701 724.18 2005-10-17 10:05:20 9 新建商品房 7206 158257.00 88.69 2005-09-16 14:50:57 10 存量房买卖 (转 载于:wWw.cnboThwiN.cOM 博 威范文 网:sql语句测试)10100 103.07 2005-08-31 20:27:06 11 存量房买卖 12980 51500.0046.66 2005-11-01 14:41:32 12 新建商品房 13000 136782.00 80.46 2005-11-01 15:37:05 13 新建商品房 16946 300844.00 146.33 2005-12-15 14:15:07 14 存量房买卖 10091 509.18 2005-08-31 19:19:25 ② 使用视图 view_AAAA 当做表 选择出如下样式数据
业务小类 件数合计金额 合计面积
1 用途变更 1151.3
2 转移登记 184.03
3 新建商品房 31 263243643197.34
4 房改售房 8252.43
5 产权人名称变更 3 778.6
6 单位产新建 311697.49
7 赠与 1 28.48
8 存量房买卖 24 4379004134.67
9 判决仲裁 2 439.41
10 继承遗赠 1 49.17
11 换证 2 228.88
12 新建房屋 17 928.91
③ 用水晶报表关联视图 view_AAAA 设计出类似于②的数据格式 需要的表
FC_Owner
EvlVal交易价格
I_OptInst,
SOName 业务小类
fc_owoom,
BArea 建筑面积
FC_Room
regdate 受理日期
篇二：sql查询语句学习测试答案
第一部分SQL查询语句的学习
单表查询 1、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值
use eee
SELECT 订购日期,订单ID,客户ID,
雇员ID
FROM 订单
WHERE 订购日期BETWEEN '1996-7-1 00:00:00' AND '1996-7-15 23:59:59'
2、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。
use eee
SELECT 供应商ID,公司名称,地区,城市,电话
FROM 供应商
WHERE 地区='华北' AND 联系人职务='销售代表'
3、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津 use eee
SELECT 供应商ID,公司名称,地区,城市,电话
FROM 供应商
WHERE 地区IN('华东', '华南') OR 城市='天津'
4、--查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值
use eee
SELECT 供应商ID,公司名称,地区,城市,电话
FROM 供应商
WHERE 地区IN('华东', '华南')
多表查询 5、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值，并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列
use eee
SELECT 订购日期,订单ID,公司名称,姓氏,名字
FROM 订单,雇员,客户
WHERE 订购日期BETWEEN '1996-7-1 00:00:00' AND '1996-7-15 23:59:59'
AND 订单.雇员ID = 雇员.雇员ID
AND 订单.客户ID = 客户.客户ID
ORDER BY 姓氏,名字ASC,订单ID DESC
6、--查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称
use eee
SELECT 订单.订单ID,公司名称,产品名称
FROM 订单,运货商,产品,订单明细
WHERE 订单.订单ID IN('10248','10254')
AND 订单.订单ID = 订单明细.订单ID
AND 订单明细.产品ID = 产品.产品ID
AND
订单.运货商= 运货商.运货商ID
7、--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣
use eee
SELECT 订单.订单ID,产品名称,数量,订单明细.单价,折扣
FROM 订单,产品,订单明细
WHERE 订单.订单ID IN('10248','10254')
AND 订单.订单ID = 订单明细.订单ID
AND 订单明细.产品ID = 产品.产品ID
篇三：sql语句练习题及答案
一 在数据库 school 中建立student , sc, course 表。
学生表、课程表、选课表属于数据库 School ，其各自的数据结构如下：
学生 Student (Sno,Sname,Ssex,Sage,Sdept)
课程表 course(Cno,Cname,Cpno,Ccredit)
学生选课 SC(Sno,Cno,Grade)
二 设定主码
1 Student表的主码：sno2 Course表的主码：cno 3 Sc表的主码：sno，cno
1写出使用 Create Table 语句创建表 student , sc, course 的SQL语句
2.3 删除student表中的元组
4在数据库school中删除关系student
5在student表添加属性sbirthdate 类型 datetime
Delete
1 删除所有 JSJ 系的男生 from Student where Sdept=’JSJ’ and Ssex=’男’; 2 删除“数据库原理”的课的选课纪录
from SC where Cno in (select Cno fromCourse where Cname=’数据库原理’);
Update
1 修改 0001 学生的系科为: JSJ
2 把陈小明的年龄加1岁，性别改为女。 2 修改李文庆的1001课程的成绩为 93 分 3 把“数据库原理”课的成绩减去1分
Select 查询语句
一 单表
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。 2查询姓名中第2个字为“明”字的学生学号、性别。 3查询 1001课程没有成绩的学生学号、课程号
4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名，结果按系排列 5按10分制查询学生的sno,cno,10分制成绩
（1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10） 6查询 student 表中的学生共分布在那几个系中。（distinct） 7查询0001号学生1001，1002课程的成绩。
二 统计
1查询姓名中有“明”字的学生人数。 2计算‘JSJ’系的平均年龄及最大年龄。 3查询学生中姓名为张明、赵英的人数
4计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 5 计算 1001,1002 课程的'平均分。
6 查询平均分大于80分的学生学号及平均分 7 统计选修课程超过 2 门的学生学号
8 统计有10位成绩大于85分以上的课程号。 9 统计平均分不及格的学生学号
10 统计有大于两门课不及格的学生学号
三 连接
1查询 JSJ 系的学生选修的课程号
2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法） 3查询数据库原理不及格的学生学号及成绩
4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法） 5查询平均分不及格的学生的学号，姓名,平均分。 6查询女学生平均分高于75分的学生姓名。
7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能
四 嵌套、相关及其他
1 查询平均分不及格的学生人数
2 查询没有选修1002 课程的学生的学生姓名
3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all） *4 查询没有选修1001，1002课程的学生姓名。
5 查询1002课程第一名的学生学号（2种方法） 6 查询平均分前三名的学生学号
7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集
8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 9 查询每门课程成绩都高于该门课程平均分的学生学号 10 查询大于本系科平均年龄的学生姓名
答案
参*
1 create table student (sno6), sname var8), ssex2), sagesmallint, sdept var15), primary key(sno));
create table sc
(sno6), cno 4),
grade decimal(12,2), primary key(sno,cno));
into student
values( ’4001’,’赵茵’,’男’,20,’SX’)
from student
student
alter table student add sbirthdate datetime
1 select sno, sname, sage from student
where ssex=’女’ and sage between 19 and 21order by sage desc; 2 select sno, ssexfrom student
where sname like ’_明% ’ ; 3 select sno, cnofrom sc
where grade is null and cno=’1001’ ; 4 select sno, sname from student
where sdept in (’JSJ’,’SX’,’WL’) and sage>25 group by sdept;
select sno, cno, grade/10.0+1 as levelfrom sc ;
select distinct sdept from student ; select grade from sc
where sno=’0001’ and (cno=’1001’ or cno=’1002’) ;
select count(*) from student where sname like ’%明% ’ ; select avg(sage),max(sage) from student where sdept=’JSJ’ ; select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno
order by avg(grade) desc ;
select cno, avg(grade) from sc where cno in(‘1001’,’1002’) group by cno ;
select sc.sno ,avg(grade) from scgroup by sc.sno
having avg(grade)>80 ;
select sno from sc group by sno having count(*)>2 ;
select cno from sc where grade>85 group by cno having count(*)=10 ; select sno from sc group by sno having avg(grade)<60 ;
select sno from sc where grade<60 group="" by="" sno="" having="">2 ;
select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’ ; a:select sname from student,sc where student.sno=sc.sno and cno=’1002’
b:select sname from student where sno in (select sno from sc where cno=’1002’)
select sno,grade from sc,course
where sc.cno=course.cno and cname=’数据库原理’ and grade<60 a:select sname from student ,sc,course
where student.sno=sc.sno and sc.cno=course.cno and grade>80 and cname=’ 数据库原理’ b:select sname from student where sno in (select sno from sc where grade>80 and cno in (select cno from course where cname=’ 数据库原理’)) select sno,sname,avg(grade) from sc,studentwhere student.sno=sc.snogroup by student.sno having avg(grade)<60
a:select sname from student where ssex=’女’ and sno in(select sno from sc group by sno having avg(grade)>75)
b:select sname from sc,student where student.sno=sc.sno and ssex=’女’group by student.sno having avg(grade)>75
select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno and ssex=’男’
select count(*) from student where sno in( select sno from sc group by sno havingavg(grade)<60)
select sname from student where sno not in（select sno from sc where cno=’1002’）
student
0001 aaX 0002 bb
0003 ccX Sc
0001 1001 0001 1002 0002 1001 0003 1002
Select sname from student where not exists(select* from sc where cno=’1002’ and sc.sno=student.sno)
a:select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc b:select sno, avg(grade) from sc group by sno
having avg(grade)=(select top 1 avg(grade) from scgroup by sno order by avg(grade) desc) c:select sno, avg(grade) from sc group by sno
having avg(grade)>=all ( select avg(grade) from sc group by sno)
select sname from student where not exists(
select * from course where cno in(‘1001’,’1002’) and
not exists(select * from sc where sno =student.sno and cno=course.cno) ) a:select top 1 sno from sc cno=’1002’ order by grade desc b:select sno from sc where cno=’1002’ and grade >=all (
;sql语句相关测试
sql语句相关测试
篇一：SQL语句测试
1、（10分）要求:
选择受理时间在2008-5-1 到 2008-6-1之间的所有申请人姓氏为“刘”的数据，并把“新受理编号”列表示成当前机器时间的年月和原受理编号年月后的数值组合
格式如下：
受理编号，新受理编号，受理时间， 申请人
200801112 201008112 2008-01-13刘XX
需要的表
I_Optinst 业务实例表
REGDATE（受理日期）
Regnum(受理编号)
Proposer（申请人）
解答：select Regnum as 受理编号, as 新受理编号,REGDATE as 受理时间,Proposer as申请人 from Optinst 2、（15分）要求：
前提：只统计业务小类“存量房买卖”
①按照月份分12月列出2008年每个月份的月份对应月份的交易总面积
②按照月份分12月列出2008年每个月份的月份对应月份的交易均价（申报价格/建筑面积）
格式
年度月份 交易总面积 年度月份交易均价（元/平方米）
2008-01 23232 2008-01 2323
2008-02 23232008-02 232
2008-03 232323 2008-03 7656
2008-04 232323 2008-03 565
2008-05 232323 2008-03 5656
2008-06 232323 2008-03 565
2008-07 232323 2008-03 67
2008-08 232323 2008-03 676
2008-09 232323 2008-03 6767
2008-10 232323 2008-03 8686
2008-11 232323 2008-03 867
2008-12 232323 2008-03 454
需要的表：
Fc_room 房间表
BAREA（建筑面积）
I_Optinst业务实例表
regdate（受理时间）
fc_owner 产权表
COSTVAL(申报价格)
EVLVAL（评估价格）
fc_owoom 房间明细表
1.select regdate as年度月份,BAREA as 交易总面积 from Fc_room,Optinst where
3、（20分）要求：
①：按照时间统计收费明细 统计格式如下
受理编号缴费人收费日期 收费名称收费金额核费人收费人
②：按照时间汇总（2008年度）统计收费项目分类 统计格式如下
收费名称 总金额
需要的表:
I_OptInst（业务实例表）
Regnum 受理编号
Proposer 申请人
I_Charge（收费实例表）
HEFEIMAN 核费人
PAYEE 收款人
CDATE 收费日期
I_ChrDtl（收费实例明细表
CNAME 收费名称
MONEY 收费金额
4、（15分）要求：用途是住宅并且建筑面积<=140>140 定义为 “非普通住宅”
用途是商业并且建筑面积>140 定义为 “商业*”
其他情况定义为“非住宅”
根据用途和面积列表出所有数据
格式
受理编号， 用途
200406000386 普通住宅
200406004631 非普通住宅
200406004633 普通住宅
200406004638 普通住宅
200406004641 非住宅
200501000004 普通住宅
200406004568 非住宅
200406005677 商业*
表：
fc_room
barea 建筑面积
BUse 用途
i_optinst
regnum 受理编号
5、（30分）工作量统计
① 选择出以下格式的数据；并创建视图名称为view_AAAA
业务小类业务实例 交易价格建筑面积 登记时间
1 存量房买卖 14100 19400.0029.98 2005-11-10 11:32:50 2 新建商品房 15041 229530.00 124.07 2005-11-21 08:59:36 3 新建商品房 15043 177363.00 101.35 2005-11-21 09:15:59 4 新建商品房 13046 71130.0023.71 2005-11-02 10:15:37 5 新建商品房 11280 148563.00 87.39 2005-10-11 09:50:48 6 新建商品房 11353 267369.00 116.04 2005-10-11 15:34:53 7 房改售房 2689 35.22 2004-06-17 08:43:00 8 产权人名称变更 11701 724.18 2005-10-17 10:05:20 9 新建商品房 7206 158257.00 88.69 2005-09-16 14:50:57 10 存量房买卖 (转 载于:wWw.cnboThwiN.cOM 博 威范文 网:sql语句测试)10100 103.07 2005-08-31 20:27:06 11 存量房买卖 12980 51500.0046.66 2005-11-01 14:41:32 12 新建商品房 13000 136782.00 80.46 2005-11-01 15:37:05 13 新建商品房 16946 300844.00 146.33 2005-12-15 14:15:07 14 存量房买卖 10091 509.18 2005-08-31 19:19:25 ② 使用视图 view_AAAA 当做表 选择出如下样式数据
业务小类 件数合计金额 合计面积
1 用途变更 1151.3
2 转移登记 184.03
3 新建商品房 31 263243643197.34
4 房改售房 8252.43
5 产权人名称变更 3 778.6
6 单位产新建 311697.49
7 赠与 1 28.48
8 存量房买卖 24 4379004134.67
9 判决仲裁 2 439.41
10 继承遗赠 1 49.17
11 换证 2 228.88
12 新建房屋 17 928.91
③ 用水晶报表关联视图 view_AAAA 设计出类似于②的数据格式 需要的表
FC_Owner
EvlVal交易价格
I_OptInst,
SOName 业务小类
fc_owoom,
BArea 建筑面积
FC_Room
regdate 受理日期
篇二：sql查询语句学习测试答案
第一部分SQL查询语句的学习
单表查询 1、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值
use eee
SELECT 订购日期,订单ID,客户ID,
雇员ID
FROM 订单
WHERE 订购日期BETWEEN '1996-7-1 00:00:00' AND '1996-7-15 23:59:59'
2、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。
use eee
SELECT 供应商ID,公司名称,地区,城市,电话
FROM 供应商
WHERE 地区='华北' AND 联系人职务='销售代表'
3、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津 use eee
SELECT 供应商ID,公司名称,地区,城市,电话
FROM 供应商
WHERE 地区IN('华东', '华南') OR 城市='天津'
4、--查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值
use eee
SELECT 供应商ID,公司名称,地区,城市,电话
FROM 供应商
WHERE 地区IN('华东', '华南')
多表查询 5、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值，并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列
use eee
SELECT 订购日期,订单ID,公司名称,姓氏,名字
FROM 订单,雇员,客户
WHERE 订购日期BETWEEN '1996-7-1 00:00:00' AND '1996-7-15 23:59:59'
AND 订单.雇员ID = 雇员.雇员ID
AND 订单.客户ID = 客户.客户ID
ORDER BY 姓氏,名字ASC,订单ID DESC
6、--查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称
use eee
SELECT 订单.订单ID,公司名称,产品名称
FROM 订单,运货商,产品,订单明细
WHERE 订单.订单ID IN('10248','10254')
AND 订单.订单ID = 订单明细.订单ID
AND 订单明细.产品ID = 产品.产品ID
AND
订单.运货商= 运货商.运货商ID
7、--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣
use eee
SELECT 订单.订单ID,产品名称,数量,订单明细.单价,折扣
FROM 订单,产品,订单明细
WHERE 订单.订单ID IN('10248','10254')
AND 订单.订单ID = 订单明细.订单ID
AND 订单明细.产品ID = 产品.产品ID
篇三：sql语句练习题及答案
一 在数据库 school 中建立student , sc, course 表。
学生表、课程表、选课表属于数据库 School ，其各自的数据结构如下：
学生 Student (Sno,Sname,Ssex,Sage,Sdept)
课程表 course(Cno,Cname,Cpno,Ccredit)
学生选课 SC(Sno,Cno,Grade)
二 设定主码
1 Student表的主码：sno2 Course表的主码：cno 3 Sc表的主码：sno，cno
1写出使用 Create Table 语句创建表 student , sc, course 的SQL语句
2.3 删除student表中的元组
4在数据库school中删除关系student
5在student表添加属性sbirthdate 类型 datetime
Delete
1 删除所有 JSJ 系的男生 from Student where Sdept=’JSJ’ and Ssex=’男’; 2 删除“数据库原理”的课的选课纪录
from SC where Cno in (select Cno fromCourse where Cname=’数据库原理’);
Update
1 修改 0001 学生的系科为: JSJ
2 把陈小明的年龄加1岁，性别改为女。 2 修改李文庆的1001课程的成绩为 93 分 3 把“数据库原理”课的成绩减去1分
Select 查询语句
一 单表
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。 2查询姓名中第2个字为“明”字的学生学号、性别。 3查询 1001课程没有成绩的学生学号、课程号
4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名，结果按系排列 5按10分制查询学生的sno,cno,10分制成绩
（1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10） 6查询 student 表中的学生共分布在那几个系中。（distinct） 7查询0001号学生1001，1002课程的成绩。
二 统计
1查询姓名中有“明”字的学生人数。 2计算‘JSJ’系的平均年龄及最大年龄。 3查询学生中姓名为张明、赵英的人数
4计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 5 计算 1001,1002 课程的'平均分。
6 查询平均分大于80分的学生学号及平均分 7 统计选修课程超过 2 门的学生学号
8 统计有10位成绩大于85分以上的课程号。 9 统计平均分不及格的学生学号
10 统计有大于两门课不及格的学生学号
三 连接
1查询 JSJ 系的学生选修的课程号
2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法） 3查询数据库原理不及格的学生学号及成绩
4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法） 5查询平均分不及格的学生的学号，姓名,平均分。 6查询女学生平均分高于75分的学生姓名。
7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能
四 嵌套、相关及其他
1 查询平均分不及格的学生人数
2 查询没有选修1002 课程的学生的学生姓名
3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all） *4 查询没有选修1001，1002课程的学生姓名。
5 查询1002课程第一名的学生学号（2种方法） 6 查询平均分前三名的学生学号
7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集
8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 9 查询每门课程成绩都高于该门课程平均分的学生学号 10 查询大于本系科平均年龄的学生姓名
答案
参*
1 create table student (sno6), sname var8), ssex2), sagesmallint, sdept var15), primary key(sno));
create table sc
(sno6), cno 4),
grade decimal(12,2), primary key(sno,cno));
into student
values( ’4001’,’赵茵’,’男’,20,’SX’)
from student
student
alter table student add sbirthdate datetime
1 select sno, sname, sage from student
where ssex=’女’ and sage between 19 and 21order by sage desc; 2 select sno, ssexfrom student
where sname like ’_明% ’ ; 3 select sno, cnofrom sc
where grade is null and cno=’1001’ ; 4 select sno, sname from student
where sdept in (’JSJ’,’SX’,’WL’) and sage>25 group by sdept;
select sno, cno, grade/10.0+1 as levelfrom sc ;
select distinct sdept from student ; select grade from sc
where sno=’0001’ and (cno=’1001’ or cno=’1002’) ;
select count(*) from student where sname like ’%明% ’ ; select avg(sage),max(sage) from student where sdept=’JSJ’ ; select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno
order by avg(grade) desc ;
select cno, avg(grade) from sc where cno in(‘1001’,’1002’) group by cno ;
select sc.sno ,avg(grade) from scgroup by sc.sno
having avg(grade)>80 ;
select sno from sc group by sno having count(*)>2 ;
select cno from sc where grade>85 group by cno having count(*)=10 ; select sno from sc group by sno having avg(grade)<60 ;
select sno from sc where grade<60 group="" by="" sno="" having="">2 ;
select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’ ; a:select sname from student,sc where student.sno=sc.sno and cno=’1002’
b:select sname from student where sno in (select sno from sc where cno=’1002’)
select sno,grade from sc,course
where sc.cno=course.cno and cname=’数据库原理’ and grade<60 a:select sname from student ,sc,course
where student.sno=sc.sno and sc.cno=course.cno and grade>80 and cname=’ 数据库原理’ b:select sname from student where sno in (select sno from sc where grade>80 and cno in (select cno from course where cname=’ 数据库原理’)) select sno,sname,avg(grade) from sc,studentwhere student.sno=sc.snogroup by student.sno having avg(grade)<60
a:select sname from student where ssex=’女’ and sno in(select sno from sc group by sno having avg(grade)>75)
b:select sname from sc,student where student.sno=sc.sno and ssex=’女’group by student.sno having avg(grade)>75
select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno and ssex=’男’
select count(*) from student where sno in( select sno from sc group by sno havingavg(grade)<60)
select sname from student where sno not in（select sno from sc where cno=’1002’）
student
0001 aaX 0002 bb
0003 ccX Sc
0001 1001 0001 1002 0002 1001 0003 1002
Select sname from student where not exists(select* from sc where cno=’1002’ and sc.sno=student.sno)
a:select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc b:select sno, avg(grade) from sc group by sno
having avg(grade)=(select top 1 avg(grade) from scgroup by sno order by avg(grade) desc) c:select sno, avg(grade) from sc group by sno
having avg(grade)>=all ( select avg(grade) from sc group by sno)
select sname from student where not exists(
select * from course where cno in(‘1001’,’1002’) and
not exists(select * from sc where sno =student.sno and cno=course.cno) ) a:select top 1 sno from sc cno=’1002’ order by grade desc b:select sno from sc where cno=’1002’ and grade >=all (
;数据库SQ习题
最佳答案：
习题
一、选择题
1．关于查询语句中ORDER BY子句使用正确的是( D )。
A．如果未指定排序字段，则默认按递增排序
B．表的字段都可用于排序
C．如果在SELECT子句中使用了DISTINCT关键字，则排序字段必须出现在查询结果中
D．联合查询不允许使用ORDER BY子句
第2～4题使用7．7节“示例分析”中数据表。
2．使用查询语句：
SELECT STUDENT.学号,STUDENT.姓名,SUM(分数)
FROM STUDENT,GRADE
WHERE STUDENT.学号=GRADE.学号
GROUP BY STUDENT.学号,STUDENT.姓名
查询结果是(A )。
A．按学号分类的每个学生所有课程成绩的总分 B．按学号分类的每个学生各课程成绩
C．全体学生的按各课程分类的成绩总分 D．所有学生所有课程成绩总分
3．使用查询语句：
SELECT MAX(分数) AS 最高分
FROM STUDENT,curriculum,GRADE
WHERE STUDENT.学号=GRADE.学号
AND curriculum.课程编号=GRADE.课程编号
AND 课程名称='数据库技术及应用'
查询的结果是( )。
A．87 B．91 C．82 D．90
很遗憾！不知道表中的记录数据，无法判断是哪个选项，但是题意是检索出：选修课程'数据库技术及应用'的学生中成绩最高的。看一下表就知道了。
4．查询选修了课程编号为“0002”的学生的学号和姓名，以下( A )语句是错误的。
A．
SELECT 学号,姓名FROM STUDENT
WHERE 学号=(SELECT 学号FROM GRADE WHERE 课程编号='0002')
B．
SELECT STUDENT.学号,STUDENT.姓名
FROM STUDENT,GRADE
WHERE STUDENT.学号=GRADE.学号AND 课程编号='0002'
C．
SELECT STUDENT.学号，STUDENT.姓名
FROM STUDENT JOIN GRADE ON STUDENT.学号=GRADE.学号
WHERE GRADE.课程编号='0002'
D．
SELECT 学号,姓名FROM STUDENT
WHERE 学号IN (SELECT 学号FROM GRADE WHERE 课程编号='0002')
5．下列关于查询结果错误的是( D )。
A．查询结果可以显示在表格中
B．查询结果可以按文本方式显示
C．以文本和表格显示的查询结果在保存时，其文件格式不同
D．不管以哪种方式查看，查询结果都会显示在查询结果窗口中
6．在T-SQL语句中，与表达式“仓库号NOT IN ('wh1','wh2')”功能相同的表达式是( D )。
A．仓库号='wh1' AND 仓库号='wh2' B．仓库号!='wh1' OR 仓库号# 'wh2'
C．仓库号<>'wh1' OR 仓库号!='wh2' D．仓库号!='wh1' AND 仓库号!='wh2'
7．在T-SQL 的SELECT语句中用于实现关系的选择运算的短语是( C )。
A．FOR B．WHILE C．WHERE D．CONDITION
8．使用SQL语句进行分组检索时，为了去掉不满足条件的分组，应当( B )。
A．使用WHERE子句 B．在GROUP BY后面使用HAVING子句
C．先使用WHERE子句，再使用HAVING子句 D．先使用HAVING子句，再使用WHERE子句
二、填空题
1．在查询语句中，应在__SELECT____子句中指定输出字段。
2．如果要使用SELECT语句返回指定条数的记录，则应使用_TOP__关键字来限定输出字段。
3．当一个子SELECT的结果作为查询的条件，即在一个SELECT命令的WHERE子句中出现另一个SELECT命令，这种查询称为__嵌套____查询。
4．连接查询可分为3种类型：_内连接___、__外连接__和交叉连接。
5．若要把查询结果存放到一个新建的表中，可使用__into tabale 或into dbf____子句。
三、判断题
1．在关系数据库SQL Server中，用于检索数据的语句是T-SQL的定义语言。 (× )
2．逻辑运算符(AND、NOT、OR)的运算顺序是AND→OR→NOT。 ( × )
3．用于WHERE子句的查询条件表达式可用的比较运算符为：=(等于)、!=或<>(不等于)、>(大于)、>=(大于等于)、<(小于)、<=(小于等于)。 ( √ )
4．SELECT语句的DISTINCT参数表示输出无重复结果的记录。 ( √ )
5．如果要使SELECT的查询结果有序输出，需要用GROUP BY子句配合。 ( × )
几道sql题求答案
1，select * from 表名 order by 姓名
2，select * from 表面 where 课程=“数据结构” and 姓名 like “%李%”
3，select max(成绩），min(成绩)，avg(成绩) from 表名 group by 成绩
4,select into 男学生表 from 表名 where 性别=“男”
5,select * from 表名 where 性别=“女” and 年龄>20
6,create uniwue clustered index i_name on 表名（s_name）
7,create nonclustered index i_score on 表名(score)
8,create view v_nopass as select 学号，姓名，科目，成绩 where 成绩<60