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

Use the pubs2 database and subqueries.

1. Generate a report which shows the book titles with the highest prices for each type. 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, title, price from titles group by type

2> having price=max(price)

 

2. Show a list of the publishers and the book types who do NOT publish business books. There should be no duplicates in the list.

1> select distinct pub_name, type from publishers p, titles t

2> where p.pub_id = t.pub_id and

3> type != 'business'

3. The company wants to do a what-if analysis on their top selling books. They want you to generate a report that shows the book title, current price, price after 10% increase and the total sales of the book for all books that had total sales greater than 5000. Give appropriate headings to report.

1> select title Title, price Price, price*1.1 Increase, total_sales

from titles where total_sales>5000

 

4. Generate a list of the authors’ last names and their state which is ordered by last name and then state.

4> select au_lname, state

5> from authors

6> order by upper(au_lname), state

5. Generate a formatted phone list of all the authors. It should have the following format:
   Phone_List
   -----------------------------------------------------------------------------
   John Doe, 302-831-3761

 

select (au_fname + ' ' + au_lname + ', ' + phone) as Phone_List

from authors

6. Use an the UPDATE command to change all blue cars to ‘maroon’ in rentals tables.

update cars set color = 'maroon' where color = 'blue'

If you encounter an error stating that you are out of space, this is probably due to your log files getting too big for you space allocation. To remedy this you need to "dump" your log file. The command for doing so is as follows.

IMPORTANT: Substitute dbname with your username since your username is the same as your database name.

The following is the command for granting permission to create tables in your database. grant create table to public or [comma delimited list of users]
go