/** * This is a C-style comment. When in a file to be run by mysql, anything * in the C-style comment will be ignored * * Below are two things that will not be discussed in class. First is a stored * procedure "printf". The second is the use of that stored procedure, using "call". * You should be able to use this procedure, but cannot modify it, etc */ # another comment style /** * This procedure to, with a single command show * An explanation of the command * The command itself * The result of executing that command * NOTE that this procedure is commented out. It is here only for completeness. * It is already defined in mysql and does not need to be recreated with each use. use flight; 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 "Use the flight database" \! echo "use flight;" use flight; /** * the stored procedure printf is stored in the flight database so * it cannot be used until the database is linked */ call printf("1 Show all databases", "show databases;"); call printf("2. Show the tables in the flight database.", "show tables;"); call printf("3. Columns in the airports table", "describe airports;"); call printf("4. Columns in the carriers table", "describe carriers;"); call printf("5. Columns in the flights table", "describe flights;"); call printf("6. Simple query", "select flightnum, date from flight"); call printf("7. Simple query", "select * from airports"); call printf("8. Column aliases to make things easier to read or use", "select name as nombre from carriers"); call printf("9. Restricting returns using where", "select * from flights where carrier='dl'"); call printf("10. show all flights that departed in the 2pm hour on January 2", "select * from flights where Month(Date)=1 and day(date)=2 and hour(departuretime)=14; "); call printf("11. show count of flights that departed in the 2pm hour on January 2", "select count(*) from flights where Month(Date)=1 and day(date)=2 and hour(departuretime)=14; "); call printf("12. show all distinct origins for flights that departed in teh 2pm hour on Jan 2", "select distinct origin from flights where Month(Date)=1 and day(date)=2 and hour(departuretime)=14"); call printf("13. As above, but just the count", "select count(distinct origin) from flights where Month(Date)=1 and day(date)=2 and hour(departuretime)=14"); call printf("14. How many flights came from each origin", "select count(origin),origin from flights group by origin;"); call printf("15. How many times does each flight number appear", "select count(*), flightnum from flights group by flightnum;"); call printf("16. what are the 10 most frequently used flight numbers", "select count(*) as cntf, flightnum from flights group by flightnum order by cntf desc limit 10;"); call printf("17. What was the largest departure delay", "select max(departuredelay) from flights;"); call printf("18. What was the largest departure delay (v2)", "select max(departuredelay), flightnum from flights;"); call printf("19. What was the largest departure delay (v3)", "select departuredelay, flightnum from flights order by departuredelay desc limit 1"); call printf("20. What is the smallest arrival delay", "select arrivaldelay from flights where arrivaldelay>0 order by arrivaldelay asc limit 1"); call printf("21. What is the smallest arrival delay (v2)", "select count(arrivaldelay), arrivaldelay from flights where arrivaldelay>0 group by arrivaldelay order by arrivaldelay asc limit 1"); call printf("22. What is the smallest arrival delay", "select flightnum, arrivaldelay from flights where arrivaldelay=1 group by flightnum order by flightnum asc"); call printf("23. Which is greater, the number of destinations or the number of origins Q1", "select count(distinct origin) from flights"); call printf("24. Which is greater, the number of destinations or the number of origins Q2", "select count(distinct destination) from flights"); call printf("25. number of carriers", "select count(*) from carriers"); call printf("26. number of carriers into philly", "select count(distinct carrier) from flights where destination='phl'; "); call printf("27. number of carriers out of philly", "select count(distinct carrier) from flights where origin='phl'"); call printf("28. How many carriers fly from each location from which you can fly directly to PHL?", "select count(distinct carrier),origin from flights group by origin;"); call printf("29. How many flights go past midnight?", "select count(*) from flights where hour(arrivaltime)hour(arrivaltime) group by origin;");