Learn SQL Hard Way - snippets

crapload_of_data.sql

INSERT INTO pet (id, name, breed, age, dead)
	VALUES (5, "shimmer", "unicorn", 500, 1);

INSERT INTO pet (id, name, breed, age, dead)
	VALUES (6, "water bottle", "faun", 4, 1);

INSERT INTO pet (id, name, breed, age, dead)
	VALUES (7, "luis", "elephant", 282, 1);

INSERT INTO pet (id, name, breed, age, dead)
	VALUES (8, "jessica", "snake", 24, 0);

INSERT INTO pet (id, name, breed, age, dead)
	VALUES (9, "mylene", "turtle", 90, 0);

INSERT INTO pet (id, name, breed, age, dead)
	VALUES (10, "jenna", "wombat", 4, 0);

INSERT INTO pet (id, name, breed, age, dead)
	VALUES (11, "bacon", "cat", 24, 0);

INSERT INTO pet (id, name, breed, age, dead)
	VALUES (12, "steak", "worm", 24, 0);

INSERT INTO person (id, first_name, last_name, age)
	VALUES (5, "courtney", "norvig", 21);

INSERT INTO person (id, first_name, last_name, age)
	VALUES (6, "kimmy", "norvig", 21);

INSERT INTO person (id, first_name, last_name, age)
	VALUES (7, "natasha", "norvig", 21);

INSERT INTO person (id, first_name, last_name, age)
	VALUES (8, "tracy", "norvig", 21);

INSERT INTO person (id, first_name, last_name, age)
	VALUES (9, "allison", "norvig", 21);

INSERT INTO person_pet (person_id, pet_id) VALUES (5, 5);

INSERT INTO person_pet (person_id, pet_id) VALUES (6, 6);

INSERT INTO person_pet (person_id, pet_id) VALUES (7, 7);

INSERT INTO person_pet (person_id, pet_id) VALUES (8, 8);

ex13_b_mods.sql

/* Add a dead column to person that"s like the one in pet. */
ALTER TABLE person ADD COLUMN dead INTEGER;

/* Add a phone_number column to person. */
ALTER TABLE person ADD COLUMN phone_number INTEGER;

/* Add a salary column to person that is float. */
ALTER TABLE person ADD COLUMN salary FLOAT;

/* Add a dob column to both person and pet that is a DATETIME. */
ALTER TABLE person ADD COLUMN dob DATETIME;
ALTER TABLE pet ADD COLUMN dob DATETIME;

/* Add a purchased_on column to person_pet of type DATETIME. */
ALTER TABLE person_pet ADD COLUMN purchased_on DATETIME;

/* Add a parent to pet column that"s an INTEGER and holds the id for this pet"s parent. */
ALTER TABLE pet ADD COLUMN parent INTEGER;

/* Update the existing database records with the new column data using UPDATE statements. Don"t forget about the purchased_on column in person_pet relation table to indicate when that person bought the pet. */
UPDATE person SET dead=0, phone_number=123, salary=123, dob="2008-11-09" WHERE first_name="zed";
UPDATE person SET dead=0, phone_number=179, salary=179, dob="2000-11-09" WHERE first_name="crystal";
UPDATE person SET dead=0, phone_number=179, salary=179, dob="1999-11-09" WHERE first_name="brianna";
UPDATE person SET dead=0, phone_number=113, salary=113, dob="2005-11-09" WHERE first_name="shasta";
UPDATE person SET dead=0, phone_number=149, salary=149, dob="2008-11-09" WHERE first_name="courtney";
UPDATE person SET dead=0, phone_number=50, salary=50, dob="2002-11-09" WHERE first_name="kimmy";
UPDATE person SET dead=0, phone_number=72, salary=72, dob="2009-11-09" WHERE first_name="natasha";
UPDATE person SET dead=0, phone_number=96, salary=96, dob="2004-11-09" WHERE first_name="tracy";
UPDATE person SET dead=0, phone_number=66, salary=66, dob="2004-11-09" WHERE first_name="allison";
UPDATE person_pet SET purchased_on="2008-11-09" WHERE pet_id=0;
UPDATE person_pet SET purchased_on="2008-11-09" WHERE pet_id=1;
UPDATE person_pet SET purchased_on="1999-11-09" WHERE pet_id=2;
UPDATE person_pet SET purchased_on="2005-11-09" WHERE pet_id=3;
UPDATE person_pet SET purchased_on="2008-11-09" WHERE pet_id=4;
UPDATE person_pet SET purchased_on="2002-11-09" WHERE pet_id=5;
UPDATE person_pet SET purchased_on="2009-11-09" WHERE pet_id=6;
UPDATE person_pet SET purchased_on="2004-11-09" WHERE pet_id=7;
/*
UPDATE pet SET parent="zed" WHERE name="fluffy";
UPDATE pet SET parent="zed" WHERE name="gigantor";
UPDATE pet SET parent="brianna" WHERE name="puffy";
UPDATE pet SET parent="shasta" WHERE name="muffin";
UPDATE pet SET parent="juna" WHERE name="sparkles";
UPDATE pet SET parent="courtney" WHERE name="shimmer";
UPDATE pet SET parent="kimmy" WHERE name="water bottle";
UPDATE pet SET parent="natasha" WHERE name="luis";
UPDATE pet SET parent="tracy" WHERE name="jessica";
UPDATE pet SET parent="allison" WHERE name="mylene";
 */
UPDATE pet SET parent="zed" WHERE id=0;
UPDATE pet SET parent="zed" WHERE id=1;
UPDATE pet SET parent="brianna" WHERE id=2;
UPDATE pet SET parent="shasta" WHERE id=3;
UPDATE pet SET parent="juna" WHERE id=4;
UPDATE pet SET parent="courtney" WHERE id=5;
UPDATE pet SET parent="kimmy" WHERE id=6;
UPDATE pet SET parent="natasha" WHERE id=7;
UPDATE pet SET parent="tracy" WHERE id=8;
UPDATE pet SET parent="allison" WHERE id=9;

/* Add 4 more people and 5 more pets and assign their ownership and what pet"s are parents. On this last part remember that you get the id of the parent, then set it in the parent column. */

/* Write a query that can find all the names of pets and their owners bought after 2004. Key to this is to map the person_pet based on the purchased_on column to the pet and parent. */

/* Write a query that can find the pets that are children of a given pet. Again look at the pet.parent to do this. It"s actually easy so don"t over think it. */

learn-sql-the-hard-way.sql

SELECT * FROM pet;

UPDATE pet SET name = "Zed's Pet" WHERE id IN ( 
           SELECT pet.id
           FROM pet, person_pet, person
           WHERE 
           person.id = person_pet.person_id AND
           pet.id = person_pet.pet_id AND 
           person.first_name = "zed"
       );
 
SELECT * FROM pet;



SELECT pet.id, person.id FROM pet, person, person_pet WHERE
	pet.id = person_pet.pet_id AND
	person.id = person_pet.person_id;

SELECT pet.name, person.first_name, purchased_on FROM pet, person, person_pet WHERE
	pet.id = person_pet.pet_id AND
	person.id = person_pet.person_id AND
	purchased_on >= "2005-00-00";