createdb.sql

SSD7 exercise 1 参考答案

1.


CREATE TABLE PUBLISHER
(
	PublisherID			VARCHAR(5)		NOT NULL,
	Name				VARCHAR(20)		NOT NULL,
	Address				VARCHAR(20)		NOT NULL,

	PRIMARY KEY(PublisherID)
);

CREATE TABLE BOOK
(
	Primary_Author		VARCHAR(25)		NOT NULL,
	Title 				VARCHAR(50)		NOT NULL,
	ISBN 				VARCHAR(10)		NOT NULL,
	PublisherID 		VARCHAR(5)		NOT NULL,
	Edition 			SMALLINT		NOT NULL,
	Date_of_Publication VARCHAR(15)		NOT NULL,
	Price  				FLOAT			NOT NULL,
	Book_Description	VARCHAR(200)	NOT NULL,
	
	PRIMARY Key (ISBN),
	FOREIGN Key(PublisherID) REFERENCES PUBLISHER(PublisherID)
);

2.


INSERT INTO PUBLISHER
VALUES('001',	'Prentice Hall',	'New York');
INSERT INTO PUBLISHER
VALUES('002',	'Tomos Timdes',		'Boston');
INSERT INTO PUBLISHER
VALUES('003',	'Atros',			'New York');
INSERT INTO PUBLISHER
VALUES('004',	'Canerrey Boss',	'Chicago');

INSERT INTO BOOK
VALUES('Alan Ezust',		'An Introduction to Design Patterns in C++ with Qt',	'0131879057',	'001',	1,	'Aug. 2006',	48.30,	'This book is dedicated to Miriam Ezust, without whom none of our work would have been possible.');
INSERT INTO BOOK
VALUES('Jasmin Blanchet',	'C++ GUI Programming with Qt 4',						'0131872494',	'002',	2,	'Jun. 2006',	25.70,	'The Only Official Best-Practice Guide to Qt 4.1 Programming.');
INSERT INTO BOOK
VALUES('Stanley B.Lippman',	'C++ Primer',											'7115151695',	'003',	4,	'Mar. 2006',	88.50,	'The best-seller of C++');
INSERT INTO BOOK
VALUES('Jacquie Barker',	'Beginning Java Objects: From Concepts To Code',		'1590594576',	'004',	2,	'Mar. 2007',	76.50,	'The book takes readers through object design, from the very beginning, at a relaxed pace.');



3.

SELECT Title, Price FROM BOOK WHERE PublisherID IN 
(SELECT PublisherID FROM PUBLISHER WHERE Name = 'Prentice Hall' OR Name = 'Atros');



4.

SELECT Name FROM Publisher WHERE PublisherID IN 
(SELECT PublisherID FROM Book WHERE Title = 'C++ Primer');