Name:_______________________
Edit this in Word. Paste and Bold the output of each command after it. 
Each blank line here indicates the pasting area for the output of the command.
Print in a small font, narrow margins, double-sided, turn in.
Use your mysql client to logon to instructor's MYSQL server, 
where a database and table have already been created for your use.
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)
Use the command history as much as possible.
To make cmis320pets the current database, use this command:
 USE cmis320pets;
A database consists of tables.
 SHOW TABLES;   #of selected/current database

A table has a structure, i.e. the name and datatype of the columns/fields:
 DESCRIBE pet;
 DESC  pet;     #the only command that can be abbreviated

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;

Distinct elimnates duplicates.
 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;

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;

Read the Tutorial (Chapter 3 or manual) for more info as you go through this.