《数据库原理与技术简明(第二版)课后习题参考答案》由会员分享，可在线阅读，更多相关《数据库原理与技术简明(第二版)课后习题参考答案（23页珍藏版）》请在金锄头文库上搜索。
1、第1章单选题：B B B B C/D C A B A简述题：1略2星期节数课程星期一1语文星期二1数学星期一2数学星期一3英语。姓名养老保险失业保险医疗保险住房公积金张三3005075400李四2705080380王五28050905003略4略5略6略7略8略9略第2章实践环节设计：1CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY, Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (Ssex = 男 OR Ssex = 女), Sage tinyint CHECK (Sage = 15 AND
2、 Sage 0), Semester tinyint CHECK (Semester 0), Period int CHECK (Period 0), PRIMARY KEY(Cno) ) CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint, CHECK (Grade = 0 AND Grade = 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno
3、) REFERENCES Course ( Cno ) )2Insert into student values(9512101,李勇,男,19,计算机系)Insert into course values(co1,数据库原理,3,4,64)Insert into sc values(9512101,co1,90)3Drop TABLE student4 ALTER TABLE SC ADD XKLB char(4) 5 ALTER TABLE SC ALTER COLUMN XKLB char(6)6 Alter table sc drop column xklb7 SELECT Sno，S
4、name FROM Student 8 SELECT Sname FROM Student WHERE Sdept = 计算机系9 SELECT Sname, Sage FROM Student WHERE Sage = 2010 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23此句等价于：SELECT Sname, Sdept, Sage FROM Student WHERE Sage =20 AND Sage=2311 SELECT Sname, Ssex FROM Student WHERE Sdept
5、 IN (信息系, 数学系, 计算机系)此句等价于：SELECT Sname, Ssex FROM Student WHERE Sdept = 信息系 OR Sdept = 数学系 OR Sdept = 计算机系12 SELECT * FROM Student WHERE Sname LIKE 张%13 SELECT * FROM Student WHERE Sname LIKE 张李刘%14 SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大%15 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NUL
6、L16 SELECT Sname FROM Student WHERE Sdept=计算机系 AND Sage226 SELECT Sno, AVG(Grade) 平均成绩, COUNT(*) 修课门数 FROM SC GROUP BY Sno HAVING COUNT(*) = 227 SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 计算机系28 SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC.
7、 Sno JOIN Course c ON c.Cno = SC.Cno WHERE Sdept = 信息系 AND Cname = VB29 SELECT S2.Sname, S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = 刘晨 AND S2.Sname != 刘晨 或SELECT Sname, Sdept FROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = 刘晨)30 SELECT Studen
8、t.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno 也可以用右外连接实现： SELECT Student.Sno, Sname, Cno, Grade FROM SC RIGHT OUTER JOIN Student ON Student.Sno = SC.Sno31SELECT Sno, Sname FROM StudentWHERE Sno IN ( SELECT Sno FROM SCWHERE Cno IN (SELECT Cno FROM CourseWHERE Cname
9、= 数据库原理) )用多表连接实现： SELECT Student.Sno, Sname FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON Course.Cno = SC.Cno WHERE Cname = 数据库原理32 SELECT Sno , Grade FROM SC WHERE Cno = c02 AND Grade ( SELECT AVG(Grade) FROM SC WHERE Cno = c02)33 INSERT INTO Student VALUES (9521105, 陈冬, 男, 18, 信息系)3
10、4 UPDATE Student SET Sage = 21 WHERE Sno = 951210135 DELETE FROM SC WHERE Grade 6036 DELETE FROM SC WHERE Grade 60 AND Sno IN ( SELECT Sno FROM Student WHERE Sdept = 计算机系 )用多表连接实现 DELETE FROM SC FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = 计算机系AND Grade 6037 delete from sc where sno in (select sno from student where sname=李勇 ) and cno in(select cno from course where cname=数据库原理 ) 多表连接方式： delete from sc from sc join student on sc.sno=student.sno join course on o=owhere sname=李勇 and cname=
