《数据库原理》上机实验点评及参考答案2017.doc

1、上机实验三基本表的建立和修改三、实习内容：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 n

2、ull , 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 );cre

3、ate 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_C

4、lass的索引。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.C

5、REATE 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。 找出0131

6、1班女学生的个人信息；select * from student where Ssex = 女 and clno = 01311 ; 找出01311班、01312班的学生姓名、性别、出生年份；select Sname,Ssex,2016- Sage as birthfrom studentwhere clno = 01311 or clno = 01312;OR：select Sname,Ssex,2016- Sage as birthfrom studentwhere clno in(01311, 01312);OR:(在T-SQL中,超大纲了)Select sname,sex,year(g

7、etdate()-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:

8、 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=”操作系统”); 找出选修了课程

9、的学生人数；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 w

10、here Speciality=计算机软件 and Inyear=2000) and Sno in (select Sno from Grade where gmark is null);四、思考题：什么情况下需要使用关系的别名？别名的作用范围是什么？答：一般几种两种情况下使用别名：1.关系名太长，为了简化；2.为了做自身连接查询。3.在同一个SQL语句中，为了对某个表进行2次扫描。别名的作用范围是当前该SQL语句，离开当前SQL语句，别名就不在存在。上机实验五SELECT语句的使用(二)三、实习内容：完成以下各项操作的SQL语句： 找出与李勇在同一个班级的学生信息； 找出所有与李勇有相同选修

11、课的学生信息； 找出年龄介于学生李勇和25岁之间的学生信息；（已知李勇年龄小于25岁）找出选修了课程操作系统的学生学号和姓名 找出没有选修1号课程的学生姓名 找出选修了全部课程的学生姓名； 找出与李勇在同一个班级的学生信息；Select * from studentWhere clno=(Select clno from student where sname=李勇);Or：本题还可以用自身连接进行查询。 找出所有与学生李勇有相同选修课程的学生信息select * from Studentwhere Sno in(select Sno from Grade where Cno in(selec

12、t 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

13、 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);Or：用集合运算select Snamefrom Studentwhere Sno in(select Snofrom Gradeexcepts

14、elect Snofrom Gradewhere 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

15、=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

16、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 Fr

17、om 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;？sele

18、ct Sname from Student where Sno in ( select Sno from Grade where Cno =all ( select Cno from Course ) )（1）查询选修了3号课程的学生学号及成绩，并按成绩的降序排列 select Sno,Gmark from Grade where Cno=3 order by Gmark desc;（2）查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列 select * from Student order by Clno,Sage desc;（3）求每个课程号及其相应的选课人数 s

19、elect 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 Gradewhere Sno=g.Sno)3;五、思考题：1 用UNION或UNION ALL将两个SELECT命令结合为一个时，结果有何不同？2 当既能用连接词查询又能用嵌套查询时，应该选择哪种查询较

20、好？为什么？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班全体学生的选课记录；

21、delete from gradewhere sno in (select sno from student where clno = 01311 ; 学生李勇已退学，从数据库中删除有关他的记录。注意：数据的一致性 删除表的次序：Grade / class /studentUPDATE ClassSETMonitor=caseWHEN Monitor=(SELECT SnoFROM StudentWHERE Sname=李勇)THENendFROMClassWHERE Clno in(SELECT ClnoFROM StudentWHERE Sname=李勇)delete from grade

22、where 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的定

23、义是一个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 sm

24、allintgo 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=casew

25、hen 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

26、 Student where Student.Sno in( select Sno from Grade where Grade.Cno=1)and Student.Clno=00312create view Stu_00312_2 -2as select *from Studentwhere Student.Sno in( select Sno from Grade where Grade.Cno=1and Grade.Gmark1983select Sno,Sname,2005-Sage -5from Stu_01312_2实验八 完整性约束Create table student(sno

27、 char(7) primary key, sname varchar(20) not null, ssex char(2) not null check(ssex in(男,女) default (男), sage smallint check(sage14), 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(number1 and number=0 and Gmark14 and Sage1 and Number0 and Gmark100)