/*
 * CMPU-311
 * Fall 2021
 * Prof. Smith
 * 
 * (Adapted and used with permission by Alan Labouseur)
 * 
 * Write some interesting SQL queries against our CAP database
 * using subqueries and set operations. 
 * - Please do not use joins; save them for the next assignment. 
 * - Enjoy the elegance and power of the relational model and 
 *   bask in its beauty.
 */

-- 1.Get all the People data for people who are customers. 
select * from people
where pid in 
    (select pid from customers);

-- 2.Get all the People data for people who are agents. 


-- 3.Get all of People data for people who are both customers and agents. 

   
-- 4.Get all of People data for people who are neither customers nor agents. 

   
-- 5.Get the ID of customers who ordered either product 'p01' or 'p07' (or both). 


-- 6.Get the ID of customers who ordered both products 'p01' and 'p07'.  
--   List the IDs in order from highest to lowest. Include each ID only once. 


-- 7.Get the first and last names of agents who sold products 'p05' or 'p07' 
--   in order by last name from Z to A. 


-- 8.Get the home city and birthday of agents booking an order for the customer 
--   whose pid is 001, sorted by home city from A to Z. 


-- 9.Get the unique ids of products ordered through any agent who takes at 
--   least one order from a customer in Toronto, sorted by id from highest to 
--   lowest. (This is not the same as asking for ids of products ordered by 
--   customers in Toronto.) 


-- 10.Get the last name and home city for all customers who place orders through 
--    agents in Teaneck or Santa Monica.