tp5

--1. Muestre todos los campos de Atención y Atención Detalle en una sola consulta
--   relacionada. Realice una segunda consulta donde no se presente repetición de campos.
select *
       from atencion a,
            detalle_atencion d

       where a.nro_atencion=d.nro_atencion
;

select a.*,
       d.codigo_servicio,
       d.cantidad_servicio

       from atencion a,
            detalle_atencion d

       where a.nro_atencion=d.nro_atencion
;

--2. Muestre todos los campos de Atención, Mascota y Veterinario en una sola consulta
--   relacionada. En el resultado de la consulta no deben existir campos repetidos.
select a.nro_atencion,
       a.fecha_atencion,
       a.codigo_mascota,
       m.nombre_mascota,
       m.codigo_especie,
       a.matricula_veterinario,
       v.nombre_veterinario as "Veterinario",
       v.domicilio,
       a.forma_pago

       from atencion a,
            mascota m,
            veterinario v

       where a.codigo_mascota        = m.codigo_mascota    and
             a.matricula_veterinario = v.matricula_veterinario;

--3. Utilizando subconsultas, muestre el “número atención” y “cantidad” del servicio cuya forma de pago es Crédito.
select nro_atencion,
       cantidad_servicio as cantidad

       from  detalle_atencion
       where nro_atencion
             in(
            select nro_atencion
                       from  atencion
                   where forma_pago='C'
             )
;

--4. Utilizando subconsultas, muestre el “nombre mascota” que figure en Atención.
select nombre_mascota

       from  mascota
       where codigo_mascota
             in(
            select codigo_mascota
                       from atencion
             )
;
--5. Utilizando subconsultas, muestre el “nombre mascota” que no figure en Atención.
select nombre_mascota

       from  mascota
       where codigo_mascota
             not in(
            select codigo_mascota
                   from atencion
             )
;

--6. Utilizando subconsultas, muestre las atenciones (Detalle Atención) que fueron atendidos
--   por el veterinario “Rivas”.
select *
       from  detalle_atencion
       where nro_atencion

             in(
        select nro_atencion
               from  atencion
                   where matricula_veterinario
                     in(
                select matricula_veterinario
                               from  veterinario
                               where nombre_veterinario='Rivas'
                 )
             )
;

--7. Elabore una vista llamada “VAtencion” que muestre los datos de Atención y Detalle
--   Atención.
create view "VAtencion" as
       select a.*,
              d.codigo_servicio,
              d.cantidad_servicio

             from atencion a,
              detalle_atencion d

             where a.nro_atencion = d.nro_atencion
;

--8. Elabore una vista llamada “Info_Veterinario” que muestre los datos del Veterinario junto
--   con la Atención realizada.
create view "Info_Veterinario" as
       select v.*,
              a.nro_atencion

              from veterinario v,
               atencion a

          where v.matricula_veterinario = a.matricula_veterinario
;

--9. Muestre la cantidad total de mascotas.
select count(*) as "Total de Mascotas"
       from mascota
;

--10. Visualice el máximo precio de un servicio.
select max(precio_servicio) as "Precio del servicio mas caro" from servicio;

--11. Muestre el importe (precio * cantidad) en cada número de atención en orden descendente.
select d.*,
       s.precio_servicio*d.cantidad_servicio as importe

       from detalle_atencion d,
        servicio s

       where s.codigo_servicio = d.codigo_servicio

       order by d.nro_atencion asc
;
--Extra
select a.nro_atencion,
       a.fecha_atencion,
       v.nombre_veterinario,
       m.nombre_mascota,
       a.forma_pago,
       sum(d.cantidad_servicio*s.precio_servicio) as "Total atencion"

       from atencion a,
            veterinario v,
        mascota m,
            detalle_atencion d,
        servicio s

       where d.codigo_servicio = s.codigo_servicio and
             a.nro_atencion    = d.nro_atencion    and
         a.codigo_mascota  = m.codigo_mascota  and
         a.matricula_veterinario = v.matricula_veterinario

       group by a.nro_atencion,
                v.matricula_veterinario,
        m.nombre_mascota

       order by a.nro_atencion
;

--12. Muestre el importe total recaudado por todas las atenciones.
select sum(s.precio_servicio*d.cantidad_servicio) as "Importe Total"

       from servicio s,
            detalle_atencion d

       where s.codigo_servicio=d.codigo_servicio
;

--13. Muestre el importe total recaudado en las atenciones que se realizaron por “Vacuna”,
--    considere que se debe aplicar un descuento del 10%.
select (s.precio_servicio*d.cantidad_servicio - s.precio_servicio*0.1) as importe

        from servicio s,
             detalle_atencion d

    where s.codigo_servicio=d.codigo_servicio and
          s.codigo_servicio='VAC'
;

--14. Obtenga la cantidad de atenciones para el nombre de mascota “Gati”.
select count(a.codigo_mascota) as "atenciones a Gati"

       from atencion a

       where a.codigo_mascota
       in(
      select codigo_mascota
             from  mascota
         where nombre_mascota='Gati'
    )
;

--15. Muestre el Nro atención y la cantidad de servicios que se realizaron en cada Detalle
--    Atención agrupados por “Nro. Atención”. (Ej: Nro Atención=1000, Cantidad Servicios=2)
select nro_atencion,
       count(nro_atencion) as "Cantidad de servicos"

       from detalle_atencion
       group by nro_atencion
       order by nro_atencion asc
;

--16. Muestre el Nro atención y el total de servicios que se realizaron en cada Detalle Atención
--    agrupados por “Nro. Atención”. (Ej: Nro Atención=1000, Total Servicios=3).
select nro_atencion,
       sum(cantidad_servicio) as "Servicios Totales"

       from detalle_atencion
       group by nro_atencion
       order by nro_atencion asc
;

--17. Utilizando subconsultas, muestre el “nombre mascota” a las cuales se les aplicó el servicio
--    “Consulta”.
select nombre_mascota
       from mascota
       where codigo_mascota
             in(
            select codigo_mascota
                   from atencion
                   where nro_atencion
                         in(
                    select nro_atencion
                           from detalle_atencion
                           where codigo_servicio
                                 in(
                                select codigo_servicio
                               from servicio
                               where descripcion_servicio='Consulta'
                          )
                  )
              )
;

--18. Utilizando subconsultas, muestre el “nombre veterinario” que atendió a la especie “Perro”.
select nombre_veterinario
       from veterinario
       where matricula_veterinario
             in(
            select matricula_veterinario
                   from atencion
               where codigo_mascota
                     in(
                select codigo_mascota
                           from mascota
                       where codigo_especie
                             in(
                        select codigo_especie
                               from especie
                               where nombre_especie = 'Perro'
                         )
                 )
             )
;

--19. Muestre el “nombre veterinario” que realizó atenciones entre las fechas 01/10/2019 y
--    02/10/2019.
select nombre_veterinario
       from veterinario
       where matricula_veterinario
             in(
        select matricula_veterinario
               from atencion
               where fecha_atencion between
                     '01/10/2019' and '02/10/2019'
             )
;