-- This is Oracle solution of Homework 1
--
-- The following are few details/comments:
-- VARCHAR is currently the same as VARCHAR2
-- pay attention to how you put ' in a string (character array)
-- Money is represented with NUMBER(7, 2) (2 decimal places)

CREATE TABLE CDs (
	author VARCHAR(30), 
	title VARCHAR(50), 
	year SMALLINT, 
	company VARCHAR(50), 
	rating SMALLINT 
		-- the following enforces the rating range
		CHECK (rating >=0 and rating <= 10),
	list_price NUMBER(7, 2),
	actual_price NUMBER(7, 2)
);

INSERT INTO CDs VALUES (
	'Barenaked Ladies', 'Rock Spectacle', 1996, 'Reprise Records', 8, 20.00, 15.99
);

INSERT INTO CDs VALUES (
	'U2', 'All that you can''t leave behind', 2000, 'Universal International Music', 
	8, 22.00, 16.99
);

INSERT INTO CDs VALUES (
	'Grant Lee Philips', 'Mobilize', 2001, 'Zoe Records', 9, 20.00, 20.00
); 

INSERT INTO CDs VALUES (
	'ABBA', 'Greatest Hits', 1992, 'Polygram Records', 9, 20.00, 10.99
);

INSERT INTO CDs VALUES (
	'Author2000', 'Title2000', 2000, 'Company2000', 2, 2000.00, 0.20
); 

SELECT title, author FROM CDs WHERE year = 2000;

DROP TABLE CDs;