Bait Interview Question #1

BaitInterviewQuestion1.sql

-- Adminer 3.3.4 MySQL dump

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `craft_list`;
CREATE TABLE `craft_list` (
  `item_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  KEY `item_id` (`item_id`),
  CONSTRAINT `craft_list_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `craft_list` (`item_id`, `quantity`) VALUES
(1,	3),
(2,	1),
(3,	2),
(4,	2);

DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `items` (`id`, `name`) VALUES
(1,	'Stone pickaxe'),
(2,	'Iron pickaxe'),
(3,	'Stone shovel'),
(4,	'Iron shovel');

DROP TABLE IF EXISTS `materials`;
CREATE TABLE `materials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `materials` (`id`, `name`) VALUES
(1,	'Wood'),
(2,	'Stone'),
(3,	'Iron');

DROP TABLE IF EXISTS `recipes`;
CREATE TABLE `recipes` (
  `item_id` int(11) NOT NULL,
  `material_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  KEY `item_id` (`item_id`),
  KEY `material_id` (`material_id`),
  CONSTRAINT `recipes_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `recipes_ibfk_2` FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `recipes` (`item_id`, `material_id`, `quantity`) VALUES
(1,	2,	3),
(1,	1,	2),
(2,	1,	2),
(2,	3,	3),
(3,	1,	2),
(3,	2,	1),
(4,	1,	2),
(4,	3,	1);

-- 2012-08-20 13:10:50