一个SQL 练习题，请帮忙做一做.

Question

要求建立一个数据一关于系，学生，班级的数据库。关系模型为:
学生(学号，姓名，年龄，班号)
班（班号，专业名，系名，入学年份）
系（系号，系名）使用查询分析器建表，要求声明：
(1)每个表的主外码，
（2）每个班级的人数不能超过20人
(3)学生的年龄介于16至30 之间
(4)学生姓名不能为空
(5)只有班级没有学生时候才能删除该班级

Answer 1

参考
Student(S#,Sname,Sage,Ssex) 学生表 
Course(C#,Cname,T#) 课程表 
SC(S#,C#,score) 成绩表 
Teacher(T#,Tname) 教师表 问题：
1、查询“”课程比“”课程成绩高的所有学生的学号； 
  SELECT a.S# FROM (SELECT s#,score FROM SC WHERE C#='001') a,(SELECT s#,score  
  FROM SC WHERE C#='002') b  
  WHERE a.score>b.score AND a.s#=b.s#;  
2、查询平均成绩大于分的同学的学号和平均成绩； 
    SELECT S#,avg(score)  
    FROM sc  
    GROUP BY S# having avg(score) >60;  
3、查询所有同学的学号、姓名、选课数、总成绩； 
  SELECT Student.S#,Student.Sname,count(SC.C#),sum(score)  
  FROM Student left Outer JOIN SC on Student.S#=SC.S#  
  GROUP BY Student.S#,Sname  
4、查询姓“李”的老师的个数； 
  SELECT count(distinct(Tname)) 
  FROM Teacher  
  WHERE Tname like '李%';  
5、查询没学过“叶平”老师课的同学的学号、姓名； 
    SELECT Student.S#,Student.Sname  
    FROM Student   
    WHERE S# not in (SELECT distinct( SC.S#) FROM SC,Course,Teacher WHERE  SC.C#=Course.C# AND Teacher.T#=Course.T# AND Teacher.Tname='叶平');  
6、查询学过“”并且也学过编号“”课程的同学的学号、姓名； 
  SELECT Student.S#,Student.Sname FROM Student,SC WHERE Student.S#=SC.S# AND SC.C#='001'and exists( SELECT * FROM SC as SC_2 WHERE SC_2.S#=SC.S# AND SC_2.C#='002');  
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 
  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='叶平' GROUP BY S# having count(SC.C#)=(SELECT count(C#) FROM Course,Teacher  WHERE Teacher.T#=Course.T# AND Tname='叶平'));  
8、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名； 
  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;  
9、查询所有课程成绩小于分的同学的学号、姓名； 
  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);  
11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名； 
    SELECT S#,Sname FROM Student,SC WHERE Student.S#=SC.S# AND C# in SELECT C# FROM SC WHERE S#='1001'; 12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名； 
    SELECT distinct SC.S#,Sname  
    FROM Student,SC  
    WHERE Student.S#=SC.S# AND C# in (SELECT C# FROM SC WHERE S#='001');  
13、把“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='叶平');  
14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名； 
    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');  
15、删除学习“叶平”老师课的SC表记录； 
    Delect SC 
    FROM course ,Teacher   
    WHERE Course.C#=SC.C# AND Course.T#= Teacher.T# AND Tname='叶平';   

Answer 2

参考小梁的blog
blog.csdn.liangck

Answer 3

(1)每个表的主外码， 
学生,班要有主外键,系只要主键
（2）每个班级的人数不能超过20人 
程序控制,select count(*) as num from 学生表 如果等于20则不能再加
(3)学生的年龄介于16至30 之间 
age between 16 and 30
(4)学生姓名不能为空 
alter table 学生 alter 姓名 not null
(5)只有班级没有学生时候才能删除该班级
delete 班 where 班号 in(
select b.班号 
from 班 a left join 学生 b on a.班号=b.班号
group by b.班号
having count(b.学生号)=0)

Answer 4

create table student 
(
 stid int identity(1,1) primary key not null
 sname  varchar(20) not null,
 sage int ,
 snum varchar(10) not null,)
create table class 
(
 cnum varchar(20) primary  key not null,
 czy  varchar(10),
 cdepete varchar(10),
 cyear datetime(),
 
 )
create table depet
(
 depnum int primary key not null,
 depname varchar(20),)select * from student where sage between '16' and '30'

Answer 5

--1),
CREATE TABLE 学生 
(学号 CHAR(8) NOT NULL PRIMARY KEY,
 姓名 VARCHAR(20),
 年龄 INT(4),
 班号 CHAR(10) REFERENCES 班(班号)
)
CREATE TABLE 班
(班号 CHAR(10) NOT NULL PRIMARY KEY,
 专业名 VARCHAR(20),
 系名 VARCHAR(10) REFERENCES 系(系名),
 入学年份 DATETIME
)
CREATE 系
(系号 CHAR(10) NOT NULL PRIMARY KEY,
 系名 VARCHAR(10),
)
--2),
SELECT A.班号 FROM (SELECT COUNT(班号) AS 人数,班号 FROM 学生 GROUP BY 班号) AS A WHERE A.人数>20
--3)
SELECT * FROM 学生 WHERE 学生.年龄 BETWEEN 16 AND 30
--4),
SELECT * FROM 学生 WHERE 学生.姓名 IS NOT NULL
--5),
DELETE 班 WHERE 班号 IS NOT IN(SELECT  班号 FROM 学生)

Answer 6

create table 学生(学号 int primary key,姓名 nvarchar(20) not null,年龄 int,班号 int) 
go
create table 班(班号 int primary key,专业名 nvarchar(50),系号 int,入学年份 int) 
go
create table 系(系号 int primary key,系名 nvarchar(50))
go
alter table 学生 add constraint fk_学生_班 foreign key (班号) references 班(班号)
go
alter table 班 add constraint fk_班_系 foreign key(系号) references 系(系号)
go
alter table 学生 add constraint check_nianling check (年龄 between 16 and 30)
go
create trigger trig_insert_students on 学生
instead of insert as
if (select count(a.学号) from 学生 a ,inserted b where a.班号=b.班号)>=20
begin
raiserror('每个班级的人数不能超过20人',16,1)
rollback tran
end
else
begin
insert 学生 select * from inserted
commit tran
end
go
create trigger trig_delete_class on 班 
instead of  delete as
if  exists(select 1 from 班 a,deleted b where a.班号=b.班号)
begin
raiserror('该班级还有学生',16,1)
rollback tran
end
else
begin
delete 班 from deleted where 班.班号=deleted.班号
commit tran
end

Answer 7

题我就不做了，说一下思路：建表用create table命令，姓名字段后加上 not null即可限制姓名不为空；学生表主键为学号，班表主键为系名+班号可以用primary key（系名，班号）（考虑到有些系班号相同）指定，系表主键为系号；在班表中应该有学号与系号两个外键，否则这三张表就没有关系了。人数不超过20人可以用if也可以用触发器看你们学得是什么。如果用触发器就在添加数据的时候检查学生表总数是否超过20人；学生表的年龄字段可以加个check约束限定范围在题目要求的数字间，同样删除时可以加触发器也可以用if。