数据库原理与技术简明教程(第二版)课后习题参考答案.docxVIP

第1章单选题：B B B B C/D C A B A简述题：1．略2．星期节数课程星期一1语文星期二1数学星期一2数学星期一3英语。。。。。。。。。。。。。。。。。。姓名养老保险失业保险医疗保险住房公积金张三00李四80王五003．略4．略5．略6．略7．略8．略9．略第2章实践环节设计：1．CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY, Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (Ssex = 男 OR Ssex = 女), Sagetinyint CHECK (Sage = 15 AND Sage =45), Sdept char (20 ) DEFAULT 计算机系 )CREATE TABLE Course ( Cno char(10) NOT NULL, Cname char(20) NOT NULL, Ccredit tinyint CHECK (Ccredit 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 ) REFERENCES Course ( Cno ) )2．Insert into student values(‘’,’李勇’,’男’,19,’计算机系’)Insert into course values(‘co1’,’数据库原理’,3,4,64)Insert into sc values(‘’,’co1’,90)3．Drop TABLE student4ALTER TABLE SC ADD XKLB char(4) 5 ALTER TABLE SC ALTER COLUMN XKLB char(6)Alter table sc drop column xklbSELECT Sno，Sname FROM Student SELECT Sname FROM Student WHERE Sdept = 计算机系SELECT Sname, Sage FROM Student WHERE Sage 20或：SELECT Sname, Sage FROM Student WHERE NOT Sage = 20SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23此句等价于：SELECT Sname, Sdept, Sage FROM Student WHERE Sage =20 AND Sage=23SELECT Sname, Ssex FROM Student WHERE Sdept IN (信息系, 数学系, 计算机系)此句等价于：SELECT Sname, Ssex FROM Student WHERE Sdept = 信息系 OR Sdept = 数学系 OR Sdept = 计算机系‘SELECT * FROM Student WHERE Sname LIKE ‘张%’SELECT * FROM Student WHERE Sname LIKE ‘[张李刘]%’SELECT Sname, Sno FROM Student WHERE Sname LIKE _[小大]%SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULLSELECT Sname FROM Student WHERE Sdept=计算机系 AND Sage20 SELECT Sno, Grade FROM SC WHERE Cno=c02 ORDER BY Grade DESCSELECT * FROM Student ORDER BY Sdept, Sage DESCSELECT COUNT （DISTINCT Sno） FROM SCSELECT SUM(Grade) FROM SC WHERE Sno = SELECT AVG(Grade) FROM SC WHERE Cno=C01SELECT MAX(Grade)