create database expandedbookbiz go use expandedbookbiz go create table authors ( au_id char(11) not null primary key, au_lname varchar(40) not null, au_fname varchar(20) not null, phone char(12) null, address varchar(40) null, city varchar(20) null, state char(2) null, zip char(5) null ) go create table publishers ( pub_id char(4) not null primary key, pub_name varchar(40) null, address varchar(40) null, city varchar(20) null, state char(2) null ) go create table titles ( title_id char(6) not null primary key, title varchar(80) not null, type char(12) null, pub_id char(4) null references publishers(pub_id), price money null, advance money null, projected_sales int null, contract bit not null, notes varchar(200) null, pubdate smalldatetime null ) go

create table editors ( ed_id char(11) not null primary key, ed_lname varchar(40) not null, ed_fname varchar(20) not null, ed_pos varchar(12) null, phone char(12) null, address varchar(40) null, city varchar(20) null, state char(2) null, zip char(5) null, ed_boss char(11) null ) go create table sales ( sonum int not null primary key, stor_id char(4) not null, ponum varchar(20) not null, sdate smalldatetime null ) go create table roysched ( title_id char(6) not null foreign key references titles(title_id), lorange int null, hirange int null, royalty decimal(4,2) null) go create table titleauthors ( au_id char(11) not null foreign key references authors(au_id), title_id char(6) not null foreign key references titles(title_id), au_ord tinyint null, royaltyshare decimal(4,2) null) go create table titleditors ( ed_id char(11) not null foreign key references editors(ed_id), title_id char(6) not null foreign key references titles(title_id), ed_ord tinyint null primary key (ed_id, title_id) ) go create table salesdetails ( sonum int not null foreign key references sales(sonum), qty_ordered smallint not null, qty_shipped smallint null, title_id char(6) not null foreign key references titles(title_id), date_shipped smalldatetime null primary key (sonum, title_id) ) go

-- add authors use expandedbookbiz go

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

Write queries (with subqueries) to answer the following questions.

1. Which publishers haven't published a cook book?

2. Which publisher has the highest priced book?

3. Which titles have more than one author (show the title)?

4. Which titles don't have an author?

5. Which authors have sold more than 100 copies of a book?

6. Which authors haven't sold any books?

7. Which authors have sold more than 50 books altogether?