Напредни извештаи од базата (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
)

SELECT
    dprc.doctor_id,
    dprc.doctor_name,
    dprc.pet_id,
    dprc.reports_count
FROM
    DoctorPetReportsCount dprc
JOIN
    MaxReportsPerPet mrpp ON dprc.pet_id = mrpp.pet_id AND dprc.reports_count = mrpp.max_reports_count
JOIN
    project.users u ON dprc.doctor_id = u.id
WHERE
    dprc.pet_id = (SELECT pet_id FROM MaxReportsPerPet ORDER BY max_reports_count DESC LIMIT 1);

За секое прашање кое е поставено во 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;

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

   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
ORDER BY
    sold_quantity, date_added
LIMIT 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;

Kолку пари се трошат за платите на вработените во секој ветеринарен центар.

   SELECT
    vc.name AS vet_center_name,
    SUM(CASE WHEN j.predictedSalery LIKE '%EUR%' THEN CAST(SUBSTRING(j.predictedSalery, 1, POSITION(' ' IN j.predictedSalery) - 1) AS DECIMAL) ELSE 0 END) AS total_salary_expense
FROM
    project.vet_centers vc
JOIN
    project.jobs j ON vc.id = j.vetCentersID
LEFT JOIN
    project.users u ON vc.id = u.jobs_id
GROUP BY
    vc.id, vet_center_name
ORDER BY
   total_salary_expense 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 b.name AS breed_name, COUNT(p.ID) AS pet_count
FROM project.breeds b
JOIN project.type_of_pets t ON b.ID = t.ID
JOIN project.pets p ON t.ID = p.typeOfPetsID
GROUP BY b.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.ID AS pet_id,
  p.color,
  p.description AS pet_description,
  p.dateOfBirthday,
  AGE(current_date, p.dateOfBirthday) AS pet_age
FROM
  project.pets p;

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

 select
	state as country,
	COUNT(*) as most_common_manufacturer
from
	project.manufacturers
group by
	state
order by
	most_common_manufacturer desc
limit 1;

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

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 product_orders_count AS (
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.description AS product_description,
        p.price AS product_price,
        COALESCE(COUNT(o.id), 0) AS orders_count
    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
)
SELECT
    product_id,
    product_name,
    product_description,
    product_price,
    orders_count
FROM
    product_orders_count
WHERE
    orders_count = (
        SELECT MIN(orders_count)
        FROM product_orders_count
    );