-- CS 328 - HW 3 - Prblem 5
-- Drew Hengehold
-- Last modified: 11 Febuary 2025

start /home/st10/create-bks.sql
start /home/st10/pop-bks.sql

spool prob5-out.txt

prompt;
prompt By Drew Hengehold;
prompt;

prompt +++++++++++++++++;
prompt Problem 5 Part a;
prompt +++++++++++++++++;

select max(ord_needed_id) "Largest PK"
from order_needed;

prompt +++++++++++++++++;
prompt Problem 5 Part b;
prompt +++++++++++++++++;

select count(*) "Quantity of ISBN <9780131103627>"
from title
where ttl_isbn = '9780131103627';

select count(*) "Quantity of ISBN <5555555555555>"
from title
where ttl_isbn = '5555555555555';


prompt +++++++++++++++++;
prompt Problem 5 Part c;
prompt +++++++++++++++++;

select ttl_qty "Quantity on Hand", ttl_order_point "Order Point", ttl_auto_order_qty "Auto Order Quantity"
from title
where ttl_isbn = '9780131103627';

prompt +++++++++++++++++;
prompt Problem 5 Part d;
prompt +++++++++++++++++;

select ttl_isbn || ' / ' || ttl_name "Available Titles"
from title; 

prompt +++++++++++++++++;
prompt Problem 5 Part e;
prompt +++++++++++++++++;

select ttl_name "Book Title", pub_name "Book Publisher"
from title, publisher
where title.pub_id = publisher.pub_id
order by "Book Publisher", "Book Title";


spool off