/* Subqueries */

/* Find publishers whose pub_id is IN a set of names returned from a subquery */
use pubs

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

/* Look at the results of the sub-query when it is run alone */

select pub_id
from titles
where titles.type = 'business'

/* Look for authors living in a city with any publisher */

select au_lname, au_fname, city
from authors
where city = any
(select city
from publishers)

/* Only two authors live in a city that also has a publisher */

/* Subquery with Exists (Subquery results in true or false */
/* If true, the row from the outer query is returned */

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

/* Try to run this sub-query by itself 
  What happens? Can you explain?*/

select *
from titles
where pub_id=publishers.pub_id
and type = 'business'

/* The above is a correlated subquery, in that the results from the */
/* subquery depend on the outer query. In other words, each row from the */
/* outer query is tested over and over in the inner query, and if any results */
/* are returned by the inner query, the outer query is returned */
/* Note that publishers is not in the from clause, yet it is in */
/* the where clause. So, the inner query relies on the outer query for */
/* the value of the publishers.pub_id */

/* Union operator */

use YourDataBaseName

/* Make a Query that contains all of the customer names
   and all of the supplier names */

select strcnm, strsname
  from tblsalescust, tblsup

/* Note the names are in 2 columns and we have a cross join 
  how do we get rid of the cross join and get all names in one column? */

  select 'Customer/Supplier' = strcnm
  from tblsalescust
 union
  select strsname
  from tblsup
  order by strcnm

/* How can we differentiate between Customers and Suppliers in the output? */

select 'Customer/Supplier' = strcnm, 'C'
  from tblsalescust
 union
  select strsname, 'S'
  from tblsup
  
/* How can we get them ordered by name? 
   Where does the Order By clause go? */

select 'Customer/Supplier' = strcnm, 'C'
  from tblsalescust
 union
  select strsname, 'S'
  from tblsup
  order by strcnm