createdb.sql

SSD7（数据库系统原理）Exercise 1

CREATE TABLE BOOK
(
	PrimaryAuthor                       CHAR(30),
	Title                               CHAR(60),
	ISBN                                CHAR(10),
	PublisherID                         INTEGER(3),
	Edition                             INTEGER,
	DateOfPublication                   DATE,
	Price                               REAL,
	BookDscription                      CHAR(100),
	PRIMARY KEY                         (ISBN),
	UNIQUE                              (Title)
);

CREATE TABLE PUBLISHER
(
	PublisherID                         INTEGER(3),
	Name                                CHAR(30),
	Address                             CHAR(50),
	PRIMARY KEY                         (PublisherID),
	UNIQUE                              (Name)
);

INSERT INTO BOOK
VALUES('June Jamrich Parsons', 'New Perspectives on Computer Concepts', '7505381059', 001, 5, '2005-06-2', 39.00, 'A foreign textbook about computer');
INSERT INTO BOOK
VALUES('Bruce Eckel', 'Thinking in Java', '7111213826', 002, 4, '2007-06-10', 108.00, 'A foreign textbook about java language');
INSERT INTO BOOK
VALUES('Thomas Connolly', 'Database Systems', '7503387847', 001, 3, '2005-03-30', 79.00, 'A foreign textbook about database system');
INSERT INTO BOOK
VALUES('Mark Allen Weiss', 'Data Structures and Problem Solving Using C++', '7302111669', 003, 2, '2005-08-5', 86.00, 'A foreign textbook about data structures');
INSERT INTO BOOK
VALUES('Ramez Elmasri', 'Fundamentals of Database Systems', '0201542633', 005, 3, '1999-08-27', 79.00, 'A foreign textbook about database system');

INSERT INTO PUBLISHER
VALUES(001, 'Electronics Industry', 'Beijing, China');
INSERT INTO PUBLISHER
VALUES(002, 'China Machine', 'Beijing, China');
INSERT INTO PUBLISHER
VALUES(003, 'Tsinghua University', 'Beijing, China');
INSERT INTO PUBLISHER
VALUES(004, 'Higher Education', 'Beijing, China');
INSERT INTO PUBLISHER
VALUES(005, 'Addison Wesley', 'New York, America');

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

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