数据库：SQLServer SQL语句练习

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29056818/viewspace-768456/