SQL查询练习及答案

问题及描述：

--1.学生表

Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表

Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号

--3.教师表

Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名

--4.成绩表

SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数

*/

--创建测试数据

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"课程成绩高的学生的信息及课程分数

--1.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score > c.score

--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)

select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a left join SC b on a.S# = b.S# and b.C# = '01' left join SC c on a.S# = c.S# and c.C# = '02' where b.score > isnull(c.score,0)

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

--2.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score < c.score

--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a left join SC b on a.S# = b.S# and b.C# = '01' left join SC c on a.S# = c.S# and c.C# = '02' where isnull(b.score,0) < c.score

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , sc b where a.S# = b.S# group by a.S# , a.Sname having cast(avg(b.score) as decimal(18,2)) >= 60 order by a.S#

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

--4.1、查询在sc表存在成绩的学生信息的SQL语句。

select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , sc b where a.S# = b.S# group by a.S# , a.Sname having cast(avg(b.score) as decimal(18,2)) < 60 order by a.S#

--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。

select a.S# , a.Sname , isnull(cast(avg(b.score) as decimal(18,2)),0) avg_score from Student a left join sc b on a.S# = b.S# group by a.S# , a.Sname having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60 order by a.S#

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

--5.1、查询所有有成绩的SQL。

select a.S# [学生编号], a.Sname [学生姓名], count(b.C#) 选课总数, sum(score) [所有课程的总成绩] from Student a , SC b where a.S# = b.S# group by a.S#,a.Sname order by a.S# --5.2、查询所有(包括有成绩和无成绩)的SQL。

select a.S# [学生编号], a.Sname [学生姓名], count(b.C#) 选课总数, sum(score) [所有课程的总成绩] from Student a left join SC b on a.S# = b.S# group by a.S#,a.Sname order by a.S# --6、查询"李"姓老师的数量

--方法1

select count(Tname) ["李"姓老师的数量] from Teacher where Tname like N'李%'

--方法2

select count(Tname) ["李"姓老师的数量] from Teacher where left(Tname,1) = N'李' /* "李"姓老师的数量----------- 1 */

--7、查询学过"张三"老师授课的同学的信息

select distinct Student.* from Student , SC , Course , Teacher where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三' order by Student.S# --8、查询没学过"张三"老师授课的同学的信息

select m.* from Student m where S# not in (select distinct SC.S# from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三') order by m.S#

--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

--方法1

select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#

--方法2

select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '02' and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '01') order by Student.S#

--方法3

select m.* from Student m where S# in ( select S# from ( select distinct S# from SC where C# = '01' union all select distinct S# from SC where C# = '02' ) t group by S# having count(1) = 2 ) order by m.S#

--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

--方法1

select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and not exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#

--方法2 select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and Student.S# not in (Select SC_2.S# from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#

--11、查询没有学全所有课程的同学的信息

--11.1、

select Student.* from Student , SC where Student.S# = SC.S# group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) < (select count(C#) from Course) --11.2

select Student.* from Student left join SC on Student.S# = SC.S# group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) < (select count(C#) from Course) --12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct Student.* from Student , SC where Student.S# = SC.S# and SC.C# in (select C# from SC where S# = '01') and Student.S# <> '01'

--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select Student.* from Student where S# in (select distinct SC.S# from SC where S# <> '01' and SC.C# in (select distinct C# from SC where S# = '01') group by SC.S# having count(1) = (select count(1) from SC where S#='01'))

--14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select student.* from student where student.S# not in (select distinct sc.S# from sc , course ,

teacher where sc.C# = course.C# and course.T# = teacher.T# and teacher.tname = N'张三') order by student.S#

--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select student.S# , student.sname , cast(avg(score) as decimal(18,2)) avg_score from student , sc where student.S# = SC.S# and student.S# in (select S# from SC where score < 60 group by S# having count(1) >= 2) group by student.S# , student.sname

--16、检索"01"课程分数小于60，按分数降序排列的学生信息

select student.* , sc.C# , sc.score from student , sc where student.S# = SC.S# and sc.score < 60 and sc.C# = '01' order by sc.score desc

--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

--17.1 SQL 2000 静态

select a.S# 学生编号, a.Sname 学生姓名, max(case https://www.docsj.com/doc/2f5892092.html,ame when N'语文' then b.score else null end) [语文], max(case https://www.docsj.com/doc/2f5892092.html,ame when N'数学' then b.score else null end) [数学], max(case https://www.docsj.com/doc/2f5892092.html,ame when N'英语' then b.score else null end) [英语], cast(avg(b.score) as decimal(18,2)) 平均分from Student a left join SC b on a.S# = b.S# left join Course c on b.C# = c.C# group by a.S# , a.Sname order by 平均分desc

--17.2 SQL 2000 动态

declare @sql nvarchar(4000) set @sql = 'select a.S# ' + N'学生编号' + ' , a.Sname ' + N'学生姓名' select @sql = @sql + ',max(case https://www.docsj.com/doc/2f5892092.html,ame when N'''+Cname+''' then b.score else null end) ['+Cname+']' from (select distinct Cname from Course) as t set @sql = @sql + ' , cast(avg(b.score) as decimal(18,2)) ' + N'平均分' + ' from Student a left join SC b on a.S# = b.S# left join Course c on b.C# = c.C# group by a.S# , a.Sname order by ' + N'平均分' + ' desc' exec(@sql)

--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

--方法1

select m.C# [课程编号], https://www.docsj.com/doc/2f5892092.html,ame [课程名称], 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.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [及格率(%)], 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)) [中等率(%)], 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)) [优良率(%)], cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [优秀率(%)] from Course m , SC n where m.C# = n.C# group by m.C# , https://www.docsj.com/doc/2f5892092.html,ame order by m.C#

--方法2

select m.C# [课程编号], https://www.docsj.com/doc/2f5892092.html,ame [课程名称], (select max(score) from SC where C# = m.C#) [最高分], (select min(score) from SC where C# = m.C#) [最低分], (select cast(avg(score) as decimal(18,2)) from SC where C# = m.C#) [平均分], cast((select count(1) from SC where C# = m.C# and score >= 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [及格率(%)], 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)) [中等率(%)],

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)) [优良率(%)], cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [优秀率(%)] from Course m order by m.C#

--19、按各科成绩进行排序，并显示排名

--19.1 sql 2000用子查询完成

--Score重复时保留名次空缺

select t.* , px = (select count(1) from SC where C# = t.C# and score > t.score) + 1 from sc t order by t.c# , px --Score重复时合并名次select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) from sc t order by t.c# , px

--19.2 sql 2005用rank,DENSE_RANK完成

--Score重复时保留名次空缺(rank完成)

select t.* , px = rank() over(partition by c# order by score desc) from sc t order by t.C# , px --Score重复时合并名次(DENSE_RANK完成) select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t order by t.C# , px

--20、查询学生的总成绩并进行排名

--20.1 查询学生的总成绩

select m.S# [学生编号] , m.Sname [学生姓名] , isnull(sum(score),0) [总成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname order by [总成绩] desc

--20.2 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。

select t1.* , px = (select count(1) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(sum(score),0) [总成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t2 where 总成绩> t1.总成绩) + 1 from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(sum(score),0) [总成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t1 order by px select t1.* , px = (select count(distinct 总成绩) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(sum(score),0) [总成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t2 where 总成绩>= t1.总成绩) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(sum(score),0) [总成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t1 order by px

--20.3 查询学生的总成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。

select t.* , px = rank() over(order by [总成绩] desc) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(sum(score),0) [总成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t order by px select t.* , px = DENSE_RANK() over(order by [总成绩] desc) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(sum(score),0) [总成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t order by px

--21、查询不同老师所教不同课程平均分从高到低显示

select m.T# , m.Tname , cast(avg(o.score) as decimal(18,2)) avg_score from Teacher m , Course n , SC o where m.T# = n.T# and n.C# = o.C# group by m.T# , m.Tname order by avg_score

desc

--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

--22.1 sql 2000用子查询完成

--Score重复时保留名次空缺

select * from (select t.* , px = (select count(1) from SC where C# = t.C# and score > t.score) + 1 from sc t) m where px between 2 and 3 order by m.c# , m.px --Score重复时合并名次select * from (select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) from sc t) m where px between 2 and 3 order by m.c# , m.px

--22.2 sql 2005用rank,DENSE_RANK完成

--Score重复时保留名次空缺(rank完成)

select * from (select t.* , px = rank() over(partition by c# order by score desc) from sc t) m where px between 2 and 3 order by m.C# , m.px

--Score重复时合并名次(DENSE_RANK完成)

select * from (select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t) m where px between 2 and 3 order by m.C# , m.px

--23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

--23.1 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60] --横向显示

select Course.C# [课程编号] , Cname as [课程名称] , sum(case when score >= 85 then 1 else 0 end) [85-100], sum(case when score >= 70 and score < 85 then 1 else 0 end) [70-85], sum(case when score >= 60 and score < 70 then 1 else 0 end) [60-70], sum(case when score < 60 then 1 else 0 end) [0-60] from sc , Course where SC.C# = Course.C# group by Course.C# , https://www.docsj.com/doc/2f5892092.html,ame order by Course.C#

--纵向显示1(显示存在的分数段)

select m.C# [课程编号] , https://www.docsj.com/doc/2f5892092.html,ame [课程名称] , 分数段= ( case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end) , count(1) 数量from Course m , sc n where m.C# = n.C# group by m.C# , https://www.docsj.com/doc/2f5892092.html,ame , ( case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end) order by m.C# , https://www.docsj.com/doc/2f5892092.html,ame , 分数段

--纵向显示2(显示存在的分数段，不存在的分数段用0显示)

select m.C# [课程编号] , https://www.docsj.com/doc/2f5892092.html,ame [课程名称] , 分数段= ( case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end) , count(1) 数量from Course m , sc n where m.C# = n.C# group by all m.C# , https://www.docsj.com/doc/2f5892092.html,ame , ( case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end) order by m.C# , https://www.docsj.com/doc/2f5892092.html,ame , 分数段

--23.2 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[ <60]及所占百分比

--横向显示

select m.C# 课程编号, https://www.docsj.com/doc/2f5892092.html,ame 课程名称,

(select count(1) from SC where C# = m.C# and score < 60) [0-60], cast((select count(1) from SC where C# = m.C# and score < 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)], (select count(1) from SC where C# = m.C# and score >= 60 and score < 70) [60-70], cast((select count(1) from SC where C# = m.C# and score >= 60 and score < 70)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)], (select count(1) from SC where C# = m.C# and score >= 70 and score < 85) [70-85], cast((select count(1) from SC where C# = m.C# and score >= 70 and score < 85)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)], (select count(1) from SC where C# = m.C# and score >= 85) [85-100], cast((select count(1) from SC where C# = m.C# and score >= 85)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)] from Course m order by m.C#

--纵向显示1(显示存在的分数段)

select m.C# [课程编号] , https://www.docsj.com/doc/2f5892092.html,ame [课程名称] , 分数段= ( case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end) , count(1) 数量, cast(count(1) * 100.0 / (select count(1) from sc where C# = m.C#) as decimal(18,2)) [百分比(%)] from Course m , sc n where m.C# = n.C# group by m.C# , https://www.docsj.com/doc/2f5892092.html,ame , ( case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end) order by m.C# , https://www.docsj.com/doc/2f5892092.html,ame , 分数段

--纵向显示2(显示存在的分数段，不存在的分数段用0显示)

select m.C# [课程编号] , https://www.docsj.com/doc/2f5892092.html,ame [课程名称] , 分数段= ( case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end) , count(1) 数量, cast(count(1) * 100.0 / (select count(1) from sc where C# = m.C#) as decimal(18,2)) [百分比(%)] from Course m , sc n where m.C# = n.C# group by all m.C# , https://www.docsj.com/doc/2f5892092.html,ame , ( case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end) order by m.C# , https://www.docsj.com/doc/2f5892092.html,ame , 分数段

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

--24.1 查询学生的平均成绩并进行排名，sql 2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。

select t1.* , px = (select count(1) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t2 where 平均成绩> t1.平均成绩) + 1 from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t1 order by px select t1.* , px = (select count(distinct 平均成绩) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t2 where 平均成绩>= t1.平均成绩) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t1 order by px

--24.2 查询学生的平均成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。

select t.* , px = rank() over(order by [平均成绩] desc) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t order by px select t.* , px = DENSE_RANK() over(order by [平均成绩] desc) from ( select m.S# [学生编号] , m.Sname [学生姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t order by px

--25、查询各科成绩前三名的记录

--25.1 分数重复时保留名次空缺

select m.* , n.C# , n.score from Student m, SC n where m.S# = n.S# and n.score in (select top 3 score from sc where C# = n.C# order by score desc) order by n.C# , n.score desc

--25.2 分数重复时不保留名次空缺，合并名次

--sql 2000用子查询实现

select * from (select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) from sc t) m where px between 1 and 3 order by m.c# , m.px --sql 2005用DENSE_RANK实现select * from (select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t) m where px between 1 and 3 order by m.C# , m.px

--26、查询每门课程被选修的学生数

select c# , count(S#)[学生数] from sc group by C#

--27、查询出只有两门课程的全部学生的学号和姓名

select Student.S# , Student.Sname from Student , SC where Student.S# = SC.S# group by Student.S# , Student.Sname having count(SC.C#) = 2 order by Student.S#

--28、查询男生、女生人数

select count(Ssex) as 男生人数from Student where Ssex = N'男' select count(Ssex) as 女生人数from Student where Ssex = N'女' select sum(case when Ssex = N'男' then 1 else 0 end) [男生人数],sum(case when Ssex = N'女' then 1 else 0 end) [女生人数] from student select case when Ssex = N'男' then N'男生人数' else N'女生人数' end [男女情况] , count(1) [人数] from student group by case when Ssex = N'男' then N'男生人数' else N'女生人数' end

--29、查询名字中含有"风"字的学生信息

select * from student where sname like N'%风%'

select * from student where charindex(N'风' , sname) > 0

--30、查询同名同性学生名单，并统计同名人数

select Sname [学生姓名], count(*) [人数] from Student group by Sname having count(*) > 1 --31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

select * from Student where year(sage) = 1990 select * from Student where datediff(yy,sage,'1990-01-01') = 0 select * from Student where datepart(yy,sage) = 1990 select * from Student where convert(varchar(4),sage,120) = '1990'

--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select m.C# , https://www.docsj.com/doc/2f5892092.html,ame , cast(avg(n.score) as decimal(18,2)) avg_score from Course m, SC n where m.C# = n.C# group by m.C# , https://www.docsj.com/doc/2f5892092.html,ame order by avg_score desc, m.C# asc

--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , sc b where a.S# = b.S# group by a.S# , a.Sname having cast(avg(b.score) as decimal(18,2)) >= 85 order by a.S#

--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select sname , score from Student , SC , Course where SC.S# = Student.S# and SC.C# = Course.C# and https://www.docsj.com/doc/2f5892092.html,ame = N'数学' and score < 60

--35、查询所有学生的课程及分数情况；

select Student.* , https://www.docsj.com/doc/2f5892092.html,ame , SC.C# , SC.score from Student, SC , Course where Student.S# = SC.S# and SC.C# = Course.C# order by Student.S# , SC.C#

--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select Student.* , https://www.docsj.com/doc/2f5892092.html,ame , SC.C# , SC.score from Student, SC , Course where Student.S# = SC.S# and SC.C# = Course.C# and SC.score >= 70 order by Student.S# , SC.C#

--37、查询不及格的课程

select Student.* , https://www.docsj.com/doc/2f5892092.html,ame , SC.C# , SC.score from Student, SC , Course where Student.S# = SC.S# and SC.C# = Course.C# and SC.score < 60 order by Student.S# , SC.C#

--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select Student.* , https://www.docsj.com/doc/2f5892092.html,ame , SC.C# , SC.score from Student, SC , Course where Student.S# = SC.S# and SC.C# = Course.C# and SC.C# = '01' and SC.score >= 80 order by Student.S# , SC.C#

--39、求每门课程的学生人数

select Course.C# , https://www.docsj.com/doc/2f5892092.html,ame , count(*) [学生人数] from Course , SC where Course.C# = SC.C# group by Course.C# , https://www.docsj.com/doc/2f5892092.html,ame order by Course.C# , https://www.docsj.com/doc/2f5892092.html,ame

--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

--40.1 当最高分只有一个时

select top 1 Student.* , https://www.docsj.com/doc/2f5892092.html,ame , SC.C# , SC.score from Student, SC , Course , Teacher where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三' order by SC.score desc

--40.2 当最高分出现多个时

select Student.* , https://www.docsj.com/doc/2f5892092.html,ame , SC.C# , SC.score from Student, SC , Course , Teacher where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三' and SC.score = (select max(SC.score) from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三')

--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

--方法1 select m.* from SC m ,(select C# , score from SC group by C# , score having count(1) > 1) n where m.C#= n.C# and m.score = n.score order by m.C# , m.score , m.S# --方法2 select m.* from SC m where exists (select 1 from (select C# , score from SC group by C# , score having count(1) > 1) n where m.C#= n.C# and m.score = n.score) order by m.C# , m.score , m.S#

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

select t.* from sc t where score in (select top 2 score from sc where C# = T.C# order by score desc) order by t.C# , t.score desc

--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select Course.C# , https://www.docsj.com/doc/2f5892092.html,ame , count(*) [学生人数] from Course , SC where Course.C# = SC.C# group by Course.C# , https://www.docsj.com/doc/2f5892092.html,ame having count(*) >= 5 order by [学生人数] desc ,

Course.C#

--44、检索至少选修两门课程的学生学号

select student.S# , student.Sname from student , SC where student.S# = SC.S# group by student.S# , student.Sname having count(1) >= 2 order by student.S#

--45、查询选修了全部课程的学生信息

--方法1 根据数量来完成

select student.* from student where S# in (select S# from sc group by S# having count(1) = (select count(1) from course))

--方法2 使用双重否定来完成

select t.* from student t where t.S# not in ( select distinct m.S# from ( select S# , C# from student , course ) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#) )

--方法3 使用双重否定来完成

select t.* from student t where not exists(select 1 from ( select distinct m.S# from ( select S# , C# from student , course ) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#) ) k where k.S# = t.S# )

--46、查询各学生的年龄

--46.1 只按照年份来算

select * , datediff(yy , sage , getdate()) [年龄] from student

--46.2 按照出生日期来算，当前月日< 出生年月的月日则，年龄减一

select * , case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1 else datediff(yy , sage , getdate()) end [年龄] from student

--47、查询本周过生日的学生

select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0

--48、查询下周过生日的学生

select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1

--49、查询本月过生日的学生

select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0

--50、查询下月过生日的学生

select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1 drop table Student,Course,Teacher,SC

数据库SQL查询例题及解答

1 实验目的 1．熟悉数据库的交互式SQL工具。 2．熟悉通过SQL对数据库进行操作。 3．完成作业的上机练习。 2 实验工具sql server 利用Sql server及其交互式查询工具-查询分析器来熟悉SQL。 3 实验内容和要求 1）实验内容： 创建数据库boat，包括Sailors，Boats，Reserves三个表，表结构如下： Sailors(sid: integer, sname: string, rating: integer, age: real) 船员（船员编号，姓名，级别，年龄） Boats(bid: integer, bname: string, color: string) 船（船编号，名称，颜色） Reserves(sid: integer, bid: integer, day: date) 租赁（船员编号，船编号，日期） (注：下划线表示主键)，并插入一定数据。 2）完成下列要求：

（1）查询所有船员的信息 （2）查询所有姓王的船员的信息 （3）查询租用过103号船的船员姓名 （4）查找租用过船只的船员编号 （5）查找rating>7且年龄>25的水手编号 （6）查找租用过红船和绿船的水手名字 （7）查找租用过红船或绿船的水手编号 （8）查找最年长的水手的年龄和名字 （9）在18岁以上水手中,对于每个rating级别中最少有两个水手以上的组中最年轻水手的年龄 （10）查找每条红色船只被租用的次数 （11）把30岁以上船员的级别调高一级 （12）删除所有年龄超过40岁的船员信息 （13）建立年龄超过25岁的船员的视图 （14）对（13）建立的视图，举一操作的例子（查询、删除、修改均可） 2）要求： a．建立boat数据库的SQL脚本，插入所有数据项的SQL脚本(包括所有的测试数据)。 b．记录完成查询要求的SQL语句脚本。 c．记录完成查询的查询结果。

SQLServer数据库试题九及答案

SQL Server数据库试题九及答案 一、选择题(每题2分，共30分) 1、下面不属于数据定义功能的SQL语句是：（） A.CREAT TABLE B.CREAT CURSOR C.UPDA TE D.ALTER TABLE 2、SQL数据库中的视图与下列哪项是对应的（）。 A.关系模式 B.存储模式 C.子模式 D.以上都不是 3、下列选项中不是数据库复制类型的是（）。 A.快照复制 B.差异复制 C.事务复制 D.合并复制 4、下列说法错误的是: （）。 A.一个基本表可以跨一个或多个存储文件 B. 一个存储文件可以跨一个或多个基本表 C.每个存储文件与外部存储器上一个物理文件对应 D.每个基本表与外部存储器上一个物理文件对应 5．显示包含警告信息或其他信息的消息框，应该使用的操作是（）。 A．Echo B．Message C．Warn D．MsgBox 6、在SQL Server 2000中，索引的顺序和数据表的物理顺序相同的索引是（）。 A.聚集索引 B.非聚集索引 C.主键索引 D.唯一索引 7. SQL Server的字符型系统数据类型主要包括（）。 A. Int、money、char B. char、varchar、text C. datetime、binary、int D. char、varchar、int 8、以下选项中哪一项不是访问数据库系统的接口（）。 A.ODBC B.OLE DB C.API D.ADO 9、在SELECT语句的WHERE子句的条件表达式中，可以匹配0个到多个字符的通配符是（） A.* B.% C.- D.? 10、SELECT语句中与HA VING子句同时使用的是（）子句。 A.ORDER BY B.WHERE C.GROUP BY D.无需配合 11~15题使用如下数据: 当前盘当前目录下有数据库db_stock,其中有表stock的内容是: 股票代码股票名称单价交易所 600600 青岛啤酒7.48 上海 600601 方正科技15.20 上海 600602 广电电子10.40 上海 600603 兴业房产12.76 上海 600604 二纺机9.96 上海 600605 轻工机械14.59 上海 000001 深发展7.48 深圳 000002 深万科12.50 深圳 11、有如下SQL语句 create view stock_view as select * from stock where 交易所=”深圳” 执行该语句后产生的视图包含的记录个数是（） A.1 B.2 C.3 D.4 12、有如下SQL语句

SQL Server习题及答案

1. 有个用户的计算机不能连接到中心机房的SQL Server 2000上。你在调试过程中发现这个用户的计算机的网络功能是正常的，而且其他用户都能正常地连接到SQL Server 2000。下面的哪些工具有助于你诊断和解决该问题？（多选） A．Enterprise Manager B．Server Network Utility C．Profiler D．Query Analyzer E．Client Network Utility 答:B和E 2. 你在SQL Server 2000创建了酒店管理系统的数据库HotelDB，并创建了表CustInfo。当下面的哪些数据库被删除的情况下，仍能正确地执行“SELECT * FROM CustInfo？A．Model B．Tempdb C．Msdb D．Master E．Pubs 答:A、C、E 3. 你正在使用SQL Server 2000开发银行交易系统，为了保证商业数据在网络传输（用TCP/IP协议）时不会被窃取，你在SQL Server 2000中启用了网络加密功能。请问该功能在哪一层被实现？ A．TCP/IP协议软件 B．超级套接字层 C．开放式数据服务 D．关系引擎 E．存储引擎 答:B 4. 你正在使用SQL Server 2000开发超市收银系统。在客户端编写软件时使用SQL语句“SELECT * FROM Products”来查询商品的信息，但是不小心把Products输入成Product。请问该错误在哪一层被发现？ A．客户端的数据库API B．客户端的NET-LIBRARY C．服务器端的开放式数据服务 D．服务器端的关系引擎 E．服务器端的存储引擎 答:D

SQLServer选择题含答案去原题

第一套选择题 1.下列说法错误的是（）。 A．内嵌表值函数没有函数主体，返回的表是单个SELECT语句的结果集B．多语句表值函数的调用与内嵌表值函数的调用方法相同 C．多语句表值函数的功能可以用标量函数来实现 D．在内嵌表值函数的定义中，不使用BEGIN…END块定义函数主体2.以下哪个事件不能激活DML触发器的执行（）。 A．SELECT B. UPDATE C. INSERT D. DELETE 3.以下哪个数据库实例不属于系统数据库（）。 A．master B．model C．msdb D．temp 4.执行以下语句： DECLARE @n int Set @n=3 WHILE @n<5 Begin IF @n=4 Print ltrim(@n)+‘的平方数为’+ltrim(@n*@n) Set @n=@n+1 End 执行完成后循环次数为（）。

A．0次B．1次 C．2次D．死循环 5.利用游标来操纵数据时，所用的FOR UPDATE子句充分利用了事务的哪个特性（）。 A．原子性B．一致性 C．永久性D．隔离性 6.关于触发器的描述，下列说法正确的是（）。 A．触发器是在数据修改前被触发，约束是在数据修改后被触发 B．触发器是一个能自动执行的特殊的存储过程 C．触发器作为一个独立的对象存在，与数据库中其他对象无关D．inserted表和deleted表是数据库中的物理表 7.关于服务器角色与数据库角色，说法正确的是（）。 A．只能将一个登录名添加为某个固定服务器角色的成员 B．只能将一个登录名添加为某个固定数据库角色的成员 C．sysadmin是固定数据库角色 D．db_owner是固定服务器角色 8.关于创建永久备份设备，下列说法错误的是（）。 A．执行系统存储过程sp_addumpdevice创建永久备份设备 B．执行系统存储过程sp_dropdevice删除永久备份设备 C．若被删除的“命名备份设备”的类型为磁盘，那么必须指定DELFILE选项D．只须指定该命名备份设备的物理名，无须指定该命名备份设备的逻辑名9.能够将‘java’课的学分赋值给变量的语句是哪一个（）。

SQL查询练习题

--练习题01：查找出已修学分低于20分的学生的姓名、性别和班主任。 select 姓名,性别,班主任 from 学生,班级 where 学生.班级编号=班级.班级编号 and 已修学分<20 --练习题02：查找出学生“王林”的班主任。 select 班主任 from 班级 where 班级编号 in(select 班级编号 from 学生 where 姓名='王林') --练习题03：查找出班主任“刘成河”班的全部男生的信息。 select 学生.* from 学生,班级 where 性别='男' and 班主任='刘成河' and 学生.班级编号=班级.班级编号 --练习题04：查找出课程“中国历史”考试及格的全部学生的学号、姓名、班级名称和分数。Select 学生.学号,姓名,班级名称,分数 from 学生,成绩,课程,班级 where 课程名称='中国历史' and 课程.课程编号=成绩.课程编号 and 学生.学号=成绩.学号 and 学生.班级编号=班级.班级编号 and 分数>=60 --练习题05：查找出全部同学的所有考试的姓名、课程名称、分数，查找结果的格式如下：Select 姓名,课程名称,分数 from 学生,课程,成绩 where 课程.课程编号=成绩.课程编号and 学生.学号=成绩.学号 --练习题01：查找出班主任“刘成河”班的全部男生的信息。 Select*from 学生 where 性别='男' and 班级编号 in(select 班级编号 from 班级 where 班主任='刘成河') --练习题02：查找出考试全及格的课程名称。 Select 课程名称 from 课程 where 课程编号 not in (select 课程编号 from 成绩 where 分数<60) select 课程名称 from 课程 where not exists (select * from 成绩 where 成绩.课程编号=课程.课程编号 and 分数<60) --练习题03：查找出所有学生的考试都及格的班级，并排除那些没有学生的班级。 select 班级名称 from 班级 where 总人数>0 and 班级编号 not in (select 班级编号 from 学生 where 学号 in (select 学号 from 成绩 where 分数<60))

sql查询题目及答案

数据库中有如下三个表： 学生表（学号id，姓名name，性别sex，系部depart，年龄age）8个学生记录 选课表（学号id，课程号cid，成绩grade） 12门课程 课程表（课程号cid，课程名cname，学分Ccredit） 6门课程 学生-课程模式 S-T : 学生表：Student(Sno,Sname,Ssex,Sage,Sdept) 课程表：Course(Cno,Cname,Cpno,Ccredit) 学生选课表：SC(Sno,Cno,Grade) 1.从学生表中查询所有同学的所有信息 select*from学生表 2.从学生表中查询所有学生的信息，并分别赋予一个别名 select学号as xuehao,姓名as xingming,性别as xingbie,系部as xibu,年龄as nianling from学生表 3.从学生表中查询姓名是Allen的学生的信息 select*from学生表 where姓名='Allen' 4.从学生表中查询 学号在1101到1199之间的所有学生的信息 select*from学生表 where学号between 1101 and 1199 5.从学生表中查询年龄小于18和大于20的所有学生的学号和姓名 select学号,姓名from学生表 where年龄<18 or年龄>20 6.从学生表中查询计算机系年龄小于20的所有学生的信息 select*from学生表 where系部='computer'and年龄<20 7.从学生表中查询姓名以A开头的学生的信息 select*from学生表 where姓名LIKE'A%'

2016sql数据库期末考试题及答案

一、单选题（共10 道试题，共50 分。）V 1. SQL Server中，保存着每个数据库对象的信息的系统表是（C）。 A. sysdatabases B. Syscolumns C. Sysobjects D. Syslogs 满分：5 分 2. 在存在下列关键字的SQL语句中，不可能出现Where子句的是（D ）。 A. Update B. Delete C. Insert D. Alter 满分：5 分 3. 在查询语句的Where子句中，如果出现了“age Between 30 and 40”,这个表达式等同于（A ）。 A. age>=30 and age<=40 B. age>=30 or age<=40 C. age>30 and age<40 D. age>30 or age<40

满分：5 分 4. 如果要在一张管理职工工资的表中限制工资的输入范围，应使用（D ）约束。 A. PDRIMARY KEY B. FOREIGN KEY C. unique D. check 满分：5 分 5. 记录数据库事务操作信息的文件是（D ）。 A. 数据文件 B. 索引文件 C. 辅助数据文件 D. 日志文件 满分：5 分 6. 要查询XSH数据库CP表中产品名含有“冰箱”的产品情况，可用（C）命令。 A. SELECT * FROM CP WHERE 产品名称LIKE ‘冰箱’ B. SELECT * FROM XSH WHERE 产

品名称LIKE ‘冰箱’ C. SELECT * FROM CP WHERE 产品名称LIKE ‘%冰箱%’ D. SELECT * FROM CP WHERE 产品名称=‘冰箱’ 满分：5 分 7. 储蓄所有多个储户，储户能够在多个储蓄所存取款，储蓄所与储户之间是（D ）。 A. 一对一的联系 B. 一对多的联系 C. 多对一的联系 D. 多对多的联系 满分：5 分 8. SQL的聚集函数COUNT、SUM、AVG、MAX、MIN不允许出现在查询语句的（D）子句之中。 A. SELECT B. HAVING C. GROUP BY… HAVING D. WHERE 满分：5 分

sql查询练习题含答案

--（1）查询20号部门的所有员工信息。 select * from emp e where e.deptno=20; --（2）查询奖金（COMM）高于工资（SAL）的员工信息。 select * from emp where comm>sal; --（3）查询奖金高于工资的20%的员工信息。 select * from emp where comm>sal*0.2; --（4）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。select * from emp e where (e.deptno=10 and e.job='MANAGER') or (e.deptno=20 and e.job='CLERK') --（5）查询所有工种不是MANAGER和CLERK， --且工资大于或等于2000的员工的详细信息。 select * from emp where job not in('MANAGER','CLERK') and sal>=2000; --（6）查询有奖金的员工的不同工种。 select * from emp where comm is not null; --（7）查询所有员工工资和奖金的和。 select (e.sal+nvl(https://www.docsj.com/doc/2f5892092.html,m,0)) from emp e; --（8）查询没有奖金或奖金低于100的员工信息。 select * from emp where comm is null or comm<100; --（9）查询员工工龄大于或等于10年的员工信息。 select * from emp where (sysdate-hiredate)/365>=10; --（10）查询员工信息，要求以首字母大写的方式显示所有员工的姓名。 select initcap(ename) from emp; select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp; --（11）显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序， --若月份相同则按入职的年份排序。 select ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'MM') month from emp order by month,year; --（12）查询在2月份入职的所有员工信息。 select * from emp where to_char(hiredate,'MM')='02' --（13）查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。 select e.ename,floor((sysdate-e.hiredate)/365)||'年' ||floor(mod((sysdate-e.hiredate),365)/30)||'月' ||floor(mod(mod((sysdate-e.hiredate),365),30))||'日' from emp e; --（14）查询从事同一种工作但不属于同一部门的员工信息。

SQL server 查询语句 练习题

SQL server 查询语句练习题 用SQL语句创建四个表： create database tongji go use tongji go create table student ( Sno varchar(20) not null primary key ,--学号 Sname varchar(20) not null,--学生姓名 Ssex varchar(20) not null, --学生性别 Sbirthday datetime,--学生出生年月 Class varchar(20)--学生所在班级 ) go create table teacher--老师 ( Tno varchar(20) not null primary key ,--教工编号（主码） Tname varchar(20) not null,--教工姓名 Tsex varchar(20) not null, --教工性别 Tbirthday datetime,--教工出生年月 Prof varchar(20),--职称 Depart varchar(20) not null--教工所在部门 ) go create table Course--课程 ( Cno varchar(20) not null primary key ,--课程号 Cname varchar(20) not null,--课程名称 Tno varchar(20) not null references teacher(Tno), --教工编号（外码）) go create table Score--分数 ( Sno varchar(20) not null references student(Sno), --学号（外码）Cno varchar(20) not null references Course(Cno), --课程号（外码）primary key(Sno,Cno), Degree Decimal(4,1),--成绩 ) 表中数据如下： 表（一）Student

SQL查询练习及答案

问题及描述： --1.学生表 Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表 Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号 --3.教师表 Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名 --4.成绩表 SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数 */ --创建测试数据 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)

数据库中SQL查询语句习题含的答案

.word 版本可编辑. 查询问题：设教学数据库Education 有三个关系： 学生关系S （SNO ，SNAME ，AGE ，SEX ，SDEPT ）；学习关系SC （SNO ，CNO ，GRADE ）；课程关系C （CNO ，CNAME ，CDEPT ，TNAME ） （1）检索计算机系的全体学生的学号，姓名和性别； （2）检索学习课程号为C2的学生学号与姓名； （3）检索选修课程名为“DS ”的学生学号与姓名； （4）检索选修课程号为C2或C4的学生学号； （5）检索至少选修课程号为C2和C4的学生学号； （6）检索不学C2课的学生姓名和年龄； （7）检索学习全部课程的学生姓名； （8）查询所学课程包含学生S3所学课程的学生学号。 （1）检索计算机系的全体学生的学号，姓名和性别； SELECT Sno ，Sname ，Sex FROM S WHERE Sdept =’CS ’； （2）检索学习课程号为C2的学生学号与姓名； （1）首先在C 表中找出“DS ”课程的课程号Cno ； （2）然后在SC 表中找出Cno 等于第一步给出的Cno 集合中的某个元素Cno ； （3）最后在S 关系中选出Sno 等于第二步中Sno 集合中某个元素的元组，取出Sno 和Sname 送入结果表列。 SELECT Sno ，Sname FROM S WHERE Sno IN （SELECT Sno FROM SC WHERE Cno IN （SELECT Cno FROM C WHERE Cname=‘DS ’））； （4）检索选修课程号为C2或C4的学生学号； SELECT Sno FROM SC WHERE Cno=‘C2’ OR Cno=‘C4’； （5）检索至少选修课程号为C2和C4的学生学号； SELECT Sno FROM SC X ，SC Y WHERE X.Sno=Y.Sno AND https://www.docsj.com/doc/2f5892092.html,o=‘C2’ AND https://www.docsj.com/doc/2f5892092.html,o=‘C4’ ； （6）检索不学C2课的学生姓名和年龄； 有学。 SELECT Sname FROM S WHERE NOT EXISTS （SELECT * FROM C WHERE NOT EXISTS （SELECT * FROM SC WHERE SC.Sno=S.Sno AND https://www.docsj.com/doc/2f5892092.html,o=https://www.docsj.com/doc/2f5892092.html,o ）)； （8）查询所学课程包含学生S3所学课程的学生学号。 分析：不存在这样的课程Y ，学生S3选了Y ，而其他学生没有选。 SELECT DISTINCT Sno FROM SC AS X WHERE NOT EXISTS （SELECT * FROM SC AS Y WHERE Y.Sno=‘S3’ AND NOT EXISTS （SELECT * FROM SC AS Z WHERE Z.Sno=X.Sno AND https://www.docsj.com/doc/2f5892092.html,o=https://www.docsj.com/doc/2f5892092.html,o ）)； 设教学数据库Education 有三个关系： 学生关系S （SNO ，SNAME ，AGE ，SEX ，SDEPT ）； 学习关系SC （SNO ，CNO ，GRADE ）； 课程关系C （CNO ，CNAME ，CDEPT ，TNAME ） 查询问题： 1：查所有年龄在20岁以下的学生姓名及年龄。 2：查考试成绩有不及格的学生的学号 3：查所年龄在20至23岁之间的学生姓名、系别及年龄。 4：查计算机系、数学系、信息系的学生姓名、性别。 5：查既不是计算机系、数学系、又不是信息系的学生姓名、性别 6：查所有姓“刘”的学生的姓名、学号和性别。 7：查姓“上官”且全名为3个汉字的学生姓名。 8：查所有不姓“张”的学生的姓名。 9：查DB_Design 课程的课程号。 10：查缺考的学生的学号和课程号。 11：查年龄为空值的学生的学号和姓名。 12：查计算机系20岁以下的学生的学号和姓名。 13：查计算机系、数学系、信息系的学生姓名、性别。 14：查询选修了C3课程的学生的学号和成绩，其结果按分数的降序排列。 15：查询全体学生的情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。 16：查询学生总人数。 17：查询选修了课程的学生人数。 18：计算选修了C1课程的学生平均成绩。

sql考试题及答案

sql考试题及答案 【篇一：sql试题及答案】 题（在每个小题四个备选答案中选出一个正确答案，填在题末的括号中）（本大题共10小题，每小题2分，总计20分） 1. （）是位于用户与操作系统之间的一层数据管理软件，它属于系统软件，它为用户或应用程序提供访问数据库的方法。数据库在建立、使用和维护时由其统一管理、统一控制。 a．dbms b．db c．dbs d．dba 2. 下列四项中，不属于sql2005实用程序的是（）。 a.对象资源管理器 b.查询分析器 c.服务管理器 d.媒体播放器 3. sql server安装程序创建4个系统数据库，下列哪个不是（）系统数据库。 a. master b. model c. pub d. msdb 4. 下列哪个不是sql 数据库文件的后缀。 a．.mdf b．.ldf c．.tif d．.ndf 5. sql语言允许使用通配符进行字符串匹配的*作，其中?%?可以表示（） a.零个字符 b.1个字符串 c. 多个字符串 d. 以上都是 6. sql语言中，条件年龄 between 15 and 35表示年龄在15至35之间，且( )。 a.包括15岁和35岁 b.不包括15岁和35岁 c.包括15岁但不包括35岁 d.包括35岁但不包括15岁 7. sql的视图是从（）中导出的。 a. 基本表 b. 视图 c. 基本表或视图 d. 数据库 8. 在sql语言中，建立存储过程的命令是() a、create procedure b、create rule c、create dure d、create file 9. sql语言中，删除表中数据的命令是( )。 a. delete b. drop

sql查询练习题2013

单表查询： 1、查询<学生信息表>，查询学生"张三"的全部基本信息 2、查询<学生信息表>，查询学生"张三"和”李四”的基本信息 3、查询<学生信息表>，查询姓"张"学生的基本信息 4、查询<学生信息表>，查询姓名中含有"四"字的学生的基本信息 5、查询<学生信息表>，查询姓名长度为三个字，姓“李”，且最后一个字是“强”的全部学生信息。 6、查询<学生信息表>，查询姓"张"或者姓”李”的学生的基本信息。 7、查询<学生信息表>，查询姓"张"并且"所属省份"是"北京"的学生信息 8、查询<学生信息表>，查询"所属省份"是"北京"、”新疆”、”山东”或者"上海"的学生的信息 9、查询<学生信息表>，查询姓"张"，但是"所属省份"不是"北京"的学生信息Select * 10、查询<学生信息表>，查询全部学生信息，并按照“性别”排序，性别相同的情况下按照“所属省份”排序，所属省份相同的情况下再按照“班级”排序 11、查询<学生信息表>，查询现有学生都来自于哪些不同的省份 12、查询<学生选修信息表>，查询没有填写成绩的学生的学号、课程号和成绩 13、查询<学生选修信息表>，查询全部填写了成绩的学生的选修信息，并按照“成绩”从高到低进行排序 聚合查询 1、统计<学生信息表>，统计共有多少个学生 2、统计<学生信息表>，统计年龄大于20岁的学生有多少个 3、统计<学生信息表>，统计入学时间在1980年至1982年的学生人数 4、统计<学生选修信息表>，统计学号为"S001"的学生的平均成绩 5、统计<学生选修信息表>，统计学号为"S001"的学生的总成绩 6、统计<学生选修信息表>，查询课程号为”C001”的课程的最高成绩 7、统计<学生信息表>，查询所有学生中的最大年龄是多少 分组查询练习 1、统计<学生选修信息表>，统计每个课程的选修人数 2、统计<学生选修信息表>，统计每个同学的总成绩 3、统计<学生信息表>，统计每个班级中每种性别的学生人数，并按照班级排序 4、统计<学生选修信息表>，统计每门课程的平均成绩,并按照成绩降序排序 5、统计<学生选修信息表>，显示有两门以上课程不及格的学生的学号

SQL练习题

测试表格 --1.学生表 Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 --2.课程表 Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号 --3.教师表 Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名 --4.成绩表 SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数 创建测试数据

学生表 Student 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'女') 科目表Course 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')

数据库SQL查询语句练习题

设教学数据库Education有三个关系： 学生关系S（SNO，SNAME，AGE，SEX，SDEPT）；学习关系SC（SNO，CNO，GRADE）；课程关系C（CNO，CNAME，CDEPT，TNAME）查询问题： （1）检索计算机系的全体学生的学号，姓名和性别； （2）检索学习课程号为C2的学生学号与姓名； （3）检索选修课程名为“DS”的学生学号与姓名； （4）检索选修课程号为C2或C4的学生学号； （5）检索至少选修课程号为C2和C4的学生学号； （6）检索不学C2课的学生姓名和年龄； （7）检索学习全部课程的学生姓名； （8）查询所学课程包含学生S3所学课程的学生学号。 （1）检索计算机系的全体学生的学号，姓名和性别； SELECT Sno，Sname，Sex FROM S WHERE Sdept ='CS'； （2）检索学习课程号为C2的学生学号与姓名； Sname ，1.SELECT SnoSname ，2.SELECT S.SnoFROM S SC ， FROM SWHERE Sno IN WHERE S.Sno=SC.Sno SELECT Sno （;C2'AND https://www.docsj.com/doc/2f5892092.html,o=‘ FROM SC )‘C2' WHERE Cno= DS）检索选修课程名为“”的学生学号与姓名（3表建立它们二者的表没有直接联系，必须通过SC表中，但S和C 本查询涉及到学号、姓名和课程名三个属性，分别存放在S和C S SC →联系。 C →基本思路： Cno；表中找出“DS”课程的课程号（1）首先在C ；Cno 集合中的某个元素Cno2）然后在SC表中找出Cno等于第一步给出的（送入结果表列。和SnameSno等于第二步中Sno 集合中某个元素的元组，取出Sno S（3）最后在关系中选出Sname ，SELECT SnoFROM S WHERE Sno IN SELECT Sno （ FROM SC WHERE Cno IN SELECT Cno （ FROM C ）；DS'） WHERE Cname=‘ 的学生学号；C2或C4（4）检索选修课程号为SELECT Sno FROM SC '；'C2 OR Cno=‘C4WHERE Cno=‘

面试真题及答案SQL面试题目汇总

面试真题 面试范本SQL面试题目汇总 1.触发器的作用？ 答：触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。 2.什么是存储过程？用什么来调用？ 答：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。 3.索引的作用？和它的优点缺点是什么？ 答：索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。 4.什么是内存泄漏？ 答：一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free或者delete释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。 5.维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？ 答：我是这样做的，尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

sql查询练习题含答案

--( 1) 查询20 号部门的所有员工信息。select * from emp e where e.deptno=20; --（2）查询奖金（COMM ）高于工资（SAL ）的员工信息。 select * from emp where comm>sal; --（3）查询奖金高于工资的20% 的员工信息。 select * from emp where comm>sal*0.2; --（4）查询10号部门中工种为MANAGER 和20 号部门中工种为CLERK 的员工的信息。select * from emp e where （e.deptno=10 and e.job='MANAGER'） or （e.deptno=20 and e.job='CLERK'） --（5）查询所有工种不是MANAGER 和CLERK ， --且工资大于或等于2000 的员工的详细信息。 select * from emp where job not in（'MANAGER','CLERK'） and sal>=2000; --（6）查询有奖金的员工的不同工种。 select * from emp where comm is not null; --（7）查询所有员工工资和奖金的和。 select （e.sal+nvl（https://www.docsj.com/doc/2f5892092.html,m,0）） from emp e; --（8）查询没有奖金或奖金低于100 的员工信息。 select * from emp where comm is null or comm<100; --（9）查询员工工龄大于或等于10 年的员工信息。 select * from emp where (sysdate-hiredate)/365>=10; --( 10) 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。 select initcap(ename) from emp; select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp; --( 11) 显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，--若月份相同则按入职的年份排序。 select ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'MM') month from emp order by month,year; --(12) 查询在 2 月份入职的所有员工信息。 select * from emp where to_char(hiredate,'MM')='02' --(13) 查询所有员工入职以来的工作期限，用“** 年**月** 日”的形式表示。 select e.e name,floor((sysdate-e.hiredate)/365)『年' ||floor(mod((sysdate-e.hiredate),365)/30)||' 月' ||floor(mod(mod((sysdate-e.hiredate),365),30))||' 日' from emp e; --( 14) 查询从事同一种工作但不属于同一部门的员工信息。

数据库中SQL查询语句习题含的答案

查询问题：设教学数据库Education 有三个关系： 学生关系S （SNO ，SNAME ，AGE ，SEX ，SDEPT ）；学习关系SC （SNO ，CNO ，GRADE ）；课程关系C （CNO ，CNAME ，CDEPT ，TNAME ） （1）检索计算机系的全体学生的学号，姓名和性别； （2）检索学习课程号为C2的学生学号与姓名； （3）检索选修课程名为“DS ”的学生学号与姓名； （4）检索选修课程号为C2或C4的学生学号； （5）检索至少选修课程号为C2和C4的学生学号； （6）检索不学C2课的学生姓名和年龄； （7）检索学习全部课程的学生姓名； （8）查询所学课程包含学生S3所学课程的学生学号。 （1）检索计算机系的全体学生的学号，姓名和性别； SELECT Sno ，Sname ，Sex FROM S WHERE Sdept =’CS ’； （2）检索学习课程号为C2的学生学号与姓名； （1）首先在C 表中找出“DS ”课程的课程号Cno ； （2）然后在SC 表中找出Cno 等于第一步给出的Cno 集合中的某个元素Cno ； （3）最后在S 关系中选出Sno 等于第二步中Sno 集合中某个元素的元组，取出Sno 和Sname 送入结果表列。 SELECT Sno ，Sname FROM S WHERE Sno IN （SELECT Sno FROM SC WHERE Cno IN （SELECT Cno FROM C WHERE Cname=‘DS ’））； （4）检索选修课程号为C2或C4的学生学号； SELECT Sno FROM SC WHERE Cno=‘C2’ OR Cno=‘C4’； （5）检索至少选修课程号为C2和C4的学生学号； SELECT Sno FROM SC X ，SC Y WHERE X.Sno=Y.Sno AND https://www.docsj.com/doc/2f5892092.html,o=‘C2’ AND https://www.docsj.com/doc/2f5892092.html,o=‘C4’ ； （6）检索不学C2课的学生姓名和年龄； 在表S 中找学生，要求这个学生学了全部课程。换言之，在S 表中找学生，在C 中不存在一门课程，这个学生没有学。 SELECT Sname FROM S WHERE NOT EXISTS （SELECT * FROM C WHERE NOT EXISTS （SELECT * FROM SC WHERE SC.Sno=S.Sno AND https://www.docsj.com/doc/2f5892092.html,o=https://www.docsj.com/doc/2f5892092.html,o ）)； （8）查询所学课程包含学生S3所学课程的学生学号。 分析：不存在这样的课程Y ，学生S3选了Y ，而其他学生没有选。 SELECT DISTINCT Sno FROM SC AS X WHERE NOT EXISTS （SELECT * FROM SC AS Y WHERE Y.Sno=‘S3’ AND NOT EXISTS （SELECT * FROM SC AS Z WHERE Z.Sno=X.Sno AND https://www.docsj.com/doc/2f5892092.html,o=https://www.docsj.com/doc/2f5892092.html,o ）)； 设教学数据库Education 有三个关系： 学生关系S （SNO ，SNAME ，AGE ，SEX ，SDEPT ）； 学习关系SC （SNO ，CNO ，GRADE ）； 课程关系C （CNO ，CNAME ，CDEPT ，TNAME ） 查询问题： 1：查所有年龄在20岁以下的学生姓名及年龄。 2：查考试成绩有不及格的学生的学号 3：查所年龄在20至23岁之间的学生姓名、系别及年龄。 4：查计算机系、数学系、信息系的学生姓名、性别。 5：查既不是计算机系、数学系、又不是信息系的学生姓名、性别 6：查所有姓“刘”的学生的姓名、学号和性别。 7：查姓“上官”且全名为3个汉字的学生姓名。 8：查所有不姓“张”的学生的姓名。 9：查DB_Design 课程的课程号。 10：查缺考的学生的学号和课程号。 11：查年龄为空值的学生的学号和姓名。 12：查计算机系20岁以下的学生的学号和姓名。 13：查计算机系、数学系、信息系的学生姓名、性别。 14：查询选修了C3课程的学生的学号和成绩，其结果按分数的降序排列。 15：查询全体学生的情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。 16：查询学生总人数。