-- CS 328 - HW 1 - Problem 4 - Drew Hengehold - 31 Jan 2025

spool bks-play-out.txt

prompt;
prompt Drew Hengehold;
prompt;


prompt;
prompt 4 part a;
prompt;

select pub_name "Name", pub_city || ', ' || pub_state "City, State"
from publisher
order by pub_name;

prompt;
prompt 4 part b;
prompt;

select ttl_name "Title Names", ttl_price "Title Price"
from title, publisher
where pub_name = 'Benjamin/Cummings' and title.pub_id = publisher.pub_id
order by ttl_name;

prompt;
prompt 4 part c;
prompt;

select pub_name "Publisher Name", COUNT(ttl_name) "# TITLES", AVG(ttl_price) "AVG PRICE"
from publisher, title
where publisher.pub_id = title.pub_id
group by pub_name
order by "AVG PRICE";

prompt;
prompt 4 part d;
prompt;

select ttl_name, ord_line_qty
from title, order_line_item
where ord_num = '11012' and title.ttl_isbn = order_line_item.ttl_isbn;

prompt;
prompt 4 part e;
prompt;

select pub_name, ord_line_qty
from order_line_item, title, publisher
where publisher.pub_id = title.pub_id 
and title.ttl_isbn = order_line_item.ttl_isbn
and order_line_item.ord_line_qty = (select max(ord_line_qty)
                                    from order_line_item);




spool off