(book number char(9) primary key,
Book title character (40),
Author number character (40),
Price currency check (price >; =0),
Press the numeric character (40)
);
Create a table author
(number char( 10) primary key,
Name characters (40),
Telephone charge (1 1)
);
Create a form publishing house
(number char(40) primary key,
Name of publishing house char(50),
Address varchar( 100)
);
Insert into a book
Values ('1',' Yi Shu','160121','5',' 2016000.
Insert into a book
Values ('2',' Volume II','1601121','20',' 20160000/kloc.
Insert into a book
Values ('3',' Volume III','1601121','39',' 20 1600004').
Insert into a book
Values ('4',' Volume IV','160112102',' 30',' 20 1600002')
Insert into a book
Value ('5',' Book 5','160112102',' 32',' 20 1600002')
Insert into a book
Value ('6',' Book 6','160112102',' 37',' 20 1600004')
Insert into a book
Values ('7',' Book 7','1601103',' 18',' 20 1600003').
Insert into a book
Values ('8',' Book 8','160112103',' 9',' 20 1600003')
Insert into a book
Values ('9',' Book 9','160112103',' 48',' 20 1600004').
Insert into a book
Values ('10',' Book 10','16012104',' 9',' 2010.
Insert into a book
Value ('1 1',' Book 1 1','160112104','/kloc'.
Insert into a book
Value ('12','No. 12','16012104',' 52',' 20/kloc-0.
Insert author
Value ('16011210/','Mailu','186 * * 8369')
Insert author
Value ('160112102',' Tiansifu','186 * * 8695')
Insert author
Value ('160112103',' Zhang Hongxuan','186 * * 8003')
Insert author
Value ('160112104',' tension','186 * * 8004')
Insert printing machine
Value ('20 16000 1',' Examination Bookstore',' Chang Lidong')
Insert printing machine
Value ('20 1600002',' Examination Bookstore Branch',' Changlixi')
Insert printing machine
Value ('20 1600003',' Examination Bookstore Chain Store',' Changlinan')
Insert printing machine
Values ('20 1600004',' Higher Education Press',' Changlibei')
Insert printing machine
Values ('20 1600005',' Peking University Publishing House',' Beijing')
go to
Create a professional publishing house
@ PressNo。 char(40)
be like
-1. Display news information
IF (@ Publisher number is not available (select a number from the publisher))
begin
Printing error: there is no such publishing house! ;
Return;
end
other
begin
Select *
From the publishing house
Where number = @ Press number
end
-2. If there is no published book, delete the record.
IF (select COUNT) from book, publisher where book. Publisher number = @ Publisher number) =0.
begin
delete
From the publishing house
Where number = @ Press number
Print' Publisher has been deleted!' ;
Return;
end
other
begin
Printing error: the publishing house has published books! ;
end
If the average price of this publishing house is lower than RMB, the price of all books will increase by%
IF(select avg(price)from books where books。 PressNo. = @ PressNo.) < 20
begin
Update books
Set price = price * 1. 1.
Where are the books? Press number = @ Press number;
end
other
Print ('error: the average price of this publishing house exceeds RMB');
-Enter the publisher number.
exec pro _ Publishing House ' @ publisher SnO '