createdb.sql

ssd7 练习一答案 供大家参考 如有错误 请多多包涵

CREATE TABLE book(
    primaryAuthor text,
    title text,
    isbn VARCHAR(30) PRIMARY KEY,
    publisherID integer ,
    edition VARCHAR(5),
    dateOfPublication DATE,
    price numeric CHECK (price > 0),
    BookDescription VARCHAR(40)
    /*ALTERNATE KEY (primaryAuthor,title,edition)*/
);
CREATE TABLE publisher(
    publisherID integer PRIMARY KEY,
    name text,
    address text
);
INSERT INTO book VALUES ('Bob','Fundamentals of Database Systems','11',111,'ONE','2006/01/02',50.00,'GOOD');
INSERT INTO book VALUES ('Tom','Computer','22',222,'TWO','2007/01/02',100.00,'GOOD');
INSERT INTO book VALUES ('Jim','Math','33',333,'THREE','2008/01/02',150.00,'GOOD');
INSERT INTO book VALUES ('Max','English','44',444,'FOUR','2009/01/02',200.00,'GOOD');
INSERT INTO publisher VALUES (111,'Addison Wesley','AMERICAN');
INSERT INTO publisher VALUES (222,'McGraw Hill','JAPAN');
INSERT INTO publisher VALUES (333,'APPLE','CHINA');
INSERT INTO publisher VALUES (444,'SUN','INDIA');
/*Write the SQL statement to retrieve the title and price of all books published by either of two publishers*/
(
SELECT book.title,book.price
FROM book,publisher
WHERE publisher.name='Addison Wesley' AND book.publisherID=publisher.publisherID 
)
UNION
(
SELECT book.title,book.price
FROM book,publisher
WHERE publisher.name='McGraw Hill' AND book.publisherID=publisher.publisherID
);


SELECT publisher.name
FROM book,publisher
WHERE book.title='Fundamentals of Database Systems' AND book.publisherID=publisher.publisherID