Start a scripting session and answer the following questions. Then mail your script to jlaker@udel.edu with "ISQL Lab 9" as the subject.

Use the pubs2 database and subqueries to answer these questions.

1. Generate a report which shows the book titles with the highest prices. The report should list the type, title, and price of the books and be grouped by their type. Create appropriate headings for columns.

1> select type 'Type', title 'Title', max(price) 'Max Price'

2> from titles

3> group by type

4> having price = max(price)

OR

1> select type, title, price as maxprice from titles t1

2> where price = (select max(price) from titles t2

3> where t1.type = t2.type

4> group by t2.type)

2. Find out which authors live in the same city as Dirk Stringer. Show their first name, last name, and the name of the city.

1> select au_fname 'First Name', au_lname 'Last Name', city 'City'

2> from authors

3> where city in (select city

4> from authors

5> where au_lname = 'Stringer'

6> and au_fname = 'Dirk')

3. The company wants to know which publishers are located in the same city as its authors. Show the publisher name and the city.

1> select pub_name 'Publisher', city 'City'

2> From publishers

3> where city in (select distinct city

4> from authors)

4. Show which publishers publish books of the type "business". Show the publishers’ names.

1> select distinct pub_name 'Publisher'

2> from publishers

3> where pub_id in (select distinct pub_id

4> from titles

5> where type = 'business')

 

5. Show a list of the publishers who do NOT publish business book. Show the publisher names only.

1> select distinct pub_name 'Publisher'

2> from publishers

3> where pub_id not in (select distinct pub_id

4> from titles where type = 'business')