Liren Large Software Subsidy 5/05.iso/a/a063/4.img/SCRIPTS/INSTPUBS.SQL

Text File | 1992-05-17 | 22.4 KB | 949 lines

/* $Header:   S:/DBMS/OS2/SCRIPTS/PVCS/INSTPUBS.SQV   5.1   09 Mar 1992 18:22:30   YUMEI  $ */
/* has primary and foreign keys*/
set nocount on

if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    drop database pubs
end
go
print 'Creating the "pubs" database'
create database pubs
go
set dateformat mdy
go
use pubs
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    execute sp_addtype id, "varchar(11)", "not null"
    execute sp_addtype tid, "varchar(6)", "not null"
end
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    create table authors
    (au_id id,
    au_lname varchar(40) not null,
    au_fname varchar(20) not null,
    phone char(12),
    address varchar(40) null,
    city varchar(20) null,
    state char(2) null,
    zip char(5) null,
    contract bit)
end
go
grant select on authors to public
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    create table publishers
    (pub_id char(4) not null,
    pub_name varchar(40) null,
    city varchar(20) null,
    state char(2) null)
end
go
grant select on publishers to public
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    create table roysched
    (title_id tid,
    lorange int null,
    hirange int null,
    royalty int null)
end
go
grant select on roysched to public
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    create table sales
    (stor_id char(4),
    ord_num varchar(20),
    date datetime,
    qty smallint,
    payterms varchar(12),
    title_id tid)
end
go
grant select on sales to public
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    create table titleauthor
    (au_id id,
    title_id tid,
    au_ord tinyint null,
    royaltyper int null)
end
go
grant select on titleauthor to public
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    create table titles
    (title_id tid,
    title varchar(80) not null,
    type char(12),
    pub_id char(4) null,
    price money null,
    advance money null,
    royalty int null,
    ytd_sales int null,
    notes varchar(200) null,
    pubdate datetime)
end
go
grant select on titles to public
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    create table stores
    (stor_id char(4),
    stor_name varchar(40) null,
    stor_address varchar(40) null,
    city varchar(20) null,
    state char(2) null,
    zip char(5) null)
end
go
grant select on stores to public
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    create table discounts
    (discounttype   varchar(40) not null,
    stor_id         char(4) null,
    lowqty          smallint null,
    highqty         smallint null,
    discount        float)
end
go
grant select on discounts to public
go
create default typedflt
as "UNDECIDED"
go
sp_bindefault typedflt, "titles.type"
go
create default datedflt
as getdate()
go
sp_bindefault datedflt, "titles.pubdate"
go
create default phonedflt
as "UNKNOWN"
go
sp_bindefault phonedflt, "authors.phone"
go
insert authors
values('409-56-7008', 'Bennet', 'Abraham',
'415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
go
insert authors
values ('213-46-8915', 'Green', 'Marjorie',
'415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618', 1)
go
insert authors
values('238-95-7766', 'Carson', 'Cheryl',
'415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705', 1)
go
insert authors
values('998-72-3567', 'Ringer', 'Albert',
'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1)
go
insert authors
values('899-46-2035', 'Ringer', 'Anne',
'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1)
go
insert authors
values('722-51-5454', 'DeFrance', 'Michel',
'219 547-9982', '3 Balding Pl.', 'Gary', 'IN', '46403', 1)
go
insert authors
values('807-91-6654', 'Panteley', 'Sylvia',
'301 946-8853', '1956 Arlington Pl.', 'Rockville', 'MD', '20853', 1)
go
insert authors
values('893-72-1158', 'McBadden', 'Heather',
'707 448-4982', '301 Putnam', 'Vacaville', 'CA', '95688', 0)
go
insert authors
values('724-08-9931', 'Stringer', 'Dirk',
'415 843-2991', '5420 Telegraph Av.', 'Oakland', 'CA', '94609', 0)
go
insert authors
values('274-80-9391', 'Straight', 'Dean',
'415 834-2919', '5420 College Av.', 'Oakland', 'CA', '94609', 1)
go
insert authors
values('756-30-7391', 'Karsen', 'Livia',
'415 534-9219', '5720 McAuley St.', 'Oakland', 'CA', '94609', 1)
go
insert authors
values('724-80-9391', 'MacFeather', 'Stearns',
'415 354-7128', '44 Upland Hts.', 'Oakland', 'CA', '94612', 1)
go
insert authors
values('427-17-2319', 'Dull', 'Ann',
'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301', 1)
go
insert authors
values('672-71-3249', 'Yokomoto', 'Akiko',
'415 935-4228', '3 Silver Ct.', 'Walnut Creek', 'CA', '94595', 1)
go
insert authors
values('267-41-2394', "O'Leary", 'Michael',
'408 286-2428', '22 Cleveland Av. #14', 'San Jose', 'CA', '95128', 1)
go
insert authors
values('472-27-2349', 'Gringlesby', 'Burt',
'707 938-6445', 'PO Box 792', 'Covelo', 'CA', '95428', 3)
go
insert authors
values('527-72-3246', 'Greene', 'Morningstar',
'615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN', '37215', 0)
go
insert authors
values('172-32-1176', 'White', 'Johnson',
'408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025', 1)
go
insert authors
values('712-45-1867', 'del Castillo', 'Innes',
'615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', '48105', 1)
go
insert authors
values('846-92-7186', 'Hunter', 'Sheryl',
'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301', 1)
go
insert authors
values('486-29-1786', 'Locksley', 'Charlene',
'415 585-4620', '18 Broadway Av.', 'San Francisco', 'CA', '94130', 1)
go
insert authors
values('648-92-1872', 'Blotchet-Halls', 'Reginald',
'503 745-6402', '55 Hillsdale Bl.', 'Corvallis', 'OR', '97330', 1)
go
insert authors
values('341-22-1782', 'Smith', 'Meander',
'913 843-0462', '10 Mississippi Dr.', 'Lawrence', 'KS', '66044', 0)
go
insert publishers
values('0736', 'New Moon Books', 'Boston', 'MA')
go
insert publishers
values('0877', 'Binnet & Hardley', 'Washington', 'DC')
go
insert publishers
values('1389', 'Algodata Infosystems', 'Berkeley', 'CA')
go
insert roysched
values('BU1032', 0, 5000, 10)
go
insert roysched
values('BU1032', 5001, 50000, 12)
go
insert roysched
values('PC1035', 0, 2000, 10)
go
insert roysched
values('PC1035', 2001, 3000, 12)
go
insert roysched
values('PC1035', 3001, 4000, 14)
go
insert roysched
values('PC1035', 4001, 10000, 16)
go
insert roysched
values('PC1035', 10001, 50000, 18)
go
insert roysched
values('BU2075', 0, 1000, 10)
go
insert roysched
values('BU2075', 1001, 3000, 12)
go
insert roysched
values('BU2075', 3001, 5000, 14)
go
insert roysched
values('BU2075', 5001, 7000, 16)
go
insert roysched
values('BU2075', 7001, 10000, 18)
go
insert roysched
values('BU2075', 10001, 12000, 20)
go
insert roysched
values('BU2075', 12001, 14000, 22)
go
insert roysched
values('BU2075', 14001, 50000, 24)
go
insert roysched
values('PS2091', 0, 1000, 10)
go
insert roysched
values('PS2091', 1001, 5000, 12)
go
insert roysched
values('PS2091', 5001, 10000, 14)
go
insert roysched
values('PS2091', 10001, 50000, 16)
go
insert roysched
values('PS2106', 0, 2000, 10)
go
insert roysched
values('PS2106', 2001, 5000, 12)
go
insert roysched
values('PS2106', 5001, 10000, 14)
go
insert roysched
values('PS2106', 10001, 50000, 16)
go
insert roysched
values('MC3021', 0, 1000, 10)
go
insert roysched
values('MC3021', 1001, 2000, 12)
go
insert roysched
values('MC3021', 2001, 4000, 14)
go
insert roysched
values('MC3021', 4001, 6000, 16)
go
insert roysched
values('MC3021', 6001, 8000, 18)
go
insert roysched
values('MC3021', 8001, 10000, 20)
go
insert roysched
values('MC3021', 10001, 12000, 22)
go
insert roysched
values('MC3021', 12001, 50000, 24)
go
insert roysched
values('TC3218', 0, 2000, 10)
go
insert roysched
values('TC3218', 2001, 4000, 12)
go
insert roysched
values('TC3218', 4001, 6000, 14)
go
insert roysched
values('TC3218', 6001, 8000, 16)
go
insert roysched
values('TC3218', 8001, 10000, 18)
go
insert roysched
values('TC3218', 10001, 12000, 20)
go
insert roysched
values('TC3218', 12001, 14000, 22)
go
insert roysched
values('TC3218', 14001, 50000, 24)
go
insert roysched
values('PC8888', 0, 5000, 10)
go
insert roysched
values('PC8888', 5001, 10000, 12)
go
insert roysched
values('PC8888', 10001, 15000, 14)
go
insert roysched
values('PC8888', 15001, 50000, 16)
go
insert roysched
values('PS7777', 0, 5000, 10)
go
insert roysched
values('PS7777', 5001, 50000, 12)
go
insert roysched
values('PS3333', 0, 5000, 10)
go
insert roysched
values('PS3333', 5001, 10000, 12)
go
insert roysched
values('PS3333', 10001, 15000, 14)
go
insert roysched
values('PS3333', 15001, 50000, 16)
go
insert roysched
values('BU1111', 0, 4000, 10)
go
insert roysched
values('BU1111', 4001, 8000, 12)
go
insert roysched
values('BU1111', 8001, 10000, 14)
go
insert roysched
values('BU1111', 12001, 16000, 16)
go
insert roysched
values('BU1111', 16001, 20000, 18)
go
insert roysched
values('BU1111', 20001, 24000, 20)
go
insert roysched
values('BU1111', 24001, 28000, 22)
go
insert roysched
values('BU1111', 28001, 50000, 24)
go
insert roysched
values('MC2222', 0, 2000, 10)
go
insert roysched
values('MC2222', 2001, 4000, 12)
go
insert roysched
values('MC2222', 4001, 8000, 14)
go
insert roysched
values('MC2222', 8001, 12000, 16)
go
insert roysched
values('MC2222', 8001, 12000, 16)
go
insert roysched
values('MC2222', 12001, 20000, 18)
go
insert roysched
values('MC2222', 20001, 50000, 20)
go
insert roysched
values('TC7777', 0, 5000, 10)
go
insert roysched
values('TC7777', 5001, 15000, 12)
go
insert roysched
values('TC7777', 15001, 50000, 14)
go
insert roysched
values('TC4203', 0, 2000, 10)
go
insert roysched
values('TC4203', 2001, 8000, 12)
go
insert roysched
values('TC4203', 8001, 16000, 14)
go
insert roysched
values('TC4203', 16001, 24000, 16)
go
insert roysched
values('TC4203', 24001, 32000, 18)
go
insert roysched
values('TC4203', 32001, 40000, 20)
go
insert roysched
values('TC4203', 40001, 50000, 22)
go
insert roysched
values('BU7832', 0, 5000, 10)
go
insert roysched
values('BU7832', 5001, 10000, 12)
go
insert roysched
values('BU7832', 10001, 15000, 14)
go
insert roysched
values('BU7832', 15001, 20000, 16)
go
insert roysched
values('BU7832', 20001, 25000, 18)
go
insert roysched
values('BU7832', 25001, 30000, 20)
go
insert roysched
values('BU7832', 30001, 35000, 22)
go
insert roysched
values('BU7832', 35001, 50000, 24)
go
insert roysched
values('PS1372', 0, 10000, 10)
go
insert roysched
values('PS1372', 10001, 20000, 12)
go
insert roysched
values('PS1372', 20001, 30000, 14)
go
insert roysched
values('PS1372', 30001, 40000, 16)
go
insert roysched
values('PS1372', 40001, 50000, 18)
go
insert sales
values('7066', 'QA7442.3', '09/13/85', 75, 'On invoice','PS2091')
go
insert sales
values('7067', 'D4482', '09/14/85', 10, 'Net 60','PS2091')
go
insert sales
values('7131', 'N914008', '09/14/85', 20, 'Net 30','PS2091')
go
insert sales
values('7131', 'N914014', '09/14/85', 25, 'Net 30','MC3021')
go
insert sales
values('8042', '423LL922', '09/14/85', 15, 'On invoice','MC3021')
go
insert sales
values('8042', '423LL930', '09/14/85', 10, 'On invoice','BU1032')
go
insert sales
values('6380', '722a', '09/13/85', 3, 'Net 60','PS2091')
go
insert sales
values('6380', '6871', '09/14/85', 5, 'Net 60','BU1032')
go
insert sales
values('8042','P723', '03/11/88', 25, 'Net 30', 'BU1111')
go
insert sales
values('7896','X999', '02/21/88', 35, 'On invoice', 'BU2075')
go
insert sales
values('7896','QQ2299', '10/28/87', 15, 'Net 60', 'BU7832')
go
insert sales
values('7896','TQ456', '12/12/87', 10, 'Net 60', 'MC2222')
go
insert sales
values('8042','QA879.1', '5/22/87', 30, 'Net 30', 'PC1035')
go
insert sales
values('7066','A2976', '5/24/87', 50, 'Net 30', 'PC8888')
go
insert sales
values('7131','P3087a', '5/29/87', 20, 'Net 60', 'PS1372')
go
insert sales
values('7131','P3087a', '5/29/87', 25, 'Net 60', 'PS2106')
go
insert sales
values('7131','P3087a', '5/29/87', 15, 'Net 60', 'PS3333')
go
insert sales
values('7131','P3087a', '5/29/87', 25, 'Net 60', 'PS7777')
go
insert sales
values('7067','P2121', '6/15/87', 40, 'Net 30', 'TC3218')
go
insert sales
values('7067','P2121', '6/15/87', 20, 'Net 30', 'TC4203')
go
insert sales
values('7067','P2121', '6/15/87', 20, 'Net 30', 'TC7777')
go
insert titleauthor
values('409-56-7008', 'BU1032', 1, 60)
go
insert titleauthor
values('486-29-1786', 'PS7777', 1, 100)
go
insert titleauthor
values('486-29-1786', 'PC9999', 1, 100)
go
insert titleauthor
values('712-45-1867', 'MC2222', 1, 100)
go
insert titleauthor
values('172-32-1176', 'PS3333', 1, 100)
go
insert titleauthor
values('213-46-8915', 'BU1032', 2, 40)
go
insert titleauthor
values('238-95-7766', 'PC1035', 1, 100)
go
insert titleauthor
values('213-46-8915', 'BU2075', 1, 100)
go
insert titleauthor
values('998-72-3567', 'PS2091', 1, 50)
go
insert titleauthor
values('899-46-2035', 'PS2091', 2, 50)
go
insert titleauthor
values('998-72-3567', 'PS2106', 1, 100)
go
insert titleauthor
values('722-51-5454', 'MC3021', 1, 75)
go
insert titleauthor
values('899-46-2035', 'MC3021', 2, 25)
go
insert titleauthor
values('807-91-6654', 'TC3218', 1, 100)
go
insert titleauthor
values('274-80-9391', 'BU7832', 1, 100)
go
insert titleauthor
values('427-17-2319', 'PC8888', 1, 50)
go
insert titleauthor
values('846-92-7186', 'PC8888', 2, 50)
go
insert titleauthor
values('756-30-7391', 'PS1372', 1, 75)
go
insert titleauthor
values('724-80-9391', 'PS1372', 2, 25)
go
insert titleauthor
values('724-80-9391', 'BU1111', 1, 60)
go
insert titleauthor
values('267-41-2394', 'BU1111', 2, 40)
go
insert titleauthor
values('672-71-3249', 'TC7777', 1, 40)
go
insert titleauthor
values('267-41-2394', 'TC7777', 2, 30)
go
insert titleauthor
values('472-27-2349', 'TC7777', 3, 30)
go
insert titleauthor
values('648-92-1872', 'TC4203', 1, 100)
go
insert titles
values ('PC8888', 'Secrets of Silicon Valley',
'popular_comp', '1389', $20.00, $8000.00, 10, 4095,
"Muckraking reporting on the world's largest computer hardware and software manufacturers.",
'06/12/85')
go
insert titles
values ('BU1032', "The Busy Executive's Database Guide",
'business', '1389', $19.99, $5000.00, 10, 4095,
"An overview of available database systems with emphasis on common business applications.  Illustrated.",
'06/12/85')
go
insert titles
values ('PS7777', 'Emotional Security: A New Algorithm',
'psychology', '0736', $7.99, $4000.00, 10, 3336,
"Protecting yourself and your loved ones from undue emotional stress in the modern world.  Use of computer and nutritional aids emphasized.",
'06/12/85')
go
insert titles
values ('PS3333', 'Prolonged Data Deprivation: Four Case Studies',
'psychology', '0736', $19.99, $2000.00, 10, 4072,
'What happens when the data runs dry?  Searching evaluations of information-shortage effects.',
'06/12/85')
go
insert titles
values ('BU1111', 'Cooking with Computers: Surreptitious Balance Sheets',
'business', '1389', $11.95, $5000.00, 10, 3876,
'Helpful hints on how to use your electronic resources to the best advantage.', '06/09/85')
go
insert titles
values ('MC2222', 'Silicon Valley Gastronomic Treats',
'mod_cook', '0877', $19.99, $0.00, 12, 2032,
'Favorite recipes for quick, easy, and elegant meals.',
'06/09/85')
go
insert titles
values ('TC7777', 'Sushi, Anyone?',
'trad_cook', '0877', $14.99, $8000.00, 10, 4095,
'Detailed instructions on how to make authentic Japanese sushi in your spare time.',
'06/12/85')
go
insert titles
values ('TC4203', 'Fifty Years in Buckingham Palace Kitchens',
'trad_cook', '0877', $11.95, $4000.00, 14, 15096,
"More anecdotes from the Queen's favorite cook describing life among English royalty.  Recipes, techniques, tender vignettes.",
'06/12/85')
go
insert titles
values ('PC1035', 'But Is It User Friendly?',
'popular_comp', '1389', $22.95, $7000.00, 16, 8780,
"A survey of software for the naive user, focusing on the 'friendliness' of each.",
'06/30/85')
go
insert titles
values('BU2075', 'You Can Combat Computer Stress!',
'business', '0736', $2.99, $10125.00, 24, 18722,
'The latest medical and psychological techniques for living with the electronic office.  Easy-to-understand explanations.',
'06/30/85')
go
insert titles
values('PS2091', 'Is Anger the Enemy?',
'psychology', '0736', $10.95, $2275.00, 12, 2045,
'Carefully researched study of the effects of strong emotions on the body.  Metabolic charts included.',
'06/15/85')
go
insert titles
values('PS2106', 'Life Without Fear',
'psychology', '0736', $7.00, $6000.00, 10, 111,
'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience.  Sample menus included, exercise video available separately.',
'10/05/85')
go
insert titles
values('MC3021', 'The Gourmet Microwave',
'mod_cook', '0877', $2.99, $15000.00, 24, 22246,
'Traditional French gourmet recipes adapted for modern microwave cooking.',
'06/18/85')
go
insert titles
values('TC3218',
'Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean',
'trad_cook', '0877', $20.95, $7000.00, 10, 375,
'Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.',
'10/21/85')
go
insert titles (title_id, title, pub_id)
values('MC3026', 'The Psychology of Computer Cooking', '0877')
go
insert titles
values ('BU7832', 'Straight Talk About Computers',
'business', '1389', $19.99, $5000.00, 10, 4095,
'Annotated analysis of what computers can do for you: a no-hype guide for the critical user.',
'06/22/85')
go
insert titles
values('PS1372',
'Computer Phobic and Non-Phobic Individuals: Behavior Variations',
'psychology', '0877', $21.59, $7000.00, 10, 375,
'A must for the specialist, this book examines the difference between those who hate and fear computers and those who don''t.',
'10/21/85')
go
insert titles (title_id, title, type, pub_id, notes)
values('PC9999', 'Net Etiquette', 'popular_comp', '1389',
'A must-read for computer conferencing.')
go
insert stores
values('7066',"Barnum's",'567 Pasadena Ave.','Tustin','CA','92789')
go
insert stores
values('7067','News & Brews','577 First St.','Los Gatos','CA','96745')
go
insert stores
values('7131','Doc-U-Mat: Quality Laundry and Books','24-A Avrogado Way','Remulade','WA','98014')
go
insert stores
values('8042','Bookbeat','679 Carson St.','Portland','OR','89076')
go
insert stores
values('6380',"Eric the Read Books",'788 Catamaugus Ave.','Seattle','WA','98056')
go
insert stores
values('7896','Fricative Bookshop','89 Madison St.','Fremont','CA','90019')
go
insert discounts
values('Initial Customer', NULL, NULL, NULL, 10.5)
go
insert discounts
values('Volume Discount', NULL, 100, 1000, 6.7)
go
insert discounts
values('Customer Discount', '8042', NULL, NULL, 5.0)
go
execute sp_primarykey titles, title_id
execute sp_primarykey titleauthor, au_id, title_id
execute sp_primarykey authors, au_id
execute sp_primarykey publishers, pub_id
execute sp_primarykey roysched, title_id
execute sp_primarykey sales, stor_id, title_id, ord_num
execute sp_primarykey stores, stor_id
execute sp_primarykey discounts, discounttype, stor_id     /* problem? */
go
execute sp_foreignkey titleauthor, titles, title_id
execute sp_foreignkey titleauthor, authors, au_id
execute sp_foreignkey roysched, titles, title_id
execute sp_foreignkey sales, titles, title_id
execute sp_foreignkey sales, stores, stor_id
execute sp_foreignkey titles, publishers, pub_id
execute sp_foreignkey discounts, stores, stor_id
go

create unique clustered index pubind
on publishers (pub_id)
go
create unique clustered index auidind
on authors (au_id)
go
create nonclustered index aunmind
on authors (au_lname, au_fname)
go
create unique clustered index titleidind
on titles (title_id)
go
create nonclustered index titleind
on titles (title)
go
create unique clustered index taind
on titleauthor (au_id, title_id)
go
create nonclustered index auidind
on titleauthor (au_id)
go
create nonclustered index titleidind
on titleauthor (title_id)
go
create nonclustered index titleidind
on sales (title_id)
go
create nonclustered index titleidind
on roysched (title_id)
go
create rule pub_idrule
as @pub_id in ("1389", "0736", "0877", "1622", "1756")
or @pub_id like "99[0-9][0-9]"
go
sp_bindrule pub_idrule, "publishers.pub_id"
go
create rule ziprule
as @zip like "[0-9][0-9][0-9][0-9][0-9]"
go
sp_bindrule ziprule, "authors.zip"
go
create trigger deltitle
on titles
for delete
as
if (select count(*) from deleted, sales
where sales.title_id = deleted.title_id) >0
begin
    rollback transaction
    print "You can't delete a title with sales."
end
go
create view titleview
as
select title, au_ord, au_lname,
price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
go
create procedure byroyalty @percentage int
as
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
go
grant execute on byroyalty to public
go
create procedure reptq1 as
select pub_id, title_id, price, pubdate
from titles
where price is not null
order by pub_id
compute avg(price) by pub_id
compute avg(price)
go
grant execute on reptq1 to public
go
create procedure reptq2 as
select type, pub_id, titles.title_id, au_ord,
 Name = substring (au_lname, 1,15),
 ytd_sales
from titles, authors, titleauthor
where titles.title_id = titleauthor.title_id
and authors.au_id = titleauthor.au_id
and pub_id is not null
order by pub_id, type
compute avg(ytd_sales) by pub_id, type
compute avg(ytd_sales) by pub_id
go
grant execute on reptq2 to public
go
create procedure reptq3 @lolimit money, @hilimit money,
@type char(12)
as
select pub_id, type, title_id, price
from titles
where price >@lolimit and price <@hilimit
 and type = @type or type like "%cook%"
order by pub_id, type
compute count(title_id) by pub_id, type
go
grant execute on reptq3 to public
go
grant create procedure to public
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    execute sp_adduser guest
end
go
if exists (select * from master.dbo.sysdatabases
        where name = "pubs")
begin
    grant all on publishers to guest
    grant all on titles to guest
    grant all on authors to guest
    grant all on titleauthor to guest
    grant all on sales to guest
    grant all on roysched to guest
    grant all on stores to guest
    grant all on discounts to guest
    grant exec on byroyalty to guest
    grant create table to guest
    grant create view to guest
    grant create rule to guest
    grant create default to guest
    grant create procedure to guest
end
go
checkpoint
go
use master
go
grant exec on sp_bindefault to guest
grant exec on sp_unbindefault to guest
grant exec on sp_bindrule to guest
grant exec on sp_unbindrule to guest
grant exec on sp_addtype to guest
grant exec on sp_droptype to guest
grant exec on sp_spaceused to guest
grant exec on sp_help to guest
grant exec on sp_helpgroup to guest
grant exec on sp_helpindex to guest
grant exec on sp_helprotect to guest
go
checkpoint
go