------CREATING TABLES

CREATE TABLE Patient(
    idPatient INTEGER NOT NULL,
    FirstName VARCHAR2(30) NOT NULL,
    LastName VARCHAR2(40) NOT NULL,
    DateOfBirth DATE NOT NULL,
    CONSTRAINT Patient_pk PRIMARY KEY (idPatient)
);

CREATE TABLE Appointment (
    idAppointment INTEGER NOT NULL,
    idPatient INTEGER NOT NULL,
    idDoctor INTEGER NOT NULL,
    "Date" DATE NOT NULL,
    price NUMBER(10,2) NOT NULL,
    Description VARCHAR2(250),
    CONSTRAINT Appointment_pk PRIMARY KEY (idAppointment)
);
CREATE TABLE  Doctor (
    idDoctor INTEGER NOT NULL,
    FirstName VARCHAR2(30) NOT NULL,
    LastName VARCHAR2(40) NOT NULL,
    Salary NUMBER(10,2) NOT NULL,
    idSpecialization INTEGER NOT NULL,
    CONSTRAINT Doctor_pk PRIMARY KEY (idDoctor)
);

CREATE TABLE Specialization (
    idSpecialization INTEGER NOT NULL,
    Name VARCHAR(50) NOT NULL,
    CONSTRAINT Specialization_pk PRIMARY KEY (idSpecialization)
);

ALTER TABLE Appointment ADD CONSTRAINT Appointment_Patient
    FOREIGN KEY (idPatient)
    REFERENCES Patient (idPatient);

ALTER TABLE Appointment ADD CONSTRAINT Appointment_Doctor
    FOREIGN KEY (idDoctor)
    REFERENCES Doctor (idDoctor);

ALTER TABLE Doctor ADD CONSTRAINT Doctor_Specialization
    FOREIGN KEY (idSpecialization)
    REFERENCES Specialization (idSpecialization);

------INSERTING VALUES

INSERT INTO Patient VALUES (1,'Mike','Wazowsky',TO_DATE('01/01/1989','DD/MM/YYYY'));
INSERT INTO Patient VALUES (2,'John','Kovalsky',TO_DATE('02/03/1976','DD/MM/YYYY'));
INSERT INTO Patient VALUES (3,'Anna','Bukowska',TO_DATE('23/11/1965','DD/MM/YYYY'));
INSERT INTO Patient VALUES (4,'Chris','Cornell',TO_DATE('12/05/1983','DD/MM/YYYY'));
INSERT INTO Patient VALUES (5,'Sara','Longfield',TO_DATE('04/02/1999','DD/MM/YYYY'));
INSERT INTO Patient VALUES (6,'Tom','Tomson',TO_DATE('11/02/2003','DD/MM/YYYY'));
INSERT INTO Patient VALUES (7,'John','Johnson',TO_DATE('31/12/2000','DD/MM/YYYY'));
INSERT INTO Patient VALUES (8,'Alex','Schneider',TO_DATE('16/04/1989','DD/MM/YYYY'));
INSERT INTO Patient VALUES (9,'John','Boddom',TO_DATE('10/01/2018','DD/MM/YYYY'));
INSERT INTO Patient VALUES (10,'Rose','Moet',TO_DATE('21/03/2018','DD/MM/YYYY'));


INSERT INTO Specialization VALUES (1,'Dentist');
INSERT INTO Specialization VALUES (2,'Urologist');
INSERT INTO Specialization VALUES (3,'Anasthesiologist');
INSERT INTO Specialization VALUES (4,'Allergist');
INSERT INTO Specialization VALUES (5,'Neurologist');
INSERT INTO Specialization VALUES (6,'Nephrologist');
INSERT INTO Specialization VALUES (7,'Gynecologist');


INSERT INTO Doctor VALUES (1,'Tameka', 'Neese',4000,1);
INSERT INTO Doctor VALUES (2,'Lilla', 'Molinaro',4200,2);
INSERT INTO Doctor VALUES (3,'Dorian', 'Yates',4400,2);
INSERT INTO Doctor VALUES (4,'Little', 'Pump',5000,3);
INSERT INTO Doctor VALUES (5,'Kendrick', 'Lamar',4200,3);
INSERT INTO Doctor VALUES (6,'Gazzy', 'Garcia',3800,4);
INSERT INTO Doctor VALUES (7,'Lilla', 'Second',4200,5);
INSERT INTO Doctor VALUES (8,'Goerge', 'Miller',5000,5);
INSERT INTO Doctor VALUES (9,'Kasia', 'Mlokosiewicz',3500,5);
INSERT INTO Doctor VALUES (10,'Charlie', 'Charlie',4600,6);
INSERT INTO Doctor VALUES (11,'Martha', 'Molinaro',4200,7);

INSERT INTO Appointment VALUES (1,1,1,TO_DATE('11/01/2011', 'DD/MM/YYYY'),100,NULL);
INSERT INTO Appointment VALUES (2,2,1,TO_DATE('13/12/2013', 'DD/MM/YYYY'),100,NULL);
INSERT INTO Appointment VALUES (3,2,2,TO_DATE('12/11/2014', 'DD/MM/YYYY'),120,NULL);
INSERT INTO Appointment VALUES (4,2,3,TO_DATE('13/05/2015', 'DD/MM/YYYY'),90,NULL);
INSERT INTO Appointment VALUES (5,2,4,TO_DATE('11/01/2013', 'DD/MM/YYYY'),300,NULL);
INSERT INTO Appointment VALUES (6,3,4,TO_DATE('13/02/2011', 'DD/MM/YYYY'),250,NULL);
INSERT INTO Appointment VALUES (7,3,4,TO_DATE('13/05/2010', 'DD/MM/YYYY'),100,NULL);
INSERT INTO Appointment VALUES (8,4,5,TO_DATE('11/11/2009', 'DD/MM/YYYY'),100,NULL);
INSERT INTO Appointment VALUES (9,4,6,TO_DATE('03/03/2011', 'DD/MM/YYYY'),100,NULL);
INSERT INTO Appointment VALUES (10,5,7,TO_DATE('05/06/2011', 'DD/MM/YYYY'),100,NULL);
INSERT INTO Appointment VALUES (11,6,7,TO_DATE('11/11/2013', 'DD/MM/YYYY'),120,NULL);
INSERT INTO Appointment VALUES (12,7,8,TO_DATE('12/03/2010', 'DD/MM/YYYY'),150,NULL);
INSERT INTO Appointment VALUES (13,7,8,TO_DATE('01/10/2010', 'DD/MM/YYYY'),200,NULL);
INSERT INTO Appointment VALUES (14,7,9,TO_DATE('01/01/2016', 'DD/MM/YYYY'),100,NULL);
INSERT INTO Appointment VALUES (15,7,10,TO_DATE('02/11/2010', 'DD/MM/YYYY'),200,NULL);
INSERT INTO Appointment VALUES (16,8,10,TO_DATE('04/04/2012', 'DD/MM/YYYY'),150,NULL);
INSERT INTO Appointment VALUES (17,9,10,TO_DATE('11/02/2016', 'DD/MM/YYYY'),120,NULL);
INSERT INTO Appointment VALUES (18,10,11,TO_DATE('31/12/2011', 'DD/MM/YYYY'),200,NULL);

-----QUERIES

--1. List names of the patients born in the year 2018.
--The result must contain first name and last name in one column, labeled "Patient". Sort results in ascending order of first names.

SELECT p.firstname || ' ' || p.lastname "Patient" FROM patient p
WHERE EXTRACT(YEAR FROM p.dateofbirth) = 2018
ORDER BY p.firstname;

--2. List all appointments in the following form: patients’ first name, patients’ last name, doctors’ first name, doctor's last name.
    --Result set must contain patients without any appointments.
SELECT a.idappointment, p.firstname, p.lastname, d.firstname, d.lastname FROM appointment a right join patient p ON a.idpatient = p.idpatient
                                                                                             join doctor d ON a.iddoctor = d.iddoctor;

--3. For each specialization, find number of doctors and their average salary.
SELECT s.name, COUNT(d.iddoctor), AVG(d.salary) FROM specialization s join doctor d ON s.idspecialization = d.IDSPECIALIZATION
GROUP BY s.name;

--4. Find number of appointments for each doctor in 2013. Results must additionally contain doctors’ first and last name.
   --Exclude dentists (specialization) and doctors who had less than five appointments.
SELECT DISTINCT d0.firstname,d0.lastname, (SELECT COUNT(a.idappointment) FROM appointment a join doctor d ON a.iddoctor = d.iddoctor WHERE d.iddoctor = d0.iddoctor)
FROM doctor d0 join specialization s ON d0.idspecialization = s.idspecialization
                 join appointment a0 ON d0.iddoctor = a0.iddoctor
  WHERE s.name <> 'Dentist';