text: /*
     Statements used in 'Learning SQL Programming'
     
  */
  
  -- 00_03
  SELECT * FROM people;
  SELECT first_name FROM people;
  
  -- 01_01
  SELECT 'Hello, World!';
  SELECT first_name FROM people;
  SELECT last_name FROM people;
  SELECT first_name, last_name FROM people;
  SELECT last_name, first_name FROM people;
  SELECT * FROM people;
  SELECT first_name, state, company FROM people;
  SELECT company, first_name, quiz_points FROM people;
  
  -- 01_02
  SELECT * FROM people WHERE state='CA';
  SELECT * FROM people WHERE state='FL';
  SELECT * FROM people WHERE state='WA';
  SELECT * FROM people WHERE state='NY';
  SELECT * FROM people WHERE shirt_or_hat='NY';
  SELECT * FROM people WHERE shirt_or_hat='hat';
  SELECT first_name, last_name FROM people WHERE shirt_or_hat='shirt';
  SELECT first_name, last_name, shirt_or_hat FROM people WHERE shirt_or_hat='shirt';
  SELECT first_name, last_name, shirt_or_hat WHERE shirt_or_hat='shirt' FROM people; -- Note: This is an invalid statement.
  
  -- 01_03
  SELECT first_name, last_name FROM people WHERE state='CA' AND shirt_or_hat='shirt';
  SELECT first_name, last_name, team FROM people WHERE state='CA' AND shirt_or_hat='shirt' AND team='blue';
  SELECT first_name, last_name, team FROM people WHERE state='CA' AND shirt_or_hat='shirt' AND team IS 'blue';
  SELECT first_name, last_name, team FROM people WHERE state='CA' AND shirt_or_hat='shirt' AND team IS NOT 'blue';
  SELECT first_name, last_name, team FROM people WHERE state='CA' AND shirt_or_hat='shirt' AND team != 'blue';
  SELECT first_name, last_name, team, shirt_or_hat, state FROM people WHERE state='CA' OR state='CO' AND shirt_or_hat='shirt';
  SELECT first_name, last_name, team, shirt_or_hat, state FROM people WHERE (state='CA' OR state='CO') AND shirt_or_hat='shirt';
  
  -- 01_04
  SELECT first_name, last_name, state FROM people WHERE state LIKE 'C%';
  SELECT first_name, last_name, state FROM people WHERE state LIKE 'N%';
  SELECT first_name, last_name, state FROM people WHERE state LIKE '%N';
  SELECT first_name, last_name, state FROM people WHERE first_name LIKE 'A%';
  SELECT first_name, last_name, state FROM people WHERE first_name LIKE 'J%';
  SELECT first_name, last_name, state FROM people WHERE first_name LIKE '%J%';
  SELECT first_name, last_name, state FROM people WHERE first_name LIKE '%on%';
  SELECT first_name, last_name, state FROM people WHERE first_name LIKE '%ch%';
  SELECT first_name, last_name, state FROM people WHERE company LIKE '%LLC';
  SELECT first_name, last_name, state, company FROM people WHERE company LIKE '%LLC';
  SELECT first_name, last_name, state, company FROM people WHERE company LIKE '%LLC' LIMIT 5;
  SELECT first_name, last_name, state, company FROM people WHERE company LIKE '%LLC' LIMIT 10;
  SELECT first_name, last_name, state, company FROM people WHERE company LIKE '%LLC' LIMIT 10 OFFSET 5;
  
  -- 01_05
  SELECT first_name, last_name FROM people;
  SELECT first_name, last_name FROM people ORDER BY first_name;
  SELECT first_name, last_name FROM people ORDER BY first_name ASC;
  SELECT first_name, last_name FROM people ORDER BY first_name DESC;
  SELECT state, last_name, first_name FROM people ORDER BY state ASC, last_name ASC;
  SELECT state, last_name, first_name FROM people ORDER BY state ASC, last_name DESC;
  
  -- 01_06
  SELECT first_name FROM people;
  SELECT first_name, LENGTH(first_name) FROM people;
  SELECT DISTINCT(first_name) FROM people;
  SELECT DISTINCT(first_name) FROM people ORDER BY first_name;
  SELECT DISTINCT(shirt_or_hat) FROM people;
  SELECT COUNT(*) FROM people WHERE state='CA';
  
  -- 02_01
  SELECT first_name, state FROM people;
  SELECT first_name, state FROM people JOIN states;
  SELECT people.first_name, people.state, states.division FROM people JOIN states ON people.state=states.state_abbrev;
  SELECT * FROM people JOIN states ON people.state=states.state_abbrev;
  SELECT * FROM people JOIN states ON people.state=states.state_abbrev WHERE people.first_name LIKE 'j%' AND states.region='South';
  
  -- 02_02
  SELECT people.first_name, people.last_name, people.state, states.state_name FROM people JOIN states ON people.state=states.state_abbrev;
  SELECT people.first_name, people.last_name, people.state, states.state_name FROM states JOIN people ON people.state=states.state_abbrev;
  SELECT people.first_name, people.last_name, people.state, states.state_name FROM states LEFT JOIN people ON people.state=states.state_abbrev;
  SELECT DISTINCT(people.state), states.state_abbrev FROM states LEFT JOIN people ON people.state=states.state_abbrev ORDER BY people.state;
  
  -- 02_03
  SELECT first_name, COUNT(first_name) FROM people;
  SELECT first_name, COUNT(first_name) FROM people GROUP BY first_name;
  SELECT first_name, COUNT(first_name) FROM people GROUP BY last_name;
  SELECT last_name, COUNT(first_name) FROM people GROUP BY last_name;
  SELECT state, COUNT(state) FROM people GROUP BY state;
  SELECT state, quiz_points, COUNT(quiz_points) FROM people GROUP BY quiz_points;
  SELECT state, quiz_points, COUNT(quiz_points) FROM people GROUP BY state, quiz_points;
  
  -- 03_02
  SELECT 4+2;
  SELECT 1/3;
  SELECT 1/3.0;
  SELECT 3>2;
  SELECT 2>3;
  SELECT 5!=3;
  SELECT first_name, quiz_points FROM people WHERE quiz_points > 70;
  SELECT first_name, quiz_points FROM people WHERE quiz_points >= 70;
  SELECT MAX(quiz_points), MIN(quiz_points) FROM people;
  SELECT SUM(quiz_points) FROM people;
  SELECT team, COUNT(*) FROM people GROUP BY team;
  SELECT team, COUNT(*) FROM people;
  SELECT team, COUNT(*), SUM(quiz_points), (SUM(quiz_points)/COUNT(*)) AS average FROM people GROUP BY team;
  
  -- 03_03
  
  -- 03_04
  SELECT first_name, last_name FROM people;
  SELECT LOWER(first_name), UPPER(last_name) FROM people;
  SELECT LOWER(first_name), SUBSTR(last_name, 1, 5) FROM people;
  SELECT LOWER(first_name), SUBSTR(last_name, 2, 4) FROM people;
  SELECT LOWER(first_name), SUBSTR(last_name, 2) FROM people;
  SELECT LOWER(first_name), SUBSTR(last_name, -2) FROM people;
  SELECT LOWER(first_name), SUBSTR(last_name, -4) FROM people;
  SELECT LOWER(first_name), REPLACE(last_name, 'a', '_') FROM people;
  SELECT quiz_points FROM people ORDER BY quiz_points;
  SELECT quiz_points FROM people ORDER BY CAST(quiz_points AS char);
  SELECT MAX(CAST(quiz_points AS CHAR)) FROM people;
  SELECT MAX(CAST(quiz_points AS INT)) FROM people;
  
  -- 03_05
  SELECT first_name, last_name FROM people;
  SELECT first_name, UPPER(last_name) FROM people;
  SELECT first_name, UPPER(last_name) AS surname FROM people;
  
  -- 04_01
  INSERT INTO people (first_name) VALUES ('Bob');
  SELECT * FROM people;
  INSERT INTO people (first_name, last_name, state, city, shirt_or_hat) VALUES ('Mary', 'Hamilton', 'OR', 'Portland', 'hat');
  SELECT * FROM people;
  INSERT INTO people (first_name, last_name) VALUES ('George', 'White'), ('Jenn', 'Smith'), ('Carol', 'Anderson');
  SELECT * FROM people;
  
  -- 04_02
  SELECT * FROM people;
  UPDATE people SET first_name='Martha' WHERE first_name='George' AND last_name='White';
  SELECT * FROM people;
  SELECT * FROM people WHERE company='Fisher LLC';
  UPDATE people SET company='Megacorp Inc' WHERE company='Fisher LLC';
  SELECT * FROM people WHERE company='Fisher LLC';
  SELECT * FROM people WHERE company='Megacorp Inc';
  
  -- 04_03
  SELECT * FROM people;
  DELETE FROM people WHERE first_name='Martha' AND last_name='White';
  SELECT * FROM people;
  DELETE FROM people WHERE id_number IS NULL;
  SELECT * FROM people;
  