-- SQL script related to Tutorial 1 on 2018-11-09

CREATE TABLE Departments(
	department_id INT NOT NULL,
	department_name CHAR(50) NOT NULL,
	CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

INSERT INTO Departments (department_id, department_name) VALUES (1, 'Computer Science and Engineering');
INSERT INTO Departments (department_id, department_name) VALUES (2, 'Mechanics and Maritime Sciences');
INSERT INTO Departments (department_id, department_name) VALUES (3, 'Mathematical Sciences');
INSERT INTO Departments (department_id, department_name) VALUES (4, 'Electrical Engineering');
INSERT INTO Departments (department_id, department_name) VALUES (5, 'MC2');

--Question 1 (solution 1)

CREATE TABLE Employees (
	employee_number INT PRIMARY KEY,
	employee_name TEXT NOT NULL,
	department INT NOT NULL REFERENCES Departments(department_id),
	salary INT NOT NULL
);

INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (1, 'Sarah', 1, 40000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (2, 'Robin', 1, 39000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (3, 'Lisa', 5, 50000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (4, 'Elton', 5, 40000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (5, 'Sandy', 5, 45000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (6, 'James', 5, 42000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (7, 'Amir', 5, 47500);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (8, 'John', 5, 49000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (9, 'Petra', 2, 30000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (10, 'Rob', 2, 35000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (11, 'Peter Joe', 4, 60000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (12, 'JOE M.', 4, 70000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (13, 'Lisa too', 5, 50000);

-- Question 1 (solution 2, if we want to use constraint form explicitly)

DROP TABLE Employees;

CREATE TABLE Employees (
	employee_number INT NOT NULL, 
	employee_name TEXT NOT NULL, 
	department INT NOT NULL,
	salary INT NOT NULL,
	CONSTRAINT employees_pk PRIMARY KEY (employee_number), 
	CONSTRAINT fk_departments FOREIGN KEY (department) REFERENCES Departments(department_id)
);

INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (1, 'Sarah', 1, 40000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (2, 'Robin', 1, 39000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (3, 'Lisa', 5, 50000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (4, 'Elton', 5, 40000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (5, 'Sandy', 5, 45000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (6, 'James', 5, 42000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (7, 'Amir', 5, 47500);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (8, 'John', 5, 49000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (9, 'Petra', 2, 30000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (10, 'Rob', 2, 35000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (11, 'Peter Joe', 4, 60000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (12, 'JOE M.', 4, 70000);
INSERT INTO Employees (employee_number, employee_name, department, salary) VALUES (13, 'Lisa too', 5, 50000);

-- Question 2

CREATE TABLE Suppliers (
	sid INT PRIMARY KEY,
	sname VARCHAR(30) NOT NULL,
	city VARCHAR(30) NOT NULL,
	street VARCHAR(30) NOT NULL
);

INSERT INTO Suppliers (sid, sname, city, street) VALUES (1, 'Sup1', 'Gothenburg', 'Gibraltargatan 1');
INSERT INTO Suppliers (sid, sname, city, street) VALUES (2, 'Sup2', 'Stockholm', 'Karin Boyes Gata 10');
INSERT INTO Suppliers (sid, sname, city, street) VALUES (3, 'Sup3', 'Uppsala', 'Gibraltargatan 2');
INSERT INTO Suppliers (sid, sname, city, street) VALUES (4, 'Sup4', 'Helsinki', 'Palm Street 3');
INSERT INTO Suppliers (sid, sname, city, street) VALUES (5, 'Sup5', 'Oslo', 'Sea Street 20');
	
CREATE TABLE Parts (
	pid INT PRIMARY KEY,
	pname VARCHAR(30) NOT NULL,
	color VARCHAR(30) NOT NULL
);

INSERT INTO Parts (pid, pname, color) VALUES (1, 'Part1', 'Yellow');
INSERT INTO Parts (pid, pname, color) VALUES (2, 'Part2', 'Blue');
INSERT INTO Parts (pid, pname, color) VALUES (3, 'Part3', 'Red');
INSERT INTO Parts (pid, pname, color) VALUES (4, 'Part4', 'Blue');
INSERT INTO Parts (pid, pname, color) VALUES (5, 'Part5', 'Brown');
INSERT INTO Parts (pid, pname, color) VALUES (6, 'Part6', 'Blue');
INSERT INTO Parts (pid, pname, color) VALUES (7, 'Part7', 'Green');
INSERT INTO Parts (pid, pname, color) VALUES (8, 'Part8', 'Blue');
INSERT INTO Parts (pid, pname, color) VALUES (9, 'Part9', 'Yellow');
INSERT INTO Parts (pid, pname, color) VALUES (10, 'Part10', 'Blue');
INSERT INTO Parts (pid, pname, color) VALUES (11, 'Part11', 'Red');
INSERT INTO Parts (pid, pname, color) VALUES (12, 'Part12', 'Blue');

CREATE TABLE Catalog (
	sid INT NOT NULL,
	pid INT NOT NULL,
	cost REAL NOT NULL,
	PRIMARY KEY (sid, pid)
);

INSERT INTO Catalog (sid, pid, cost) VALUES (1,1,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (2,1,110);
INSERT INTO Catalog (sid, pid, cost) VALUES (3,1,90);
INSERT INTO Catalog (sid, pid, cost) VALUES (1,2,200);
INSERT INTO Catalog (sid, pid, cost) VALUES (2,2,220);
INSERT INTO Catalog (sid, pid, cost) VALUES (3,2,200);
INSERT INTO Catalog (sid, pid, cost) VALUES (1,4,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (5,4,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (3,4,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (2,3,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (5,3,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (1,3,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (1,5,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (5,5,100);
INSERT INTO Catalog (sid, pid, cost) VALUES (2,5,100);

-- Question 3

DROP TABLE Departments, Employees;

CREATE TABLE Employees (
	empId INT PRIMARY KEY,
	name TEXT NOT NULL,
	department INT NOT NULL,
	salary INT NOT NULL
);

INSERT INTO Employees (empId, name, department, salary) VALUES (1, 'Sarah', 1, 40000);
INSERT INTO Employees (empId, name, department, salary) VALUES (2, 'Robin', 1, 39000);
INSERT INTO Employees (empId, name, department, salary) VALUES (3, 'Lisa', 5, 50000);
INSERT INTO Employees (empId, name, department, salary) VALUES (4, 'Elton', 5, 40000);
INSERT INTO Employees (empId, name, department, salary) VALUES (5, 'Sandy', 5, 45000);
INSERT INTO Employees (empId, name, department, salary) VALUES (6, 'James', 5, 42000);
INSERT INTO Employees (empId, name, department, salary) VALUES (7, 'Amir', 5, 47500);
INSERT INTO Employees (empId, name, department, salary) VALUES (8, 'John', 5, 49000);
INSERT INTO Employees (empId, name, department, salary) VALUES (9, 'Petra', 2, 30000);
INSERT INTO Employees (empId, name, department, salary) VALUES (10, 'Rob', 2, 35000);
INSERT INTO Employees (empId, name, department, salary) VALUES (11, 'Peter Joe', 4, 60000);
INSERT INTO Employees (empId, name, department, salary) VALUES (12, 'JOE M.', 4, 70000);
INSERT INTO Employees (empId, name, department, salary) VALUES (13, 'Lisa too', 5, 50000);

-- Question 4

DROP TABLE Employees;

CREATE TABLE Employees (
	employeeId VARCHAR(10) PRIMARY KEY,
	employeeName TEXT NOT NULL,
	street TEXT NOT NULL,
	city TEXT NOT NULL,
	email TEXT
);

INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E01', 'Sarah', 'Eklandagatan 3', 'Gothenburg');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E02', 'Leon', 'Eklandagatan 5', 'Gothenburg');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E03', 'Sandy', 'Eklandagatan 13', 'Gothenburg');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E04', 'Andy', 'Lindholmen 3', 'Gothenburg');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E05', 'Anton', 'Lindholmen 3', 'Gothenburg');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E06', 'Elsa', 'Eklandagatan 30', 'Gothenburg');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E07', 'Amir', 'Eklandagatan 3', 'Stockholm');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E08', 'Lily', 'Eklandagatan 5', 'Stockholm');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E09', 'Randy', 'Eklandagatan 13', 'Stockholm');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E10', 'James', 'Lindholmen 3', 'Stockholm');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E11', 'John', 'Lindholmen 10', 'Stockholm');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E12', 'Anna', 'Eklandagatan 30', 'Stockholm');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E13', 'Johnny', 'Lindholmen 11', 'Gothenburg');
INSERT INTO Employees (employeeId, employeeName, street, city) VALUES ('E14', 'Anna-Marie', 'Eklandagatan 30', 'Stockholm');

CREATE TABLE Companies (
	companyId VARCHAR(10) PRIMARY KEY,
	companyName TEXT NOT NULL,
	city TEXT NOT NULL
);

INSERT INTO Companies (companyId, companyName, city) VALUES ('C01', 'First Bank Corporation', 'Gothenburg');
INSERT INTO Companies (companyId, companyName, city) VALUES ('C02', 'Small Bank Corporation', 'Gothenburg');
INSERT INTO Companies (companyId, companyName, city) VALUES ('C03', 'First Bank Corporation', 'Stockholm');
INSERT INTO Companies (companyId, companyName, city) VALUES ('C04', 'Small Bank Corporation', 'Stockholm');


CREATE TABLE Works (
	employee VARCHAR(10) REFERENCES Employees(employeeId),
	company VARCHAR(10) REFERENCES Companies(companyId),
	salary INT NOT NULL,
	PRIMARY KEY (employee, company),
	CONSTRAINT salaryMustBePositive CHECK (salary > 0)
);

INSERT INTO Works (employee, company, salary) VALUES ('E01', 'C01', 50000);
INSERT INTO Works (employee, company, salary) VALUES ('E02', 'C01', 60000);
INSERT INTO Works (employee, company, salary) VALUES ('E03', 'C01', 70000);

INSERT INTO Works (employee, company, salary) VALUES ('E04', 'C02', 50000);
INSERT INTO Works (employee, company, salary) VALUES ('E05', 'C02', 55000);
INSERT INTO Works (employee, company, salary) VALUES ('E06', 'C02', 45000);

INSERT INTO Works (employee, company, salary) VALUES ('E07', 'C03', 50000);
INSERT INTO Works (employee, company, salary) VALUES ('E08', 'C03', 60000);
INSERT INTO Works (employee, company, salary) VALUES ('E09', 'C03', 70000);

INSERT INTO Works (employee, company, salary) VALUES ('E10', 'C04', 50000);
INSERT INTO Works (employee, company, salary) VALUES ('E11', 'C04', 55000);
INSERT INTO Works (employee, company, salary) VALUES ('E12', 'C04', 45000);

INSERT INTO Works (employee, company, salary) VALUES ('E13', 'C04', 45000);

INSERT INTO Works (employee, company, salary) VALUES ('E14', 'C01', 45000);


CREATE TABLE Manages (
	manager VARCHAR(10) REFERENCES employees(employeeId),
	employee VARCHAR(10) REFERENCES employees(employeeId),
	PRIMARY KEY (manager, employee)
);

INSERT INTO Manages (manager, employee) VALUES ('E01', 'E02');
INSERT INTO Manages (manager, employee) VALUES ('E01', 'E03');
INSERT INTO Manages (manager, employee) VALUES ('E01', 'E14');
INSERT INTO Manages (manager, employee) VALUES ('E10', 'E11');
INSERT INTO Manages (manager, employee) VALUES ('E10', 'E12');
INSERT INTO Manages (manager, employee) VALUES ('E10', 'E13');
INSERT INTO Manages (manager, employee) VALUES ('E04', 'E05');
INSERT INTO Manages (manager, employee) VALUES ('E04', 'E06');
INSERT INTO Manages (manager, employee) VALUES ('E07', 'E08');
INSERT INTO Manages (manager, employee) VALUES ('E07', 'E09');

------------------------


CREATE TABLE Wards(
	number INT PRIMARY KEY,
	numBeds INT NOT NULL
);

INSERT INTO Wards (number, numBeds) VALUES (1,10);
INSERT INTO Wards (number, numBeds) VALUES (2,10);
INSERT INTO Wards (number, numBeds) VALUES (3,3);
INSERT INTO Wards (number, numBeds) VALUES (4,3);

CREATE TABLE Patients( 
	pid VARCHAR(10) PRIMARY KEY,
	name TEXT NOT NULL,
	year INT NOT NULL,
	gender CHAR(1) NOT NULL
	CONSTRAINT yearMustBePositive CHECK (year>0),
	CONSTRAINT genderChar CHECK (gender IN ('M', 'F'))
);

INSERT INTO Patients (pid, name, year, gender) VALUES ('P1', 'Andy', '1970', 'M');
INSERT INTO Patients (pid, name, year, gender) VALUES ('P2', 'Benny', '1970', 'M');
INSERT INTO Patients (pid, name, year, gender) VALUES ('P3', 'Lisa', '1976', 'F');
INSERT INTO Patients (pid, name, year, gender) VALUES ('P4', 'Anne', '1990', 'F');INSERT INTO Patients (pid, name, year, gender) VALUES ('P5', 'Lucy', '1980', 'F');
INSERT INTO Patients (pid, name, year, gender) VALUES ('P6', 'Penny', '1975', 'F');
INSERT INTO Patients (pid, name, year, gender) VALUES ('P7', 'Sheldon', '1971', 'M');
INSERT INTO Patients (pid, name, year, gender) VALUES ('P8', 'Barry', '1940', 'M');

CREATE TABLE PatientInWard(
	pid VARCHAR(10) REFERENCES Patients(pid),
	ward INT REFERENCES Wards(number),
	PRIMARY KEY (pid, ward)
);

INSERT INTO PatientInWard (pid, ward) VALUES ('P1', 1);
INSERT INTO PatientInWard (pid, ward) VALUES ('P2', 1);
INSERT INTO PatientInWard (pid, ward) VALUES ('P3', 2);
INSERT INTO PatientInWard (pid, ward) VALUES ('P4', 2);
INSERT INTO PatientInWard (pid, ward) VALUES ('P5', 3);
INSERT INTO PatientInWard (pid, ward) VALUES ('P6', 3);
INSERT INTO PatientInWard (pid, ward) VALUES ('P7', 4);
INSERT INTO PatientInWard (pid, ward) VALUES ('P8', 4);

CREATE TABLE Tests(
	patient VARCHAR(10) REFERENCES Patients(pid),
	testDate DATE NOT NULL,
	testHour TIME NOT NULL,
	temperature REAL NOT NULL,
	heartRate INT NOT NULL
);

INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P1', '2018-11-01', '10:00', 35.5, 150);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P1', '2018-11-01', '11:00', 36, 152);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P2', '2018-11-01', '10:10', 36.2, 140);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P2', '2018-11-01', '15:00', 36, 150);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P2', '2018-11-01', '17:00', 36, 150);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P3', '2018-11-02', '10:00', 35.5, 150);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P3', '2018-11-02', '16:00', 35, 145);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P3', '2018-11-02', '18:00', 35.5, 150);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P8', '2018-11-02', '11:00', 39, 150);
INSERT INTO Tests (patient, testDate, testHour, temperature, heartRate) VALUES ('P8', '2018-11-02', '18:00', 37, 150);


CREATE TABLE Planets(
	star TEXT NOT NULL,
	name TEXT NOT NULL,
	distance REAL NOT NULL CHECK (distance > 0),
	mass REAL NOT NULL CHECK (mass > 0),
	atmosphere BOOLEAN NOT NULL,
	oxygen REAL NOT NULL CHECK ((oxygen = 0 AND NOT atmosphere) OR (atmosphere AND oxygen >= 0)),
	water REAL NOT NULL,
	PRIMARY KEY (star, name),
	UNIQUE (star, distance)
);

/*
CREATE TABLE Planets(
	star text NOT NULL,
	name text NOT NULL,
	distance float NOT NULL CHECK (distance > 0),
	mass float NOT NULL CHECK (mass > 0),
	atmosphere boolean NOT NULL,
	oxygen float NOT NULL CHECK ((oxygen = 0 and NOT atmosphere) OR (atmosphere AND oxygen >= 0)),
	water float NOT NULL,
	PRIMARY KEY (star, name),
	UNIQUE (star, distance)
);
*/
INSERT INTO Planets (star, name, distance, mass, atmosphere, oxygen, water) VALUES ('Sun', 'Earth', 149.6, 100000, TRUE, 20, 75);
INSERT INTO Planets (star, name, distance, mass, atmosphere, oxygen, water) VALUES ('Kerbol', 'Duna', 140.6, 1000, TRUE, 70, 70);

