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(20),
  Title                  VARCHAR(40),
  ISBN                   VARCHAR(40),
  PublisherID            INTEGER,
  Edition                INTEGER,
  "Date of Publication"  DATE,
  Price                  NUMERIC(5,2),
  "Book Description"     VARCHAR(100),
   CONSTRAINT PK_BOOK PRIMARY KEY(ISBN),
   CONSTRAINT FK_BOOK_PUBLISHER 
      FOREIGN KEY(PublisherID) 
      REFERENCES PUBLISHER(PublisherID)  
);

INSERT INTO PUBLISHER
VALUES(1001,'Addison Wesley','New York');
INSERT INTO PUBLISHER
VALUES(1002,'Xinhua','Beijing');
INSERT INTO PUBLISHER
VALUES(1003,'McGraw Hill','Washington DC');
INSERT INTO PUBLISHER
VALUES(1004,'Zhanghua','Beijing');

INSERT INTO BOOK
VALUES('LuWei','Data Structure','001-45678-01',1001,1,TO_DATE('2005/01/01','YYYY/MM/DD'),34.20,'Computer Type');
INSERT INTO BOOK
VALUES('LuWei','Data Structure','001-45678-02',1002,2,TO_DATE('2005/01/01','YYYY/MM/DD'),34.20,'Computer Type');
INSERT INTO BOOK
VALUES('Norman','Fundamentals of Database Systems','002-45678-01',1003,1,TO_DATE('2005/01/01','YYYY/MM/DD'),34.20,'Computer Type');
INSERT INTO BOOK
VALUES('Norman','Fundamentals of Database Systems','002-45678-02',1004,2,TO_DATE('2005/01/01','YYYY/MM/DD'),34.20,'Computer Type');

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