Advanced SQL Queries for Veterinary Management Systems

Advanced SQL Querying for Veterinary Databases

This document outlines several advanced SQL queries designed to manage and extract meaningful data from a veterinary clinic database. These queries cover aspects such as staff performance, financial statistics, and complex data filtering.

1. Veterinarians with Low Appointment Volume

The following query identifies veterinarians managed by a specific supervisor ('COL06') who have handled fewer than two appointments. This is useful for monitoring workload distribution.

SELECT 
    TRIM(v.nombre) || ' ' || TRIM(v.apellidos) AS "Veterinario",
    v.especialidad AS "Especialidad",
    COUNT(c.fechahora) AS "Total citas"
FROM VETERINARIO v
LEFT JOIN CITA c ON c.numColegiado = v.numColegiado
WHERE v.numcolegiadojefe = 'COL06'
GROUP BY v.numColegiado, v.nombre, v.apellidos, v.especialidad
HAVING COUNT(c.fechaHora) < 2
ORDER BY "Total citas" ASC, "Veterinario" ASC;

2. Specialty Cost Statistics and Financial Thresholds

This query calculates the average cost and total number of appointments per specialty, filtering for those that exceed the average cost recorded during the first week of June 2026.

SELECT 
    v.especialidad AS "Especialidad",
    COUNT(c.fechahora) AS "Numero citas",
    ROUND(AVG(c.coste), 2) AS "Coste medio"
FROM CITA c
JOIN VETERINARIO v ON v.numColegiado = c.numColegiado
GROUP BY v.especialidad
HAVING AVG(c.coste) > (SELECT AVG(coste) FROM CITA WHERE fechahora BETWEEN TO_DATE('01/06/2026', 'DD/MM/YYYY') AND TO_DATE('07/06/2026', 'DD/MM/YYYY'))
ORDER BY "Coste medio" DESC;

3. Organizational Hierarchy and Specialty Filtering

To understand the reporting structure, this query lists veterinarians and their respective supervisors, excluding those in Surgery or Traumatology.

SELECT 
    TRIM(v.nombre) || ' ' || TRIM(v.apellidos) AS "Veterinario",
    NVL2(v.numcolegiadojefe, (TRIM(j.nombre) || ' ' || TRIM(j.apellidos)), '-') AS "Jefe"
FROM VETERINARIO v
LEFT JOIN VETERINARIO j ON j.numColegiado = v.numColegiadoJefe
WHERE v.especialidad NOT IN ('Cirugia', 'Traumatologia')
ORDER BY "Veterinario" ASC;

4. Species Treated by Supervisory Staff

This query identifies the distinct species treated by veterinarians who hold supervisory roles, excluding a specific colegiado ('COL01').

SELECT DISTINCT 
    m.especie AS "Especie"
FROM MASCOTA m
JOIN CITA c ON c.chipmascota = m.chip
JOIN VETERINARIO v ON v.numcolegiadojefe = c.numcolegiado
WHERE c.numcolegiado != 'COL01'
ORDER BY "Especie";

5. Multi-Species Veterinarians (Dogs and Cats)

This query finds veterinarians who have treated both dogs and cats, demonstrating the use of intersecting subqueries.

SELECT 
    numcolegiado AS "Numero colegiado", 
    TRIM(UPPER(nombre)) AS "Nombre"
FROM VETERINARIO
WHERE numcolegiado IN (SELECT numcolegiado FROM cita c JOIN mascota m ON c.chipmascota = m.chip WHERE m.especie = 'Perro')
  AND numcolegiado IN (SELECT numcolegiado FROM cita c JOIN mascota m ON c.chipmascota = m.chip WHERE m.especie = 'Gato');

6. Top Spending Pets: View Creation

Finally, we create a SQL View to track the top 3 pets (older than 4 years) with the highest total expenditure, excluding owners with the surname 'Ruiz'.

CREATE VIEW v_gasto AS
SELECT 
    m.chip AS "Chip",
    TRIM(m.nombre) AS "Mascota",
    m.especie AS "Especie",
    SUM(c.coste) AS "Gasto total"
FROM CITA c
JOIN MASCOTA m ON m.chip = c.chipMascota
JOIN PROPIETARIO p ON p.dni = m.dniPropietario
WHERE m.fechaNacimiento IS NOT NULL
  AND MONTHS_BETWEEN(SYSDATE, m.fechaNacimiento) / 12 > 4
  AND TRIM(p.apellidos) NOT LIKE 'Ruiz%'
GROUP BY m.chip, m.nombre, m.especie
ORDER BY "Gasto total" DESC, "Mascota" ASC
FETCH FIRST 3 ROWS ONLY;