createdb.sql

ssd7 练习1 92分的 不是完美的 自己改进哈

CREATE TABLE PUBLISHER    /*创建表PUBLISHER*/

(
  PublisherID    VARCHAR(20) NOT NULL UNIQUE, /*PublisherID的含义是出版社的ID*/
   
  PublisherName  VARCHAR(50) UNIQUE,  /*PublisherName的含义是出版社的名称*/
   
  Address        VARCHAR(100),  /*Address的含义是出版社的地址*/
  
  PRIMARY KEY (PublisherID)

);




CREATE TABLE BOOK         /*创建表BOOK*/

(         
 
  Primary_Author          VARCHAR(50),  /*Primary Author的含义是书的作者的名字*/
  
  Title                   VARCHAR(50)  UNIQUE,  /*Title的含义是书名*/
 
  ISBN		          VARCHAR(50) NOT NULL UNIQUE, /*ISBN的含义是国际标准书号*/
 
  PublisherID	          VARCHAR(20) not null,  /*PublisherID的含义是出版社的ID*/
  
  Edition	          VARCHAR(50),  /* Edition的含义是书的编辑的名字*/
  
  Date_of_Publication     DATE,  /*此系统DATE数据类型命名为DATE*/
 
  Price                   NUMERIC(4,2), /*Price 的含义是书的价格*/
 
  Book_Description        VARCHAR(200), /* Book_Description 的含义是书内容的描述*/
 
  PRIMARY KEY(ISBN),
 
  FOREIGN KEY(PublisherID) REFERENCES PUBLISHER(PublisherID)

);




INSERT INTO  PUBLISHER(PublisherID,PublisherName,Address)

values('100084', '清华大学出版社', '北京清华大学学研大厦' );

INSERT INTO PUBLISHER(PublisherID,PublisherName,Address)

values('100037', '机械工业出版社', '北京市西城区百万庄大街22号' );

INSERT INTO PUBLISHER(PublisherID,PublisherName,Address)

values('100044', '中国水利水电出版社', '北京市三里河路6号' );

INSERT INTO PUBLISHER(PublisherID,PublisherName,Address)

values('710071', '西安电子科技大学出版社', '西安市太白南路2号' );




INSERT INTO BOOK(Primary_Author,Title,ISBN,PublisherID,Edition,Date_of_Publication  ,Price,Book_Description)

values('Karen Carlisi.Susana Christie', '博采英语', '7-302-04345-0', '100084', '2001年6月第1版', '2006-10-1', 26.50, '有趣味的原版听力材料 ');

INSERT INTO BOOK(Primary_Author,Title,ISBN,PublisherID,Edition,Date_of_Publication  ,Price,Book_Description)
values('王雷', 
'编译原理课程设计', '7-111-15877-6', '100037', '2005年3月第1版', '2005-3-1', 23.00,'分析一个真正实用的现代编译系统 ');

INSERT INTO BOOK(Primary_Author,Title,ISBN,PublisherID,Edition,Date_of_Publication  ,Price,Book_Description)
values('阎菲', 'Java程序设计教程', '7-5084-1798-4', '100044', '2004年2月第1版', '2005-4-1', 27.00	, '深入浅出地介绍Java语言');

INSERT INTO BOOK(Primary_Author,Title,ISBN,PublisherID,Edition,Date_of_Publication  ,Price,Book_Description)
values('汤子瀛', '计算机操作系统', '7-5606-0496-X/TP.0232', '710071', '2001年8月第2版',	'2005-5-1', 24.00, '介绍操作系统的相关知识');


	


  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 = 'Java程序设计教程' AND BOOK.PublisherID = PUBLISHER.PublisherID;