Agree on the task

[domcermak]

[domcermak]

Decision

Name: Vaccination register

Entities

• patients
• doctors
• vaccines
• registered_vaccinations
• hospitals
• hosplitals_doctors (decomposed M:N table -> doctors >-< hospitals)

patients >- registered_vaccinations -< doctors >- hosplitals_doctors -< hospitals
                          v
                          |
                      vaccines

patients

• id PK int
• name nvarchar
• surname nvarchar
• birth_number bigint

doctors

• id PK int
• name nvarchar
• surname nvarchar
• salary int

vaccines

• id PK int
• name nvarchar
• manufacturer nvarchar
• price int

registered_vaccinations

• id PK int
• patient_id FK int
• doctor_id FK int
• vaccine_id FK int
• time datetime
• completed bit

hospitals

• id PK int
• name nvarchar
• city nvarchar

hosplitals_doctors

• id PK int
• doctor_id FK
• hospital_id FK

Queries

SELECT in SELECT

Count of registered vaccinations for every patient

SELECT 
p.name, 
p.surname, 
(
SELECT 
COUNT(registered_vaccinations.id) 
FROM 
registered_vaccinations
WHERE
p.id = registered_vaccinations.patient_id
) AS registration_count 
FROM 
patients p INNER JOIN registered_vaccinations rv ON rv.patient_id = p.id 
GROUP BY
p.id,
p.name,
p.surname
ORDER BY 
COUNT(rv.id) DESC;

SELECT in FROM

List patients with birth number greater then 9000000000 and name 'Lucie'

SELECT
     * 
FROM (
     SELECT
            * 
     FROM 
            patients
     WHERE
            patients.birth_number > 9000000000
     ) AS p 
WHERE
     p.name = 'Lucie';

SELECT in WHERE

Doctors with a higher salary than average

SELECT
     *
FROM 
     doctors 
WHERE 
     salary > (SELECT AVG(salary) FROM doctors);

GROUP BY

Hospitals where the number of working doctors is greater than or equals to 3

SELECT 
     h.*,
     COUNT(hd.id) AS count_of_doctors
FROM 
     hospitals h LEFT JOIN hospitals_doctors hd ON h.id = hd.hospital_id
GROUP BY 
     h.id,
     h.name,
     h.city
HAVING
     COUNT(hd.id) >= 3 
ORDER BY
     COUNT(hd.id) DESC;

Batch operation

Patients who have all of their registrations completed

SELECT
    *
FROM 
     patients EXCEPT (
           SELECT
                 p.*
           FROM
                 patients p INNER JOIN registered_vaccinations rv ON rv.patient_id = p.id
           WHERE
                 rv.completed = 0
      );

LEFT JOIN

Vaccines and their usages

SELECT
     v.*,
     COUNT(rv.id) AS usage
FROM
     vaccines v LEFT JOIN registered_vaccinations rv ON v.id = rv.vaccine_id
GROUP BY
     v.id,
     v.name,
     v.manufacturer,
     v.price
ORDER BY
     COUNT(rv.id) DESC;

Java app

API commands

• register_patient <patient-name> <patient-surname> <birth-number> <vaccine-id> <doctor-id> <schedule-time>
• list <table name> [id]
• delete_patient <patient-id>
• cancel_registration <registered_vaccination_id>
• set_vaccination_completed <registered_vaccination_id>
• set_vaccination_incomplete <registered_vaccination_id>
• change_vaccination_time <registered_vaccination_id> <date> <time>
• select_in_select
• select_in_where
• select_in_from
• groupy_by
• batch_operation
• left_join
• exit
• help