createdb.sql

ssd7数据库的联系一到练习三

CREATE TABLE PUBLISHER(
  PublisherID      INTEGER,
  Name             VARCHAR(20),
  Address          VARCHAR(40),
  CONSTRAINT PK_PUBLISHER PRIMARY KEY(PublisherID)
);

CREATE TABLE BOOK( 
  "Primary Author"       VARCHAR(100),
  Title                  VARCHAR(100),
  ISBN                   VARCHAR(40),
  PublisherID            INTEGER,
  Edition                INTEGER,
  "Date of Publication"  DATE,
  Price                  NUMERIC(5,2),
  "Book Description"     VARCHAR(1000),
   CONSTRAINT PK_BOOK PRIMARY KEY(ISBN),
   CONSTRAINT FK_BOOK_PUBLISHER 
      FOREIGN KEY(PublisherID) 
      REFERENCES PUBLISHER(PublisherID)  
);


INSERT INTO PUBLISHER
VALUES(1001,'Addison Wesley','Washington');
INSERT INTO PUBLISHER
VALUES(1002,'McGraw Hill','New York');
INSERT INTO PUBLISHER
VALUES(1003,'Prentice Hall PTR','New York');

INSERT INTO BOOK
VALUES('Mark Allen Weiss','Data Structures and Algorithm Analysis in C++','9780321441461',1001,3,TO_DATE('2006/02/28','YYYY/MM/DD'),48.99,'Mark Allen Weiss’ innovative approach to algorithms and data structures teaches the simultaneous development of sound analytical and programming skills for the advanced data structures course. Readers learn how to reduce time constraints and develop programs efficiently by analyzing the feasibility of an algorithm before it is coded.');
INSERT INTO BOOK
VALUES('Abraham Silberschatz, Henry F.Korth, S.Sudarshan','Fundamentals of Database Systems','9780321415066',1002,5,TO_DATE('2006/10/1','YYYY/MM/DD'),69.5,'Clear explanations of theory and design, broad coverage of models and real systems, and an up-to-date introduction to modern database technologies result in a leading introduction to database systems');
INSERT INTO BOOK
VALUES('Bruce Eckel','Thinking in Java','9780131872486',1003,4,TO_DATE('2007/6/19','YYYY/MM/DD'),42.99,'Thinking in Java should be read cover to cover by every Java programmer, then kept close at hand for frequent reference. The exercises are challenging, and the chapter on Collections is superb! Not only did this book help me to pass the Sun Certified Java Programmer exam; it’s also the first book I turn to whenever I have a Java question.');
INSERT INTO BOOK
VALUES('Stanley B.Lippman,Josée LaJoie,Barbara E.Moo','C++ Primer Plus ','9780201721485',1001,4,TO_DATE('2006/3/31','YYYY/MM/DD'),35.99,'Just as C++ has evolved since the last edition, so has the authors approach to teaching it. They now introduce the C++ standard library from the beginning, giving readers the means to write useful programs without first having to master every language detail. Highlighting todays best practices, they show how to write programs that are safe, can be built quickly, and yet offer outstanding performance. Examples that take advantage of the library, and explain the features of C++, also show how to make the best use of the language. As in its previous editions, the book authoritative discussion of fundamental C++ concepts and techniques makes it a valuable resource even for more experienced programmers.');

SELECT BOOK.Title,BOOK.Price
FROM BOOK,PUBLISHER
WHERE BOOK.PublisherID=PUBLISHER.PublisherID 
  AND (PUBLISHER.Name='Addison Wesley' OR PUBLISHER.Name='McGraw Hill'); 

SELECT PUBLISHER.Name
FROM PUBLISHER,BOOK
WHERE BOOK.PublisherID=PUBLISHER.PublisherID
  AND BOOK.Title='Fundamentals of Database Systems';