createdb.sql

ssd7卡耐基教程

DROP TABLE Book;
/*Create table Book*/
CREATE TABLE Book 
(
  PrimaryAuthor varchar(20),
  Title varchar(50),
  ISBN varchar(50) NOT NULL,
  PublisherID integer,
  Edition varchar(10),
  DateOfPublication date,
  Price numeric(7,2),
  Description varchar(100),
  PRIMARY KEY (ISBN)
);

/*Insert Data for the table `book` */
insert  into Book values ('贾慧星','天下足球①','7-301-04815-6',1000,'edition1','2005-1-10','16.99','体育类图书');
insert  into Book values('沙朋','天下足球②','	7-301-04815-7',1001,'edition2','2006-1-10','

17.99','体育类图书');
insert  into Book values('白鹏','天下足球③','7-301-04815-

8',1002,'edition3','2007-1-10','18.99','体育类图书');
insert  into Book values('郑楠辉','天下足球④','7-301-04815-9',1003,'edition4','2008-1-10','

19.99','体育类图书');


DROP TABLE PUBLISHER;
/*创建表PUBLISHER*/
CREATE TABLE PUBLISHER(  
PublisherID integer NOT NULL , --取值唯一，不许取空值
PublisherName VARCHAR(50), 
Address VARCHAR(100),
primary key (PublisherID));

insert into PUBLISHER
values('1000', '陕西第一出版社', '西安市沣惠南路31号' );
insert into PUBLISHER
values('1001', '陕西第二出版社', '西安市太白路20号' );
insert into PUBLISHER
values('1002', '陕西第三出版社', '西安市子午路55号' );
insert into PUBLISHER
values('1003', '陕西第四出版社', '西安市含光路66号' );
(
	select Book.title, Book.price
	from   Book, PUBLISHER
	where  Book.PublisherID = PUBLISHER.PublisherID and PUBLISHER.PublisherName = '陕西第一出版社'
)
union
(
	select Book.title, Book.price
	from   Book, PUBLISHER
	where  Book.PublisherID = PUBLISHER.PublisherID and PUBLISHER.PublisherName = '陕西第二出版社'
);


select PUBLISHER.PublisherName
from   Book, PUBLISHER
where  Book.title = '天下足球④' and Book.PublisherID = PUBLISHER.PublisherID