Happy Foggy Thursday,

Just to reiterate last night's MySQL assignments...

1.  Read chapter 13 in the PHP book.  (that's the first chapter
that discusses MySQL.  There is also reading assigned for  PHP --
chapters 9 & 10 I believe.)

2.  For the database assignment, (1) sketch the database table(s) that you
think would be useful for a library that loaned CD's; (2) develop
the create table statements that correspond to the table sketches.

3.  optional not to be turned in

Login to your grace account and go into mysql 
(type 
mysql youraccountname
from the linux prompt on grace)

(1) you can try out your create statements to see if they work.
(you will need to drop any tables before (re-)creating them.)

(2) Using instructions from Randy (forthcoming) you can
load the presidents database.  After that you can (from
within mysql) guess what the following SELECT statements
will do and  try them out.


SELECT last_name, first_name FROM president WHERE last_name="ROOSEVELT";

SELECT last_name, first_name FROM president WHERE last_name="roosevelt";

SELECT last_name, first_name, birth FROM president WHERE birth < "1750-1-1";

SELECT last_name, first_name, birth, state FROM president WHERE birth < 
"1750-1-1" AND (state="VA" OR state="MA");

SELECT last_name, first_name, state FROM president 
WHERE state="VA" OR state="MA";

SELECT last_name, first_name FROM president WHERE death IS NULL;

SELECT last_name, first_name, suffix FROM president WHERE suffix IS NOT NULL;

SELECT last_name, first_name FROM president 
WHERE death <=> NULL;

SELECT last_name, first_name, suffix FROM president 
WHERE NOT (suffix <=> NULL);

SELECT last_name, first_name FROM president ORDER BY last_name;

SELECT last_name, first_name FROM president ORDER BY last_name DESC;

SELECT last_name, first_name, state FROM president 
ORDER BY state DESC, last_name ASC;

SELECT last_name, first_name, birth FROM president ORDER BY birth LIMIT 5;

SELECT last_name, first_name, birth FROM president ORDER BY birth LIMIT 10, 5;

SELECT last_name, first_name FROM president ORDER BY RAND() LIMIT 1;

SELECT CONCAT(first_name," ",last_name),CONCAT(city,", ",state) FROM president;

SELECT CONCAT(first_name," ",last_name) AS Name, CONCAT(city,", ",state) 
As Birthplace FROM president;

SELECT CONCAT(first_name," ",last_name) AS "President Name", 
CONCAT(city,", ",state) As "Place of Birth" FROM president;

SELECT last_name, first_name, death FROM president 
WHERE death >= "1970-01-01" AND death < "1980-01-01";

SELECT last_name, first_name, birth FROM president 
WHERE MONTHNAME(birth) = "March";

SELECT COUNT(*) FROM president;

SELECT COUNT(*),COUNT(suffix),COUNT(death) FROM president;

SELECT COUNT(DISTINCT state) FROM president;

SELECT state, COUNT(*) FROM president GROUP BY state;

SELECT state, COUNT(*) AS count FROM president GROUP BY state 
ORDER BY count DESC;

SELECT MONTH(birth) as Month, MONTHNAME(birth) as Name, COUNT(*) AS count FROM 
president GROUP BY Name ORDER BY Month;