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

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 ALTER TABLE Booksale ALTER COLUMN amount INT P89习题 SELECT * FROM SC SELECT sname,sage FROM Student SELECT * FROM SC WHERE Grade BETWEEN 70 AND 80 SELECT sname,sage FROM Student WHERE sdept=计算机系 AND sage BETWEEN 18 AND 20 SELECT MAX(grade) FROM SC WHERE CNO=c01 SELECT MAX(sage),MIN(sage) FROM Student WHERE sdept=计算机系 SELECT sdept,COUNT(*) FROM Student GROUP BY sdept SELECT cno,COUNT(*),MAX(grade) FROM SC GROUP BY cno SELECT sno,COUNT(*),SUM(grade) FROM SC GROUP BY sno ORDER BY COUNT(*) SELECT sno,SUM(grade) FROM SC GROUP BY sno HAVING SUM(grade)200 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) SELECT sname,cno,grade FROM Student JOIN SC ON Student.sno=SC.sno WHERE grade80 ORDER BY grade DESC 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