Question
use master go drop database PublisherTitleAuthor go create database PublisherTitleAuthor go use PublisherTitleAuthor go create table authors ( AuthorID char(11) not null primary key, AuthorLastName
use master
go
drop database PublisherTitleAuthor
go
create database PublisherTitleAuthor
go
use PublisherTitleAuthor
go
create table authors
(
AuthorID char(11) not null primary key,
AuthorLastName varchar(40) not null,
AuthorFirstName varchar(20) not null,
Phone char(12) null,
AuthorAddress varchar(40) null,
City varchar(20) null,
AuthorState char(2) null,
Zip char(5) null
)
go
create table publishers
(
PublisherID char(4) not null primary key,
PublisherName varchar(40) null,
PublisherAddress varchar(40) null,
City varchar(20) null,
PublisherState char(2) null
)
go
create table titles
(
TitleID char(6) not null primary key,
Title varchar(80) not null,
Genre char(12) null,
PublisherID char(4) null
references publishers(publisherid),
Price money null,
Advance money null,
ProjectedSales int null,
Contract bit not null,
Notes varchar(200) null,
PublishDate smalldatetime null
)
go
create table titleauthors
(
AuthorID char(11) not null
foreign key references authors(authorid),
TitleID char(6) not null
foreign key references titles(titleid),
AuthorOrder tinyint null,
RoyaltyShare decimal(4,2) null)
go
-- add publishers
insert into publishers
values('0736', 'New Age Books', '1 1st St','Boston', 'MA')
go
insert into publishers
values('0877', 'Binnet & Hardley','2 2nd Ave.', 'Washington', 'DC')
go
insert into publishers
values('1389', 'Algodata Infosystems', '3 3rd Dr.','Berkeley', 'CA')
go
-- add authors
insert into authors
values('409-56-7008', 'Bennet', 'Abraham',
'415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705')
go
insert into authors
values ('213-46-8915', 'Green', 'Marjorie',
'415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618')
go
insert into authors
values('238-95-7766', 'Carson', 'Cheryl',
'415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705')
go
insert into authors
values('998-72-3567', 'Ringer', 'Albert',
'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152')
go
insert into authors
values('899-46-2035', 'Ringer', 'Anne',
'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152')
go
insert into authors
values('722-51-5454', 'DeFrance', 'Michel',
'219 547-9982', '3 Balding Pl.', 'Gary', 'IN', '46403')
go
insert into authors
values('807-91-6654', 'Panteley', 'Sylvia',
'301 946-8853', '1956 Arlington Pl.', 'Rockville', 'MD', '20853')
go
insert into authors
values('893-72-1158', 'McBadden', 'Heather',
'707 448-4982', '301 Putnam', 'Vacaville', 'CA', '95688')
go
insert into authors
values('724-08-9931', 'Stringer', 'Dirk',
'415 843-2991', '5420 Telegraph Av.', 'Oakland', 'CA', '94609')
go
insert into authors
values('274-80-9391', 'Straight', 'Dick',
'415 834-2919', '5420 College Av.', 'Oakland', 'CA', '94609')
go
insert into authors
values('756-30-7391', 'Karsen', 'Livia',
'415 534-9219', '5720 McAuley St.', 'Oakland', 'CA', '94609')
go
insert into authors
values('724-80-9391', 'MacFeather', 'Stearns',
'415 354-7128', '44 Upland Hts.', 'Oakland', 'CA', '94612')
go
insert into authors
values('427-17-2319', 'Dull', 'Ann',
'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301')
go
insert into authors
values('672-71-3249', 'Yokomoto', 'Akiko',
'415 935-4228', '3 Silver Ct.', 'Walnut Creek', 'CA', '94595')
go
insert into authors
values('267-41-2394', 'O''Leary', 'Michael',
'408 286-2428', '22 Cleveland Av. #14', 'San Jose', 'CA', '95128')
go
insert into authors
values('472-27-2349', 'Gringlesby', 'Burt',
'707 938-6445', 'PO Box 792', 'Covelo', 'CA', '95428')
go
insert into authors
values('527-72-3246', 'Greene', 'Morningstar',
'615 297-2723', '22 Graybar Rd.', 'Nashville', 'TN', '37215')
go
insert into authors
values('172-32-1176', 'White', 'Johnson',
'408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025')
go
insert into authors
values('712-45-1867', 'del Castillo', 'Innes',
'615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', '48105')
go
insert into authors
values('846-92-7186', 'Hunter', 'Sheryl',
'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301')
go
insert into authors
values('486-29-1786', 'Locksley', 'Chastity',
'415 585-4620', '18 Broadway Av.', 'San Francisco', 'CA', '94130')
go
insert into authors
values('648-92-1872', 'Blotchet-Halls', 'Reginald',
'503 745-6402', '55 Hillsdale Bl.', 'Corvallis', 'OR', '97330')
go
insert into authors
values('341-22-1782', 'Smith', 'Meander',
'913 843-0462', '10 Misisipi Dr.', 'Lawrence', 'KS', '66044')
go
-- add titles
insert into titles
values ('PC8888', 'Secrets of Silicon Valley',
'popular_comp', '1389', 40.00, 8000.00, 4095,1,
'Muckraking reporting on the world''s largest computer hardware and
software manufacturers.',
'06/12/1998')
go
insert into titles
values ('BU1032', 'The Busy Executive''s Database Guide',
'business', '1389', 29.99, 5000.00, 4095, 1,
'An overview of available database systems with emphasis on common
business applications. Illustrated.',
'06/12/1998')
go
insert into titles
values ('PS7777', 'Emotional Security: A New Algorithm',
'psychology', '0736', 17.99, 4000.00, 3336, 1,
'Protecting yourself and your loved ones from undue emotional stress in
the modern world. Use of computer and nutritional aids emphasized.',
'06/12/1998')
go
insert into titles
values ('PS3333', 'Prolonged Data Deprivation: Four Case Studies',
'psychology', '0736', 29.99, 2000.00, 4072,1,
'What happens when the data runs dry? Searching evaluations of
information-shortage effects.',
'06/12/1998')
go
insert into titles
values ('BU1111', 'Cooking with Computers: Surreptitious Balance Sheets',
'business', '1389', 21.95, 5000.00, 3876, 1,
'Helpful hints on how to use your electronic resources to the best
advantage.',
'06/09/1998')
go
insert into titles
values ('MC2222', 'Silicon Valley Gastronomic Treats',
'mod_cook', '0877', 29.99, 0.00, 2032, 1,
'Favorite recipes for quick, easy, and elegant meals tried and tested by
people who never have time to eat, let alone cook.',
'06/09/1998')
go
insert into titles
values ('TC7777', 'Sushi, Anyone?',
'trad_cook', '0877', 29.99, 8000.00, 4095, 1,
'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/1998')
go
insert into titles
values ('TC4203', 'Fifty Years in Buckingham Palace Kitchens',
'trad_cook', '0877', 21.95, 4000.00, 15096, 1,
'More anecdotes from the Queen''s favorite cook describing life among
English royalty. Recipes, techniques, tender vignettes.',
'06/12/1998')
go
insert into titles
values ('PC1035', 'But Is It User Friendly?',
'popular_comp', '1389', 42.95, 7000.00, 8780, 1,
'A survey of software for the naive user, focusing on the ''friendliness''
of each.',
'06/30/1998')
go
insert into titles
values('BU2075', 'You Can Combat Computer Stress!',
'business', '0736', 12.99, 10125.00, 18722, 1,
'The latest medical and psychological techniques for living with the
electronic office. Easy-to-understand explanations.',
'06/30/1998')
go
insert into titles
values('PS2091', 'Is Anger the Enemy?',
'psychology', '0736', 21.95, 2275.00, 2045, 1,
'Carefully researched study of the effects of strong emotions on the body.
Metabolic charts included.',
'06/15/1998')
go
insert into titles
values('PS2106', 'Life Without Fear',
'psychology', '0736', 17.00, 6000.00, 111, 1,
'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/1998')
go
insert into titles
values('MC3021', 'The Gourmet Microwave',
'mod_cook', '0877', 12.99, 15000.00, 22246, 1,
'Traditional French gourmet recipes adapted for modern microwave
cooking.',
'06/18/1998')
go
insert into titles
values('TC3218',
'Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean',
'trad_cook', '0877', 40.95, 7000.00, 375, 1,
'Profusely illustrated in color, this makes a wonderful gift book for a
cuisine-oriented friend.',
'10/21/1998')
go
insert into titles (titleid, title, publisherid, contract)
values('MC3026', 'The Psychology of Computer Cooking', '0877', 0)
go
insert into titles
values ('BU7832', 'Straight Talk About Computers',
'business', '1389', 29.99, 5000.00, 4095, 1,
'Annotated analysis of what computers can do for you: a no-hype guide for
the critical user.',
'06/22/1998')
go
insert into titles
values('PS1372',
'Computer Phobic and Non-Phobic Individuals: Behavior Variations',
'psychology', '0736', 41.59, 7000.00, 375, 1,
'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/1998')
go
insert into titles (titleid, title, genre, publisherid, contract, notes)
values('PC9999', 'Net Etiquette', 'popular_comp', '1389', 0,
'A must-read for computer conferencing debutantes!.')
go
-- add title author
insert into titleauthors
values('409-56-7008', 'BU1032', 1, .60)
go
insert into titleauthors
values('486-29-1786', 'PS7777', 1, 1.00)
go
insert into titleauthors
values('486-29-1786', 'PC9999', 1, 1.00)
go
insert into titleauthors
values('712-45-1867', 'MC2222', 1, 1.00)
go
insert into titleauthors
values('172-32-1176', 'PS3333', 1, 1.00)
go
insert into titleauthors
values('213-46-8915', 'BU1032', 2, .40)
go
insert into titleauthors
values('238-95-7766', 'PC1035', 1, 1.00)
go
insert into titleauthors
values('213-46-8915', 'BU2075', 1, 1.00)
go
insert into titleauthors
values('998-72-3567', 'PS2091', 1, .50)
go
insert into titleauthors
values('899-46-2035', 'PS2091', 2, .50)
go
insert into titleauthors
values('998-72-3567', 'PS2106', 1, 1.00)
go
insert into titleauthors
values('722-51-5454', 'MC3021', 1, .75)
go
insert into titleauthors
values('899-46-2035', 'MC3021', 2, .25)
go
insert into titleauthors
values('807-91-6654', 'TC3218', 1, 1.00)
go
insert into titleauthors
values('274-80-9391', 'BU7832', 1, 1.00)
go
insert into titleauthors
values('427-17-2319', 'PC8888', 1, .50)
go
insert into titleauthors
values('846-92-7186', 'PC8888', 2, .50)
go
insert into titleauthors
values('756-30-7391', 'PS1372', 1, .75)
go
insert into titleauthors
values('724-80-9391', 'PS1372', 2, .25)
go
insert into titleauthors
values('724-80-9391', 'BU1111', 1, .60)
go
insert into titleauthors
values('267-41-2394', 'BU1111', 2, .40)
go
insert into titleauthors
values('672-71-3249', 'TC7777', 1, .40)
go
insert into titleauthors
values('267-41-2394', 'TC7777', 2, .30)
go
insert into titleauthors
values('472-27-2349', 'TC7777', 3, .30)
go
insert into titleauthors
values('648-92-1872', 'TC4203', 1, 1.00)
go
Could anyone use the database above to help me answer these question? I use the SQL Server. Thank you.
Complete following queries:
Outer Joins
Add a new publishers using your name for the publisher name. Display all publishers and the titles theyve published.
Display all books and any the royalty share(s) associated with each book.
Display all authors and the titles of any books theyve written.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started