Напредни извештаи од базата (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,
  --  vc.description AS vet_center_description,
    COUNT(DISTINCT pvvc.id_pets) AS number_of_pets_treated,
    AVG(EXTRACT(YEAR FROM age(current_date, p.dateofbirthday))) AS average_pet_age
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, vc.description
ORDER BY
    number_of_pets_treated DESC;

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;

Прикажување на мобилен оператор на кој што се најчесто корисниците претплатници. Доколку мобилниот оператор Telecom започнува на 072 или 071, мобилниот оператор VIP започнува на 078 или 075.

   SELECT
    CASE
        WHEN SUBSTRING(u.number, 1, 3) IN ('072', '071') THEN 'Telecom'
        WHEN SUBSTRING(u.number, 1, 3) IN ('078', '075') THEN 'VIP'
        ELSE 'Other Operator'
    END AS mobile_operator,
    COUNT(u.id) AS number_of_users
FROM
    project.users u
where
    u.number LIKE '072%' OR
    u.number LIKE '071%' OR
    u.number LIKE '078%' OR
    u.number LIKE '075%'
GROUP BY
    mobile_operator
ORDER BY
    number_of_users 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
FROM
  project.vet_centers vc
JOIN
  project.cities c ON vc.citiesID = c.ID
WHERE
  c.name = 'Ohrid';

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

SELECT u.name, u.lastname, COUNT(p.ID) AS pet_count
FROM project.users u
JOIN project.pets p ON u.ID = p.usersID
GROUP BY u.ID
ORDER BY pet_count DESC
LIMIT 1;

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

 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;