createdb.sql

ssd7 Exercise1 练习1 答案

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

CREATE TABLE BOOK( 
  "Primary Author"       VARCHAR(20),
  Title                  VARCHAR(40),
  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(5548,'Addison Wesley','New York');
INSERT INTO PUBLISHER
VALUES(4862,'McGraw Hill','Washington DC');
INSERT INTO PUBLISHER
VALUES(8976,'nwpu publisher','xian');
INSERT INTO PUBLISHER
VALUES(1597,'nwu publisher','xian');

INSERT INTO BOOK
VALUES('Bruce Eckel','Thinking in C++','7-111-09162',5548,2,TO_DATE('3/2002','YYYY/MM/DD'),76.00,'This  book  is  a  thorough  rewrite  of  the  first  edition  to  reflect  all  of  the  changes  introduced  in  C++  by  the  finalization  of  the  C++  Standard,  and  also  to  reflect ');
INSERT INTO BOOK
VALUES('Bruce Eckel','Fundamentals of Database Systems','7-111-13165-7',5548,2,TO_DATE('1/2004','YYYY/MM/DD'),56.00,'Computer type');
INSERT INTO BOOK
VALUES('kaifu.li','Do best yourself','7-010-0512-40',4862,1,TO_DATE('9/2005','YYYY/MM/DD'),49.00,'It must be importent for you。');
INSERT INTO BOOK
VALUES('yang.li','Crazy English','3-1564-1567-1534',8976,3,TO_DATE('1/2006','YYYY/MM/DD'),64.00,'improve your english');

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';