-- --------------------------------------------------------------------
-- Russ's contacts
--
-- This script is useful for chapters 2, 6 and 8 of Head First SQL.
-- Some names differ slightly. See also contacts.sql and contacts2.sql.
-- Consume this script thus:
--
-- mysql> source C:\Users\russ\dev\russ-contacts.sql
-- --------------------------------------------------------------------
-- DROP   DATABASE headfirst_sql;
-- CREATE DATABASE headfirst_sql;
-- USE             headfirst_sql;

DROP   TABLE russ_interests;
DROP   TABLE russ_contacts;
CREATE TABLE russ_contacts
(
	id         INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	lastname   VARCHAR(32),
	firstname  VARCHAR(16),
	phone      VARCHAR(10),
	email      VARCHAR(64),
	gender     CHAR(1),
	birthday   DATE,
	profession VARCHAR(64),
	city       VARCHAR(64),
	state      VARCHAR(2),
	status     VARCHAR(32),
	interests  VARCHAR(128),
	seeking    VARCHAR(128)
);

INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 872, 'Sullivan', 'Regis', '5554531122', 'regis@kathieleeisaflake.com', 'M', '1955-03-20', 'comedian', 'Cambridge', 'MA', 'single', 'animals, trading cards, geocaching', 'single woman' );
INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 459, 'Ferguson', 'Alexis', '5550983476', 'alexangel@yahoo.com', 'F', '1956-09-19', 'artist', 'Pflugerville', 'MA', 'single', 'animals', 'single man' );

INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 341, 'Moore', 'Nigel', '5552311111', 'nigelmoore@ranchersrule.com', 'M', '1975-08-28', 'rancher', 'Austin', 'TX', 'single', 'animals, horseback riding, movies', 'single woman' );
INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 1854, 'Fiore', 'Carla', '5557894855', 'cfiore@fioreanimalclinic.com', 'F', '1974-01-07', 'veternarian', 'Round Rock', 'TX', 'single', 'horseback riding, movies, animals, mystery novels, hiking', 'single man' );
INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 2066, 'Bettinghart', 'Chad', '5553451212', 'purplechad@hanging.com', 'M', '1980-03-21', 'retail', 'San Francisco', 'CA', 'single', 'parades, sewing, interior design', 'single man' );

-- Additional contacts from page 361...
SELECT "Additional contacts from page 361" AS "COMMENT";      -- (this is how to inject a status comment into MySQL output)
INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 3001, 'Everett', 'Joan', '5555559870', 'jeverett@mightygumball.net', 'F', '1978-04-03', 'artist', 'Salt Lake City', 'UT', 'single', 'sailing, hiking, cooking', 'single man' );
INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 3002, 'Singh', 'Paul', '5555558222', 'ps@tikibeanlounge.com', 'M', '1980-12-10', 'professor', 'New York', 'NY', 'single', 'dogs, spelunking', 'single woman' );
INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 3003, 'Baldwin', 'Tara', '5555553432', 'tara@breakneckpizza.com', 'F', '1970-09-01', 'chef', 'Boston', 'MA', 'single', 'movies, reading, cooking', 'single man' );

-- I added this one...
INSERT INTO russ_contacts ( id, firstname, lastname, phone, email, gender,  birthday, profession, city, state, status, interests, seeking )
VALUES ( 9999, 'Kennedy', 'Caroline', '5555557888', 'jk@deadkennedys.com', 'F', '1956-09-01', 'socialite', 'Boston', 'MA', 'single', 'fun, movies, reading, cooking', 'single man' );


-- -------------------------------------------------------------
-- Queries...
-- -------------------------------------------------------------
SELECT * FROM russ_contacts;

-- Nigel's query, based on interests, page 283...
SELECT "Nigel's query from page 283..." AS "COMMENT";
SELECT * FROM russ_contacts WHERE gender = 'F' AND status = 'single' AND state = 'TX' AND seeking LIKE '%single man%' AND birthday > '1970-08-28' AND birthday < '1980-08-28' AND interests LIKE '%animals%';

-- Regis' query, page 286...
SELECT "Regis' query from page 286..." AS "COMMENT";
SELECT * FROM russ_contacts WHERE gender = 'F' AND status = 'single' AND state = 'MA' AND seeking LIKE '%single man%' AND birthday > '1950-08-28' AND birthday < '1960-08-28';

-- Regis' updated query, page 288...
SELECT "Regis' updated query from page 288..." AS "COMMENT";
SELECT * FROM russ_contacts WHERE gender = 'F' AND status = 'single' AND state = 'MA' AND seeking LIKE '%single man%' AND birthday > '1950-03-20' AND birthday < '1960-03-20' AND SUBSTRING_INDEX( interests, ',', 1 ) = 'animals';

-- A query that produces something for Regis...
SELECT "A query that produces a viable date for Regis'..." AS "COMMENT";
SELECT * FROM russ_contacts WHERE gender = 'F' AND status = 'single' AND state = 'MA' AND seeking LIKE '%single man%' AND birthday > '1950-03-20' AND birthday < '1960-03-20';

-- -------------------------------------------------------------
-- Alter the columns on the fly...
-- (Listing the result will show NULLs.)
-- -------------------------------------------------------------
ALTER TABLE russ_contacts
    ADD COLUMN interest1 VARCHAR(64),
    ADD COLUMN interest2 VARCHAR(64),
    ADD COLUMN interest3 VARCHAR(64),
    ADD COLUMN interest4 VARCHAR(64),
    ADD COLUMN interest5 VARCHAR(64);
SELECT firstname, lastname, interest1, interest2, interest3, interest4, interests FROM russ_contacts;

-- -------------------------------------------------------------
-- Update the new columns with information from the old, single
-- single column "interests", page 290 (solution page 341).
-- -------------------------------------------------------------
UPDATE russ_contacts SET interest1 = SUBSTRING_INDEX( interests, ',', 1 );
SELECT firstname, lastname, interest1, interest2, interest3, interest4, interests FROM russ_contacts;
UPDATE russ_contacts SET interests = TRIM( RIGHT( interests, ( LENGTH( interests ) - LENGTH( interest1 ) - 1 ) ) );
SELECT firstname, lastname, interest1, interest2, interest3, interest4, interest5, interests FROM russ_contacts;

UPDATE russ_contacts SET interest2 = SUBSTRING_INDEX( interests, ',', 1 );
UPDATE russ_contacts SET interests = TRIM( RIGHT( interests, ( LENGTH( interests ) - LENGTH( interest2 ) - 1 ) ) );
SELECT firstname, lastname, interest1, interest2, interest3, interest4, interest5, interests FROM russ_contacts;

UPDATE russ_contacts SET interest3 = SUBSTRING_INDEX( interests, ',', 1 );
UPDATE russ_contacts SET interests = TRIM( RIGHT( interests, ( LENGTH( interests ) - LENGTH( interest3 ) - 1 ) ) );
SELECT firstname, lastname, interest1, interest2, interest3, interest4, interest5, interests FROM russ_contacts;

UPDATE russ_contacts SET interest4 = SUBSTRING_INDEX( interests, ',', 1 );
UPDATE russ_contacts SET interests = TRIM( RIGHT( interests, ( LENGTH( interests ) - LENGTH( interest4 ) - 1 ) ) );
SELECT firstname, lastname, interest1, interest2, interest3, interest4, interest5, interests FROM russ_contacts;

UPDATE russ_contacts SET interest5 = SUBSTRING_INDEX( interests, ',', 1 );
UPDATE russ_contacts SET interests = TRIM( RIGHT( interests, ( LENGTH( interests ) - LENGTH( interest5 ) - 1 ) ) );
SELECT firstname, lastname, interest1, interest2, interest3, interest4, interest5, interests FROM russ_contacts;

-- Regis' (super) updated query, page 292...
SELECT * FROM russ_contacts WHERE gender = 'F' AND status = 'single' AND state = 'MA' AND seeking LIKE '%single man%' AND birthday > '1950-03-20' AND birthday < '1960-03-20' AND
    (
	        interest1 = 'animals'
	     OR interest2 = 'animals'
	     OR interest3 = 'animals'
	     OR interest4 = 'animals'
	     OR interest5 = 'animals'
	 )
	 AND
	 (
	        interest1 = 'trading cards'
	     OR interest2 = 'trading cards'
	     OR interest3 = 'trading cards'
	     OR interest4 = 'trading cards'
	     OR interest5 = 'trading cards'
	 )
	 AND
	 (
	        interest1 = 'geocaching'
	     OR interest2 = 'geocaching'
	     OR interest3 = 'geocaching'
	     OR interest4 = 'geocaching'
	     OR interest5 = 'geocaching'
	 );


-- -------------------------------------------------------------
-- Create the russ_interests table
-- -------------------------------------------------------------
CREATE TABLE russ_interests (
	id         INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
   interest   VARCHAR(64) NOT NULL,
   contact_id INTEGER NOT NULL,
	CONSTRAINT russ_contacts_contact_id_fk
      FOREIGN KEY( contact_id )
      REFERENCES russ_contacts( id )
);

-- Describing the new table, see MUL on contact_id:
DESC russ_interests;