14.- Ejercicios resueltos. Consultas HQL

DAM_AD_03_R_010

Consultas HQL - 1

A partir del modelo de datos que se muestra, creado en la unidad anterior.

Y cuyo mapeo genera las siguientes clases java:

Realiza las siguientes consultas HQL:

Obtener los datos de los clientes y sus compras (obtener los objetos):

from Clientes c left join c.comprases order by c.codigoCli

Obtener lo mismo pero ahora visualizar: código cliente, nombre, localidad, numero de compra, fecha_compra. Si el cliente no tiene compras que se visualice 0 en el número de compra.

select c.codigoCli, c.nombre, c.localidad, c.tlf, coalesce(co.numcompra,0),co.fechacompra
from Clientes c left join c.comprases co
order by c.codigoCli

Obtener por cada cliente el detalle de las compras que ha realizado, visualizar código de cliente, nombre del cliente, localidad, numero_compra, código artículo, unidades compradas, precio de artículo, importe (precio del artículo por las unidades compradas)

select c.codigoCli as codigocli, c.nombre as nombre, c.localidad as localidad, 
    c.tlf as tlf, co.numcompra as numcompra, det.articulos.codarticulo as codartic, 
    det.unidades as unidades_compradas, det.articulos.precio as precio,
    det.articulos.precio * det.unidades as importe
from Clientes c join c.comprases co join co.detallecomprases det
order by c.codigoCli

Obtener El total de cada compra de cada cliente. Visualizar código de cliente, nombre, localidad, número_compra, importe_compra

select c.codigoCli as codigocli, c.nombre as nombre, c.localidad as localidad, 
   c.tlf as tlf, co.numcompra as numcompra, sum( det.articulos.precio * det.unidades) as importecompra
from Clientes c join c.comprases co join co.detallecomprases det
group by c.codigoCli , c.nombre , c.localidad , c.tlf , co.numcompra
order by c.codigoCli

Obtener el total de todas las compras de cada cliente. Y el número de compras realizadas, visualizar el código de cliente, el nombre, la localidad, el teléfono, el número de compras que ha realizado y el importe total de las compras:

select c.codigoCli as codigocli, c.nombre as nombre, c.localidad as localidad, 
    c.tlf as tlf, count(distinct co.numcompra) as numecompras, 
    sum( det.articulos.precio * det.unidades) as importetotal
from Clientes c join c.comprases co
join co.detallecomprases det
group by c.codigoCli , c.nombre , c.localidad , c.tlf 
order by c.codigoCli

Obtener para cada artículo las unidades compradas por los clientes, el importe y el nombre de su proveedor. Que se visualicen también los artículos sin compras. Visualizar código de artículo, denominación, stock, precio, unidades compradas, importe de las compras, nombre proveedor:

select a.codarticulo as codarticulo, a.denominacion as denominacion , 
    a.stock as stock, a.precio as pv, coalesce(sum(det.unidades), 0) as sumaunidades,
    coalesce(a.precio * sum(det.unidades),0) as importecompras, 
    a.proveedores.nombre as nombreproveedor
from Articulos a left join a.detallecomprases det
group by a.codarticulo , a.denominacion, a.stock, a.precio, a.proveedores.nombre
order by a.codarticulo

Obtener para cada proveedor los artículos que suministra. Visualizar cod proveedor, nombre proveedor, cod artículo, denominación artículo.

select p.codigoProv as codprov, p.nombre as nombreprov, ar.codarticulo as codarti, ar.denominacion as denoarti
from Proveedores p join p.articuloses ar
order by p.codigoProv

Visualizar por cada proveedor el número de artículos que suministra:

select p.codigoProv as codprov, p.nombre as nombreprov, count(ar.codarticulo) as numeartic
from Proveedores p join p.articuloses ar
group by p.codigoProv , p.nombre 
order by p.codigoProv

Clientes de la localidad de TALAVERA que compraron artículos de la zona CENTRO

select distinct c.codigoCli, c.nombre, c.localidad
from Clientes c join c.comprases co join co.detallecomprases det
  where upper(c.localidad) = 'TALAVERA' and
        upper(det.articulos.zona) = 'CENTRO'
order by c.codigoCli

Proveedores que suministran artículos de la zona Centro, visualizar su código, nombre y localidad:

select distinct p.codigoProv, p.nombre, p.localidad
from Proveedores p  join p.articuloses ar
where upper(zona) = 'CENTRO'
order by p.codigoProv

DAM_AD_03_R_020

Consultas HQL-2

A partir del modelo de datos (ORACLE) que se muestra en la figura:

Y cuyo mapeo a clases java es el siguiente:

Realizar las siguientes consultas:

Visualizar nombre de profesor , la fecha de nacimiento, nombre de centro y código, de cada uno de los profesores (1 profesor sólo pertenece a un centro).

select profe.nombreApe, profe.fechaNac , profe.c1Centros.nomCentro, profe.c1Centros.codCentro
from C1Profesores as profe

--Esto hace lo mismo:
select profe.nombreApe, profe.fechaNac , cen.nomCentro, cen.codCentro
from C1Profesores as profe, C1Centros as cen 
where cen.codCentro=profe.c1Centros

--Esto hace lo mismo:
select profe.nombreApe, profe.fechaNac , cen.nomCentro, cen.codCentro
from C1Profesores as profe, C1Centros as cen 
where cen.codCentro=profe.c1Centros.codCentro

Visualizar nombre de profesor , la fecha de nacimiento, nombre de centro y código, de los profesores que nacieron en febrero (1 profesor sólo pertenece a un centro).

select profe.nombreApe, profe.fechaNac , profe.c1Centros.nomCentro, 
profe.c1Centros.codCentro
from C1Profesores as profe
where to_char(profe.fechaNac,'mm')='02'

Visualizar nombre de profesor , fecha de nacimiento, nombre de su centro, dirección del centro, especialidad y nombre de especialidad del profesor, todos los datos van incluidos en la clase C1Profesores (1 profesor tiene solo un centro y una especialidad, los datos se cogen de la clase C1Profesores):

select profe.nombreApe, profe.fechaNac , profe.c1Centros.nomCentro, 
profe.c1Centros.direccion, profe.c1Especialidad.especialidad, profe.c1Especialidad.nombreEspe 
from C1Profesores as profe

-- Esto hace lo mismo, utilizando las tres clases: 
select profe.nombreApe, profe.fechaNac , cen.nomCentro, cen.direccion, espe.especialidad, espe.nombreEspe
from C1Profesores as profe, C1Centros as cen , C1Especialidad as espe
where cen.codCentro=profe.c1Centros and profe.c1Especialidad = espe.especialidad


-- Si ponemos esto visualiza los objetos centros y especialidad del profesor:
select profe.nombreApe, profe.fechaNac , profe.c1Centros, profe.c1Especialidad
from C1Profesores as profe

Asignaturas y profesores que la imparten (salen los objetos):

from C1Asignaturas as asi left join asi.c1Profesoreses

Profesores y asignaturas que imparten

from C1Profesores as profe left join profe.c1Asignaturases asi
order by profe.codProf

Nombres de las asignaturas y los profesores que las imparten (1 asignatura muchos profes, utilizamos join con el set c1Profesoreses de la clase C1Asignaturas):

select asi.codAsig, asi.nombreAsi, profe.codProf, profe.nombreApe
from C1Asignaturas as asi join asi.c1Profesoreses as profe

Nombres de las asignaturas y los profesores que las imparten, visualizar también las asignaturas que no las imparte ningún profesor:

select asi.codAsig, asi.nombreAsi, profe.codProf, profe.nombreApe
from C1Asignaturas as asi left join asi.c1Profesoreses as profe

Nombres de los profesores y nombres de las asignaturas que imparten. Sacar también los profesores que no imparten ninguna asignatura (1 profesor imparte varias asignaturas utilizamos join con el set c1Asignaturases de la clase C1Profesores) :

select profe.codProf, profe.nombreApe,profe.fechaNac,asi.nombreAsi,asi.codAsig
from C1Profesores as profe left join profe.c1Asignaturases as asi

Nombres de las asignaturas y el número de profesores que las imparten (1 asignatura muchos profes, utilizamos join con el set c1Profesoreses de la clase C1Asignaturas):

select asi.codAsig, asi.nombreAsi, count(profe.codProf)
from C1Asignaturas as asi join asi.c1Profesoreses as profe
group by asi.codAsig, asi.nombreAsi

Para que salgan también las asignaturas sin profesor:

select asi.codAsig, asi.nombreAsi, count(profe.codProf)
from C1Asignaturas as asi left join asi.c1Profesoreses as profe
group by asi.codAsig, asi.nombreAsi

Si se desea saber la asignatura qué más profesores tiene (En ORACLE se pueden añadir funciones de grupo anidadas como select max(count()):

select asi.codAsig, asi.nombreAsi, count(profe.codProf)
from C1Asignaturas as asi left outer join asi.c1Profesoreses as profe
group by asi.codAsig, asi.nombreAsi
having count(profe.codProf) =  (select max(count(profe.codProf))
                                from C1Asignaturas as asi join asi.c1Profesoreses as profe
                                group by asi.codAsig)
-- O también

select asi.codAsig, asi.nombreAsi, count(profe.codProf)
from C1Asignaturas as asi left outer join asi.c1Profesoreses as profe
group by asi.codAsig, asi.nombreAsi
having count(profe.codProf) = (select max(count(*))
from C1Profesores as pf join pf.c1Asignaturases ass
group by ass.codAsig)

Nombres de los profesores y el número de asignaturas que imparte (1 profesor imparte varias asignaturas utilizamos join con el set c1Asignaturases de la clase C1Profesores) :

select profe.codProf, profe.nombreApe, count(asi.codAsig)
from C1Profesores as profe left join profe.c1Asignaturases as asi
group by profe.codProf, profe.nombreApe

Si se desea saber el profesor que imparte más asignaturas:

select profe.codProf, profe.nombreApe, count(asi.codAsig)
from C1Profesores as profe left join profe.c1Asignaturases as asi
group by profe.codProf, profe.nombreApe
having count(asi.codAsig) =
     (select max(count(asig.codAsig)) from C1Profesores as profes 
        join profes.c1Asignaturases as asig
        group by profes.codProf)

-- O También

select profe.codProf, profe.nombreApe, count(asi.codAsig)
from C1Profesores as profe left join profe.c1Asignaturases as asi
group by profe.codProf, profe.nombreApe
having count(asi.codAsig) =  (select max(count(asig.codAsig)) from C1Asignaturas as asig 
                               join asig.c1Profesoreses p
                               group by p.codProf)

Nombres de los centros y el número de profesores que tiene (a 1 centro pertenecen varios profesores utilizamos join con el set c1Profesoreses de la clase C1Centros) :

select cen.codCentro, cen.nomCentro, count(prof.codProf)
from C1Centros as cen left join cen.c1Profesoreses as prof
group by cen.codCentro, cen.nomCentro

Si se desea saber el centro con más profesores:

select cen.codCentro, cen.nomCentro, count(prof.codProf)
from C1Centros as cen left join cen.c1Profesoreses as prof
group by cen.codCentro, cen.nomCentro
having count(prof.codProf) = ( 
               select max(count(prof.codProf)) from C1Centros as cen 
                left join cen.c1Profesoreses as prof
                group by cen.codCentro, cen.nomCentro )

-- O También

Select cen.codCentro, cen.nomCentro, count(prof.codProf)
from C1Centros as cen left join cen.c1Profesoreses as prof
group by cen.codCentro, cen.nomCentro
having count(prof.codProf) = (
select max(count(*)) from C1Profesores as pf
group by pf.c1Centros.codCentro )

Nombres de los centros que empiezan por ‘I’ y el número de profesores que tienen:

select cen.codCentro, cen.nomCentro, count(prof.codProf)
from C1Centros as cen left join cen.c1Profesoreses as prof
group by cen.codCentro, cen.nomCentro
having cen.nomCentro like 'I%'

Nombre de la provincia que tiene más centros.

-- número de centros por provincia
select provincia , count(*) from C1Centros group by provincia
-- provincia con más centros
select provincia , count(*) from C1Centros 
group by provincia
having count(*) = (select max(count(*)) from C1Centros group by provincia)

Códigos y Nombres de los profesores y de sus jefes de departamento ( 1 profesor tiene un jefe de departamento, utilizamos sólo la clase C1Profesores)

select profe.codProf, profe.nombreApe, profe.c1Profesores.codProf as cod_jefe, 
profe.c1Profesores.nombreApe as nom_jefe
from C1Profesores as profe

Datos de los jefes de departamento y el número de profesores que dirige, si no dirige que salga 0, (1 profesor jefe tiene varios subordinados, utilizamos join con el set c1Profesoreses de la clase C1Profesores)

select profe.codProf, profe.nombreApe, count(prof.codProf)
from C1Profesores as profe join profe.c1Profesoreses as prof
group by profe.codProf, profe.nombreApe

Si queremos que salgan todos los profes tanto los jefes como los que no son jefes:

select profe.codProf, profe.nombreApe, count(prof.codProf)
from C1Profesores as profe left outer join profe.c1Profesoreses as prof
group by profe.codProf, profe.nombreApe

Si se desea saber el jefe qué más subordinados tiene:

select profe.codProf, profe.nombreApe, count(prof.codProf)
from C1Profesores as profe left outer join profe.c1Profesoreses as prof
group by profe.codProf, profe.nombreApe
having count(prof.codProf) = 
     (select max(count(prof.codProf)) from C1Profesores as profe 
      left join profe.c1Profesoreses as prof
       group by profe.codProf, profe.nombreApe)

Obra publicada con Licencia de Documentación Libre de GNU (GNU Free Documentation License) (Versión local)

« Anterior | Siguiente »

14.- Ejercicios resueltos. Consultas HQL

DAM_AD_03_R_010

Consultas HQL - 1

DAM_AD_03_R_020

Consultas HQL-2

Retroalimentación

Descarga las tablas de profesores.