Answers

answers.sql

#List the names and total profit for all salespeople who have orders with customers in IL																											
SELECT
  sp.name, SUM(o.profit) as Profit
FROM
  salespeople AS sp
INNER JOIN
  orders AS o ON sp.id = o.salesperson_id
INNER JOIN 
  customers AS c ON c.id = o.cust_id
WHERE 
  c.state = 'IL'
GROUP BY 
  sp.name;
  
#List the names of all salespeople who do NOT have any orders														
SELECT 
  sp.name
FROM 
  salespeople AS sp
LEFT JOIN
  orders AS o ON sp.id = o.salesperson_id
WHERE
  o.order_num IS NULL;

#List the names of those salespeople who have two or more orders. 																												
SELECT 
  sp.name
FROM
  salespeople AS sp
INNER JOIN
  orders AS o ON sp.id = o.salesperson_id
GROUP BY 
  sp.name
HAVING 
  COUNT(*) >= 2;

#List each Order's % of total profit and % of regional profit.																												
SELECT
  o.order_num as 'Order number',
  (o.profit / (SELECT SUM(profit) FROM orders) * 100) AS '% of total profit',
  (o.profit / (
    SELECT
      SUM(profit) 
    FROM 
      orders AS o2
    INNER JOIN 
      salespeople AS sp2 ON sp2.id = o2.salesperson_id
    WHERE
      sp2.region = sp.region
  ) * 100) AS '% of regional profit'
FROM 
  orders AS o
INNER JOIN 
  salespeople AS sp ON sp.id = o.salesperson_id;

#Create a table called MonthlyTopRegion with columns Region, Order_Month, Total_Profit.  For each month insert one row into this table containing the Region that had the highest profit														
CREATE TABLE MonthlyTopRegion
(
  Order_Month CHAR(20),
  Region CHAR(100),
  Total_Profit NUMERIC(15,2)
);				

INSERT INTO MonthlyTopRegion (
  SELECT 
    order_month,
    region, 
    MAX(monthly_profit)
  FROM (
     SELECT  
       o2.order_month, 
       region, 
       SUM(profit) AS monthly_profit
     FROM 
       orders AS o2
     INNER JOIN 
       salespeople sp on sp.id = o2.salesperson_id
     GROUP BY 
       o2.order_month, sp.region
  ) profit_totals
  GROUP BY 
    order_month
);

http://sqlfiddle.com/#!9/ed66d0/2