Напредни извештаи од базата (SQL и складирани процедури)

Прикажување на совет чија што дата на побарување не е постара од 6 месеци гледано од денешната дата, прикажување на корисник кој го побарал советот и прикажување на број на одговори на прашањето.

   SELECT
    bpc.id AS consultation_id,
    bpc.date_askes AS claim_date,
    bpc.title AS consultation_title,
    bpc.description AS consultation_description,
    u.id AS user_id,
    u.name AS user_name,
    COUNT(bpa.id) AS answers_count,
    ARRAY_AGG(bpa.reply) AS all_answers
FROM
    project.blog_post_for_consultations bpc
LEFT JOIN
    project.users u ON bpc.users_id = u.id
LEFT JOIN
    project.blog_post_answers bpa ON bpc.id = bpa.blogpostconsid
WHERE
    bpc.date_askes >= current_date - interval '6 months'
GROUP BY
    bpc.id, bpc.date_askes, bpc.title, bpc.description, u.id, u.name
ORDER BY
    answers_count DESC;

Прикажување на вработен (доктор) кој што има напишено најмногу извештаи за едно исто милениче.

За да го тестираме овој прашалник ги додадовме следниве податоци:

  INSERT INTO project.reports
(description, usersid, petsid)
VALUES('Za isto zivotno', 8, 1);

WITH DoctorPetReportsCount AS (
    SELECT
        u.id AS doctor_id,
        u.name AS doctor_name,
        r.petsID AS pet_id,
        COUNT(r.id) AS reports_count
    FROM
        project.reports r
    LEFT JOIN
        project.users u ON r.usersID = u.id
    GROUP BY
        u.id, u.name, r.petsID
),

MaxReportsPerPet AS (
    SELECT
        pet_id,
        MAX(reports_count) AS max_reports_count
    FROM
        DoctorPetReportsCount
    GROUP BY
        pet_id
),

PetsWithMaxReports AS (
    SELECT
        pet_id,
        max_reports_count
    FROM
        MaxReportsPerPet
    WHERE
        max_reports_count = (SELECT MAX(max_reports_count) FROM MaxReportsPerPet)
)

SELECT
    dprc.doctor_id,
    dprc.doctor_name,
    dprc.pet_id,
    dprc.reports_count
FROM
    DoctorPetReportsCount dprc
JOIN
    PetsWithMaxReports pwmr ON dprc.pet_id = pwmr.pet_id AND dprc.reports_count = pwmr.max_reports_count
JOIN
    project.users u ON dprc.doctor_id = u.id;

За секое прашање кое е поставено во Blog Post For Consultations колку одговори има, од кои корисници се дадени одговорите и каква е улогата на корисниците кои одговориле(admin, manager, employee, standard).

SELECT
    bpc.ID AS question_id,
    bpc.title AS question_title,
    COUNT(bpa.ID) AS number_of_answers,
    u."name" AS user_name,
    r.type AS user_role
FROM
    project.blog_post_for_consultations bpc
JOIN
    project.blog_post_answers bpa ON bpc.ID = bpa.blogpostconsid
JOIN
    project.users u ON bpa.usersID = u.ID
JOIN
    project.roles r ON u.role_id = r.ID
GROUP BY
    bpc.ID, u.ID, r.ID  -- Include the role ID in the GROUP BY clause
ORDER BY
    bpc.ID;

Прикажување на продукт кој е прв додаден во системот за продажба и за него постојат најмалку нарачки кои биле направени од корисниците.

WITH ProductSales AS (
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.description AS product_description,
        p.price AS product_price,
        MIN(o.quantity) AS sold_quantity,
        p.dateadded AS date_added
    FROM
        project.products p
    LEFT JOIN
        project.product_are_made_orders pamo ON p.id = pamo.id_products
    LEFT JOIN
        project.orders o ON pamo.id_orders = o.id
    GROUP BY
        p.id, p.name, p.description, p.price, p.dateadded
),

RankedProducts AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY sold_quantity, date_added) AS rn
    FROM
        ProductSales
)

SELECT
    product_id,
    product_name,
    product_description,
    product_price,
    sold_quantity,
    date_added
FROM
    RankedProducts
WHERE
    rn = 1;

Секој ветеринарен центар колку вкупно мачиња а колку вкупно кучиња примил, и колку е просечната возраст на мачиња и кучиња во секој ветеринарен центар.

SELECT
    vc.name AS vet_center_name,
    COUNT(CASE WHEN p.typeOfPetsID = (SELECT ID FROM proekt.type_of_pets WHERE kind_of_pet = 'Dog') THEN 1 END) AS total_dogs,
    COUNT(CASE WHEN p.typeOfPetsID = (SELECT ID FROM proekt.type_of_pets WHERE kind_of_pet = 'Cat') THEN 1 END) AS total_cats,
    AVG(CASE WHEN p.typeOfPetsID = (SELECT ID FROM proekt.type_of_pets WHERE kind_of_pet = 'Dog') THEN EXTRACT(YEAR FROM age(current_date, p.dateOfBirthday)) END) AS avg_age_dogs,
    AVG(CASE WHEN p.typeOfPetsID = (SELECT ID FROM proekt.type_of_pets WHERE kind_of_pet = 'Cat') THEN EXTRACT(YEAR FROM age(current_date, p.dateOfBirthday)) END) AS avg_age_cats
FROM
    project.vet_centers vc
JOIN
    project.pets_visit_vet_centers pvvc ON vc.ID = pvvc.id_vet_centers
JOIN
    project.pets p ON pvvc.id_pets = p.ID
GROUP BY
    vc.ID, vc.name
ORDER BY
    vc.name;

Koja e најновата терапија според датум која е препишена, за која дијагноза е препишена, и во кој ветеринарен центар е препишена.

 WITH LatestTherapies AS (
    SELECT
        ptvc.id_pets,
        MAX(t.appoitmentDate) AS latest_therapy_date
    FROM
        project.pets_visit_vet_centers ptvc
    JOIN
        project.therapy t ON ptvc.id_pets = t.diagnosticsID
    GROUP BY
        ptvc.id_pets
)

SELECT
    vc.name AS vet_center_name,
    p.id AS pet_id,
    --p.description AS pet_description,
    d.description AS diagnosis_description,
    t.description AS latest_therapy_description,
    lt.latest_therapy_date
FROM
    project.vet_centers vc
JOIN
    project.pets_visit_vet_centers ptvc ON vc.id = ptvc.id_vet_centers
JOIN
    project.pets p ON ptvc.id_pets = p.id
JOIN
    project.diagnostics d ON p.id = d.usersID
LEFT JOIN
    LatestTherapies lt ON p.id = lt.id_pets
LEFT JOIN
    project.therapy t ON lt.latest_therapy_date = t.appoitmentDate AND p.id = t.diagnosticsID
ORDER BY
   latest_therapy_date DESC;

За секој ветеринарен центар, колку пари одвојува за нови вработени и која е просечната плата по вработен.

WITH SalaryData AS (
    SELECT
        v.id AS vet_center_id,
        v.name AS vet_center_name,
        j.predictedsalery AS salary
    FROM
        vet_centers v
    JOIN
        jobs j ON v.id = j.vetCentersID
),
SalaryAggregates AS (
    SELECT
        vet_center_id,
        vet_center_name,
        COUNT(salary) AS number_of_employees,
        SUM(CAST(REPLACE(salary, ' EUR', '') AS DECIMAL)) AS total_salary_expenses,
        AVG(CAST(REPLACE(salary, ' EUR', '') AS DECIMAL)) AS average_salary
    FROM
        SalaryData
    GROUP BY
        vet_center_id, vet_center_name
)

SELECT
    sa.vet_center_name,
    sa.number_of_employees,
    sa.total_salary_expenses,
    sa.average_salary
FROM
    SalaryAggregates sa
ORDER BY
    sa.total_salary_expenses DESC;

Прикажување на ветеринарни центри кои што корисникот може да ги посети доколку живее во Охрид, број на слободни работни места во тие ветеринарни центри и број на посетители.

SELECT
    vc.name AS vet_center_name,
    vc.description AS vet_center_description,
    vc.adress AS vet_center_address,
    vc.phoneNumber AS vet_center_phone_number,
    COUNT(DISTINCT j.ID) AS number_of_employees,
    COUNT(DISTINCT pv.id_pets) AS number_of_pets_visited
FROM
    project.vet_centers vc
LEFT JOIN
    project.jobs j ON vc.ID = j.vetCentersID
LEFT JOIN
    project.pets_visit_vet_centers pv ON vc.ID = pv.id_vet_centers
WHERE
    vc.citiesID = (SELECT ID FROM project.cities WHERE name = 'Ohrid')
GROUP BY
    vc.ID, vc.name, vc.description, vc.adress, vc.phoneNumber;

Прикажување која терапија за која дијагноза е препишена и од кој доктор е препишена.

 SELECT
    t.description AS therapy_description,
    d.description AS diagnosis_description,
    u.name AS doctor_name,
    u.lastname AS doctor_lastname
FROM
    project.therapy t
JOIN
    project.diagnostics d ON d.id = t.diagnosticsID
JOIN
    project.users u ON u.id = d.usersID
JOIN
    project.pets p ON p.id = d.usersID
ORDER BY
    t.id;

Прикажување на продукти кои што би можело да се потрошат најбрзо бидејќи има побарано најголема количина за нив.

SELECT
  p.name AS product_name,
  p.description AS product_description,
  AVG(o.quantity) AS avg_sales_quantity
FROM
  project.products p
JOIN
  project.product_are_made_orders po ON p.ID = po.id_products
JOIN
  project.orders o ON po.id_orders = o.ID
GROUP BY
  p.ID
ORDER BY
  avg_sales_quantity DESC;

За секој миленик кои болести се дијагностицирани, какви лекови и терапија е препишена.

WITH PetDiagnosis AS (
    SELECT
        p.id AS pet_id,
        p.name AS pet_name,
        d.description AS diagnosis_description
    FROM
        pets p
    JOIN
        diagnostics_established_pets dep ON p.id = dep.id_pets
    JOIN
        diagnostics d ON dep.id_diagnostics = d.id
),
PetMedications AS (
    SELECT
        p.id AS pet_id,
        m.name AS medicine_name,
        m.description AS medicine_description
    FROM
        pets p
    JOIN
        therapy_takes_pets ttp ON p.id = ttp.id_pets
    JOIN
        therapy t ON ttp.id_therapy = t.id
    JOIN
        medecines m ON t.diagnosticsID = m.diagnosticsID
),
PetTherapy AS (
    SELECT
        p.id AS pet_id,
        t.description AS therapy_description,
        t.appoitmentDate AS therapy_date
    FROM
        pets p
    JOIN
        therapy_takes_pets ttp ON p.id = ttp.id_pets
    JOIN
        therapy t ON ttp.id_therapy = t.id
)

SELECT
    pd.pet_id,
    pd.pet_name,
    pd.diagnosis_description,
    pm.medicine_name,
    pm.medicine_description,
    pt.therapy_description,
    pt.therapy_date
FROM
    PetDiagnosis pd
LEFT JOIN
    PetMedications pm ON pd.pet_id = pm.pet_id
LEFT JOIN
    PetTherapy pt ON pd.pet_id = pt.pet_id
ORDER BY
    pd.pet_id, pt.therapy_date;