-- John Aromando -- SQL Subqueries --

use ogcPubLev2

select AVG(qty) as AverageQty
from sales
-- average is 205

--finds all sales data based on the average
select custname, c.custnum, ordnum, s.partnum, bktitle, qty
from sales as s
inner join Customers as c on s.custnum = c.custnum
inner join Titles as t on s.partnum = t.partnum
where qty > 205
order by qty desc

--use subquery to combine both previous queries into one sql statement
select ordnum, qty
from sales
where qty > (select AVG(qty) from Sales)
order by qty

--placing a copy of sub query in select clause adds transparency
select ordnum, custnum,
	(select AVG(qty) from sales) as AvgQty,
	qty as QtysGreaterThanAvg
from Sales
where qty > (select AVG(qty) from Sales)
order by qty

select custname, c.custnum, ordnum, s.partnum, bktitle, qty as QtysGreaterThanAvg,
	(select AVG(qty) from Sales) as AvgQty
from Sales as s
inner join Customers as c on s.custnum = c.custnum
inner join Titles as t on s.partnum = t.partnum
where qty > (select AVG(qty) from Sales)
order by qty desc

--find reps with greater than avg commission rates
select lname, (select AVG(commrate) from Slspers) as AvgCommrate, commrate, COUNT(sa.ordnum) as NumOfSales
from Slspers as s
inner join Sales as sa on s.repid = sa.repid
where commrate > (select AVG(commrate) from Slspers)
group by lname, commrate
order by commrate desc, NumOfSales desc

--filtering for books that are selling versus those that aren't, compares lists from 2 different tables and returns the matching sold books
select partnum, bktitle
from Titles
where partnum in (select partnum from Sales)

--unsold books 
select partnum, bktitle
from Titles
where partnum not in (select partnum from Sales)

--sales reps not performing
select repid, lname 
from Slspers
where repid not in (select repid from Sales)

--create list of customers combined orders
select Sales.custnum, SUM(qty) as SumQtyByCust
from Sales
group by Sales.custnum
order by SumQtyByCust

--shows only the top record instead of all
select top 1 custnum, SUM(qty) as SumQtyByCust 
from Sales 
group by custnum
order by sumqtybycust 

--single order quantities greater than 'any' grouped order in sub qry
select distinct custname
from Sales as s
inner join Customers as c on s.custnum = c.custnum
where qty > any --all = all others combined
	(select SUM(qty) as SumQtyByCust from Sales group by custnum)
order by custname desc

select custnum from Customers as c
where custnum in (select custnum from Sales as s)

select custname from customers as c
where exists (select * from Sales as s) --exists can be misleading

--Multi-level 
select custnum, custname, address, city
from Customers
where custnum in
(
select custnum
from Sales
where partnum in
(
select partnum
from Titles
where slprice>35
)
)
order by custname asc