createdb.sql

answer to sd7 Exercise1

DROP TABLE Publisher CASCADE;
DROP TABLE Book CASCADE;

CREATE TABLE Publisher(
	PublisherID varchar(40),
	Name varchar(200),
	Address varchar(200),
	PRIMARY KEY (PublisherID)
);

CREATE TABLE Book(
	PrimaryAuthor varchar(200),
	Title varchar(200),
	ISBN varchar(40),
	PublisherID varchar(40),
	Edition varchar(10),
	DateOfPublication  date,
	Price varchar(40),
	BookDescription varchar(1000),
	PRIMARY KEY (ISBN),
	FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)
	
);

insert into Publisher values('001','POST&TELECOM PRESS','NO.14Xizhaosi St.Chongzuo District,Beijing,China');

insert into Publisher values('002','Addison Wesley','77 Mellon St. New York,NY 10056-0009');

insert into Publisher values('003','McGraw Hill','55 Water St. New York, NY 10014-0004');

insert into Publisher values('004','Prentice Hall','99 Narcissus St. New York,NY 10029-0007');

insert into Book values('Scott W.Ambler,Pramodkumar J.Sadalage','Refactoring Databases: Evolutionary Database Design','0321293533','002','1-1','2007-06-01'::date,'65.00','This comprehensive guide and reference helps you overcome the practical obstacles to refactoring real-world databases by covering every fundamental concept underlying database refactoring. Using start-to-finish examples, the authors walk you through refactoring simple standalone database applications as well as sophisticated multi-application scenarios. You will master every task involved in refactoring database schemas, and discover best practices for deploying refactorings in even the most complex production environments.');

insert into Book values('Robert J. Calvin','EMBA: Entrepreneurial Management','0071450920','003','2-1','2004-08-17'::date,'127.00','From spotting the right opportunity and writing a successful business plan to raising capital, enhancing productivity, and building customer loyalty, Entrepreneurial Management helps budding entrepreneurs master the planning and growth issues required to make any new business a success.');

insert into Book values('James Hasik,Stacey Rudnick,Ryan Hackney','McGraw-Hill GMAT with CD-ROM, 2009 Edition (Mcgraw Hill Gmat)','0071598448','003','1-1','2008-11-25'::date,'306.00','Completely revised and updated for 2009, McGraw-Hill GMAT brings all of McGraw-Hill business and education expertise to bear on helping students achieve the best score possible. It is packed with topic reviews, test taking strategies, up-to-theminute test information, and plenty of practice tests and drills. Authoritative and practical, it is the ideal tool to help GMAT takers get ready for test day.');

insert into Book values('Ramez Elmasri,Shamkant B. Navathe','Fundamentals of Database Systems','0321204484','004','4-1','2004-10-06'::date,'266.00','Fundamentals of Database Systems has become the world-wide leading textbook because it combines clear explanations of theory and design, broad coverage of models and real systems, and excellent examples with up-to-date introductions and modern database technologies. This book has been revised and updated to reflect the latest trends in technological and application development. This fourth edition expands on many of the most popular database topics, including SQL, security, and data mining along with an introduction to UML modeling and an entirely new chapter on XML and Internet databases.');



select title,price from Book where PublisherID in (select PublisherID from Publisher where Name ='Addison Wesley' or Name='McGraw Hill');

select Name from Publisher where PublisherID=(select PublisherID from Book where Title='Fundamentals of Database Systems');