/* Join Examples - Join.sql  */

/* Cartesian Product (Cross Join) of two tables */
/* First, what is in each of two tables */

use pubs

select * from authors

select * from publishers

/* Now make a query with no condition */
/* Returns 184 rows. 23 authors * 8 publishers */

select au_fname, au_lname, pub_name
from authors, publishers

/* Make a CROSS JOIN query
   What does it give?*/

select au_fname, au_lname, pub_name
from authors cross join publishers

/* Establish a condition 
   What question do we answer with this next query?*/

select au_fname, au_lname, pub_name
from authors, publishers
where authors.city = publishers.city

/* Make an outer Join of each type 
  first a left outer join with old operator*/

select au_fname, au_lname, pub_name
from authors, publishers
where authors.city *= publishers.city

/* Now with the new 'Left' Keyword */

select au_fname, au_lname, pub_name
from authors left join publishers
on authors.city = publishers.city

/* Now do the same with the 'Right' keyword */

select au_fname, au_lname, pub_name
from authors, publishers
where authors.city =* publishers.city

select au_fname, au_lname, pub_name
from authors right join publishers
on authors.city = publishers.city

/* Now for a full Join
  Can you relate this to a Right and a Left Join?? */

select au_fname, au_lname, pub_name
from authors full join publishers
on authors.city = publishers.city

/* Inner Joins */

/* Find out what authors wrote which books */
/* using two types of inner join syntax */

/* First use "Where Clause' Join */

select type, au_lname, au_fname, title
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
order by type

/* Now a 'From clause' Join */

select type, au_lname, au_fname, title
from authors
join titleauthor on authors.au_id = titleauthor.au_id
join titles on titles.title_id = titleauthor.title_id
order by type

/* Find all authors who live in the same zip code as another author
   do not display rows where an author is living in the same zip
   code as him/her self. Use a self-join on  authors 
   Can you explain why this is a self-join?
   Can you explain what the last line is doing in each of the
   next tow queries?*/

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
from authors au1, authors au2
where au1.zip = au2.zip
and (au1.au_lname + au1.au_fname) != (au2.au_lname + au2.au_fname)

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
from authors au1
join authors au2 on au1.zip = au2.zip
and (au1.au_lname + au1.au_fname) != (au2.au_lname + au2.au_fname)

/* How do we find the authors who do not live in a city in 
   which a publisher is located?
   Does the following do it? Look carefully! */

select distinct au_lname, authors.city
from publishers, authors
where publishers.city != authors.city

/* This last query finds authors who live in a city where some publisher is not located
   which is all of them */

/* Try again with a sub-query*/

select distinct au_lname, authors.city
from publishers, authors
where authors.city not in
(select publishers.city from publishers, authors
where authors.city = publishers.city)

/* Theta Joins */

/*Find Authors who live in states that come alphabetically after 
 Massachusetts, the state where New Moon Books is located 
(no one said these queries had to make sense!*/

select pub_name, p.state, a.state
from publishers p, authors a
where a.state > p.state
and pub_name = 'New Moon Books'

/* Get States before Mass
  Can you explain what is happening? */

select pub_name, p.state, a.state
from publishers p, authors a
where a.state < p.state
and pub_name = 'New Moon Books'


/* Not Equal joins are usually self joins */

/* Find which authors in Oakland live in the same zip code area in Oakland */

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
from authors au1, authors au2
where au1.city = 'Oakland' and au1.state = 'CA'
and au1.zip = au2.zip
and au1.au_id <> au2.au_id

/* Change from <> to < */

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
from authors au1, authors au2
where au1.city = 'Oakland' and au1.state = 'CA'
and au1.zip = au2.zip
and au1.au_id < au2.au_id

/* Now try > */

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
from authors au1, authors au2
where au1.city = 'Oakland' and au1.state = 'CA'
and au1.zip = au2.zip
and au1.au_id > au2.au_id

/* Get names of folks with same last names */

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
from authors au1, authors au2
where au1.au_lname = au2.au_lname

/* That seems to give most people */
/* Try again and check first names */

select au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
from authors au1, authors au2
where au1.au_lname = au2.au_lname
and au1.au_fname <> au2.au_fname

/* Cube operator */

use /* Your database name goes here - wee need Bellpeek */

select strCnm,'Sales' = sum(intQty*curPrice)
from tblSalescust Cust join tblCustOrder Ord on Cust.strCid = Ord.strCid
join tblCustOrdItem Item on Ord.strIid = Item.strIid
join tblProduct Prod on Item.strPid = Prod.strPID
group by strCnm
   
/* Same query with Where clause join */

select strCnm,'Sales' = sum(intQty*curPrice)
from tblSalescust Cust,tblCustOrder Ord, tblCustOrdItem Item, tblProduct Prod
where Cust.strCid = Ord.strCid and
 Ord.strIid = Item.strIid and
 Item.strPid = Prod.strPID
 group by strCnm

/* Add cube operator and note last row */

select strCnm,'Sales' = sum(intQty*curPrice)
from tblSalescust Cust join tblCustOrder Ord on Cust.strCid = Ord.strCid
join tblCustOrdItem Item on Ord.strIid = Item.strIid
join tblProduct Prod on Item.strPid = Prod.strPID
group by strCnm
with cube

/* Add more columns in select and follow rules for aggfregate functions*/
/* Note added row after each Customer and added rows at end for products */

select strCnm,strPname,'Sales' = sum(intQty*curPrice)
from tblSalescust Cust join tblCustOrder Ord on Cust.strCid = Ord.strCid
join tblCustOrdItem Item on Ord.strIid = Item.strIid
join tblProduct Prod on Item.strPid = Prod.strPID
group by strCnm, strPname
with cube

/* Add a datepart to the select list */

/* Get interesting report - Particularly as you look at last rows */

select strCnm,strPname,datepart(yy,dtmOrddte),'Sales' = sum(intQty*curPrice)
from tblSalescust Cust join tblCustOrder Ord on Cust.strCid = Ord.strCid
join tblCustOrdItem Item on Ord.strIid = Item.strIid
join tblProduct Prod on Item.strPid = Prod.strPID
group by strCnm, strPname, datepart(yy,dtmOrddte)
with cube

/* Try the Grouping Function */
/* A 0 means the column contains a value, a 1 means there is a null in the column */
/* which means the values in the column have been grouped together by the cube operator */ 

select strCnm,grouping(strCnm),strPname, grouping(strPname), 'Sales' = sum(intQty*curPrice)
from tblSalescust Cust join tblCustOrder Ord on Cust.strCid = Ord.strCid
join tblCustOrdItem Item on Ord.strIid = Item.strIid
join tblProduct Prod on Item.strPid = Prod.strPID
group by strCnm, strPname
with cube

/* Using the Rollup operator with 4 columns in the select statement */

select strCnm,strPname,datepart(year,dtmOrddte),'Sales' = sum(intQty*curPrice)
from tblSalescust Cust join tblCustOrder Ord on Cust.strCid = Ord.strCid
join tblCustOrdItem Item on Ord.strIid = Item.strIid
join tblProduct Prod on Item.strPid = Prod.strPID
group by strCnm, strPname, datepart(year,dtmOrddte)
with rollup