Unit 4


I. Using both a join and a subquery, select the publishers 
   that publish business books.
---------------------------------------------------------------------
1. Join version

select pub_name 
  from publishers, titles 
 where publishers.pub_id = titles.pub_id 
   and type = 'business';



1.1 subquery version

select pub_name 
  from publishers
 where pub_id in (select pub_id 
                   from titles
                  where type = 'business');






II. Using both a join and a subquery, 
    select the number of authors that wrote business books.
-------------------------------------------------------------------
2. join version

select distinct count(TA.au_id)
  from titles, titleauthor TA
 where titles.title_id = TA.title_id
   and titles.type = 'business';



2.1. subquery version

select distinct count(TA.au_id)
  from titleauthor TA
 where TA.title_id in (select title_id 
                         from titles
                        where type = 'business');





III.  Select a price list as shown below

select pub_name as 'Publisher', title as 'Title' , price as 'Price'
  from publishers, titles
 where publishers.pub_id = titles.pub_id
group by pub_name
order by pub_name desc;





IV. Select all of the titles published by Algodata Infosystems

select title
  from titles, publishers
 where titles.pub_id = publishers.pub_id
   and publishers.pub_name = 'Algodata Infosystems';





V. Using an exists, find all of the publishers that publish business books

select distinct pub_name 
  from publishers 
 where exists (select 1 
                 from titles 
                where pub_id = publishers.pub_id 
                  and type = 'business')