-- ----------------------------------------------------------------
-- contacts3.sql
--
-- This script brings in the my_contacts table, modifies then
-- exercises it according to material in chapter 8 of Head First
-- SQL.
--
-- See page 360.
--
-- For convenience, the table has been renamed to just 'contacts.'
--
-- mysql> source C:\Users\russ\dev\contacts3.sql
-- ----------------------------------------------------------------

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

-- Add an id field in preparation to do some join operations...
ALTER TABLE contacts
	ADD COLUMN id INTEGER AUTO_INCREMENT NOT NULL FIRST,
	ADD PRIMARY KEY ( id );

SELECT * FROM contacts;

-- What is the significance of the foreign key link shown on this page?
SELECT c.last_name, c.first_name, p.profession
	FROM contacts as c
	INNER JOIN profession1 AS p
	ON c.id = p.id;

-- Equijoins, page 365-6...
-- Since we don't the status, zip_code, etc. tables, we can't do the others
SELECT c.email, p.profession
	FROM contacts AS c
	INNER JOIN profession1 AS p
	ON c.id = p.id;