给自己看的_select score from sc where cid=001-CSDN博客

本文链接：https://blog.csdn.net/zhaokuo719/article/details/7558625

select * from sc
select top 10 sid from (select top 20 sid from sc order by sid desc)a
select * into stu from student

select top 5 sid from stu

select top 3 sid from stu
where sid not in(select top 3 sid from stu)

select top 5 sid from (select top 9 sid from stu order by sid desc)a

select * from (select row_number() over(order by sid) as row_number,* from stu)
t_rowtable
where row_number>5 and row_number<9
order by sid

--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select distinct a.sid as '学号'from
(select sid,score from sc where cid='001') a,
(select sid,score from sc where cid='002') b
where a.score>b.score and a.sid=b.sid

--2、查询平均成绩大于60分的同学的学号和平均成绩；
select sid as '学号',avg(score)as '平均成绩'
from sc
group by sid
having avg(score)>60

--3、查询所有同学的学号、姓名、选课数、总成绩；
select student.sid as'学号',
student.sname as'姓名',
count(sc.cid)as'选课数',
sum(sc.score)as'总成绩'
from student,sc
where student.sid=sc.sid
group by student.sid,student.sname

--4、查询姓“李”的老师的个数；
select count(*)as'个数' from teacher
where tname like'李%'

--5、查询没学过“叶平”老师课的同学的学号、姓名；
select student.sid as '学号', student.sname as'姓名'
from student
where sid not in
(
select sid from sc
where cid in
(
  select cid from course
  where tid not in
  (
   select tid from teacher
   where tname='叶平'
  )
)
)

--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select sid as'学号',sname as'姓名' from student
where sid in(select sid from sc where cid='001')
intersect
select sid as'学号',sname as'姓名' from student
where sid in(select sid from sc where cid='002')

select Student.Sid,Student.Sname
from Student,SC
where Student.Sid=SC.Sid and SC.Cid='001'
and exists( Select * from SC as SC_2
where SC_2.Sid=SC.Sid and SC_2.Cid='002');

--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select sid as'学号',sname as'姓名' from student
where sid in
(
select sid from sc,course,teacher
    where sc.cid=course.cid and course.tid=teacher.tid and teacher.tname='叶平'
    group by sid
having count(sc.cid)>=(select count(course.cid) from course,teacher
      where course.tid=teacher.tid and teacher.tname='叶平')
)

--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select distinct a.sid as '学号',a.sname as'姓名'from
(select sc.sid,sname,score from sc ,student where sc.sid=student.sid and cid='001') a,
(select sc.sid,sname,score from sc ,student where sc.sid=student.sid and cid='002') b
where a.score>b.score and a.sid=b.sid

--9、查询所有课程成绩小于60分的同学的学号、姓名；
select Sid as'学号',Sname as'姓名'
from Student
where Sid not in (select Student.Sid from Student,SC
where Student.Sid=SC.Sid and score>60 group by student.sid)

--10、查询没有学全所有课的同学的学号、姓名；
select Sid as'学号',Sname as'姓名'
from student
where sid not in(
    select sid from sc
    group by sid having count(cid)>=
    (select count(cid) from course)
    )

--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select distinct student.Sid as'学号',Sname as'姓名'
from student,sc
where student.sid=sc.sid and cid in(select cid from sc where sid='1001')

--12、查询至少学过学号为“001”同学所有课程中的一门课的其他同学学号和姓名；
select distinct student.Sid as'学号',Sname as'姓名'
from student,sc
where student.sid=sc.sid and cid in(select cid from sc where sid='001')
group by student.sid,sname
having student.sid!='001'

--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update sc
set score=(select avg(score) from sc)
where cid=(select cid from course,teacher
where course.tid=teacher.tid and tname='叶平'
)

--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号；
select distinct student.Sid as'学号',Sname as'姓名'
from student,sc
where student.sid=sc.sid and cid in(select distinct cid from sc where sid='1002')
group by student.sid,sname
having student.sid<>'1002'
and count(cid)>=(select count(cid) from sc where sid='1002')

--15、删除学习“叶平”老师课的SC表记录；
delete from sc
where cid in
(
  select cid from course
  where tid =
  (
   select tid from teacher
   where tname='叶平'
  )
)

--16、向SC表中插入一些记录，这些记录要求符合以下条件：
/*没有上过编号“003”课程的同学学号、002、002号课的平均成绩；*/
insert into sc
select distinct sid,'002',(select avg(score)
from sc where cid='002')
from sc
where sid not in (select sid from sc where cid='003')

select * from sc

--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，
/*按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分*/
select T.sid as'学生ID',
(select score from sc where sc.sid=t.sid and cid=(select cid from course where cname='数据库'))as'数据库',
(select score from sc where sc.sid=t.sid and cid=(select cid from course where cname='企业管理'))as'企业管理',
(select score from sc where sc.sid=t.sid and cid=(select cid from course where cname='英语'))as'英语',
count(T.cid)as'有效课程数',
avg(T.score)as'平均成绩'
from sc as T
group by T.sid
order by avg(T.score) desc

--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid as'课程ID',
max(score) as'最高分',
min(score) as'最低分'
from sc
group by cid

--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT t.Cid AS 课程号,
max(course.Cname)AS 课程名,
isnull(AVG(score),0) AS 平均成绩,
100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.Cid=course.Cid
GROUP BY t.Cid
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

select avg(score)as'平均成绩',
100*(select count(score) from sc where score>60)/(select count(score)from sc) as'及格率'
from sc
group by cid
order by avg(score),
100*(select count(score) from sc where score>60)/(select count(score)from sc) desc

--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），
      /*马克思（002），UML （003），数据库（004）*/
select distinct (select avg(score) from sc where cid=001)as'企业管理平均成绩',
  100*(select count(score) from sc where score>60 and cid=001)/(select count(score)from sc where cid=001) as'企业管理及格率',
       (select avg(score) from sc where cid=002)as'马克思平均成绩',
  100*(select count(score) from sc where score>60 and cid=002)/(select count(score)from sc where cid=002) as'马克思及格率',
       (select avg(score) from sc where cid=003)as'UML平均成绩',
  100*(select count(score) from sc where score>60 and cid=003)/(select count(score)from sc where cid=003) as'UML及格率',
       (select avg(score) from sc where cid=004)as'数据库平均成绩',
  100*(select count(score) from sc where score>60 and cid=004)/(select count(score)from sc where cid=004) as'数据库及格率'
from sc

--21、查询不同老师所教不同课程平均分从高到低显示
select avg(score) as'平均成绩'
from sc,course
where sc.cid=course.cid
group by course.cid,tid
order by avg(score) desc

SELECT max(Z.Tid) AS 教师ID,
  MAX(Z.Tname) AS 教师姓名,
  C.Cid AS 课程ID,
  MAX(C.Cname) AS 课程名称,
  AVG(Score) AS 平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.Cid=C.Cid and C.Tid=Z.Tid
GROUP BY C.Cid
ORDER BY AVG(Score) DESC

--22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），
      /*马克思（002），UML （003），数据库（004）
      [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩*/
with T
as
(select top 10 student.sid as'学生ID',
  student.sname as'学生姓名',
  (select top 1 score from sc where student.sid=sc.sid and cid='001') as'企业管理',
  (select top 1 score from sc where student.sid=sc.sid and cid='002') as'马克思',
  (select top 1 score from sc where student.sid=sc.sid and cid='003') as'UML',
  (select top 1 score from sc where student.sid=sc.sid and cid='004') as'数据库',
  avg(score)as'平均成绩',
  row_number() over(order by avg(score) desc) as row_number
from student,sc as t
where t.sid=student.sid
group by student.sid,student.sname
order by avg(score) desc
)
select* from T where row_number>=3 and row_number<=6

--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select distinct course.cid as'课程ID',
  course.cname as'课程名称',
  (select count(*) from sc where course.cid=sc.cid and score<=100 and score>=85 group by cid )as '100-85',
  (select count(*) from sc where course.cid=sc.cid and score<=100 and score<= 85 and score>=70 group by cid )as'85-70',
  (select count(*) from sc where course.cid=sc.cid and score<=100 and score<=70 and score>=60 group by cid )as'70-60',
  (select count(*) from sc where course.cid=sc.cid and score<=100 and score<=60 and score>=0 group by cid )as'<60'
from course
select cid,count(*) from sc where score<=100 and score>=85 group by cid

--24、查询学生平均成绩及其名次
select avg(score)as'平均成绩',dense_rank() over(order by avg(score) desc) as'名次' from sc
group by sid
order by avg(score) desc

--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.Sid as 学生ID,t1.Cid as 课程ID,Score as 分数
      FROM SC t1
      WHERE score IN (SELECT TOP 3 score
              FROM SC
              WHERE t1.Cid= sc.Cid
            ORDER BY score DESC
              )
      ORDER BY t1.Cid;

select * from
(select top 3 student.sid,student.sname,sc.cid,sc.score
from student,sc
where student.sid=sc.sid and cid='001' order by score desc) x
union all
select * from
(select top 3 student.sid,student.sname,sc.cid,sc.score
from student,sc
where student.sid=sc.sid and cid='002' order by score desc) y

--26、查询每门课程被选修的学生数
select cid,count(sid)as'学生数'
from sc
group by cid

--27、查询出只选修了一门课程的全部学生的学号和姓名
select student.Sid as'学号',student.Sname as'姓名'
from student
where sid in(select sid from sc
   group by sid
   having count(cid)=1
   )

--28、查询男生、女生人数
select count(*)as'男生人数'from student where ssex='男'
select count(*)as'女生人数'from student where ssex='女'

--29、查询姓“张”的学生名单
select * from student
where sname like'张%'
insert into student values(8,'张一安',14,'d',21)

--30、查询同名同性学生名单，并统计同名人数
select sname, count(*)as'人数'
from student
group by sname
having count(*)>1

--31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from student
where sage between '1981.1.1'and'1981.12.31'????///????

--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cid, avg(score)as'平均成绩'
from sc
group by cid
order by avg(score),cid desc

--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select student.sid as'学号',
student.sname as'姓名',
avg(score)as'平均成绩'
from student,sc
where student.sid=sc.sid
group by student.sid,student.sname
having avg(score)>85

--34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
select student.sname as'姓名',isnull(score,0) as'分数'
from sc,student
where sc.sid=student.sid and score<60 and cid
in(select cid from course where cname='数据库')

--35、查询所有学生的选课情况；
select student.sid,
  student.sname,
  sc.cid,
  course. cname
from student,sc,course
where student.sid=sc.sid and sc.cid=course.cid

--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select student.sname as'姓名',
course.cname as'课程名称',
sc.score as'分数'
from student,course, sc
where student.sid=sc.sid and sc.cid=course.cid
group by student.sid,course.cname,sc.score, student.sname
having avg(score)>=70

--37、查询不及格的课程，并按课程号从大到小排列
select course.cid,course.cname
from course,sc
where course.cid=sc.cid and score<60
order by cid desc

select cid from sc where score <60 order by Cid ;

--38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select student.sid as'学号',
student.sname as'姓名'
from student,sc
where student.sid=sc.sid and cid='003' and score>=80

--39、求选了课程的学生人数
select count(distinct sid) from sc
where cid in(select cid from course)

select count(distinct sid) from sc

--40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select student.sname as'姓名',max(sc.score)as'成绩'
from student,sc
where student.sid=sc.sid and sc.cid in
  (
    select cid from course
    where tid in
    (
     select tid from teacher
     where tname='叶平'
    )
  )
group by student.sname

--41、查询各个课程及相应的选修人数
select cid,count(sid)as'人数' from sc
group by cid

--42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distinct t.sid as'学号',
t.cid as'课程号',
t.score as'学生成绩'
from sc t,sc
where sc.score=t.score and sc.cid!=t.cid and t.sid=sc.sid

select distinct A.Sid,B.score from SC A ,SC B where
A.Score=B.Score and A.Cid <>B.Cid ;

--43、查询每门功课成绩最好的前两名
SELECT t1.Sid as 学生ID,
    t1.Cid as 课程ID,
    Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
                FROM SC
                WHERE t1.Cid= Cid
                ORDER BY score DESC
                )
ORDER BY t1.Cid;

select * from
(select top 2 student.sid,student.sname,sc.cid,sc.score
from student,sc
where student.sid=sc.sid and cid='001' order by score desc) x
union all
select * from
(select top 2 student.sid,student.sname,sc.cid,sc.score
from student,sc
where student.sid=sc.sid and cid='002' order by score desc) y
或：
select sid,cid,score from (select *,row_number() over (partition by cid order by score desc) as xulie from sc) s where s.xulie=1 or s.xulie=2
--44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，
/*查询结果按人数降序排列，若人数相同，按课程号升序排列*/
select sc.cid as'课程号',count(sid)as'选修人数'
from sc
group by cid
having count(sid)>2

--45、检索至少选修两门课程的学生学号
select sid as'学号' from sc
group by sid
having count(cid)>=2

--46、查询全部学生都选修的课程的课程号和课程名
select sc.cid as'课程号',
course.cname as'课程名'
from course,sc
where sc.cid=course.cid
group by sc.cid,course.cname
having count(distinct sid)=(select count(sid)from student)

--47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select sid as'学号',
    sname as'姓名'
from student
where Sid not in (select Sid from Course,Teacher,SC
     where Course.Tid=Teacher.Tid
     and SC.Cid=course.Cid and Tname='叶平'
     )

--48、查询两门以上不及格课程的同学的学号及其平均成绩
select sc.sid as'学号',avg(score) as'平均成绩'
from sc
where score<60
group by sc.sid
having count(score)>=2

--49、检索“004”课程分数小于60，按分数降序排列的同学学号
select sc.sid as'学号',sc.score as '成绩'
from sc
where score<60 and cid='004'
order by sc.sid desc

--50、删除“002”同学的“001”课程的成绩
delete from sc
where sid='2'and cid='001'
select *from sc