This script sets up the tables and example rows for our SQL coding challenge at RockThePost.

gistfile1.txt

-- Setup some tables.
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE `internal_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_user_id` int(11) DEFAULT NULL,
  `sender_user_id` int(11) DEFAULT NULL,
  `recipient_user_id` int(11) DEFAULT NULL,
  `body` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- Hydrate some example users;);nsert into users (`name`)
insert into users (`name`) values ('John'); -- 1
insert into users (`name`) values ('Mike'); -- 2
insert into users (`name`) values ('Greg'); -- 3
insert into users (`name`) values ('Bill'); -- 4
insert into users (`name`) values ('Kate'); -- 5
insert into users (`name`) values ('Sue'); -- 6
insert into users (`name`) values ('Linda'); -- 7
insert into users (`name`) values ('Mary'); -- 8

-- Linda wrote to Bill.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (7, 7, 4, 'Hi Bill. I am Linda.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (4, 7, 4, 'Hi Bill. I am Linda.', now());

-- Sue wrote to Bill.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (6, 6, 4, 'Hi Bill. I am Sue.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (4, 6, 4, 'Hi Bill. I am Sue.', now());

-- Sue wrote to Bill again.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (6, 6, 4, 'Hi Bill. You really seem great.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (4, 6, 4, 'Hi Bill. You really seem great.', now());

-- Bill wrote back to Sue.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (6, 4, 6, 'Hi Sue. I am Bill. Nice to meet you.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (4, 4, 6, 'Hi Sue. I am Bill. Nice to meet you.', now());

-- Sue wrote to Mike.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (6, 6, 2, 'Hi Mike. I am Sue.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (2, 6, 2, 'Hi Mike. I am Sue.', now());

-- Mike wrote back to Sue
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (6, 2, 6, 'Hi Sue. I am Mike. Nice to meet you.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (2, 2, 6, 'Hi Sue. I am Mike. Nice to meet you.', now());

-- John wrote to all of the girls. Nobody ever writes back. :(
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (1, 1, 5, 'Hi Kate. I am John.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (5, 1, 5, 'Hi Kate. I am John.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (1, 1, 6, 'Hello Sue. I am John.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (6, 1, 6, 'Hello Sue. I am John.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (1, 1, 7, 'Hello Linda. I am John.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (7, 1, 7, 'Hello Linda. I am John.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (1, 1, 8, 'Hello Mary. I am John.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (8, 1, 8, 'Hello Mary. I am John.', now());

-- Greg wrote to Linda.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (3, 3, 7, 'Hi Lindo. I am Greg.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (7, 3, 7, 'Hi Lindo. I am Greg.', now());

-- Linda responded to Greg.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (3, 7, 3, 'Hi Greg. I am Linda. Nice to meet you.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (7, 7, 3, 'Hi Greg. I am Linda. Nice to meet you.', now());

-- Greg wanted to fix a typo so he sent a second message.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (3, 3, 7, 'Hi Linda. Its Greg. Sorry for the typo!', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (7, 3, 7, 'Hi Linda. Its Greg. Sorry for the typo!', now());

-- Bill responded to Linda.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (4, 4, 7, 'Hi Linda. Its Bill... nice to meet you.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (7, 4, 7, 'Hi Linda. Its Bill... nice to meet you.', now());

-- Bill responded to Linda a second time.
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (4, 4, 7, 'Hi Linda. Its Bill again... I like your hat.', now());
insert into internal_messages (owner_user_id, sender_user_id, recipient_user_id, body, `created_at`) values (7, 4, 7, 'Hi Linda. Its Bill again... I like your hat.', now());