Sql经典面试题，练手题

--Student(S#,Sname,Sage,Ssex) 学生表 
--Course(C#,Cname,T#) 课程表 
--SC(S#,C#,score) 成绩表 
--Teacher(T#,Tname) 教师表
--建表语句 ：
CREATE TABLE student 
  ( 
     s#    INT, 
     sname nvarchar(32), 
     sage  INT, 
     ssex  nvarchar(8) 
  ) CREATE TABLE course 
  ( 
     c#    INT, 
     cname nvarchar(32), 
     t#    INT  ) CREATE TABLE sc 
  ( 
     s#    INT, 
     c#    INT, 
     score INT  ) CREATE TABLE teacher 
  ( 
     t#    INT, 
     tname nvarchar(16) 
  ) 
--插入测试数据语句 ：
insert into Student select 1,N'刘一',18,N'男' union all select 2,N'钱二',19,N'女' union all select 3,N'张三',17,N'男' union all select 4,N'李四',18,N'女' union all select 5,N'王五',17,N'男' 
union all select 6,N'赵六',19,N'女' ;
insert into Teacher select 1,N'叶平' union all select 2,N'贺高' union all select 3,N'杨艳' union all select 4,N'周磊' ;
insert into Course select 1,N'语文',1 union all select 2,N'数学',2 union all select 3,N'英语',3 union all select 4,N'物理',4 ;
insert into SC select 1,1,56 union all select 1,2,78 union all select 1,3,67 union all select 1,4,58 union all select 2,1,79 union all 
select 2,2,81 union all select 2,3,92 union all select 2,4,68 union all
select 3,1,91 union all select 3,2,47 union all select 3,3,88 union all select 3,4,56 union all select 4,2,88 union all select 4,3,90 union all
 select 4,4,93 union all select 5,1,46 
union all select 5,3,78 union all select 5,4,53 union all select 6,1,35 union all select 6,2,68 union all select 6,4,71;


--查询“001”课程比“002”课程成绩高的所有学生的学号；
select a.S# from (select S#,score from sc where C#='1') a ,    --思路需要找出001比002成绩高的学生学号，那么我们就先筛选出001和002课程成绩，分为A表和B表 然后对比
(select S#,score from sc where C#='2') b
where a.score>b.score and a.s#=b.s#

--查询平均成绩大于60分的同学的学号和平均成绩；
select s#,AVG(score) from sc   --AVG平均数函数,having类似where,设置函数条件时要用having
group by s#
having AVG(score)>60
--查询所有同学的学号、姓名、选课数、总成绩；
select A.s#,A.sname, COUNT(B.c#),sum(b.score) from Student as A  --COUNT统计次数，sum求和
right join  sc as B on  A.s#=B.s#
GROUP BY A.s#,A.sname
--查询姓“李”的老师的个数； 
select COUNT(distinct(tname))  from Teacher where tname like  '李%' --distinct去重
--查询没学过“叶平”老师课的同学的学号、姓名；
--写法一
select s#,sname from Student
where s# not in
(select distinct(s#) from sc,Course,Teacher where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='叶平')  --先找出学过叶平老师的学生
--写法二
select s#,sname from Student
where s# not in
(select A.s# from sc AS A
inner join Course as B ON A.c#=B.c#
inner join Teacher AS C ON B.t#=c.t#
where C.tname='叶平')

--查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； 
--写法一
select a.S#,C.sname from (select S#,score from sc where C#='1' ) a ,    
(select S#,score from sc where C#='2') b,Student C
 where a.s#=b.s# and a.s#=C.s# 
 --写法二
 select Student.S#,Student.Sname from Student,SC where    --查询学生和成绩表，条件为学过课程1且课程2也存在的学生,exists：存在的意思，not exists:不存在
 Student.S#=SC.S# and SC.C#='1'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='2');
 --查询学过“叶平”老师所教的所有课的同学的学号、姓名； 
 --写法一
 select A.s#,D.sname from sc AS A
 LEFT JOIN Course AS B ON A.c#=B.c#
 LEFT JOIN Teacher AS C ON B.t#=C.t#
 LEFT JOIN Student AS D ON A.s#=D.s#
 WHERE C.tname='叶平'
 --写法二
  select S#,Sname 
  from Student 
  --in：满足的意思，通过查询成绩表、课程表，老师表找出叶平老师教的学生学号
  where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'
 --按照学号分组统计课程表中叶平老师教授的课程数
  group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平')); 
  --查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
  --写法一
  select a.S#,C.sname from (select S#,score from sc where C#='1' ) a ,    
(select S#,score from sc where C#='2') b,Student C
 where a.s#=b.s# and a.s#=C.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; 

  --查询所有课程成绩小于60分的同学的学号、姓名；
  select s#,sname from student
  where s#  not in (select a.s# from sc a,student b where a.s#=b.s# and a.score>60 )

  --查询没有学全所有课的同学的学号、姓名； 
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); 
--查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； 
    select distinct S#,Sname from Student,SC where Student.S#=SC.S# and SC.C# in (select C# from SC where S#='1001'); 
--查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名； 
    select distinct SC.S#,Sname 
    from Student,SC 
    where Student.S#=SC.S# and C# in (select C# from SC where S#='001'); 
--把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； 
    update SC set score=(select avg(SC_2.score) 
    from SC SC_2 
    where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平'); 
--查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名； 
    select S# from SC where C# in (select C# from SC where S#='1002') 
    group by S# having count(*)=(select count(*) from SC where S#='1002'); 
--删除学习“叶平”老师课的SC表记录； 
    Delete SC 
    from course ,Teacher  
    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平'; 
--向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、 号课的平均成绩；
     
    Insert SC select S#,'002',(Select avg(score) 
    from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 
--按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生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)   
--查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
    SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 
    FROM SC L ,SC AS R 
    WHERE L.C# = R.C# and        L.score = (SELECT MAX(IL.score) 
                      FROM SC AS IL,Student AS IM 
                      WHERE L.C# = IL.C# and IM.S#=IL.S# 
                      GROUP BY IL.C#) 
        AND        R.Score = (SELECT MIN(IR.score) 
                      FROM SC AS IR 
                      WHERE R.C# = IR.C# 
                  GROUP BY IR.C# 

感谢提供：http://xqtesting.com/blog/sql-216.html