第3-4-5章课后题答案

Page 39:
10、
（1）create table 图书表
（书号nchar(6) primary key,
书名nvarchar(30) not null,
第一作者char(10) not null,
出版日期smalldatetime,
价格numeric(4,1));
(2) create table 书店表
（书店编号nchar(6) primary key,
店名nvarchar(30) not null,
电话char(8) check(电话like [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]),
地址varchar(40),
邮政编码char(6));
(3) create table 图书销售表
（书号nchar(6) foreign key references 图书表（书号），
书店编号nchar(6) foreign key references 书店表（书店编号），
销售日期smalldatetime,
销售数量tinyint check(销售数量>=1),
Primary key(书号,书店编号, 销售日期)）；
11、alter table 图书表add 印刷数量int check(印刷数量>=1000);
12、alter table 书店表drop column 邮政编码；
13、alter table 销售表alter column销售数量int;
Page65:
1、select * from sc;
2、select sname,sage from student where sdept=’计算机系’;
3、select sno,cno,grade from sc where grade between 70 and 80;
4、select sname, sage from student where sdept=’计算机系’ and ssex=’男’ and sage between 18 and 20;
5、select max(grade) from sc where cno=’c01’;
6、select max(sage) 最大年龄, min(sage) 最小年龄from student where sdept=’计算机系’;
7、select sdept,count(*) from student group by sdept;
8、select cno,count(*),max(grade) from sc group by cno;
9、select sno,count(*),sum(grade) from sc group by sno order by count(*);
10、select sno,sum(grade) from sc group by sno having sum(grade)>200;
11、select sname,sdept from student s join sc on s.sno=sc.sno where cno=’c02’;
12、select sname,cno,grade from student s join sc on s.sno=sc.sno
where grade>80 order by grade desc;
13、(1) select sno,sname,sdept from student where sno not in(select sno from sc );
(2)select s.sno,sname,sdept
from student s left join sc on s.sno=sc.sno
where sc.sno is null;
14、(1)select cname, semester from course where semester in
(select semester from course where cname=’VB’);
(2)select ame,c1.semester from course c1 join course c2 on c1.semester=c2.semester
where ame=’VB’；
15、(1)select sname,sdept,sage from student where sage in
(select sage from student where sname=’李勇’);
(2)Select s1.sname, s1.sdept, s1.sage
from student s1 join student s2 on s1.sage=s2.sage
where s2.sname=’李勇’;
16、(1)select sname,sdept from student where sno in
(select sno from sc where cno=’c01’);
(2)select s.sno,sname,cno,grade from student s join sc on s.sno=sc.sno
where s.sno in( select sno from sc where grade>80) and sdept=’数学系’;
select s.sno,sname,cno,grade from student s join sc on s.sno=sc.sno
where s.sno in( select sno from sc where grade>80)
and sdept=’数学系’ and grade>80;
(3)select sname from student s join sc on s.sno=sc.sno
where grade=(select max(grade)
from sc join student s on sc.sno=sc.sno
where sdept=’计算机系’ )
And sdept=’计算机系’;
(4)Select sname, sdept, ssex, grade from student s join sc on s.sno=sc.sno
where s.sno in( select sno from sc join course c on o=o
where cname=’数据结构’ and grade=
(select max(grade) from sc join course c on o=o
Where cname=’数据结构’））
17、（1）select sname,sdept From student Where sno not in
(select sno from sc join course on o=o Where cname=’vb’);
（2）select sname,sdept From student Where not exist
(select * from sc join course on o=o
Where cname=’vb’ and student.sno=sc.sno);
18、(1)select sname,ssex from student
where sdept=’计算机系’ and not exits
(select * from sc where sc.sno=student.sno);
(2) select sname,ssex from student
where sdept=’计算机系’ and sno not in( select sno from sc);
19、create table test_t
(col1 int,
Col2 char(10) not null,
Col3 char(10) );
Insert into test_t values(null, ‘B1’,null);
Insert into test_t values(1,’B2’,’C2’);
Insert into test_t values(2,’B3’,null);
20、delete from sc where grade<50;
21、delete from course where cno not in (select cno from sc);
22、delect from sc where grade<60 and sno in
(select sno from student where sdept=’计算机系’)
and cno in
(select cno from course where cname=’vb’);
23、update course set credit=credit+2 where semester=’2’;
24|、update course set credit=3 where cname=’VB’;
25、update student set sage=sage+1 where sdept=’计算机系’;
26、update sc set grade=grade+5
where sno in (select sno from student where sdept=’信息系’)
and cno in (select cno from course where cname=’计算机文化学’);
page72:
3、
1）create view x_1（sno, sname,dept,cno,cname,credit）As
Select s.sno, sname,dept,cno,cname,credit
From student s join sc on s.sno=sc.sno
Join course c on o=o
2)create view x_2（sno,sname,cname,grade）
As
Select s.sno,sname,cname,grade
From student s join sc on s.sno=sc.sno
Join course c on o=o
3) create view x_3(sno, c1)
As
Select sno, count(*) From sc Group by sno
4) create view x_4(sno, c2)
As
Select sno, sum(credit)
From sc join course c on o=o
Where grade>=60
Group by sno
5) create view x_5（sno,sname,grade）
As
Select s.sno, sname,grade
From student s join sc on s.sno=sc.sno
Join course c on o=o
Where sdept=’计算机’ and cname=’vb’
And grade=
(select max(grade)
from student s join sc on s.sno=sc.sno
Join course c on o=o
Where sdept=’计算机’ and cname=’vb’))
4、
1）select sname,cname,grade from x_2 where grade>=90
2) select sno,c1 from x_3 where c1>3
3) select sname,c1 from x_1 join x_3 on x_1.sno=x_3.sno Where sdept=’计算机’ and c1>3;
4) select x_1.sno,sname,sdept,c2 From x_1 join x_4 on x_1.sno=x_4.sno Where c2>10;
5) select sname,sage,sdept,c2 From student s join x_4 on s.sno=x_4.sno
Where sage>=20 and c2>10;
5、
Alter view x_4(sno,c2,c1)
As
Select sno,sum(credit),count(*)
From sc join course c on o=o
Group by sno
6、
Alter view x_5(sno, sname, sdept, grade)
As
Select s.sno, sname,sdept,grade
From student s join sc on s.sno=sc.sno
Join course c on o=o
Where cname=’vb’ and
grade=(select max(grade)
From sc join course c on o=o
Where cname=’VB’)。