CREATE TABLE TerritoryDescription(
	TerritoryID INT PRIMARY KEY,
	TerritoryDescription VARCHAR(50) NOT NULL
);

CREATE TABLE Technician(
	EmployeeID INT PRIMARY KEY,
	LastName VARCHAR(20) NOT NULL,
	FirstName VARCHAR(20) NOT NULL,
	LocationID INT NOT NULL,
	Address VARCHAR(100) NOT NULL,
	City VARCHAR(30) NOT NULL,
	PostalCode VARCHAR(15) NOT NULL,
	MobilePhone VARCHAR(10),
	Email VARCHAR(30) NOT NULL,
	CONSTRAINT fk_TechLoc FOREIGN KEY(LocationID)
		REFERENCES TerritoryDescription(TerritoryID)
);

CREATE TABLE TechnicianTerritory(
	EmployeeID INT NOT NULL,
	TerritoryID INT NOT NULL,
	CONSTRAINT pk_TechTtry PRIMARY KEY(EmployeeID, TerritoryID),
	CONSTRAINT fk_TechTryTech FOREIGN KEY(EmployeeID)
		REFERENCES Technician(EmployeeID),
	CONSTRAINT fk_TechTryTry FOREIGN KEY(TerritoryID)
		REFERENCES TerritoryDescription(TerritoryID)
);

CREATE TABLE Employee(
	EmployeeID INT PRIMARY KEY,
	LocationID INT NOT NULL,
	Address VARCHAR(50) NOT NULL,
	City VARCHAR(30) NOT NULL,
	ZipCode VARCHAR(15) NOT NULL,
	Phone VARCHAR(10) NOT NULL,
	Email VARCHAR(30) NOT NULL,
	CONSTRAINT fk_EmpLoc FOREIGN KEY(LocationID)
		REFERENCES TerritoryDescription(TerritoryID)
);

CREATE TABLE WorkTicket(
	TicketID INT PRIMARY KEY,
	EmployeeID INT NOT NULL,
	TechnicianID INT NOT NULL,
	Address VARCHAR(50) NOT NULL,
	City VARCHAR(30) NOT NULL,
	Region VARCHAR(20),
	PostalCode VARCHAR(15) NOT NULL,
	Phone VARCHAR(15) NOT NULL,
	Email VARCHAR(30) NOT NULL,
	CONSTRAINT fk_TktEmp FOREIGN KEY(EmployeeID)
		REFERENCES Employee(EmployeeID),
	CONSTRAINT fk_TktTech FOREIGN KEY(TechnicianID)
		REFERENCES Technician(EmployeeID)
);

CREATE TABLE Materials(
	PartID INT PRIMARY KEY,
	PartName VARCHAR(30) NOT NULL,
	Qty INT NOT NULL,
	Cost NUMERIC(5, 2) NOT NULL
);

CREATE TABLE TicketDetails(
	TicketID INT NOT NULL,
	PartID INT NOT NULL,
	WorkDetail VARCHAR(100),
	CONSTRAINT pk_TktDtl PRIMARY KEY(TicketID, PartID),
	CONSTRAINT fk_TktDtlTkt FOREIGN KEY(TicketID)
		REFERENCES WorkTicket(TicketID),
	CONSTRAINT fk_TktDtlMtrl FOREIGN KEY(PartID)
		REFERENCES Materials(PartID)
);


INSERT INTO TerritoryDescription(TerritoryID, TerritoryDescription) VALUES 
('1', 'CS Teritory'),
('2', 'Comics'),
('3', 'Agricultural'),
('4', 'Pure Maths'),
('5', 'Biotics'),
('6', 'Science');

INSERT INTO technicianterritory(EmployeeID, TerritoryID) VALUES 
('1', '1'),
('1', '3'),
('2', '2'),
('3', '5'),
('4', '4'),
('5', '6');

INSERT INTO technician (EmployeeID, LastName, FirstName, LocationID, Address, City, PostalCode, MobilePhone, Email) VALUES 
('1', 'Parker', 'wendy', '1234', '71 wall street', 'New Hamshire', '298 345', '9002345432', 'rob@fantastic.com'),
('2', 'Mario', 'Di Silva', '345676', '1265 Bramley', 'Huddersfield', 'HD1 7DF', '01484 555669', 'Silva@dwp.gsi.gov.uk'),
('3', 'Arthur', 'Great', '1234', '123 The Heathers', 'Northallerton', 'DL6 7TW', '9002345432', 'ag@ibm.co.uk'),
('4', 'Parker', 'wendy', '1234', 'The Grange Grange', 'Durham', 'DH9 8OQ', '0191 555 3654', 'mike@grange.co.uk'),
('5', 'Parker', 'wendy', '1234', 'Folyfoot Lane', 'New HamshireNewcastle-upon-Tyne	', 'NE1 3KA	', '0191 555 5287', 'grant@newc.co.uk');	

INSERT INTO employee (`EmployeeID`, `LocationID`, `Address`, `City`, `ZipCode`, `Phone`, `Email`) VALUES 
('1', '1', '82 Stafford Street', 'middlesbrough', 'TS7 7FG', '01642 555222', 'Sam.Jackson@dwp.gsi.gov.uk'),
('2', '2', 'Jingle Bell Lane', 'Durham', 'DH4 6DP', '0191 555 6567', 'Judith.Claxon@dwp.gsi.gov.uk'),
('3', '3', '89,High Street', 'Hartelepool', 'TS24 6RT', '01429 555865', 'Peter.Singh@dwp.gsi.gov.uk'),
('4', '4', '88,Thimblemill', 'middlesbrough', 'TS5 7YU', '01642 555987', 'Judith.Lacy@dwp.gsi.gov.uk'),
('5', '5', '84,Helperby', 'Hartelepool', 'TS24 9UY', '01429 555645', 'Jon.Smith@dwp.gsi.gov.uk');

INSERT INTO materials (`PartID`, `PartName`, `Qty`, `Cost`) VALUES 
('1', 'Lever', '23', '345'),
('2', 'Conner', '2', '33'),
('3', 'Glass', '20', '19'),
('4', 'Piper', '23', '4'),
('5', 'Exhaust', '3', '53');

INSERT INTO ticketdetails (`TicketID`, `PartID`, `WorkDetail`) VALUES 
('1', '1', 'plastering'),
('2', '2', 'elecric repair'),
('3', '3', 'window panes'),
('4', '4', 'water leakage'),
('5', '5', 'garage tools');

INSERT INTO workticket (`TicketID`, `EmployeeID`, `TechnicianID`, `Address`, `City`, `Region`, `PostalCode`, `Phone`, `Email`) VALUES 
('1', '1', '1', '123 The Heathers', 'Northallerton', 'Central', 'DHG VFG', '01642 555987', 'Singh@dwp.gsi.gov.uk'),
('2', '2', '2', '88,Thimblemill Lane', 'Middlesbrough', 'western', 'TS5 7YU', '01429 555865', 'Lacy@dwp.gsi.gov.uk'),
('3', '3', '3', '84,Helperby High', 'Hartelepool', 'western', 'TS24 9UY', '01642 555222', 'Smith@dwp.gsi.gov.uk'),
('4', '4', '4', '82,Stafford Street', 'leeds', 'northern', 'HD1 7XZ', '01429 555645', 'mike@grange.co.uk'),
('5', '5', '5', '79,Wakefield Road', 'Northallerton', 'eastern', 'TS7 7FG', '01484 555698', 'Walters@dwp.gsi.gov.uk');


UPDATE WorkTicket SET email='Judith.Lacy@dwp.gsi.gov.uk' WHERE employeeID = 3;

DELETE FROM `territorydescription` WHERE `TerritoryID`='5';

SELECT * FROM `territorydescription` WHERE `TerritoryID` BETWEEN 5 AND 6;

SELECT * FROM workticket WHERE Email LIKE "%Lacy%";

SELECT TOP 50 PERCENT * FROM materials;

SELECT DISTINCT(Address) FROM technician;

SELECT COUNT(PartID) as theCount FROM ticketdetails;

SELECT SUM(cost) FROM materials;