-- ----------------------------------------------------------------
-- russ-toys.sql
--
-- This script creates the toys table, then exercises it according
-- to material in chapter 8 of Head First SQL.
--
-- See page 357-8.
--
--
-- mysql> source C:\Users\russ\dev\russ-toys.sql
-- ----------------------------------------------------------------
DROP TABLE more_boys;
DROP TABLE russ_boys;
DROP TABLE russ_toys;
CREATE TABLE russ_toys
(
	id		INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	toy	VARCHAR(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO russ_toys ( id, toy ) VALUES ( 1, 'hula hoop' );
INSERT INTO russ_toys ( id, toy ) VALUES ( 2, 'balsa glider' );
INSERT INTO russ_toys ( id, toy ) VALUES ( 3, 'toy soldiers' );
INSERT INTO russ_toys ( id, toy ) VALUES ( 4, 'harmonica' );
INSERT INTO russ_toys ( id, toy ) VALUES ( 5, 'baseball cards' );
INSERT INTO russ_toys ( id, toy ) VALUES ( 6, 'tinker toys' );
INSERT INTO russ_toys ( id, toy ) VALUES ( 7, 'etch-a-sketch' );
INSERT INTO russ_toys ( id, toy ) VALUES ( 8, 'slinky' );

SELECT * FROM russ_toys;

CREATE TABLE russ_boys
(
	id		INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	boy	VARCHAR(32)
);

INSERT INTO russ_boys ( id, boy ) VALUES ( 1, 'Davey' );
INSERT INTO russ_boys ( id, boy ) VALUES ( 2, 'Bobby' );
INSERT INTO russ_boys ( id, boy ) VALUES ( 3, 'Beaver' );
INSERT INTO russ_boys ( id, boy ) VALUES ( 4, 'Richie' );

SELECT * FROM russ_boys;

SELECT "Cartesion (cross) join..." AS "COMMENT";

SELECT t.toy, b.boy
	FROM russ_toys AS t
	CROSS JOIN russ_boys AS b;

-- The equijoin, page 364...
SELECT "The equijoin..." AS "COMMENT";
CREATE TABLE more_boys
(
	id			INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	boy		VARCHAR(32),
	toy_id	INTEGER NOT NULL
);

INSERT INTO more_boys ( id, boy, toy_id ) VALUES ( 1, 'Davey', 3 );
INSERT INTO more_boys ( id, boy, toy_id ) VALUES ( 2, 'Bobby', 5 );
INSERT INTO more_boys ( id, boy, toy_id ) VALUES ( 3, 'Beaver', 2 );
INSERT INTO more_boys ( id, boy, toy_id ) VALUES ( 4, 'Richie', 1 );

SELECT * FROM russ_toys;
SELECT * FROM more_boys;

SELECT b.boy, t.toy
	FROM more_boys AS b
	INNER JOIN russ_toys AS t
		ON b.toy_id = t.id
	ORDER BY b.boy;

-- The non-equijoin, page 367...
SELECT "Toys each boy doesn't have..." AS "COMMENT";
SELECT more_boys.boy, russ_toys.toy
	FROM more_boys
	INNER JOIN russ_toys
		ON more_boys.toy_id <> russ_toys.id
	ORDER BY more_boys.boy;

-- The natural join, page 368...
SELECT more_boys.boy, russ_toys.toy
	FROM more_boys
	NATURAL JOIN russ_toys;