📄 installunipubs2
字号:
'business', '1389', $19.99, $5000.00, 4095,
"An overview of available database systems with emphasis on common business applications. Illustrated.",
'06/12/86', 1)
go
insert titles
values ('PS7777', 'Emotional Security: A New Algorithm',
'psychology', '0736', $7.99, $4000.00, 3336,
"Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized.",
'06/12/88', 1)
go
insert titles
values ('PS3333', 'Prolonged Data Deprivation: Four Case Studies',
'psychology', '0736', $19.99, $2000.00, 4072,
'What happens when the data runs dry? Searching evaluations of information-shortage effects on heavy users.',
'06/12/88', 1)
go
insert titles
values ('BU1111', 'Cooking with Computers: Surreptitious Balance Sheets',
'business', '1389', $11.95, $5000.00, 3876,
'Helpful hints on how to use your electronic resources to the best advantage.', '06/09/88', 1)
go
insert titles
values ('MC2222', 'Silicon Valley Gastronomic Treats',
'mod_cook', '0877', $19.99, $0.00, 2032,
'Favorite recipes for quick, easy, and elegant meals, tried and tested by people who never have time to eat, let alone cook.',
'06/09/89', 1)
go
insert titles
values ('TC7777', 'Sushi, Anyone?',
'trad_cook', '0877', $14.99, $8000.00, 4095,
'Detailed instructions on improving your position in life by learning how to make authentic Japanese sushi in your spare time. 5-10% increase in number of friends per recipe reported from beta test.',
'06/12/87', 1)
go
insert titles
values ('TC4203', 'Fifty Years in Buckingham Palace Kitchens',
'trad_cook', '0877', $11.95, $4000.00, 15096,
"More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes.",
'06/12/85', 1)
go
insert titles
values ('PC1035', 'But Is It User Friendly?',
'popular_comp', '1389', $22.95, $7000.00, 8780,
"A survey of software for the naive user, focusing on the 'friendliness' of each.",
'06/30/86', 1)
go
insert titles
values('BU2075', 'You Can Combat Computer Stress!',
'business', '0736', $2.99, $10125.00, 18722,
'The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.',
'06/30/85', 1)
go
insert titles
values('PS2091', 'Is Anger the Enemy?',
'psychology', '0736', $10.95, $2275.00, 2045,
'Carefully researched study of the effects of strong emotions on the body. Metabolic charts included.',
'06/15/89', 1)
go
insert titles
values('PS2106', 'Life Without Fear',
'psychology', '0736', $7.00, $6000.00, 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/90', 1)
go
insert titles
values('MC3021', 'The Gourmet Microwave',
'mod_cook', '0877', $2.99, $15000.00, 22246,
'Traditional French gourmet recipes adapted for modern microwave cooking.',
'06/18/85', 1)
go
insert titles
values('TC3218',
'Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean',
'trad_cook', '0877', $20.95, $7000.00, 375,
'Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.',
'10/21/90', 1)
go
insert titles (title_id, title, pub_id, contract)
values('MC3026', 'The Psychology of Computer Cooking', '0877', 0)
go
insert titles
values ('BU7832', 'Straight Talk About Computers',
'business', '1389', $19.99, $5000.00, 4095,
'Annotated analysis of what computers can do for you: a no-hype guide for the critical user.',
'06/22/87', 1)
go
insert titles
values('PS1372',
'Computer Phobic and Non-Phobic Individuals: Behavior Variations',
'psychology', '0877', $21.59, $7000.00, 375,
'A must for the specialist, this book examines the difference between those who hate and fear computers and those who think they are swell.',
'10/21/90', 1)
go
insert titles (title_id, title, type, pub_id, notes, contract)
values('PC9999', 'Net Etiquette', 'popular_comp', '1389',
'A must-read for computer conferencing debutantes!', 0)
go
dump transaction unipubs2 with truncate_only
go
insert stores
values('7066', "Barnum's", '567 Pasadena Ave.', 'Tustin', 'CA',
'USA', '92789', 'Net 30')
go
insert stores
values('7067', 'News & Brews', '577 First St.', 'Los Gatos', 'CA',
'USA', '96745', 'Net 30')
go
insert stores
values('7131', 'Doc-U-Mat: Quality Laundry and Books', '24-A Avrogado Way',
'Remulade', 'WA', 'USA', '98014', 'Net 60')
go
insert stores
values('8042', 'Bookbeat', '679 Carson St.', 'Portland', 'OR', 'USA',
'89076', 'Net 30')
go
insert stores
values('6380', "Eric the Read Books", '788 Catamaugus Ave.', 'Seattle', 'WA',
'USA', '98056', 'Net 60')
go
insert stores
values('7896', 'Fricative Bookshop', '89 Madison St.', 'Fremont', 'CA',
'USA', '90019', 'Net 60')
go
insert stores
values('5023', "Thoreau Reading Discount Chain", '20435 Walden Expressway',
'Concord', 'MA',
'USA', '01776', 'Net 60')
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('Huge Volume Discount', NULL, 1001, NULL, 10)
go
insert discounts
values('Customer Discount', '8042', NULL, NULL, 5.0)
go
insert blurbs values ("486-29-1786", "If Chastity Locksley didn't
exist, this troubled world would have created her! Not only did she
master the mystic secrets of inner strength to conquer adversity when
she encountered it in life, but, after ""reinventing herself"", as she
says, by writing ""Emotional Security: A New Algorithm"" following the
devastating loss of her cat Old Algorithm, she also founded Publish or
Perish, the page-by-page, day-by-day, write-yourself-to-wellness
encounter workshops franchise empire, the better to share her inspiring
discoveries with us all. Her ""Net Etiquette,"" a brilliant social
treatise in its own right and a fabulous pun, is the only civilized
alternative to the gross etiquette often practiced on the public
networks.")
insert blurbs values ("648-92-1872", "A chef's chef and a raconteur's
raconteur, Reginald Blotchet-Halls calls London his second home. ""Th'
palace kitchen's me first 'ome, act'lly!"" Blotchet-Halls' astounding
ability to delight our palates with palace delights is matched only by
his equal skill in satisfying our perpetual hunger for delicious
back-stairs gossip by serving up tidbits and entrees literally fit for
a king!") insert blurbs values ("998-72-3567", "Albert Ringer was born
in a trunk to circus parents, but another kind of circus trunk played a
more important role in his life years later. He grew up as an
itinerant wrestler and roustabout in the reknowned Ringer Brothers and
Betty and Bernie's Circus. Once known in the literary world only as
Anne Ringer's wrestling brother, he became a writer while recuperating
from a near-fatal injury received during a charity benefit bout with a
gorilla. ""Slingshotting"" himself from the ring ropes, Albert flew
over the gorilla's head and would have landed head first on the
concrete. He was saved from certain death by Nana, an elephant he
befriended as a child, who caught him in her trunk. Nana held him so
tightly that three ribs cracked and he turned blue from lack of
oxygen. ""I was delirious. I had an out-of-body experience! My whole
life passed before me. I promised myself 'If I get through this, I'll
use my remaining time to share what I learned out there.' I owe it all
to Nana!""")
insert blurbs values ("899-46-2035", "Anne Ringer ran away from the
circus as a child. A university creative writing professor and her
family took Anne in and raised her as one of their own. In this warm
and television-less setting she learned to appreciate the great
classics of literature. The stream of aspiring and accomplished
writers that flowed constantly through the house confirmed her
repudiation of the circus family she'd been born into: ""Barbarians!""
The steadily growing recognition of her literary work was, to her,
vindication. When her brother's brush with death brought them together
after many years, she took advantage of life's crazy chance thing and
broke the wall of anger that she had constructed to separate them.
Together they wrote, ""Is Anger the Enemy?"" an even greater
blockbuster than her other collaborative work, with Michel DeFrance,
""The Gourmet Microwave.""")
insert blurbs values ("672-71-3249", "They asked me to write about
myself and my book, so here goes: I started a restaurant called ""de
Gustibus"" with two of my friends. We named it that because you really
can't discuss taste. We're very popular with young business types
because we're young business types ourselves. Whenever we tried to go
out to eat in a group we always got into these long tiresome
negotiations: ""I just ate Italian,"" or ""I ate Greek yesterday,"" or
I NEVER eat anything that's not organic!"" Inefficient. Not what
business needs today. So, it came to us that we needed a restaurant we
could all go to every day and not eat the same thing twice in a row
maybe for a year! We thought, ""Hey, why make people choose one kind
of restaurant over another, when what they really want is a different
kind of food?"" At de Gustibus you can eat Italian, Chinese, Japanese,
Greek, Russian, Tasmanian, Iranian, and on and on all at the same
time. You never have to choose. You can even mix and match! We just
pooled our recipes, opened the doors, and never looked back. We're a
big hit, what can I say? My recipes in ""Sushi, Anyone?"" are used at
de Gustibus. They satisfy crowds for us every day. They will work for
you, too. Period!")
insert blurbs values ("409-56-7008", "Bennet was the classic too-busy
executive. After discovering computer databases he now has the time to
run several successful businesses and sit on three major corporate
boards. Bennet also donates time to community service organizations.
Miraculously, he also finds time to write and market executive-oriented
in-depth computer hardware and software reviews. ""I'm hyperkinetic,
so being dynamic and fast-moving is a piece of cake. But being
organized isn't easy for me or for anyone I know. There's just one
word for that: `databases!' Databases can cure you or kill you. If you
get the right one, you can be like me. If you get the wrong one, watch
out. Read my book!""")
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 unique clustered index salesind
on sales (stor_id, ord_num)
go
create nonclustered index titleidind
on salesdetail (title_id)
go
create nonclustered index salesdetailind
on salesdetail (stor_id, ord_num)
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 title_idrule
as
@title_id like "BU[0-9][0-9][0-9][0-9]" or
@title_id like "[MT]C[0-9][0-9][0-9][0-9]" or
@title_id like "P[SC][0-9][0-9][0-9][0-9]" or
@title_id like "[A-Z][A-Z]xxxx" or
@title_id like "[A-Z][A-Z]yyyy"
/*valid values: BU, MC, TC, PS, PC + 4 digits
**or
**any two uppercase letters followed by x's or y's
*/
go
sp_bindrule title_idrule, "titles.title_id"
go
sp_bindrule title_idrule, "salesdetail.title_id"
go
create trigger deltitle
on titles
for delete
as
if (select count(*) from deleted, salesdetail
where salesdetail.title_id = deleted.title_id) >0
begin
rollback transaction
print "You can't delete a title with sales."
end
go
create trigger totalsales_trig
on salesdetail
for insert, update, delete
as
/* Save processing: return if there are no rows affected */
if @@rowcount = 0
begin
return
end
/* add all the new values */
/* use isnull: a null value in the titles table means
** "no sales yet" not "sales unknown"
*/
update titles
set total_sales = isnull(total_sales, 0) + (select sum(qty)
from inserted
where titles.title_id = inserted.title_id)
where title_id in (select title_id from inserted)
/* remove all values being deleted or updated */
update titles
set total_sales = isnull(total_sales, 0) - (select sum(qty)
from deleted
where titles.title_id = deleted.title_id)
where title_id in (select title_id from deleted)
go
create view titleview
as
select title, au_ord, au_lname,
price, total_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 procs for use by APT Sales Example */
create proc history_proc @stor_id char(4)
as
select date,
sales.ord_num,
qty,
salesdetail.title_id,
discount,
price,
total = qty * price * (1 - discount/100)
from sales, salesdetail, titles
where sales.stor_id = @stor_id
and sales.ord_num = salesdetail.ord_num
and titles.title_id = salesdetail.title_id
order by date desc, sales.ord_num
go
create proc discount_proc as
select discounttype, stor_id, lowqty, highqty, discount
from discounts
go
create proc storename_proc
@stor_name univarchar(40)
as
declare @lowered_name univarchar(40)
select @lowered_name = lower(@stor_name) + "%"
select stor_name,
stor_id,
stor_address,
city,
state,
postalcode,
country
from stores
where lower(stor_name) like @lowered_name
return @@rowcount
go
create proc storeid_proc
@stor_id char(4)
as
select stor_name,
stor_id,
stor_address,
city,
state,
postalcode,
country
from stores
where stor_id = @stor_id
return @@rowcount
go
create proc insert_sales_proc
@stor_id char(4),
@ordernum univarchar(20),
@orderdate varchar(40)
as
insert sales
values(@stor_id, @ordernum, @orderdate)
go
create proc insert_salesdetail_proc
@stor_id char(4),
@ord_num varchar(20),
@title_id tid,
@qty smallint,
@discount float
as
insert salesdetail
values(@stor_id, @ord_num, @title_id, @qty, @discount)
go
create proc title_proc ( @title univarchar(80))
as
select @title = lower( @title ) + "%"
select title, title_id, price
from titles
where lower(title) like @title
return @@rowcount
go
create proc titleid_proc ( @title_id univarchar(80))
as
select @title_id = lower( @title_id ) + "%"
select title, title_id, price
from titles
where lower(title_id) like @title_id
return @@rowcount
go
grant exec on history_proc to public
grant exec on discount_proc to public
grant exec on titleid_proc to public
grant exec on storename_proc to public
grant exec on storeid_proc to public
grant exec on insert_sales_proc to public
grant exec on insert_salesdetail_proc to public
grant exec on title_proc to public
go
grant create procedure to public
go
if exists (select * from master.dbo.sysdatabases
where name = "unipubs2")
begin
execute sp_adduser guest
end
go
if exists (select * from master.dbo.sysdatabases
where name = "unipubs2")
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 salesdetail to guest
grant all on roysched to guest
grant all on stores to guest
grant all on discounts to guest
grant all on au_pix to guest
grant all on blurbs to guest
grant exec on byroyalty to guest
grant exec on history_proc to guest
grant exec on discount_proc to guest
grant exec on titleid_proc to guest
grant exec on storename_proc to guest
grant exec on storeid_proc to guest
grant exec on insert_sales_proc to guest
grant exec on insert_salesdetail_proc to guest
grant exec on title_proc 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
use sybsystemprocs
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
dump transaction unipubs2 with truncate_only
go
use unipubs2
go
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -