-- Seth McNamee
-- Test 3

-- 1

DELETE FROM employee
WHERE absences > 3;

-- 2

INSERT INTO tools (fk_payroll_number, purchase_date, payroll_deduct, tool_name, tool_cost, payment_amount, last_payment_amount, first_payment_date, last_payment_date)
VALUES (37, 2000-01-01, 'Y', "FOUNTAIN PEN", 23.00, 5.00, 3.00, 2000-01-31, 2000-05-30);

-- 3

UPDATE employee
SET absences = absences + 2
WHERE fk_department = "INT";

-- 4

CREATE INDEX tools_purchase_date_ix
	ON tools (purchase_date);

-- 5

UPDATE glasses
SET optician = "Uptown Optical"
WHERE optician = "Downtown Optical";

-- 6

DROP DATABASE IF EXISTS movies;

CREATE DATABASE movies;

USE movies;

CREATE TABLE dvd (
	dvd_id		INT		PRIMARY KEY		AUTO_INCREMENT,
	part_number		INT		NOT NULL		UNIQUE,
	fk_genre_id		CHAR(4),
	release_date	DATETIME	DEFAULT NOW(),
	resolution		VARCHAR(4),
	director		VARCHAR(30),
	name			VARCHAR(40),
	CONSTRAINT genre_fk_genre_id
		FOREIGN KEY (fk_genre_id)
		REFERENCES genre (genre_id),
	CONSTRAINT chk_resolution
		CHECK resolution IN ("SD", "HD", "FULL", "QHD", "2K", "4K", "8K")
);