Name:_______________________
Using Notepad, edit this, PASTE the output of each command after it.
Print in a small font, narrow margins, turn in.

Read the Tutorial as you go throught this.
Use the mysql client to logon to your MySQL server.

Use the SHOW statement to find out what databases currently exist on the server:
 SHOW DATABASES;
(commands and names do NOT have to be capitalized)
(SQL statements are terminated by a semicolon)

 CREATE DATABASE menagerie;

Creating a database does not select it for use; you must do that explicitly. 
To make menagerie the current database, use this command:
 USE menagerie;

 SHOW TABLES;   #of selected/current database

Use a CREATE TABLE statement to specify the layout of your table:
 CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
   species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
(varchar is a variable-length string, char is fixed-length string)
Now   SHOW TABLES;  will list your pet table.

To verify that your table was created the way you expected, use a DESCRIBE statement:
 DESCRIBE pet;
 DESC  pet;     #the only command that can be abbreviated?

(download the pet.txt from the class web site.  Save it directly as is (i.e do NOT copy and paste it)
To load the text file pet.txt into the pet table, use this command:
 LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
(path is relative to folder you started mysql from.  Simplest if it's in the same folder.)
(Note that Nulls are represented by \N in a file)

Add a new record using an INSERT statement like this:
 INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Modify a record using an UPDATE statement:
 UPDATE pet SET birth='1989-08-31' WHERE name='Bowser';

Delete a record using a DELETE statement:
DELETE FROM pet WHERE name='Puffball';


The SELECT statement is used to pull information from a table. 
The simplified general form of it is:
SELECT what_to_select
 FROM which_table
 WHERE conditions_to_satisfy;

The simplest form of SELECT retrieves everything from a table:
* means all columns.  The Where clause is optional:
 SELECT * FROM pet;

Select a particular row(s):
 SELECT * FROM pet WHERE name='Bowser';

 SELECT * FROM pet WHERE birth>='1998-1-1';

 SELECT * FROM pet WHERE species='dog' AND sex='f';    #boolean operators

 SELECT * FROM pet WHERE species='snake' OR species='bird';

 SELECT * FROM pet WHERE species='cat' AND sex='m' OR species='dog' AND sex='f';

 SELECT * FROM pet WHERE death IS NULL;         #test if Null. Never do: =NULL
 SELECT * FROM pet WHERE death IS NOT NULL;     #test if not Null

Select a particular column(s):
 SELECT name,birth FROM pet; 
 SELECT owner FROM pet;
 SELECT DISTINCT owner FROM pet;
 
Select a particular column(s) of particular row(s):
 SELECT name,species,birth FROM pet WHERE species='dog' OR species='cat';
 
Sorting:
 SELECT name,birth FROM pet ORDER BY birth;
 SELECT name,birth FROM pet ORDER BY birth DESC;
primary, secondary sorts:
 SELECT name,species,birth FROM pet ORDER BY species,birth DESC;

Skip 3.3.4.5
 
Patterns (wildcards): 
SQL: % any string, _ one char
 SELECT * FROM pet WHERE name LIKE 'b%';   #starts b
 SELECT * FROM pet WHERE name LIKE '%fy';  #ends fy
 SELECT * FROM pet WHERE name LIKE '%w%';  #contains w
 SELECT * FROM pet WHERE name LIKE '_____'; #5 chars


Counting:
 SELECT COUNT(*) FROM pet;      #all rows

 #counts of each group:
 SELECT owner,COUNT(*) FROM pet  GROUP BY owner;
 SELECT species,COUNT(*) FROM pet  GROUP BY species;
 SELECT sex,COUNT(*) FROM pet  GROUP BY sex;

 SELECT sex,COUNT(*) FROM pet  WHERE species='dog'  GROUP BY sex;
 SELECT sex,COUNT(*) FROM pet  WHERE species='dog' OR species='cat'  GROUP BY sex;

 #selecting a field and Count without Grouping By that field is error or meaningless:
 --  SELECT sex, COUNT(*) FROM pet;

 #combos:
 SELECT species,sex,COUNT(*) FROM pet GROUP BY species,sex;
 SELECT species,sex,COUNT(*) 
  FROM pet
  WHERE species='dog' OR species='cat'
  GROUP BY species,sex;
 SELECT species,sex,COUNT(*) 
  FROM pet
  WHERE sex IS NOT NULL
  GROUP BY species,sex;

Skip 3.3.4.9
and 3.5 to the end of the Tutorial for now.