数据库-张-上机实验点评及参考答案2014(16页).doc
-数据库-张-上机实验点评及参考答案2014-第 16 页上机实验三基本表的建立和修改三、实习内容：1. 启动MSSQL Server服务，打开Enterprise Manager和Query Analyzer。2. 在Query Analyzer中用CREATE TABLE命令在实验二创建的GradeManager数据库中定义基本表：学生表（Student）、课程表（Course），利用Enterprise Manager的图形化功能建立班级表（Class）以及成绩表（Grade）。create table Student ( Sno char(7) primary key , Sname varchar(20) not null , Ssex char(2) not null, Sage Smallint , Clno char(5)not null);create table Class( Clno char(5) primary key, Speciality varchar(20) not null, Inyear char(4) not null, Number integer , Monitor char(7) );create table Course(Cno char(1) primary key, Cname varchar(20) not null, Credit Smallint );create table Grade(Sno char(7) references student(sno), Cno char(1) references course(cno), Gmark numeric,Primary key(sno,cno);四、针对以上四个表，用SQL语言完成以下各项操作。 给学生表增加一属性Nation（民族），数据类型为Varchar（20）； 删除学生表中新增的属性Nation； 向成绩表中插入记录（”2001110”，”3”，80）； 修改学号为”2001110”的学生的成绩为70分； 删除学号为”2001110”的学生的成绩记录； 为学生表创建一个名为IX_Class的索引。1.ALTER TABLE Student ADD Nation varchar(20);2.ALTER TABLE Student DROP COLUMN Nation;3.Insert into grade(sno,cno,gmark) values(2001110，3，80）点评：该语句没有语法错误，但好多同学在执行时出现了问题，是对的。但同学们要知道为什么会出现这个问题。4.UPDATE Grade SET Gmark=70WHERE Sno='2001110'5.DELETE FROM GradeWHERE Sno='2001110'6.CREATE INDEX IX_Class ON Student(Clno Asc);7.DROP INDEX Student.IX_Class; 五、思考题在定义基本表语句时，NOT NULL参数的使用有何作用？答：Not Null参数，可以保证在插入数据时，该属性列的取值不为空。上机实验四SELECT语句的使用(一)三、实习内容：完成以下各项操作的SQL语句： 找出所有被学生选修了的课程号；select distinct cno from grade;该语句实现的路径应该是Grade表， Course表中可能有某门课，一个学生都没有选修的。所以不能是查询Course表的Cno。 找出01311班女学生的个人信息；select * from student where Ssex = '女' and clno = '01311' ; 找出01311班、01312班的学生姓名、性别、出生年份；select Sname,Ssex,2012- Sage as birthfrom studentwhere clno = '01311' or clno = '01312'OR：select Sname,Ssex,2012- Sage as birthfrom studentwhere clno in('01311', '01312');OR:(在T-SQL中,超大纲了，呵呵)Select sname,sex,year(getdate()-sageFrom studentWhere clno in (01311,01312); 找出所有姓李的学生的个人信息；select * from studentwhere Sname like '李%' 找出学生李勇所在班级的学生人数；Select count(*) from studentWhere clno in (Select clno from studentWhere sname=”李勇”);Or: Select number from classWhere clno in (Select clno from studentWhere sname=”李勇”);Or: Select number from class,studentWhere sname=李勇 and class.clno=student.clno; 找出课程名为操作系统的平均成绩、最高分、最低分；Select avg(gmark),Max(gmark),Min(gmark)From grade,courseWhere cname=”操作系统” and o=o;Or: Select avg(gmark),Max(gmark),Min(gmark)From grade Where cno=(Select cno from course where cname=”操作系统”); 找出选修了课程的学生人数；select count(distinct Sno)from grade; 找出选修了课程操作系统的学生人数。Select count(sno) From gradeWhere cno=(Select cno from course Where cname=”操作系统” );Or: Select count(sno) From grade,courseWhere o=o and cname=”操作系统”;（9）找出2000级计算机软件班的成绩为空的学生姓名。select Snamefrom Studentwhere Clno in (select Clno from Class where Speciality='计算机软件' and Inyear=2000) and Sno in (select Sno from Grade where gmark is null);四、思考题：什么情况下需要使用关系的别名？别名的作用范围是什么？答：一般几种两种情况下使用别名：1.关系名太长，为了简化；2.为了做自身连接查询。3.在同一个SQL语句中，为了对某个表进行2次扫描。别名的作用范围是当前该SQL语句，离开当前SQL语句，别名就不在存在。上机实验五SELECT语句的使用(二)三、实习内容：完成以下各项操作的SQL语句： 找出与李勇在同一个班级的学生信息； 找出所有与李勇有相同选修课的学生信息； 找出年龄介于学生李勇和25岁之间的学生信息；（已知李勇年龄小于25岁）找出选修了课程操作系统的学生学号和姓名 找出没有选修1号课程的学生姓名 找出选修了全部课程的学生姓名； 找出与李勇在同一个班级的学生信息；Select * from studentWhere clno=(Select clno from student where sname=李勇); 找出所有与学生李勇有相同选修课程的学生信息select * from Studentwhere Sno in(select Sno from Grade where Cno in(select Cno from Gradewhere Sno in(select Sno from Studentwhere Sname='李勇')and Sname <>'李勇' 找出年龄介于学生李勇和25岁之间的学生信息；Select * from studentWhere sage between (Select sage from student Where sname=李勇)And 25; 找出选修了课程操作系统的学生学号和姓名；Select sno,sname from studentWhere sno in( Select sno from grade Where cno =( Select cno from course Where cname =操作系统); 找出所有没有选修1号课程的学生姓名；Select sname from studentWhere not exist(Select sno from grade where cno=1 and sno=student.sno);OR: Select sname from studentWhere sno not in(Select sno from grade Where cno=1); 找出选修了全部课程的学生姓名（提示：可找出这样的学生，没有一门课程是他不选修的。）下面这个编程的思路有问题，是错误的哦：SELECT SnameFROM StudentWHERE Sno IN (SELECT Sno FROM Grade WHERE Cno=1) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=2) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=3) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=4) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=5) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=6) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=7) 解一:Select sname from student Where not exists(Select * from course where not exists(Select * from grade Where sno=student.sno and cno=o);解二:Select sname from student Where sno in(Select sno from grade Group by sno Having count(*)=Select count(*) from course);OR:Select sname from student Where (Select count(cno) from grade Group by sno )=(Select count(*) from course);解法三：Select SnameFrom StudentWHERE not exists (Select Cno From Course except Select Cno From Grade WHERE Student.Sno=Grade.Sno);类似表达的还有：-1.SELECT Sname FROM StudentWHERE (select count(cno) from course) =(SELECT count(sno) FROM Grade GROUP BY Sno) -2.SELECT Sname FROM StudentWHERE Sno=ANY(SELECT Sno FROM Grade GROUP BY Sno HAVING(COUNT(*)= (SELECT COUNT(*) FROM Course)ORDER BY Sname;（1）查询选修了3号课程的学生学号及成绩，并按成绩的降序排列 select Sno,Gmark from Grade where Cno='3' order by Gmark desc;（2）查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列 select * from Student order by Clno,Sage desc;（3）求每个课程号及其相应的选课人数 select Cno,count(Sno) as 选课人数 from Grade group by Cno;（4）查询选修了3门以上课程的学生学号 select Sno,count(Cno) as 选课门数 from Grade group by Sno having count(Cno)>3;select sno from Grade gwhere (select COUNT(cno) from Grade where Sno=g.Sno)>3;五、思考题：1 用UNION或UNION ALL将两个SELECT命令结合为一个时，结果有何不同？2 当既能用连接词查询又能用嵌套查询时，应该选择哪种查询较好？为什么？3 库函数能否直接使用在：SELECT选取目标、HAVING子句、WHERE子句、GROUP BY列名中？上机实验六SQL的存储操作三、实习内容：完成以下各项操作的SQL语句： 将01311班全体学生的成绩置零； 删除2001级计算机软件的全体学生的选课记录； 学生李勇已退学，从数据库中删除有关他的记录； 对每个班，求学生的平均年龄，并把结果存入数据库。 将01311班的全体学生的成绩置零；update gradeset gmark=0where sno in (select sno from student where clno=01311) 删除01311班全体学生的选课记录；delete from gradewhere sno in (select sno from student where clno = 01311 ; 学生李勇已退学，从数据库中删除有关他的记录。注意：数据的一致性 删除表的次序：Grade / class /studentdelete from gradewhere sno in(select sno from student where sname ='李勇');update classset number = number -1where clno = (select clno from student where sname ='李勇');delete from studentwhere sname ='李勇'作业点评：有同学的语句如下：delete from student a join Grade b on a.Sno=b.Sno join Course c on c.Cno=b.Cno where Sname='李勇'注意：ANSI SQL的定义是一个Delete语句只能对一个表进行删除操作！ 对每个班，求学生的平均年龄，并把结果存入数据库； 解法一：建表、插入create table avgage(clno char(5), avgage smallint);insert into avgage select clno,avg(sage) from student grpup by clno;解法二：建视图create view v_avgageasselect clno , avg(sage) as avg_agefrom student解法三：修改Class表后插入alter table class add avgage smallintgo update classset avgage = (select age(sage) from student group by clno having student.clno = class.clno)解法四：直接建表插入select clno,avg(sage) as avg_age into avgagefrom student下面这种根据表中的数据进行编程的解法的思维是错误的，数据是随时都会变化的（虽然Case语句用的不错 J J）：alter table Class add Cage smallint nullupdate Class set Cage=casewhen Clno='00311' then (select AVG(Sage) from Student where Clno='00311')when Clno='00312' then (select AVG(Sage) from Student where Clno='00312')when Clno='01311' then (select AVG(Sage) from Student where Clno='01311')endfrom Class五、思考题：DROP命令和DELETE命令的本质区别是什么？19. 视图操作create view Stu_00312_1 -1 as select * from Student where Student.Sno in( select Sno from Grade where Grade.Cno='1')and Student.Clno='00312'create view Stu_00312_2 -2as select *from Studentwhere Student.Sno in( select Sno from Grade where Grade.Cno='1'and Grade.Gmark<=60)and Student.Clno='00312'create view Stu_year -3 as select Sno ,Sname,2005-Sage birthyearfrom Studentselect Sname -4from Stu_yearwhere birthyear>1983select Sno,Sname,2005-Sage -5from Stu_01312_2实验八 完整性约束Create table student(sno char(7) primary key, sname varchar(20) not null, ssex char(2) not null check(ssex in('男','女') default ('男'), sage smallint check(sage<65 and sage>14), clno char(5) not null)alter table course add primary key(cno);alter table course add constraint PK_course primary key(cno);alter table course add constraint chk_cred check(credit in (1,2,3,4,5,6);alter table class add primary key(clno);alter table class add check(number>1 and number<100);alter table class add foreign key (monitor) references student(sno);alter table student add foreign key(clno) references class(clno);alter table grade add primary key(sno,cno);alter table grade add foreign key (cno) references course(cno);alter table grade add constraint chk_mark check(gmark>=0 and Gmark<=100);点评：要注意外部码定义的时候，首先被参照关系（父表）必须已经定义了主码（或者唯一性约束定义），另外如果表中已经有数据的，表中已有数据不能违反参照完整性约束。假如数据库中已有Student、Grade、Course和Class表，再添加以上约束：Student： alter table Student add Check(Ssex in('男','女') alter table Student add default '男' for ssex alter table Student add check(Sage>14 and Sage<65)alter table Class/先给Class表创建主键，否则Student不能建立与Class的外部约束 add primary key(clno) alter table Student add foreign key (clno) references Class(clno) on update CASCADECourse表 alter table Course add primary key(cno) alter table Course add check (Credit in(1,2,3,4,5,6)Class表 alter table Class add check(Number>1 and Number<100) alter table Class add foreign key (Monitor) references Student(sno)Grade表 alter table Grade add foreign key(sno) references Student(sno) alter table Grade add foreign key(cno) references Course(cno) alter table Grade add check (Gmark>0 and Gmark<100)
