Tsql案例：题库，学生课程成绩相关查询

View Code

--建立表
create table Questions
( intQuestionID int,
vchQuestionText varchar(512)
)
go
create table TestQuestions
(intQuestionID int,
vchQuestionText varchar(512)
)
GO
--插入数据
declare @intCounter int
declare @vchQuestion varchar(64)
set nocount on
select @intCounter=1
while (@intCounter<=100)
    begin
       select @vchQuestion='Test Question #'+
          Convert(varchar(8),@intCounter)
       insert into Questions values(@intCounter,@vchQuestion)
       select @intCounter=@intCounter+1
    end
go
--准备查询语句，得到随机考题
declare @intQuestionid int
set nocount on
delete from TestQuestions
--rand() 返回0到1的随机数，
select @intQuestionid=ceiling(rand()*100)  --返回1~100随机数
    while(select count(*) from TestQuestions)<100
    begin
      while(select count(*) from TestQuestions
        where intQuestionID=@intQuestionID)=1 --如果已经存在，则再次产生新随机数
        begin
           select @intQuestionid=ceiling(rand()*100)
        end
      insert into TestQuestions
        select * from Questions
        where intQuestionID=@intQuestionID
        end

select * from Questions
select * from TestQuestions

View Code

--课程表
DROP TABLE [Course]
CREATE TABLE [Course] (  [C#] [int] NULL , [Cname] [varchar] (10) NULL ,  [T#] [int] NULL )

INSERT [Course] ( [C#] , [Cname] , [T#] ) VALUES ( 1 , 'chinese' , 1 )
INSERT [Course] ( [C#] , [Cname] , [T#] ) VALUES ( 2 , 'math' , 2 )
INSERT [Course] ( [C#] , [Cname] , [T#] ) VALUES ( 3 , 'phy' , 3 )
INSERT [Course] ( [C#] , [Cname] , [T#] ) VALUES ( 4 , 'art' , 1 )
INSERT [Course] ( [C#] , [Cname] , [T#] ) VALUES ( 5 , 'english' , 3 )
--成绩表
DROP TABLE [SC]
CREATE TABLE [SC] (  [S#] [int] NULL ,  [C#] [int] NULL ,  [Score] [int] NULL )

INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 1 , 1 , 90 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 1 , 2 , 90 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 1 , 3 , 87 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 1 , 4 , 78 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 2 , 1 , 89 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 2 , 2 , 78 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 2 , 3 , 98 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 2 , 4 , 60 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 3 , 1 , 90 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 3 , 2 , 67 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 3 , 3 , 78 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 3 , 4 , 90 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 4 , 1 , 89 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 4 , 2 , 78 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 4 , 3 , 90 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 4 , 4 , 78 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 4 , 5 , 56 )
INSERT [SC] ( [S#] , [C#] , [Score] ) VALUES ( 3 , 5 , 34 )
--学生表
DROP TABLE [Student]
CREATE TABLE [Student] (  [S#] [int] NULL , [Sname] [varchar] (10) NULL ,  [Sage] [int] NULL , [Ssex] [tinyint] NULL )

INSERT [Student] ( [S#] , [Sname] , [Sage] , [Ssex] ) VALUES ( 1 , 'Sname1' , 23 , 1 )
INSERT [Student] ( [S#] , [Sname] , [Sage] , [Ssex] ) VALUES ( 2 , 'Sname2' , 23 , 1 )
INSERT [Student] ( [S#] , [Sname] , [Sage] , [Ssex] ) VALUES ( 3 , 'Sname3' , 21 , 0 )
INSERT [Student] ( [S#] , [Sname] , [Sage] , [Ssex] ) VALUES ( 4 , 'Sname4' , 20 , 0 )

--教师表
DROP TABLE [Teacher]
CREATE TABLE [Teacher] (  [T#] [int] NULL , [Tname] [varchar] (10) NULL )

INSERT [Teacher] ( [T#] , [Tname] ) VALUES ( 1 , 'Sir Zhang' )
INSERT [Teacher] ( [T#] , [Tname] ) VALUES ( 2 , 'Miss Wang' )
INSERT [Teacher] ( [T#] , [Tname] ) VALUES ( 3 , 'Sir Li' )
INSERT [Teacher] ( [T#] , [Tname] ) VALUES ( 4 , 'Miss Li' )
INSERT [Teacher] ( [T#] , [Tname] ) VALUES ( 5 , 'Sir Duan' )

--1.查询“1”课程比“2”课程成绩高的所有学生的学号
select a.S# from
( select S#,score from sc where C#=1 ) a,
--得到两个基表
  (select S#,score from sc where C#=2) b
where a.S#=b.S# and a.score>b.score

  Select S#,Sname from (select Student.S#,Student.Sname,score ,
(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
  from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;

--2、查询平均成绩大于60分的同学的学号和平均成绩；

select avg(score),S# from sc group by S#
having avg(score)>60
--3、查询所有同学的学号、姓名、选课数、总成绩；
select a.S#, a.sname, count(1),sum(b.score)
from student a,sc b
where a.S#=b.S# group by a.S# ,a.sname
-- a.s#已经完成分组了
--4、查询姓“Li”的老师的个数；
select count(1)
from teacher where Tname like '%Li'
--5、查询学过“Sir Li”老师课的同学的学号、姓名；
--li可能教多门课程

select Student.S#,Student.Sname
    from Student
    where S#  in (select distinct( SC.S#) from SC,Course,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='Sir Li');

select S#,Sname from student where S# in(
select distinct(S#)
--得到上sir li课的学生号
from  sc join
(
select c.C# from teacher t,course c
where Tname='Sir Li'  and t.T#=c.T#) b
--得到li 教的课程号
on  sc.C#=b.C#
)

--两种方法思路一样，只是第一种是第二种的合并简化
--6、查询学过“1”并且也学过编号“2”课程的同学
---的学号、姓名；

select s#,sname from student
where s# in (
select S# from sc
--查询sc表，得到学习1,2课程的学生编号
where c#=1 or C#=2)

select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S#
and SC.C#=1  --先满足条件课程 1 ，
and exists
( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=2) --再满足条件2
--第一种效率更高，第二种方法是相关子查询，效率就低了

--7、查询学过Sir Li 老师所教的所有课
-- 的同学的学号、姓名；

select  student.s#
from student ,sc,
(select c.c#
from course c join teacher  t
on c.T#=t.T#
where t.tname='Sir Li' ) a
where sc.c#=a.c# and student.s#=sc.s#
having count(sc.c#)=(select count(c#) from course,teacher
where teacher.tname='sir li' and teacher.t#=course.T#)
--- 首先这个语句得到的结果是错误的，因为这种全
--部的左连接是不符合题目中 ’全部‘两字的要求
select S#,Sname
  from Student
  where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and
Teacher.T#=Course.T# and Teacher.Tname='Sir Li' group by S# having count(SC.C#)=(
select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='Sir Li'));
--些方法正确，思路：先得到上sir Li课程的全部学生，再---由having 添加条件

--8 查询课程编号“2”的成绩比课程编号“1”课程低的所有
--同学的学号、姓名；

select s#,sname from student where s# in (
select a.S# from
( select S#,score from sc where C#=1 ) a,
--得到两个基表
  (select S#,score from sc where C#=2) b
where a.S#=b.S# and a.score>b.score)
---思路：先得到 c#=1与C#=2的两个虚表，
-- 连接两个虚表比较两个课程分数

Select S#,Sname ,c#,score2,score
from (
    select Student.S#,Student.Sname,score ,c#,(
    select score
    from SC SC_2
    where SC_2.S#=Student.S# and SC_2.C#=2) score2
    from Student,SC
    where Student.S#=SC.S# and C#=1
)  S_2
where score2 <score;
---思路：我还没弄明白
-- 9、查询所有课程成绩小于60分的同学的学号、姓名；
  select S#,Sname
  from Student
  where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

--10、查询没有学全所有课的同学的学号、姓名；

select Student.S#,Student.Sname
    from Student,SC
    where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#)
<(select count(C#) from Course);

View Code

--11、查询至少有一门课与学号为 4 的
--同学所学相同的同学的学号和姓名；
select distinct student.s#,sname
from student left join sc
on  student.s#=sc.s# and c# in
(
select c# from sc where s#=4
)

select s#,sname
from student where
s# in
( select   SC.S#
    from Student,SC
    where Student.S#=SC.S# and C# in (select C# from SC where S#=4)
)
--思路：先查到学号4同学的课程

--14、查询和3 号的同学学习的课程完全相同的其
--他同学学号和姓名；
select s#,a.c# from
(select  c# from sc where s#=1 ) b
left join
(select s.s#,sc.c# from
sc,student s
where sc.s#=s.s#
) a
on a.c#=b.c#
group by s# having count(a.c#)=2 (
select count(c#) from sc where s#=1 )
--这个方法是错误的，
--错误思路：查询到所有学生的课程，然后与1同学的
--课程连接，然后having条件限制，
---------------

    select S# from SC where C# in (select C# from SC where S#=3)
    group by S# having count(*)=(select count(*) from SC where S#=3);
---方法二正确，用in 而不用连接
--17、按平均成绩从高到低显示所有学生的“数据库”、
--“企业管理”、“英语”三门的课程成绩，按如下形式
--显示：学生ID,,数据库,企业管理,英语,有效课程数,
--有效平均分

    SELECT S# as 学生ID
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
    FROM SC AS t
    GROUP BY S#
    ORDER BY avg(t.score)

--19、按各科平均成绩从低到高和及格率的百分数从高到
--低顺序
SELECT t.C# 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 及格百分数
, SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
--因为/count(*)得到的是小数，但是在sql中保存为
--整数即0,所以要在前面*100
    FROM SC T,Course
    where t.C#=course.C#
    GROUP BY t.C#
    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

--20、查询如下课程平均成绩和及格率的百分数
--(用"1行"显示): 企业管理（1），马克思（2）
--，OO&UML （3），数据库（4）

select
sum(case when c#=1 then score else 0 end)/sum(case when c#=1 then 1 else 0 end) as '1课程平均分',
100*sum(case when c#=1 and score>=60 then 1 else 0 end)/sum(case when c#=1 then 1 else 0 end) as '1课程及格百分数'
,sum(case when c#=2 then score else 0 end)/sum(case when c#=2 then 1 else 0 end) as '2课程平均分',
100*sum(case when c#=2 and score>=60 then 1 else 0 end)/sum(case when c#=2 then 1 else 0 end) as '2课程及格百分数'
,sum(case when c#=3 then score else 0 end)/sum(case when c#=3 then 1 else 0 end) as '3课程平均分',
100*sum(case when c#=3 and score>=60 then 1 else 0 end)/sum(case when c#=3 then 1 else 0 end) as '3课程及格百分数'
,sum(case when c#=4 then score else 0 end)/sum(case when c#=4 then 1 else 0 end) as '4课程平均分',
100*sum(case when c#=4 and score>=60 then 1 else 0 end)/sum(case when c#=4 then 1 else 0 end) as '4课程及格百分数'

from sc
--在case中也可以应用条件 and
--21 查询不同老师所教不同课程平均分从高到低显示
select  max(c.t#) as '教师编号', max(c.tname) as '老师姓名',
a.c# as '课程编号', max(b.cname) as '课程名称',avg(score) as '平均成绩',
  a.c#
from sc a,course b ,teacher c
where a.c#=b.C# and b.t#=c.T#  group by a.c#
--,c.t#  本来应按照老师，课程分组的，但是注意
--这里对课程分组后
--也就对老师分组了，因为一个课程只能被一个老师任职
--思路分析为了得到'老师编号’，
--‘老师姓名'等字段，对其做了聚合函数的处理
--23 统计各课程中各分数段的人数显示如下：课程ID,
--课程名称,[100-85],[85-70],[70-60],[ <60]

select sc.c#,a.cname ,
sum(case when score between 85 and 100 then 1 else 0 end ) as '[100-85]',
SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70] ,
SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60] ,
SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
from sc,course a
where  sc.c#=a.c#
group by sc.c# ,a.cname
--思路： case可以对行进行统计，产生列效果

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

SELECT 1+(SELECT COUNT( distinct 平均成绩)
              FROM (SELECT S#,AVG(score) AS 平均成绩  FROM SC GROUP BY S# ) AS T1 ---内层的子查询
            WHERE 平均成绩 > T2.平均成绩) as 名次,  S# as 学生学号,平均成绩
    FROM (SELECT S#,AVG(score) 平均成绩
            FROM SC
        GROUP BY S#
        ) AS T2 --外层查询，t2为内层子查询提供条件
    ORDER BY 平均成绩 desc;
--思路分析：内层子查询在这里产生了新一列，
-- 是相关子查

View Code

--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
      FROM SC t1
      WHERE score IN (SELECT TOP 3 score
              FROM SC
              WHERE t1.C#= C#
            ORDER BY score DESC
              )
      ORDER BY t1.C#,分数
--思路分析：相关子查询，你的思路错在：
--想要用group by c#,但是group by必然与聚合函数相关，
--但是这里需要的不是sum,count之类的聚合而是top 3,
--没有聚合计算的要求
--27 查询出只选修了一门课程的全部学生的学号和姓名
select sc.s#,student.sname,count(c#) as 选课数
from sc,student
where sc.s#=student.s# group by sc.s# ,student.sname having count(c#)=1
--28、查询男生、女生人数
select sum(case ssex when 1 then 1 else 0 end) '男生',
       sum(case ssex when 0 then 1 else 0 end) '女生' from student
-- 40、查询选修 sir li 老师所授课程的学生中，
--成绩最高的学生姓名及其成绩
    select Student.Sname,score
    from Student,SC,Course C,Teacher
    where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='sir li' and SC.score=(select max(score)from SC where C#=C.C# );
--注意分析：sir li并不只教了一门课，所以用
--了相关子查询

--46、查询全部学生都选修的课程的课程号和课程名
select c#,cname from
course
where c# in (
select  c#
from  sc
group by c#
having count(c#)=(select count(1) from student)
)

询

Tsql案例： 题库，学生课程成绩相关查询