ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库:SQLServer SQL语句练习

数据库:SQLServer SQL语句练习

原创 Linux操作系统 作者:果果的空间 时间:2013-08-14 10:41:23 0 删除 编辑
1、查询“001”课程比“002”课程成绩高的所有学生的学号
select sc1.S#,sc1.C# from SC sc1
 where sc1.C#='001' and sc1.score < (select sc2.score from SC sc2
  where sc2.C#='002' and sc1.S#=sc2.S#)
 
2.查询平均成绩大于60分的同学的学号和平均成绩
select S#, AVG(score) from SC group by S# having AVG(score)>60
select S#, AVG(score) from SC group by S# having AVG(score)>60 order by AVG(score) DESC
 
3、查询所有同学的学号、姓名、选课数、总成绩;
select stu.S#, stu.Sname, COUNT(sc.C#),SUM(sc.score) from Student stu,SC sc
 where stu.S#=sc.S# group by stu.S#,stu.Sname
 
4、查询姓“李”的老师的个数;
select COUNT(*) from Teacher where Tname like '李%'
 
5、查询没学过“叶平”老师课的同学的学号、姓名;
select stu.S#,stu.Sname from SC sc,Student stu
 where stu.S#=sc.S# and  C#
  in(select C# from Course  where T#
   not in (select T# from Teacher where tname='叶平'))
 
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select S#,Sname from Student
 where S# in (select S# from SC where C# in( '001','002')
   group by S# having count(S#)=2)
 
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
 select stu.S#,stu.Sname from SC sc,Student stu
 where stu.S#=sc.S# and sc.C# in(select c.C# from Course c
  where c.T# in (select T# from Teacher where Tname='叶平'))
 
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
 select S#,Sname from Student where S#
 in(select sc1.S# from SC sc1,SC sc2
  where sc1.S#=sc2.S#
   and sc1.C#='001' and sc2.C#='002'and sc2.Score
 
9、查询所有课程成绩小于60分的同学的学号、姓名;
select stu.S#,stu.Sname from Student stu,SC sc
 where sc.score<60 and stu.S#=sc.S#
 
30、查询同名同性学生名单,并统计同名人数
 
Select Sname,Ssex,count(*) as num from Student
 group by Sname,Ssex having count(*)>1
 
 
31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)
select Sname,Sage from Student where Sage=2013-1981
Select * from Student Where year(Sage)=1981
 
32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
 
select
 c.C#,
 (select AVG(score) from SC where C#= c.C#) as avgValue
from (select C# from SC group by C#) as c
order by avgValue,c.C# desc
 
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
 
select
 c.S#, stu.Sname,
 (select AVG(score) from SC where S#= c.S#) as avgValue
from (select S#  from SC group by S#) as c
inner join Student stu on stu.S#=c.S#
where (select AVG(score) from SC where S#= c.S#) > 85
 
34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数
  
/*34、查询课程名称为“数据库”,且分数低于的学生姓名和分数*/
select Cname from Course where Cname='数据库'
select score from SC where score < 60
select stu.Sname,sc.score from Student stu,SC sc
 where stu.S#=sc.S# and sc.C#=(select top 1 C# from Course where Cname='数据库')
  and sc.score <60
  
select stu.Sname,s.score from Student stu
inner join SC s on s.S# = stu.S#
inner join Course c on c.C# = s.C#
where c.Cname='数据库' and s.score <60
 
35、查询所有学生的选课情况;
  
select stu.S#,stu.Sname ,cou.Cname from Student stu,SC SC ,Course cou
where stu.S#=SC.S# and SC.C#=cou.C# group by stu.S#,stu.Sname,cou.Cname
 
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
 
select stu.Sname ,cou.Cname,s.score from Student stu,SC s,Course cou
 where stu.S#=s.S# and s.C#=cou.C# and s.score>70
 group by stu.S#,stu.Sname,cou.Cname,s.score
 
37、查询不及格的课程,并按课程号从大到小排列
  
select stu.Sname,cou.C#,cou.Cname,s.score from Student stu,SC s,Course cou
 where stu.S#=s.S# and s.C#=cou.C# and s.score<60 order by cou.C# DESC
 
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
  
select stu.S#,stu.Sname from Student stu,SC s
where stu.S#=s.S# and s.C#='003' and s.score>80
 
39、求选了课程的学生人数
select C#, count(distinct S#) from SC group by C# order by C#
 
select count(*) from (select S# from SC SC1 where SC1.S# in
(select S# from SC SC2 group by S#) group by S#)
消息 102,级别 15,状态 1,第 2 行
')' 附近有语法错误
 
select count(s3.S#) from
(select s1.S# from SC s1 where s1.S# in
(select s2.S# from SC s2 group by s2.S#)) as s3
group by s3.S#
 
select count(*) as COUNTNUM from
(select s1.C# from SC s1 where s1.C# in
(select s2.C# from SC s2 group by s2.C#)) as s3
group by s3.C#
 
40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
 
/* 40查询选修"叶平"老师所授课程的学生中,成绩最高的学生姓名及其成绩*/
select Stu.Sname,SC.score from Student Stu
inner join SC on SC.S#=stu.S#
inner join (
select SC.C#,MAX(score) as mscore from SC inner join Course c on sc.C#=c.C# inner join Teacher t on t.T#=c.T# and Tname='叶平' group by SC.C#) as g on g.mscore=SC.score AND SC.C#=g.C#
inner join Course c on sc.C#=c.C# inner join Teacher t on t.T#=c.T# and Tname='叶平'
 
/*查询叶平老师教的每门课程最高成绩是多少*/
select SC.C#,MAX(score) as HighScore from SC inner join Course c on SC.C#=c.C#
inner join Teacher t on t.T#=c.T# and Tname='叶平' group by SC.C#
/*查询叶平老师教的课程最高成绩是多少*/
select MAX(score) as HighScore from SC inner join Course c on SC.C#=c.C#
inner join Teacher t on t.T#=c.T# and Tname='叶平'
 
/* 40查询选修"叶平"老师所授课程的学生中,学生姓名及其成绩,按照降序排序*/
select stu.Sname,s.score from Student stu,SC s
where stu.S#=s.S# and s.C# in
 (select c.C# from Course c where c.T# in
   (select distinct t.T# from Teacher t where t.Tname='叶平') )
   order by s.score DESC

41、查询各个课程及相应的选修人数
/*41、查询各个课程及相应的选修人数*/
select s1.C#,cou.Cname,count(s1.C#)
from SC s1,Course cou
where cou.C#=s1.C#
group by s1.C#,cou.Cname
select cou.Cname,cou.C#,count(cou.C#) as 人数 from Course cou
inner join SC s on s.C#=cou.C# group by cou.C#,cou.Cname
 
 
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
 
/*42、查询不同课程成绩相同的学生的学号、课程号、学生成绩(学号重复了)*/
select scr.*,sc.C# from (
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)) as scr
inner join SC on scr.S# = SC.S# AND scr.score = SC.score
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29056818/viewspace-768456/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-18

  • 博文量
    60
  • 访问量
    114927