DROP TABLE IF EXISTS Counselor;
DROP TABLE IF EXISTS Subject;


CREATE TABLE Counselor (
       counselor_id INTEGER PRIMARY KEY,
       first_name VARCHAR (50),
       nick_name VARCHAR (50),
       last_name VARCHAR (50),
       telephone VARCHAR (25),
       email VARCHAR (50),
       member_since DATE DEFAULT '0000-00-00');


CREATE TABLE Subject (
	subject_id INTEGER PRIMARY KEY,
	name VARCHAR (50),
	description TEXT,
	counselor_idfk SMALLINT UNSIGNED);


INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
                ('Jake', '"The Snake"', 'Roberts', '412 565-5656', 'snake@ifpwafcad.com', '2003-09-10');
INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
               	('Andre', '"The Giant"', '', '606 443-4567', 'bobo@ifpwafcad.com', '2001-01-12');
INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
               	('Brutus', '"The Barber"', 'Beefcake', '555 555-4432', 'beefcake@ifpwafcad.com', '2005-03-08');
INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
               	('Randy', '"Macho Man"', 'Savage', '555 317-4444', 'machoman@hotmail.com', '2000-11-10');
INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
               	('Ricky', '"The Dragon"', 'Steamboat','334 612-5678', 'r_steamboat@ifpwafcad.com', '1996-01-01');
INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
               	('George', '"The Animal"', 'Steele', '412 565-5656', 'george@ifpwafcad.com', '1996-01-01');
INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
               	('Koko', '"The Birdman"', 'B. Ware', '553 499-8162', 'birdman@ifpwafcad.com', '1999-12-03');
INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
               	('Greg', '"The Hammer"', 'Valentine', '617 889-5545', 'valentino@ifpwafcad.com', '1998-05-07');
INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES  	
               	('Bobby', '"The Brain"', 'Heenan', '777 513-3333', 'b_heenan@ifpwafcad.com', '2002-07-09');


INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
                ('Financial Consultancy', 'Investment advice and financial planning guidance, helping you to maximize your net worth through proper asset allocation. This includes the stocks, bonds, mutual funds, insurance products, and gambling strategies proven to work.', '9');
INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
		('Existential Psychotherapy', 'Often wonder what the purpose of life is? After learning the basics of Existential Psychotherapy, you''ll understand why you''re happy when you''re feeling happy, and why you''re not feeling happy when you''re not happy, allowing you to transcend to a state of pure bliss.', '7');
INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
	        ('Temper Management', 'Are your angry outbursts affecting your relationships with loved-ones? Do tantrums at work hinder your ability to perform? Temper management helps you to channel your anger into positive, life-changing productivity.', '4');
INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
		('Past-Life Regression', 'Past-Life Regression is a journey of the soul, backward and forward through time, like a yo-yo.', '2');
INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
	       	('Marriage Guidance', 'Even if you share a solid, caring and mutually beneficial relationship with your spouse, you may both still need urgent counseling. There''s only one way to find out. Contact us now!', '1');
INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
	       	('Crisis Management', 'Whether you''re a fireman, executive CEO, or housewife, applying crisis management techniques at the right moment can be life-saving for you as well as all those around you.', '3');
INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
	       	('Dream Analysis', 'Dream Analysis will allow you to delve into the depths of your subconcious. Your counselor will put you through a rigorous, disciplined training program, allowing you to remain in a waking state while dreaming. By the end, you''ll be able to analyse your dreams while you are having them!', '8');
INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
	       	('Hypnosis', 'Contrary to popular belief, hypnosis can be a powerful and effective form of counseling.', '6');
INSERT INTO Subject (name, description, counselor_idfk)
	VALUES   
		('Reiki', 'Need a massage but are afraid to let a stranger touch your body? Reiki could be the perfect solution for you.', '5');

