第1章
单选题： B BBBC/D C A B A 简述题：
1．略 2．
星期 星期一 星期二 星期一 星期一 。。。。。。 姓名 张三 李四 王五 3．略 4．略 5．略 6．略 7．略 8．略 9．略
节数 1 1 2 3 。。。。。。 课程 语文 数学 数学 英语 。。。。。。 医疗保险 75 80 90 住房公积金 400 380 500 养老保险 300 270 280 失业保险 50 50 50 第2章
实践环节设计：
1．CREATE 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 Sage <=45), Sdept char (20 ) DEFAULT '计算机系' )
CREATE TABLE Course ( Cnochar(10) NOT NULL, Cname char(20) NOT NULL,
Ccredittinyint CHECK (Ccredit> 0),
Semester tinyint CHECK (Semester > 0), Period int CHECK (Period > 0), PRIMARY KEY(Cno) )
CREATE TABLE SC ( Snochar(7) NOT NULL, Cnochar(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(‘9512101’,’李勇’,’男’,19,’计算机系’)
Insert into course values(‘co1’,’数据库原理’,3,4,64) Insert into scvalues(‘9512101’,’co1’,90)
3．Drop TABLE student
4 ALTER TABLE SC ADD XKLB char(4)
5 ALTER TABLE SC
ALTER COLUMN XKLB char(6) 6 Alter table sc drop column xklb 7 SELECT Sno，Sname FROM Student
8 9
SELECT Sname FROM Student WHERE Sdept = '计算机系' SELECT Sname, Sage FROM Student WHERE Sage < 20
或：SELECT Sname, Sage FROM Student WHERE NOT Sage >= 20 10
SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23
此句等价于：SELECT Sname, Sdept, Sage FROM Student WHERE Sage >=20 AND Sage<=23 11
SELECT Sname, Ssex FROM Student
WHERE Sdept IN ('信息系', '数学系', '计算机系') 此句等价于：SELECT Sname, Ssex FROM Student
WHERE Sdept = '信息系' OR Sdept = '数学系' OR
Sdept = '计算机系‘ 12 13 14 15 16
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 NULL SELECT Sname FROM Student
WHERE Sdept=计算机系' AND Sage<20 17 18
SELECT Sno, Grade FROM SC
WHERE Cno='c02' ORDER BY Grade DESC SELECT * FROM Student
ORDER BY Sdept, Sage DESC 19 20 21 22 23
SELECT COUNT （DISTINCT Sno） FROM SC
SELECT SUM(Grade) FROM SC WHERE Sno = '9512101' SELECT AVG(Grade) FROM SC WHERE Cno='C01'
SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno='C01' SELECT Cno AS 课程号, COUNT(Sno) AS 选课人数
FROM SC GROUP BY Cno
24 SELECT Sno 学号, COUNT(*) 选课门数, AVG(Grade) 平均成绩 FROM SC GROUP BY Sno
25 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >2
26 SELECT Sno, AVG(Grade) 平均成绩, COUNT(*) 修课门数 FROM SC GROUP BY Sno HAVING COUNT(*) >= 2 27 SELECT Sname, Cno, Grade
FROM Student JOIN SC ONStudent.Sno = SC.Sno WHERE Sdept = '计算机系' 28 SELECT Sname, Cname, Grade
FROM Student s JOIN SC ON s.Sno = SC. Sno
JOIN Course c ON c.Cno = SC.Cno
WHERE Sdept = '信息系' AND Cname = 'VB' 29 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 Student
WHERE Sdept IN
(SELECT Sdept FROM Student
WHERE Sname = '刘晨')
30 SELECT Student.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.Sno
31SELECT Sno, Sname FROM Student
WHERE Sno IN ( SELECTSno FROM SC
WHERE Cno IN
(SELECT Cno FROM Course
WHERE Cname = '数据库原理') )
用多表连接实现：
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, '信息系') 34 UPDATE Student SET Sage = 21
WHERE Sno = '9512101'
35 DELETE FROM SC WHERE Grade < 60 36 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 < 60
37 delete from sc where sno in (select sno from student where sname=’李勇’ ) and cno in
(select cno from course where cname=’数据库原理’ ) 多表连接方式： delete from sc
fromsc join student on sc.sno=student.sno join course on sc.cno=course.cno
where sname=’李勇’ and cname=’数据库原理’ 38