-- ----------------------------------------------------------------
-- contacts2.sql
--
-- This script brings in the my_contacts table, then exercises it
-- according to material in chapter 8 of Head First SQL.
--
-- See page 352.
--
-- For convenience, the table has been renamed to just 'contacts.'
-- Table 'profession' has been renamed to 'profession1' in order
-- not to conflict with later stuff we're experimenting with.
--
-- mysql> source C:\Users\russ\dev\contacts2.sql
-- ----------------------------------------------------------------
DROP TABLE profession1;

source C:\Users\russ\dev\contacts.sql

-- Method 1: create, select and insert at (nearly) the same time...
SELECT "Method 1: create, select and insert at same time..." AS "COMMENT";
CREATE TABLE profession1
(
	id				INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	profession	VARCHAR(20)
);

INSERT INTO profession1 ( profession )
	SELECT profession FROM contacts
	GROUP BY profession
	ORDER BY profession;

SELECT * FROM profession1;

-- Method 2: create using select, then alter to add primary key...
SELECT "Method 2: create using select, then alter to add primary key..." AS "COMMENT";
DROP TABLE profession1;
CREATE TABLE profession1 AS
	SELECT profession FROM contacts
	GROUP BY profession
	ORDER BY profession;
SELECT * FROM profession;
ALTER TABLE profession1
	ADD COLUMN id INTEGER AUTO_INCREMENT NOT NULL FIRST,
	ADD PRIMARY KEY ( id );

SELECT * FROM profession;

-- Method 3: create with primary key and select all in one...
SELECT "Method 3: create with primary key and select all in one..." AS "COMMENT";
DROP TABLE profession1;
CREATE TABLE profession1
(
	id				INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	profession	VARCHAR(20)
) AS
	SELECT profession FROM contacts
	GROUP BY profession
	ORDER BY profession;

SELECT * FROM profession1;
