SQL编程题大集合

1、问题及描述：
学生表
Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
课程表
Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,T# 教师编号
教师表
Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名
成绩表
SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数

创建测试数据：

create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(Tid varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

（4）查询学过“张三”老师授课的同学的信息
第0种方法（四重select查找）

select s.* from student s where s.Sid in
      (select sid from sc where Cid in 
         (select cid from course c where c.Tid in
             (select t.Tid from teacher t where t.Tname="张三")
          )
      ) 

第一种方法（三重select查找）

 select s.* from student s where s.Sid in
      (select sid from sc where Cid in 
         (select cid from course c,teacher t where
             c.Tid=t.Tid and Tname="张三")
      ) 

第二种方法（两重select查找）

 select s.* from student s ,sc where s.Sid=sc.Sid and sc.Cid in 
         (select cid from course c,teacher t where
             c.Tid=t.Tid and Tname="张三")

第三种方法（一重select查找）

select s.* from student s,sc,course c,teacher t where
       s.Sid=sc.Sid and sc.Cid=c.Cid and c.Tid=t.Tid and Tname="张三"

（5）查询没学过“张三”老师讲授的任一门课程的学生姓名
第0种方法：

 select s.sname from student s where s.sname not in(
    select s.sname from student s where s.sid in 
      (select sc.Sid from sc where sc.Cid in 
         (select c.cid from course c where c.Tid in 
             (select t.Tid FROM teacher t where t.Tname="张三")
          )
        )
  )

第一种方法：

select s.sname from student s where s.sname not in(
   select s.sname from student s,sc,course c,teacher t
        where s.sid=sc.sid and sc.Cid=c.Cid and c.Tid=t.Tid and t.Tname="张三"
)

（6）检索"01"课程分数小于60，按分数降序排列的学生信息

select s.* ,sc.score from student s,sc where
        s.sid=sc.sid and sc.score<60 and cid="01" ORDER BY sc.score DESC