--list account ids that have more that 3 students associated with it
select aid, count(*) 
from pws 
where del = 0 
group by aid 
having count(*) > 3

-- display PWA records that have more than 3 students associated with it.
select * 
from pwa 
where del = 0 and aid in (select aid from pws where del = 0 group by aid having count(*) > 3)


-- displaying STU records for an associated PWA email address.
select sc, id, ln, fn 
from stu 
where del = 0 and id in (select id from pws where del = 0 and aid in (select aid from pwa where del = 0 and  em = 'me@example.com'))

-- using a sub-query to return matching rows.
select * FROM tst where pid in (select id from stu where del = 0 and sc = 1)

-- using a subquery as a nested table
-- let's SELECT the most recent ENR record
select * from enr inner join (select subENR.id, max(subENR.ed) as maxDT from enr as subENR group by subENR.id) as e on enr.id = e.id and enr.ed = e.maxDT

--Using subqueries as fields.
select stu.id, stu.ln,
(select max(dt) from ads where ads.pid = stu.id) as e,
(select min(dt) from ads where ads.pid = stu.id) as l,
(select de from cod where tc = 'stu' and fc = 'lf' and cod.cd = stu.lf) as lf
from stu

--use cross apply
select stu.id, t1.ADSmaxdt, t1.ADSminDT,  t2.de
from stu
CROSS APPLY   -- CROSS APPLY works similar to an "INNER JOIN". Changing this to an OUTER APPLY changes this query to before as if you were using a LEFT OUTER JOIN
(
select max(dt) as ADSmaxdt, min(dt) ADSminDT FROM ads where ads.pid = stu.id) as  t1
CROSS apply 
(select de from cod where tc = 'stu' and fc = 'lf' and cod.cd = stu.lf) as t2