#cs464/cs564 - Homework #4 - Solutions

# P1.
SELECT phone
 FROM dealership
 WHERE name = 'Southwest BMW';
 
# P2.
SELECT manuf, model
 FROM cars
 WHERE basePrice < 15000 OR topSpeed > 150;
 
/* P3. different comment types */
SELECT sp.name
 FROM (salesPerson sp INNER JOIN carsSold cs ON sp.id = cs.salesPerson)
 INNER JOIN dealership D on cs.dealershipID = D.id
 WHERE D.Name = 'Garcia Honda' AND cs.model = 'CR-V';
 
SELECT name
 FROM salesPerson sp
 WHERE sp.id IN
 (SELECT cs.salesPerson
 FROM carsSold cs INNER JOIN dealership D ON cs.dealershipID = 
D.id
 WHERE D.Name = 'Garcia Honda' AND cs.model = 'CR-V');
 
# P4.
SELECT DISTINCT phone
 FROM dealership D INNER JOIN dealerSells ds on D.id = 
ds.dealershipID
 WHERE ds.manuf IN( 'Dodge', 'MiniCooper');
 
SELECT DISTINCT phone
 FROM dealership D INNER JOIN dealerSells ds on D.id = 
ds.dealershipID
 WHERE ds.manuf = 'Dodge' OR ds.manuf = 'MiniCooper';
 
SELECT phone
 FROM dealership
 WHERE id IN
 (SELECT dealershipID
 FROM dealerSells
 WHERE manuf IN ('Dodge','MiniCooper') );
 
SELECT phone
 FROM dealership
 WHERE id IN
 (SELECT dealershipID
 FROM dealerSells
 WHERE manuf = 'Dodge' OR manuf = 'MiniCooper');
 
# P5.
SELECT D.name
 FROM (salesPerson sp INNER JOIN worksAt wa ON sp.id = wa.salesPersonID)
 INNER JOIN dealership D ON wa.dealershipID = D.id
 WHERE sp.name = 'Joe Smith'
 AND wa.commissionRate >= ALL (
 SELECT commissionRate
 FROM salesPerson sp2 INNER JOIN worksAt wa2 ON sp2.id = 
wa2.salesPersonID
 WHERE sp2.name = 'Joe Smith' );
 
SELECT D.name
 FROM (SELECT sp.id as spID, MAX(commissionRate) as MxRate
 FROM salesPerson sp INNER JOIN worksAt wa2 ON sp.id = 
wa2.salesPersonID
 WHERE sp.name = 'Joe Smith'
 GROUP BY sp.id ) as MXtbl
 INNER JOIN
 (worksAt wa INNER JOIN dealership D ON wa.dealershipID = 
D.id)
 ON MXtbl.spID = wa.salesPersonID AND MXtbl.MxRate = 
wa.commissionRate;
 
# P6. (empty set) is solution
SELECT sp.name
 FROM (salesPerson sp INNER JOIN carsSold cs ON sp.Id = cs.salesPerson)
 INNER JOIN worksAt wa
 ON cs.dealershipID = wa.dealershipID AND sp.Id = wa.salesPersonID
 WHERE cs.manuf = 'Dodge' AND wa.commissionRate >= .20;
 

# P7.
SELECT D.name
 FROM dealership D INNER JOIN
 (SELECT dealershipID, count(dealershipID) as CarCnt
 FROM dealerSells ds
 GROUP BY dealershipID) as CntTbl
 ON D.id = CntTbl.dealershipID
 WHERE CarCnt >= ALL
 (SELECT count(dealershipID) FROM dealerSells ds2 GROUP BY 
dealershipID);
 
# P8.
SELECT DD.name
 FROM ( SELECT dealershipID, count(*) as NumSold
 FROM (SELECT DISTINCT dealershipID, manuf, model FROM 
carsSold ) as WS
 GROUP BY dealershipID) as NumSoldTbl INNER JOIN dealership DD
 on NumSoldTbl.dealershipID = DD.ID
 WHERE NumSold >= ALL
 ( SELECT count(*) as NumSold2
 FROM (SELECT DISTINCT dealershipID, manuf, model FROM 
carsSold ) as WS2
 GROUP BY dealershipID );
 
# P9.
SELECT sp.name
 FROM salesPerson sp INNER JOIN
 (SELECT DISTINCT salesPerson, dealershipId,manuf,model FROM 
carsSold ) as WtWr
 ON sp.Id = WtWr.salesPerson
 GROUP BY sp.name, manuf, model
 HAVING count(dealershipId) > 1;
 

# P10.
SELECT manuf,model
 FROM (SELECT DISTINCT dealershipID, manuf,model FROM carsSold) as SoldA2
 GROUP BY manuf,model
HAVING Count(*) >= ALL (
 SELECT Count(*)
 FROM (SELECT DISTINCT dealershipID, manuf,model FROM carsSold) as 
SoldA2
 GROUP BY manuf,model );
 


# P11. 
SELECT manuf
 FROM (SELECT c.manuf, c.model, (cs.salesPrice - c.basePrice) as Profit
 FROM cars c INNER JOIN carsSold cs ON c.manuf = cs.manuf AND
c.model = cs.model) ptable
 GROUP BY manuf
HAVING SUM(Profit) >= ALL(
SELECT SUM(Profit) as Total_Profit
 FROM (SELECT c.manuf, c.model, (cs.salesPrice - c.basePrice) as Profit
 FROM cars c INNER JOIN carsSold cs ON c.manuf = cs.manuf AND
c.model = cs.model) ptable
 GROUP BY manuf);
  

# P12.
SELECT AVG(topspeed)
 FROM (cars c INNER JOIN carsSold cs ON c.manuf = cs.manuf AND c.model 
= cs.model)
 WHERE dateTimeOfSale between '2012-03-01' AND '2012-06-01'
 AND salesPerson IN ( SELECT id from salesPerson WHERE gender = 'F')
 GROUP BY c.manuf, c.model;
 
# P13.
SELECT c.manuf, c.model, c.topspeed
FROM (cars c INNER JOIN carsSold cs ON c.manuf = cs.manuf AND c.model =
cs.model)
 INNER JOIN salesPerson sp ON cs.salesPerson = sp.id
 WHERE sp.name LIKE 'Mike%'
AND topspeed >= ALL (
SELECT topspeed
FROM (cars c INNER JOIN carsSold cs ON c.manuf = cs.manuf AND c.model =
cs.model)
 INNER JOIN salesPerson sp ON cs.salesPerson = sp.id
 WHERE sp.name LIKE 'Mike%');
 

# EC
SELECT sp.name, D.name, NumSold, AvgCommission
 FROM
 (SELECT salesPerson, cs.dealershipID, COUNT(cs.model) as NumSold,
 AVG(wa.commissionRate * (cs.salesPrice-c.basePrice)) as 
AvgCommission
 FROM (carsSold cs INNER JOIN cars c on c.manuf = cs.manuf AND 
c.model = cs.model)
 INNER JOIN worksAt wa
 ON cs.salesPerson = wa.salesPersonID AND cs.dealershipID 
= wa.dealershipID
 GROUP BY salesPerson, cs.dealershipID) as CT
 INNER JOIN salesPerson sp ON CT.salesPerson = sp.id
 INNER JOIN dealership D ON CT.dealershipID = D.id
 ORDER BY AvgCommission DESC;
 

# EC
SELECT sp.name, D.name, AVG( cs.salesPrice - c.basePrice) as AvgProfit,
 AVG( wa.commissionRate * (cs.salesPrice - c.basePrice)) as
AvgCommission
 FROM (((carsSold cs INNER JOIN cars c on c.manuf = cs.manuf AND c.model
= cs.model)
 INNER JOIN worksAt wa
 ON cs.salesPerson = wa.salesPersonID AND cs.dealershipID =
wa.dealershipID) INNER JOIN salesPerson sp on cs.salesPerson = sp.id ) 
INNER JOIN dealership D on cs.dealershipID = D.id
WHERE cs.DateTimeOfSale > DATE_ADD(Now(), INTERVAL -10 YEAR )
GROUP BY cs.salesPerson, cs.dealershipID;
 
/*---------------------------------------------------------------------
 * Student Submitted alternatives:
 */
                                            
#P4
SELECT phone FROM `dealership` JOIN `dealerSells` ON id = dealershipID 
WHERE (manuf='Dodge' OR manuf='MiniCooper') AND stopped IS NULL;

#P4
SELECT phone AS phoneNumber FROM dealership 
WHERE id IN(SELECT dealershipID FROM dealerSells 
WHERE manuf IN('Dodge','MiniCooper') AND stopped IS NULL);

#P4
SELECT DS.PHONE FROM  
dealerSells DS1 
INNER JOIN dealerSells DS2 ON DS1.DEALERSHIPID=DS2.DEALERSHIPID 
INNER JOIN dealership DS ON DS1.DEALERSHIPID=DS.ID 
WHERE DS1.MANUF='MiniCooper' and DS2.MANUF='Dodge' 
AND IFNULL(DS1.STOPPED, '2099-12-31') > CURDATE() AND IFNULL(DS2.STOPPED, '2099-12-31') > CURDATE();

#P5
SELECT name FROM `dealership` JOIN `worksAt` ON id = dealershipID 
WHERE commissionRate = (SELECT MAX(commissionRate)
FROM `worksAt` JOIN `salesPerson`
ON salesPersonID = id WHERE name='Joe Smith');

#P5
SELECT dealership.name  
FROM dealership,worksAt,salesPerson WHERE dealership.id=worksAt.dealershipID  
AND  salesPerson.name='Joe Smith' AND worksAt.commissionRate=(SELECT MAX(commissionRate)
FROM worksAt,salesPerson WHERE  worksAt.salesPersonID=salesPerson.id AND salesPerson.name='Joe Smith');

#P5
SELECT name FROM dealership
WHERE (id IN (SELECT dealershipID 
  FROM 	worksAt WHERE (commissionRate IN ( 
    SELECT MAX(commissionRate) 
    FROM worksAt WHERE 	(salesPersonID = ( 
      SELECT id FROM salesPerson 
      WHERE (name='Joe Smith') )) 
  GROUP BY salesPersonID ))));

#6
SELECT name FROM salesPerson JOIN 
(SELECT salesPersonID, carsSold.dealershipID, commissionRate 
FROM worksAt JOIN carsSold ON worksAt.salesPersonID = carsSold.salesPerson 
AND worksAt.dealershipID = carsSold.dealershipID WHERE manuf='Dodge') tb1 
ON salesPerson.id = tb1.salesPersonID WHERE commissionRate > 20;

#P8
SELECT name as 'Dealer Name' FROM (SELECT dealershipID, COUNT(DISTINCT model) as modelCount 
FROM carsSold GROUP BY dealershipID) tbl JOIN dealership ON dealershipID=id 
WHERE modelCount>=ALL(SELECT COUNT(DISTINCT model)
FROM dealerSells GROUP BY dealershipID)

#P9
SELECT name as 'Salesman Name' FROM salesPerson JOIN 
(SELECT salesPerson, model, COUNT(DISTINCT dealershipID) as dealerCount 
FROM carsSold GROUP BY salesPerson, model) tbl ON id=salesPerson WHERE dealerCount>1

#P9
SELECT name  
FROM salesPerson  
WHERE id IN 
(SELECT DISTINCT c1.salesPerson 
  FROM carsSold AS c1, carsSold AS c2 
    WHERE c1.manuf = c2.manuf 
        AND c1.model = c2.model 
        AND c1.salesPerson = c2.salesPerson 
        AND c1.dealershipID <> c2.dealershipID);

#P9
SELECT name FROM salesPerson JOIN carsSold ON salesPerson.id = carsSold.salesPerson 
GROUP BY name, model HAVING ( COUNT( DISTINCT dealershipID ) ) > 1;

#P9
SELECT DISTINCT C.name FROM carsSold AS A 
JOIN carsSold AS B on A.salesPerson = B.salesPerson 
AND A.dealershipID <> B.dealershipID 
AND A.model = B.model 
JOIN salesPerson AS C on A.salesPerson = C.id;

#P9
SELECT DISTINCT salesPerson.name
FROM salesPerson join carsSold ON salesPerson.ID = carsSold.salesPerson
WHERE EXISTS (SELECT *
FROM carsSold as cs2
WHERE carsSold.dealershipID <> cs2.dealershipID 
AND carsSold.model = cs2.model AND salesPerson.ID = cs2.salesPerson);

#P9
SELECT DISTINCT name FROM salesPerson JOIN (SELECT * FROM carsSold cs1 WHERE EXISTS 
	(SELECT * FROM carsSold cs2 WHERE cs2.dealershipID <> cs1.dealershipID 
	AND cs2.model = cs1.model AND cs2.salesPerson = cs1.salesPerson)) AS tb1 
ON salesPerson.id = tb1.salesPerson;

#P10
SELECT model FROM (SELECT model, COUNT(DISTINCT dealershipID) as dealerCount 
FROM carsSold GROUP BY model) tbl WHERE dealerCount>=ALL (SELECT COUNT(DISTINCT dealershipID) 
FROM carsSold GROUP BY model);

#P11
SELECT name  
FROM manufacturer  
WHERE name IN  
 (SELECT cars.manuf FROM cars, carsSold 
 WHERE cars.manuf = carsSold.manuf 
     AND  (salesPrice - basePrice) =  
 (SELECT MAX(salesPrice - basePrice) FROM cars, carsSold)); 


#P13
SELECT manuf, model FROM (`salesPerson` JOIN `carsSold` ON salesPerson = id)
NATURAL JOIN `cars` WHERE name LIKE 'Mike%' AND topSpeed = (SELECT MAX(topSpeed)
FROM `carsSold` NATURAL JOIN `cars`);

#P13
SELECT cc.manuf, cc.model
FROM cars cc
INNER JOIN (
    SELECT cars.manuf, cars.model, MAX(topSpeed) as max
    FROM cars
    JOIN (
        SELECT manuf, model
        FROM carsSold
        JOIN (
            SELECT id
            FROM salesPerson
            WHERE substring_index(name, ' ', 1) = 'Mike'
        ) AS mikes
        ON carsSold.salesPerson = mikes.id
    ) AS mikesCars
    ON cars.manuf = mikesCars.manuf AND cars.model = mikesCars.model
) AS topSpeeds
ON cc.topSpeed = topSpeeds.max;