DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS PersonFriend;
CREATE TABLE Person (
  name varchar(20)
);

CREATE TABLE PersonFriend (
  id   int AUTO_INCREMENT PRIMARY KEY,
  name varchar(20),
  friend varchar(20)
);

INSERT INTO Person VALUES ('Alice');
INSERT INTO Person VALUES ('Bob');
INSERT INTO Person VALUES ('Zach');
INSERT INTO Person (name) VALUES ("Sylvester"),("Randall"),("Maris"),("Wyatt"),("Emma"),("Allen"),("Melodie"),("Shelly"),("Mallory"),("Gwendolyn");
INSERT INTO Person (name) VALUES ("Andrew"),("Candice"),("Justin"),("Aline"),("Edan"),("Trevor"),("Darrel"),("Harper"),("Wade"),("Lacota");
INSERT INTO Person (name) VALUES ("Cain"),("Ori"),("Alice"),("Leroy"),("Adrian"),("Julian"),("Brielle"),("Daphne"),("Lysandra"),("Beau");
INSERT INTO Person (name) VALUES ("Rhoda"),("Fitzgerald"),("Karleigh"),("Deacon"),("Melinda"),("Brynn"),("Hannah"),("Arsenio"),("Isabella"),("Emily");
INSERT INTO Person (name) VALUES ("Pandora"),("Zia"),("Madeline"),("Stephen"),("Omar"),("Cherokee"),("Erica"),("Amir"),("Shad"),("Xanthus");
INSERT INTO Person (name) VALUES ("Perry"),("Erich"),("Rosalyn"),("Kamal"),("Jayme"),("Maxwell"),("Rudyard"),("Hashim"),("Gil"),("Melanie");
INSERT INTO Person (name) VALUES ("Anastasia"),("Perry"),("Danielle"),("Kylie"),("Inga"),("Dylan"),("Beck"),("Jordan"),("Denise"),("James");
INSERT INTO Person (name) VALUES ("Peter"),("Piper"),("Elvis"),("Savannah"),("Ethan"),("Branden"),("Jaime"),("Hasad"),("Amelia"),("Lillian");
INSERT INTO Person (name) VALUES ("Beverly"),("Petra"),("Scarlett"),("Lesley"),("Joan"),("Timon"),("Claire"),("Nash"),("Grant"),("Tasha");
INSERT INTO Person (name) VALUES ("Cora"),("Yael"),("Luke"),("Jena"),("Xena"),("Anthony"),("Kessie"),("Taylor"),("Ryan"),("Urielle");

INSERT INTO PersonFriend (name,friend) VALUES ('Alice','Bob');
INSERT INTO PersonFriend (name,friend) VALUES ('Bob','Alice');
INSERT INTO PersonFriend (name,friend) VALUES ('Bob','Zach');
INSERT INTO PersonFriend (name,friend) VALUES ('Zach','Alice');
INSERT INTO PersonFriend (name, friend) VALUES ("Sylvester","Randall"),("Maris","Wyatt"),
("Emma","Allen"),("Melodie","Shelly"),("Mallory","Gwendolyn");
INSERT INTO PersonFriend (name, friend) VALUES ("Andrew","Candice"),("Justin","Aline"),("Edan","Trevor"),("Darrel","Harper"),("Wade","Lacota");
INSERT INTO PersonFriend (name, friend) VALUES ("Cain","Ori"),("Alice","Leroy"),("Adrian","Julian"),("Brielle","Daphne"),("Lysandra","Beau");
INSERT INTO PersonFriend (name, friend) VALUES ("Rhoda","Fitzgerald"),("Karleigh","Deacon"),("Melinda","Brynn"),("Hannah","Arsenio"),("Isabella","Emily");
INSERT INTO PersonFriend (name, friend) VALUES ("Pandora","Zia"),("Madeline","Stephen"),("Omar","Cherokee"),("Erica","Amir"),("Shad","Xanthus");
INSERT INTO PersonFriend (name, friend) VALUES ("Perry","Erich"),("Rosalyn","Kamal"),("Jayme","Maxwell"),("Rudyard","Hashim"),("Gil","Melanie");
INSERT INTO PersonFriend (name, friend) VALUES ("Anastasia","Perry"),("Danielle","Kylie"),("Inga","Dylan"),("Beck","Jordan"),("Denise","James");
INSERT INTO PersonFriend (name, friend) VALUES ("Peter","Piper"),("Elvis","Savannah"),("Ethan","Branden"),("Jaime","Hasad"),("Amelia","Lillian");
INSERT INTO PersonFriend (name, friend) VALUES ("Beverly","Petra"),("Scarlett","Lesley"),("Joan","Timon"),("Claire","Nash"),("Grant","Tasha");
INSERT INTO PersonFriend (name, friend) VALUES ("Cora","Yael"),("Luke","Jena"),("Xena","Anthony"),("Kessie","Taylor"),("Ryan","Urielle");

SELECT * FROM Person;
SELECT * FROM PersonFriend;

/* Find Bob's friends */
/*EXPLAIN*/ 
SELECT PersonFriend.friend
FROM Person JOIN PersonFriend
  ON Person.name = PersonFriend.name
WHERE Person.name = 'Bob';

/* find persons who are friends with Bob */
/*EXPLAIN*/ 
SELECT Person.name
FROM Person JOIN PersonFriend
  ON Person.name = PersonFriend.name
WHERE PersonFriend.friend = 'Bob';

/* Find Alice's friends of friends */
/*EXPLAIN*/ 
SELECT pf1.name as PERSON, pf3.name as FRIEND_OF_FRIEND
FROM PersonFriend pf1 JOIN Person
  ON pf1.name = Person.name
JOIN PersonFriend pf2
  ON pf1.friend = pf2.name
JOIN PersonFriend pf3
  ON pf2.friend = pf3.name
WHERE pf1.name = 'Alice' AND pf3.name <> 'Alice';