数据库课后部分习题答案(何玉洁版)

P55习题10.
CREATE TABLE [Book](
[bno] [nchar](6)CONSTRAINT [PK_Book] PRIMARY KEY,
[bname] [nvarchar](50)NOT NULL,
[author] [char](10)NOT NULL,
[date] [smalldatetime],
[price] [decimal](5, 1),
)
CREATE TABLE [Bookshop](
[bsno] [nchar](6)CONSTRAINT [PK_Bookshop] PRIMARY KEY,
[bsname] [nvarchar](50)NOT NULL,
[tel] [char](8) CONSTRAINT [CK_Bookshop_tel]
CHECK([tel] like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), [addr] [nvarchar](40),
[zip] [char](6),
)
CREATE TABLE [Booksale](
[bno] [nchar](6)NOT NULL,
[bsno] [nchar](6)NOT NULL,
[date] [smalldatetime] NOT NULL,
[amount] [tinyint] CONSTRAINT [CK_Booksale_amount]
CHECK([amount]>=1),
CONSTRAINT [PK_Booksale] PRIMARY KEY([bno],[bsno],[date]),
CONSTRAINT [FK_Booksale_Book] FOREIGN KEY([bno])
REFERENCES [Book] ([bno]),
CONSTRAINT [FK_Booksale_Bookshop] FOREIGN KEY([bsno]) REFERENCES [Bookshop] ([bsno]),
)
11. ALTER TABLE Book ADD amount INT CONSTRAINT CK_Book_amount
CHECK(amount>=100)
12. ALTER TABLE Bookshop DROP COLUMN zip
13.ALTER TABLE Booksale ALTER COLUMN amount INT
P89习题
1.SELECT*FROM SC
2.SELECT sname,sage FROM Student
3.SELECT*FROM SC WHERE Grade BETWEEN 70 AND 80
4.SELECT sname,sage FROM Student WHERE sdept='计算机系'AND sage BETWEEN 18
AND 20
5.SELECT MAX(grade)FROM SC WHERE CNO='c01'
6.SELECT MAX(sage),MIN(sage)FROM Student WHERE sdept='计算机系'
7.SELECT sdept,COUNT(*)FROM Student GROUP BY sdept
8.SELECT cno,COUNT(*),MAX(grade)FROM SC GROUP BY cno
9.SELECT sno,COUNT(*),SUM(grade)FROM SC GROUP BY sno ORDER BY COUNT(*)
10.SELECT sno,SUM(grade)FROM SC GROUP BY sno HAVING SUM(grade)>200
11.SELECT sname,sdept FROM Student JOIN SC ON Student.sno=SC.sno WHERE CNO='c01'
或：
SELECT sname,sdept FROM Student WHERE sno IN(SELECT sno FROM SC WHERE cno='c01')
12.SELECT sname,cno,grade FROM Student JOIN SC ON Student.sno=SC.sno WHERE
grade>80 ORDER BY grade DESC
13.SELECT Student.sno,sname,sdept FROM Student LEFT JOIN SC ON Student.sno=SC.sno
WHERE SC.sno is NULL
或：
SELECT Student.sno,sname,sdept FROM Student WHERE NOT EXISTS(SELECT*
FROM SC WHERE Student.sno=SC.sno )
14.SELECT cname,semester FROM Course WHERE semester=(SELECT semester FROM
Course WHERE cname='VB')
或：
SELECT ame,C2.semester FROM Course C1 JOIN Course C2 ON
C1.semester=C2.semester WHERE ame='VB'
15.SELECT sname,sdept,sage FROM Student WHERE sage IN(SELECT sage FROM Course
WHERE sname='李勇')
16.SELECT TOP 2 sname,sage FROM Student WHERE sdept='计算机系'ORDER BY Sage
17.SELECT TOP 2 WITH TIES sname,sdept,grade FROM Student JOIN SC ON
Student.sno=SC.sno JOIN Course ON o=o WHERE cname='VB'ORDER BY grade DESC
18.SELECT TOP 2 WITH TIES sno,COUNT(*)FROM SC GROUP BY sno ORDER BY
COUNT(*)DESC
19.SELECT TOP1 WITH TIES sdept,COUNT(*)FROM Student GROUP BY sdept ORDER
BY COUNT(*)DESC
20.子查询
1)SELECT sname,sdept FROM Student WHERE sno IN (SELECT sno FROM SC
WHERE cno='c01')
2)SELECT Student.sno,sname,cno,grade FROM Student JOIN SC ON Student.sno=SC.sno
WHERE Student.sno IN(SELECT sno FROM Student WHERE sdept='数学系')AND grade>80
或：
SELECT Student.sno,sname,cno,grade FROM Student JOIN SC ON Student.sno=SC.sno
WHERE sdept='数学系'AND grade>80
错误：
SELECT Student.sno,sname,cno,grade FROM Student JOIN SC ON Student.sno=SC.sno
WHERE sdept='数学系'AND Student.sno IN(SELECT sno FROM SC WHERE
grade>80)
或：
SELECT Student.sno,sname,cno,grade FROM Student JOIN SC ON Student.sno=SC.sno
WHERE Student.sno IN(SELECT sno FROM Student WHERE sdept='数学系')AND
Student.sno IN(SELECT sno FROM SC WHERE grade>80)
3)SELECT sname FROM Student WHERE sno IN(SELECT sno FROM SC WHERE
grade=(SELECT MAX(grade)FROM SC WHERE sno IN(SELECT sno FROM Student
WHERE sdept='计算机系')))
4)SELECT sname,sdept,ssex,grade FROM Student JOIN SC ON Student.sno=SC.sno
WHERE cno IN(SELECT cno FROM Course WHERE cname='计算机文化学') AND
grade=(SELECT MAX(grade)FROM SC WHERE cno IN(SELECT cno FROM Course
WHERE cname='计算机文化学'))
21.SELECT sname,sdept FROM Student WHERE sno NOT IN(SELECT sno FROM SC
WHERE cno=(SELECT cno FROM Course WHERE cname='VB'))
或：
SELECT sname,sdept FROM Student WHERE NOT EXISTS(SELECT*FROM SC WHERE Student.sno=sno AND cno=(SELECT cno FROM Course WHERE cname='VB'))
22.SELECT sname,ssex FROM Student WHERE NOT EXISTS(SELECT*FROM SC
WHERE sno=Student.sno)
或：
SELECT sname,ssex FROM Student LEFT JOIN SC ON Student.sno=SC.sno WHERE cno IS NULL
23.SELECT sname,cname FROM Student JOIN SC ON Student.sno=SC.sno JOIN Course ON
o=o WHERE Student.sno IN(SELECT TOP 1 WITH TIES Student.sno FROM Student JOIN SC ON Student.sno=SC.sno WHERE sdept='计算机系'GROUP BY Student.sno ORDER BY AVG(grade))
24.SELECT cname,semester,credit FROM Course WHERE cno IN(SELECT TOP 1 WITH TIES
cno FROM SC WHERE cno IN(SELECT cno FROM Course WHERE semester BETWEEN 1 AND 5)GROUP BY cno ORDER BY COUNT(sno))
错误：
SELECT cname,semester,credit FROM Course WHERE semester BETWEEN 1 AND 5 AND cno IN(SELECT TOP 1 WITH TIES cno FROM SC GROUP BY cno ORDER BY COUNT(sno))
25.
CREATE TABLE test_t
(
COL1 int,
COL2 char(10)NOT NULL,
COL3 char(10),
)
INSERT test_t VALUES(NULL,'B1',NULL)
INSERT test_t VALUES(1,'B2','C2')
INSERT test_t(COL1,COL2)VALUES(2,'B3')
26.DELETE FROM SC WHERE grade<50
27.DELETE FROM Course WHERE NOT EXISTS(SELECT*FROM SC WHERE
o=cno )
或：
DELETE Course FROM Course LEFT JOIN SC ON o=o WHERE o IS NULL
28.DELETE SC FROM Student JOIN SC ON Student.sno=SC.sno JOIN Course ON
o=o WHERE sdept='计算机系'AND cname='VB'AND grade<60
或：
DELETE SC WHERE sno IN(SELECT sno FROM Student WHERE sdept='计算机系')AND cno IN(SELECT cno FROM Course WHERE cname='VB')AND grade<60
29.DELETE SC WHERE cno IN(SELECT cno FROM Course WHERE cname='VB')AND
grade=(SELECT MIN(grade)FROM SC WHERE cno IN(SELECT cno FROM Course WHERE cname='VB'))
或：
DELETE SC WHERE cno IN(SELECT cno FROM Course WHERE cname='VB')AND sno IN(SELECT TOP 1 WITH TIES sno FROM Course JOIN SC ON o=o
WHERE cname='VB'ORDER BY grade)
30.UPDATE Course SET credit=credit+2 WHERE semester=2
31.UPDATE Course SET credit=3 WHERE cname='VB'
32.UPDATE Student SET sage=sage+1 WHERE sdept='计算机系'
33.UPDATE SC SET grade=grade+5 FROM Student JOIN SC ON Student.sno=SC.sno JOIN
Course ON o=o WHERE sdept='信息系'AND cname='计算机文化学'
或：
UPDATE SC SET grade=grade+5 WHERE sno IN(SELECT sno FROM Student WHERE sdept='信息系')AND cno IN(SELECT cno FROM Course WHERE cname='计算机文化学') 34.UPDATE Course SET credit=credit-1WHERE cno IN(SELECT TOP 1 WITH TIES
o FROM Course LEFT JOIN SC ON o=o GROUP BY o ORDER BY COUNT(sno))
错误：
UPDATE Course SET credit=credit-1 WHERE cno IN(SELECT TOP 1 WITH TIES cno
FROM SC GROUP BY cno ORDER BY COUNT(sno))。