createdb.sql

ssd7 ex1 Design the following two tables and turn in your submissions in an HTML file named BookPub

-- Table: Publisher

-- DROP TABLE "Publisher";
CREATE TABLE Publisher
(
  PublisherID char(4) NOT NULL,
  Name varchar(50),
  Address varchar(100),
  unique(name,address),
  Primary key(PublisherID)
);
-- Table: "BOOK"

-- DROP TABLE "BOOK"; 
CREATE TABLE Book
(
   PrimaryAuthor varchar(30), 
   Title varchar(40), 
   ISBN varchar(20) NOT NULL, 
   PublisherID char(4), 
   Edtion varchar(20), 
   DateOfpublishcation date, 
   Price float(8), 
   BookDescription varchar(400),
   Primary Key (ISBN),
   unique (Title, PublisherID ),
   FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)
);
--Insert data into pubisher.
INSERT INTO publisher (publisherid, name, address) VALUES ('001', 'NWPU Publisher', 'YouYI Road');
INSERT INTO publisher (publisherid, name, address) VALUES ('002', 'XiDian University Publisher', 'Changan Road');
INSERT INTO publisher (publisherid, name, address) VALUES ('003', 'QingHua University Publisher', 'Beijing City');
INSERT INTO publisher (publisherid, name, address) VALUES ('004', 'Zhejiang University Publisher', 'Zhejiang Province');

--Insert data into book.
INSERT INTO book (primaryauthor, title, isbn, publisherid, edtion, dateofpublishcation, price, bookdescription)
       VALUES ('Brookshear, J. Glenn', 'Computer system concepts for real life', 'S782.2-2', '001', 'The first edtion', '2008-03-02', 89, 'About syestem');
INSERT INTO book (primaryauthor, title, isbn, publisherid, edtion, dateofpublishcation, price, bookdescription) 
       VALUES ('Parsons, June Jamrich.', 'Tomorow''s Computers', 'S789.2-2', '004', 'The first edtion', '2008-02-05', 89, 'About software');
INSERT INTO book (primaryauthor, title, isbn, publisherid, edtion, dateofpublishcation, price, bookdescription) 
       VALUES ('Stair, Ralph M', 'Exploring tomorrow''s technology', 'S783.2-2 ', '002', 'The first edtion', '2008-03-05', 92, 'About computer');
INSERT INTO book (primaryauthor, title, isbn, publisherid, edtion, dateofpublishcation, price, bookdescription) 
       VALUES ('Pfaffenberger, Bryan', 'Computers in your future', 'S682.2-2', '003', 'The first edtion', '2008-01-05', 108, 'About computer');

--Write the SQL statement to retrieve the title and price of all books published by either of two publishers 
SELECT  Title, Price
FROM    Book
WHERE   PublisherID in(SELECT PublisherID from Publisher where Name='NWPU Publisher' OR Name='QingHua University Publisher')

--Write the SQL statement to retrieve the Publisher name of a particular book title 
SELECT Name
FROM PUBLISHER 
WHERE PublisherId in(SELECT PublisherID from Book where Title='Tomorow''s Computers')