--
-- q1. Employee names with the skill '10in Table Saw'.
--
select distinct e.EmployeeName
from employee_t e, employeeskills_t es, skill_t s
where e.EmployeeID = es.EmployeeID
and es.SkillID = s.SkillID
and s.SkillDescription = '10in Table Saw';

--
-- q2. Orders and their details (products and quantities) done
--     by the saleperson with id #2. Only orders with
--     order lines are listed.
--
select distinct o.OrderID as "order id", o.OrderDate as date, 
	p.ProductDescription as product, 
	ol.OrderedQuantity as quantity
from order_t o, orderline_t ol, product_t p
where o.OrderID = ol.OrderID
and ol.ProductID = p.ProductID
and o.SalesPersonID = 2;

--
-- q3. List the order id that contains the ordering of the
--     product 'Birch Coffee Tables' and the quantity of
--     'Birch Coffee Tables' ordered.
--
select distinct o.OrderID as "order id", ol.OrderedQuantity as quantity
from order_t o, orderline_t ol, product_t p
where o.OrderID = ol.OrderID
and ol.ProductID = p.ProductID
and p.ProductDescription = 'Birch Coffee Tables';

--
--  q4. List the states and the number of customers they have.
--      Display the result in the descending order of the number
--      of customers.
--
select distinct c.CustomerState as state,
	count(c.CustomerId) as count
from customer_t c
group by c.CustomerState
order by count desc;

-- 
-- q5. List the states and the number of customers they have.
--      Display the result in the descending order of the number
--      of customers. List only those states with two or
--      more customers.
--
select distinct c.CustomerState as state,
	count(c.CustomerId) as count
from customer_t c
group by c.CustomerState
having count >= 2
order by count desc;

--
-- q6. List the names of the products that use the material with id
--     FINGLS, but not the material with id NF1.
--
select distinct p.ProductDescription
from product_t p, uses_t u
where p.ProductID = u.ProductID
and u.MaterialID = 'FINGLS'
and p.ProductID not in 
	(select distinct u2.ProductID
	 from uses_t u2 
	 where u2.MaterialID = 'NF1');