/**
use hurricane;
DELIMITER ;;

DROP PROCEDURE IF EXISTS printf;
CREATE PROCEDURE printf(theexpl TEXT, thecmd TEXT)
BEGIN

  select '' as '';
  select theexpl as '';
  select thecmd as '';
  PREPARE stmt FROM thecmd;
  execute stmt;
 END;

;;

DELIMITER ;
**/

\! echo
\! echo
\! echo "Switch to the flight database";
use flight; # must say the database to get the stored procedure
call printf("1. select with a subquery", "SELECT eee.ccc, eee.ooo from (select count(origin) as ccc, origin ooo from flights group by origin) as eee");
call printf("2. select with a subquery using WITH", "With eee as (select count(origin) as ccc, origin ooo from flights group by origin) select * from eee;");
call printf("3. first create temp table eee with counts by origin, then temps table fff with max count from eee, and finally from eee and fff select teh max count and print the origin", "with eee(cnt, org) as (select count(origin), origin from flights where Origin!='phl' group by origin), fff(mxx) as (select max(eee.cnt) from eee)  select eee.cnt,eee.org from eee,fff where eee.cnt=fff.mxx");
call printf("4. Same as previous, just moving the location of the column name aliases", "with eee as (select count(origin) as cnt, origin as org from flights where Origin!='phl' group by origin), fff as (select max(eee.cnt) as mxx from eee)  select eee.cnt,eee.org from eee, fff where eee.cnt=fff.mxx;
");
call printf("5. Max value is correct, but the flight number is wrong", "select max(flightnum), date, departuretime from flights");
call printf("6. note use for subquery in the where clause.  When doing so, need a single column.  Also if = need a single row.", "select * from flights as fl  where fl.flightnum = (select max(flightnum) from flights);");
call printf("7. Same as previous, just moves the location of the subquery", "with eee as (select max(flightnum) as mx from flights) select fl.flightnum, fl.date from flights as fl, eee  where fl.flightnum=eee.mx");
call printf("8. Inefficient way to get the right answer", "select count(origin) as cc, origin from flights where origin!='phl' group by origin order by cc desc limit 1;");
call printf("9. simle query, but ugly results", "select * from flights where month(date)=1 and day(date)=1 and hour(departuretime)<12 and origin='phl'");
call printf("10. JOIN", "select f.carrier, f.date, f.departuretime, a.name, a.country from flights as f join airports as a on f.destination=a.tla  where month(date)=1 and day(date)=1 and hour(departuretime)<12 and origin='phl'");
call printf("11. After join can select on stuff from either table", "select f.carrier, f.date, f.departuretime, a.name, a.country from flights as f join airports as a on f.destination=a.tla  where month(date)=1 and day(date)=1 and hour(departuretime)<12 and origin='phl' and a.country!='usa'");
call printf("12. Use full airline names from carrier table", "select c.name, f.date, f.departuretime, a.name, a.country from flights as f join airports as a on f.destination=a.tla  join carriers as c on c.acronym=f.carrier where month(date)=1 and day(date)=1 and hour(departuretime)<12 and origin='phl' and a.country!='usa';
");
call printf("13. find all departures from PHL in January to a US destination on a non-US based carrier", "select c.name, c.country, f.date, f.departuretime, a.name, a.country from flights as f join airports as a on f.destination=a.tla  join carriers as c on c.acronym=f.carrier where month(date)=1 and day(date)=1 and origin='phl' and a.country='usa' and c.country not like '% States'");

\! echo
\! echo
\! echo "Switch to hurricane database";
use hurricane;

call printf("", "show tables;");
call printf("", "describe typename;");
call printf("", "describe hurricane");
call printf("", "describe observation");
call printf("14. Name of Easternmost point (in the western hemisphere) hurricanes whose names start with A", "with mn as (select hid, min(longitude) from observation group by hid) select * from mn join hurricane he on he.hid=mn.hid where he.name like 'A%';");
call printf("15. name of easternmost hurricane", "select ob.type, ob.hid, date, he.name, ob.longitude from observation as ob inner join hurricane as he on he.hid=ob.hid inner join (select min(longitude) as minl from observation where longitudehemi='w' and type='hu') as tmax on ob.longitude=tmax.minl where ob.longitudehemi='W' and type='hu'");
call printf("16. Name of first observed storm in database that has a name other than unnamed (with the full name of the type)", "select ob.hid, min(date), nt.name, hu.name from observation as ob inner join typename as nt on ob.type=nt.type inner join hurricane as hu on hu.hid=ob.hid where hu.name <> 'UNNAMED'");
call printf("17. First observation for each named storm in database (with the full name of the type)", "select ob.hid, min(date), nt.name, hu.name from observation as ob inner join typename as nt on ob.type=nt.type inner join hurricane as hu on hu.hid=ob.hid where hu.name!='UNNAMED'  group by ob.hid order by date");
call printf("18. Last observation of each named hurricane when the storm was a typhoon", "select ob.hid, max(ob.date), ob.time, nt.name, hu.name from observation as ob inner join typename as nt on ob.type=nt.type inner join hurricane as hu on hu.hid=ob.hid where hu.name!='UNNAMED' and ob.type='DB' group by ob.hid;");
call printf("19 LOTS", "select count(*) from hurricane as hu, observation as ob;");
call printf("", "select count(*) from hurricane as hu, observation as ob where hu.hid=ob.hid ");
call printf("20. ", "select hu.hid, hu.name, ob.type from hurricane as hu, observation as ob where hu.hid=ob.hid;");
call printf("21. same query but using join", "select hu.hid, hu.name, ob.type  from hurricane as hu inner join observation as ob  on hu.hid=ob.hid");