--Name:			Timothy Duke Perry
--Course Code:	INTN 2201
--Date:			2014-03-31

DROP TABLE IF EXISTS auto_info;

CREATE TABLE auto_info(
	id_num INTEGER PRIMARY KEY,
	-- Every vehicle must have a name, and it can be any length
	make VARCHAR(15) NOT NULL,
	model VARCHAR(20) NOT NULL,
	year SMALLINT NOT NULL,
	owner VARCHAR(25),
	-- Every vehicle must cost money
	msrp MONEY NOT NULL,
	-- msrp NUMERIC(10,2) NOT NULL,
	purchase_date DATE
);
-- http://www.teslamotors.com/blog/2013-model-s-price-increase
INSERT INTO auto_info(id_num, make, model, year, msrp) VALUES(
	1,
	'Tesla',
	'Model S (85 kWh)',
	2013,
	-- $79,900 USD to CAD
	89264.28);
-- http://www.autocar.co.uk/car-review/suzuki/splash/specs
INSERT INTO auto_info(id_num, make, model, year, msrp) VALUES(
	2,
	'Suzuki',
	'Splash',
	2014,
	-- £9,090 GBP to CAD
	16735.05);
-- From Batman Begins
-- http://kotaku.com/this-batman-tumbler-cost-over-1-million-is-going-to-r-499152006
INSERT INTO auto_info(id_num, make, model, year, msrp) VALUES(
	3,
	'Wayne ASD',
	'Batmobile',
	2005,
	-- $1.6 million USD to CAD
	1787520.00);
-- http://usnews.rankingsandreviews.com/cars-trucks/Lexus_LFA/
INSERT INTO auto_info(id_num, make, model, year, msrp) VALUES(
	4,
	'Lexus',
	'LFA',
	2012,
	-- $375,000 USD to CAD
	419175.00);
-- http://www.cars.com/toyota/tercel/1997/specifications
INSERT INTO auto_info(id_num, make, model, year, msrp) VALUES(
	5,
	'Toyota',
	'Tercel',
	1997,
	-- $2,750 USD to CAD
	3072.30);

SELECT id_num, make, model, year, msrp
	FROM auto_info
	ORDER BY year ASC;

UPDATE auto_info SET owner = 'Timothy Duke Perry'
	WHERE model = 'Batmobile';

DELETE FROM auto_info WHERE model = 'Tercel';

