-- CMPU 311 
-- CAP Queries
-- Class exercise

-- 1. List the order number and total dollars of all orders.    
select orderNum, totalUSD 
from orders;


-- 2. List the last name and home city of people whose prefix is Dr..    
select lastName, homeCity
from people
where prefix = 'Dr.';


-- 3. List id, name, and price of products with quantity more than 1007.    
select prodId, name, priceUSD
from products 
where qtyonhand > 1007;


-- 4. List the first name and home city of people born in the 1950s.    
select firstName, homeCity
from people
where dob between '1950-01-01' and '1959-12-31';


-- 5. List the prefix and last name of people who are not Mr..    
select prefix, lastName
from people
where not prefix = 'Mr.';


-- 6. List all fields for products in neither Dallas nor Duluth 
--    that cost US$3 or more.    
select *
from products 
where city not in ('Dallas', 'Duluth') and
      priceUSD >= 3.00;

-- 7. List all fields for orders in March.    
select * 
from orders 
where date_part('month', dateOrdered) = 3;
     
-- 8. List all fields for orders in February of US$20,000 or more.    
select * 
from orders 
where date_part('month', dateOrdered) = 2 and 
      totalUSD >= 20000;


-- 9. List all orders from the customer whose id is 007.    
select * 
from orders 
where custId = 007;
     
     
-- 10. List all orders from the customer whose id is 005.
select * 
from orders 
where custId = 005;


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