\c postgres
DROP DATABASE exercise;
CREATE DATABASE exercise;
\c exercise

-- Exercise 1: Basics. Based on the Departments table below, create a SQL table called Employees that stores employee number, employee name, department and salary information. The primary key for the Employees table should be the employee number. Create a foreign key on the Employees table that references the Departments table based on the department_id field.

CREATE TABLE Departments (
  department_id INT NOT NULL,
  department_name TEXT NOT NULL,
  CONSTRAINT department_pk PRIMARY KEY (department_id)
);

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 Departments (department_id, department_name) VALUES (0, 'CSE');

INSERT INTO Departments VALUES (1, 'DIT');

INSERT INTO Employees VALUES (0, 'Matti', 0, 1000000);


-- Exercise 2:
-- Exercise 2: Suppliers. Consider the following relational schema:
-- Suppliers(*sid:integer*, sname:string,
--           city:string, street:string)
-- Parts(*pid:integer*, pname:string,color:string)
-- Catalog(*sid:integer,pid:integer*,cost:real)
-- Find the names of all suppliers who have supplied a non-blue part.

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

INSERT INTO Suppliers VALUES (0, 'Volvo', 'Gothenburg', 'Hisingen');
INSERT INTO Suppliers VALUES (1, 'Intel', 'Seattle', '1st Avenue');

CREATE TABLE Parts (
  pid INT PRIMARY KEY,
  pname TEXT NOT NULL,
  color TEXT NOT NULL CHECK (color IN ('red', 'green', 'blue'))
);

INSERT INTO Parts VALUES (0, 'X40', 'red');
INSERT INTO Parts VALUES (1, 'V44', 'green');
INSERT INTO Parts VALUES (2, 'i7-440', 'blue');
INSERT INTO Parts VALUES (3, 'V50', 'red');



-- Catalog(*sid:integer,pid:integer*,cost:real)

CREATE TABLE Catalog (
  pid INT NOT NULL REFERENCES Parts (pid),
  sid INT NOT NULL REFERENCES Suppliers (sid),
  cost FLOAT NOT NULL,
  PRIMARY KEY (pid, sid)
);

INSERT INTO Catalog VALUES (0, 0, 5000.5);
INSERT INTO Catalog VALUES (1, 0, 10000.4);
INSERT INTO Catalog VALUES (2, 1, 100.3);


-- Find the names of all suppliers who have supplied a non-blue part.

SELECT sname
FROM Suppliers
WHERE sid IN ( SELECT Suppliers.sid
			   FROM Catalog, Parts, Suppliers
			   WHERE Parts.pid = Catalog.pid
				 AND  Suppliers.sid = Catalog.sid
				 AND  color != 'blue');
 
-- Exercise 3: Employees. Consider the following table:

-- Employees(*empId*, name, department, salary)

-- The columns empId and name are of type text, while department and salary are of type integer.

DROP TABLE Employees;

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

INSERT INTO Employees VALUES (0, 'Gloin', 1, 5000);
INSERT INTO Employees VALUES (1, 'Bifur', 5, 2000);
INSERT INTO Employees VALUES (2, 'Bofur', 2, 1000);
INSERT INTO Employees VALUES (3, 'Thrain', 1, 2000);
INSERT INTO Employees VALUES (4, 'Balin', 5, 1500);
INSERT INTO Employees VALUES (5, 'JOE', 5, 1500);
INSERT INTO Employees VALUES (6, 'joe', 5, 1500);
INSERT INTO Employees VALUES (7, 'Joe', 5, 1500);

-- b) Write a query to find the maximum salary from each department.

SELECT department, MAX(salary)
FROM Employees
GROUP BY department;

-- a) Write a query to find the employees who get a higher salary than anyone in department 5 (you may assume that department 5 has more than one employee).

SELECT name
FROM Employees
WHERE salary > ( SELECT MAX(salary)
				 FROM Employees
				 WHERE department = 5
				 GROUP BY department);

-- c) Find all employee records containing the word ,,Joe", regardless of whether it was stored as JOE, Joe or joe.


SELECT *
FROM Employees
WHERE UPPER(name) LIKE '%JOE%';



--- Exercise 4:
-- Employees(*employeeId*, employeeName, street, city)
-- Companies(*companyId*, companyName, city)
-- Works(*employee, company*, salary)
-- Manages(*manager, employee*)

-- The information on which company an employee works for and their current salary is stored in a relation Works. Assume that all people work for at most one company. The information on which employees have manager roles and who they manage is stored in the relation Manages.




-- Employees(*employeeId*, employeeName, street, city)
DROP TABLE Employees;
CREATE TABLE Employees (
  employeeId INT PRIMARY KEY,
  employeeName TEXT NOT NULL,
  street TEXT NOT NULL,
  city TEXT NOT NULL
);
-- Companies(*companyId*, companyName, city)

CREATE TABLE Companies (
  companyId INT PRIMARY KEY,
  companyName TEXT NOT NULL,
  companyCity TEXT NOT NULL
);

-- Works(*employee, company*, salary)
CREATE TABLE Works (
  employee INT NOT NULL REFERENCES Employees (employeeId),
  company INT NOT NULL REFERENCES Companies (companyId),
  salary INT NOT NULL,
  PRIMARY KEY (employee, company)
);

-- Manages(*manager, employee*)

CREATE TABLE Manages (
  manager INT NOT NULL REFERENCES Employees (employeeId),
  employee INT NOT NULL REFERENCES Employees (employeeId),
  PRIMARY KEY (manager, employee)
);


INSERT INTO Employees VALUES (0, 'AAA', '1st Street', 'New York');
INSERT INTO Employees VALUES (1, 'BBB', '1st Street', 'New York');
INSERT INTO Employees VALUES (2, 'CCC', '2nd Street', 'New York');
INSERT INTO Employees VALUES (3, 'DDD', '1st Street', 'Seattle');
INSERT INTO Employees VALUES (4, 'EEE', '4th Street', 'Seattle');
INSERT INTO Employees VALUES (5, 'FFF', '5th Avenue', 'Palo Alto');
INSERT INTO Employees VALUES (6, 'GGG', '6th Avenue', 'Palo Alto');
INSERT INTO Employees VALUES (7, 'HHH', '1st Avenue', 'Mountain View');

INSERT INTO Companies VALUES (0, 'First Bank Corporation', 'New York');
INSERT INTO Companies VALUES (1, 'Amazon', 'Seattle');
INSERT INTO Companies VALUES (2, 'Starbucks', 'Seattle');
INSERT INTO Companies VALUES (3, 'Facebook', 'Palo Alto');
INSERT INTO Companies VALUES (4, 'Small Bank Corporation', 'Palo Alto');
INSERT INTO Companies VALUES (5, 'Google', 'Mountain View');

INSERT INTO Works VALUES (0, 0, 100000);
INSERT INTO Works VALUES (1, 1, 10000);
INSERT INTO Works VALUES (2, 0, 5000);
INSERT INTO Works VALUES (3, 2, 5000);
INSERT INTO Works VALUES (4, 3, 100000);
INSERT INTO Works VALUES (5, 4, 500);
INSERT INTO Works VALUES (6, 5, 100000);
INSERT INTO Works VALUES (7, 5, 120000);

INSERT INTO Manages VALUES (0, 2);
INSERT INTO Manages VALUES (7, 6);
INSERT INTO Manages VALUES (4, 4);


-- Write a SQL query for the following tasks:
-- a) Find the names, street address, and cities for all employees who work for 'First Bank Corporation' and earn more than $10,000.

SELECT employeeName, street, city
FROM Employees, Works, Companies
WHERE employeeId = employee
  AND companyId = company
  AND companyName = 'First Bank Corporation'
  AND salary > 10000;

-- b) Find the names of all employees who:
--  i) live in the same city as the company they work for.

SELECT employeeName
FROM Employees, Works, Companies
WHERE employeeId = employee
  AND companyId = company
  AND city = companyCity;



--  ii) live on the same street in the same city as their manager.

SELECT E.employeeName
FROM Employees as E, Employees as M, Manages
WHERE E.employeeId = employee
  AND M.employeeId = manager
  AND E.street = M.street
  AND E.city   = M.city;


--  iii) earn more than every employee of 'Small Bank Corporation'

SELECT employeeName
FROM Employees, Works
WHERE employeeId = employee
  AND salary > ( SELECT MAX(salary)
				 FROM Companies, Works
				 WHERE company = companyId
				 AND companyName = 'Small Bank Corporation');


--  c) Find the name of the company that has the smallest payroll (i.e. the smallest total sum of salaries).


SELECT companyName, company, SUM(salary) as sums
FROM Works, Companies
WHERE company = companyId
GROUP BY (company, companyName)
ORDER BY sums ASC
LIMIT 1;



-- d) Assuming that the Employee relation had an email column with NULL values, write a query to update the email values.

-- UPDATE Employees SET email = '...@...' WHERE employeeId=5;

-- e) Find all employees who are not managers.


SELECT employeeName from Employees
where employeeId NOT IN (SELECT manager from Manages);

-- Exercise 5:
-- Wards(number, numBeds)
-- Patients(pid, name, year, sex)
-- PatientInWard(pid, ward)
-- Tests(patient, testDate, testHour, temperature, heartRate)

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

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

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

-- Tests(patient, testDate, testHour, temperature, heartRate)

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

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);

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

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);

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);

-- A ward is identied by its number. The numBeds attribute is the number of beds in that ward. Patients are identied by their personal identication number. The name, year of birth and biological sex ('M' or 'F') of each patient is stored in the Patients relation. The ward to which each patient is assigned is stored in the PatientInWard relation.  During their stay in the hospital, patients will undergo routine tests. The date, hour, and measurements (temperature and heart rate) of each test is performed is recorded, and stored in the database. A patient will normally undergo multiple tests over the course of their hospitalization.

-- Implement the schema in SQL with the appropriate constraints and types, and do the following:
--a) Write a SQL query that finds the temperature and heart rate measured in each test carried out on patients born before 1950.

SELECT temperature, heartRate
FROM Tests, Patients
WHERE pid = patient
  AND year < 1950;


-- b) Create a view, FreeBeds(ward, numBeds) where ward is a ward number, and numBeds is the number of available beds in that ward. 

CREATE VIEW FreeBeds (ward, numBeds) AS (
  SELECT ward, (numBeds - COUNT(pid))
  FROM Wards, PatientInWard
  WHERE ward = number
  GROUP BY (ward, numBeds)
);


-- Exercise 6:

-- Planets(star, name, distance, mass, atmosphere, oxygen, water)

-- We assume that all stars have dierent names, and that planet names are only unique within their star system. For simplicity, we also assume that each star system has exactly one star, and that all planets have circular orbits around their star at dierent distances.  A planet's position indicates its order within the star system, e.g. Earth is the 3rd planet around the Sun, after Mercury and Venus. If a planet has oxygen or other gases, it has an atmosphere. If it does not have an atmosphere, it has no gases. The surface of a planet is either all water, all land, or somewhere in-between.
-- Do the following:
-- a) Implement the relation in a SQL table, with reasonable types and constraints. Store distances in millions of km, e.g. for Earth the value should be 149.6.

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 INT NOT NULL
  CONSTRAINT oxygen_percent CHECK (oxygen >= 0 AND oxygen <= 100),
  CONSTRAINT oxygen_atmos CHECK ((atmosphere AND oxygen > 0) OR ((NOT atmosphere) AND oxygen = 0)),
  water FLOAT NOT NULL
  CONSTRAINT water_check CHECK ((water >= 0 AND water <= 100)),
  PRIMARY KEY (star, name),
  UNIQUE (star, distance) 
);


-- b) Assume that the relation contains the planet ,,Duna" in orbit around a star called ,,Kerbol". Write a SQL query to find how many planets are in orbits around their star that are larger than Duna's orbit around Kerbol.

INSERT INTO Planets VALUES ('Kerbol', 'Jool', 380.4, 100000, True, 10, 1);
INSERT INTO Planets VALUES ('Kerbol', 'Duna', 170.4, 20000, True, 10, 1);
INSERT INTO Planets VALUES ('Kerbol', 'Kerbin', 149.4, 50000, True, 21, 71);

INSERT INTO Planets VALUES ('Kerbol', 'Eve', 100.2, 10000, False, 0, 0);
INSERT INTO Planets VALUES ('Kerbol', 'Moho', 45.4, 5000, False, 0, 0);

--b)

SELECT COUNT(*) FROM Planets WHERE distance > (SELECT distance FROM Planets WHERE star='Kerbol' AND name='Duna');


-- c) We say that a planet is ,,habitable" if it satises the following conditions:
--  i. it orbits at a distance between 100 and 200 million kilometers (inclusive) of its star (i.e. in the ,,Goldilocks zone"), -- ii. it has has an atmosphere, and an oxygen percentage between 15 and 25% (inclusive),
-- iii. it has water on its surface.
-- Create the view Habitable(name, star, status), where status is 'habitable' if the planet name around star is habitable and 'uninhabitable' if the planet is uninhabitable.

CREATE VIEW HabitHelper (name, star) AS (
 SELECT name, star
 FROM Planets
 WHERE distance >= 100 AND distance <= 200
   AND atmosphere
   AND oxygen >= 15 AND oxygen <= 25
   AND water > 0
);

CREATE VIEW Habitable (name, star, status) AS (
 (SELECT name, star, 'habitable'
  FROM HabitHelper)
 UNION
 (SELECT name, star, 'uninhabitable'
  FROM Planets
  WHERE (name,star) NOT IN (select name, star from HabitHelper))
);

