### Tutorial from DuBois, MySQL
###
### David Scott, 10 October, 2007
select now();
select now(),
user(),
version()
;

### This is the sample database used in the book
use samp_db;
select database();

### Entering data into a table
insert into president values
(
  'Bush','George', null,'New Haven','CT', '1946-07-06',null
)
### Restore the original state of the database
### Will not delete George W. or George H.W.
delete from president where first_name= 'George';

### select can do calculations, display text, access functions
### Items to be selected are separated by commas
select 2+2, 'Hello, world', version();

### Basic use of select command with where clause
select last_name, first_name from president
  where last_name = 'Bush'

### Can use Boolean logic to create complex conditions
### Use brackets to ensure conditions are combined correctly
select last_name, first_name, birth, state from president
  where birth <'1750-1-1' and (state='VA' or state='MA');

### The syntax for selecting when the value is null is different
### death = null will not work
select last_name, first_name from president
  where death is null;

### Output can be sorted as ascending or descending
### Default is ascending
### Ordering can be done on multiple columns
select last_name, first_name, state from president
  order by state desc, last_name asc;

### The number of records produced can be limited
select last_name, first_name, birth  from president
  order by birth limit 5;

### The concat function concatenates text items
select concat(first_name,' ',last_name), 
  concat(city,' ',state)
  from president limit 5;

### When a new column is created it can be given an appropriate name
select concat(first_name,' ',last_name) as name, 
  concat(city,' ',state) as birthplace
  from president limit 5;

### There are functions to deal with dates
select last_name, first_name, death
  from president 
  where death >= '1970-01-01' and death < '1980-01-01';

### Functions are available giving the month, day etc
select last_name, first_name, birth
  from president 
  where monthname(birth)='March' and dayofmonth(birth) = 29;

### There are functions to deal with numerical data
### Normal arithmetic operations are possible
select last_name, first_name, birth, death,
  floor((to_days(death) - to_days(birth)/365)) as age
  from president where death is not null
  order by age desc limit 5;

### Selecting using wildcards uses like rather than =
select last_name, first_name from president
  where last_name like 'W%';

### Other pattern matching is possible
### _ matches any single character
### This matches any four characters
select last_name, first_name from president
  where last_name like '____';

### distinct removes duplicates from the table
select distinct state from president order by state;

### count(*) gives the number of rows selected by your query
select count(*) from president;

### count(*) counts every row
### count(columnname) only counts non-null values
select count(*), count(suffix), count(death) 
  from president;

### count() can be combined with distinct 
### counts only the number of distinct values
select count(distinct state) as 'State Count' 
  from president;


### count combined with group can be used to create crosstabulations
select state, count(*) as count from president
  group by state order by count desc limit 5;

### the obvious statistical functions are available
select state as State,
  round(avg((to_days(death) - to_days(birth))/365.25),2) 
    as Age
  from president where death is not null
  group by state order by age limit 4;

### combining information from tables is very important
### this example combines a table with itself
### also illustrates identification of the correct column
### where the same name appears in more than one table
select p1.last_name, p1.first_name, p1.city, p1.state
  from president as p1, president as p2
  where p1.city = p2.city and p1.state = p2.state
        and (p1.last_name != p2.last_name or 
             p1.first_name != p2.first_name)
  order by state, city, last_name;