Ejercicios propuestos y resueltos consultas SQL (Jardinería)

03/01/20189 comentarios

Hola a todos, hoy os dejo una serie de ejercicios sobre la base de datos oracle.

Todos los ejercicios que proponemos están resueltos en este mismo post, intenta hacerlo por ti mismo y si te quedas atascado puedes mirar la solución. Recuerda, que no tiene por que estar igual tu solución con la del post, el objetivo es que aprendas no que me copies la solución.

Aquí te dejo la base de datos de jardinería que debes importar.

Si no tienes instalado oracle database XE, os dejo algunos manuales:

Como instalar Oracle Database XE en Windows

Como instalar SQL Developer en Windows

Al final de cada ejercicio, os indico el resultado esperado de la consulta. Si hay muchos datos, indico el numero de filas.

Os dejo el modelo Entidad – Relación de la base de datos:

1. Muestra la ciudad y el codigo postal de las oficinas de España.

Spoiler

select ciudad, codigopostal 
from OFICINAS 
where lower(pais) = 'españa';

select ciudad, codigopostal

from OFICINAS

where lower(pais) = 'españa';

2. Obtener el nombre y apellidos del jefe de la empresa.

Spoiler

select nombre, apellido1, apellido2 
from empleados 
where codigojefe is null;

select nombre, apellido1, apellido2

from empleados

where codigojefe is null;

3. Mostrar el nombre y cargo de los empleados que no sean directores de oficina.

Spoiler

select nombre, puesto 
from empleados 
where lower(puesto) <> 'director oficina';

select nombre, puesto

from empleados

where lower(puesto) <> 'director oficina';

4. Muestra el número de empleados que hay en la empresa.

Spoiler

select count(*) as "Num empleados" 
from empleados;

1 2	select count(*) as "Num empleados" from empleados;

5. Muestra el número de clientes norteamericanos.

Spoiler

select count(*) as "Num clientes" 
from clientes 
where upper(pais) = 'USA';

select count(*) as "Num clientes"

from clientes

where upper(pais) = 'USA';

6. Número de clientes de cada país.

Spoiler

select pais, count(*) as "Num clientes" 
from clientes 
group by pais;

select pais, count(*) as "Num clientes"

from clientes

group by pais;

7. Muestra el nombre del cliente y el nombre de su representante de ventas (si lo tiene).

Spoiler

select c.nombrecliente as "Nombre cliente", e.nombre as "Nombre representante" 
from clientes c, empleados e 
where c.codigoempleadorepventas=e.CODIGOEMPLEADO;

select c.nombrecliente as "Nombre cliente", e.nombre as "Nombre representante"

from clientes c, empleados e

where c.codigoempleadorepventas=e.CODIGOEMPLEADO;

8. Nombre de los clientes que hayan hecho un pago en 2007

Spoiler

select distinct c.nombrecliente 
from clientes c, pagos p 
where c.CODIGOCLIENTE=p.CODIGOCLIENTE 
and p.FECHAPAGO like '%/2007';

select distinct c.nombrecliente

from clientes c, pagos p

where c.CODIGOCLIENTE=p.CODIGOCLIENTE

and p.FECHAPAGO like '%/2007';

9. Los posibles estados de un pedido.

Spoiler

select distinct lower(estado) 
from pedidos

1 2	select distinct lower(estado) from pedidos

10. Muestra el número de pedido, el nombre del cliente, la fecha de entrega y la fecha requerida de los pedidos que no han sido entregados a tiempo.

Spoiler

select p.codigopedido, c.nombrecliente, p.fechaentrega, p.fechaesperada 
from clientes c, pedidos p 
where c.codigocliente = p.CODIGOCLIENTE 
and p.FECHAESPERADA < p.fechaentrega;

select p.codigopedido, c.nombrecliente, p.fechaentrega, p.fechaesperada

from clientes c, pedidos p

where c.codigocliente = p.CODIGOCLIENTE

and p.FECHAESPERADA < p.fechaentrega;

11. Muestra el código, nombre y gama de los productos que nunca se han pedido (detalle pedidos).

Spoiler

select p.codigoproducto, p.nombre, p.gama 
from productos p, GAMASPRODUCTOS g 
where p.gama=g.gama 
and not exists (select CODIGOPRODUCTO from detallepedidos where codigoproducto=p.CODIGOPRODUCTO);

select p.codigoproducto, p.nombre, p.gama

from productos p, GAMASPRODUCTOS g

where p.gama=g.gama

and not exists (select CODIGOPRODUCTO from detallepedidos where codigoproducto=p.CODIGOPRODUCTO);

Debe devolver 132 registros.

12. Muestra el nombre y apellidos de los empleados que trabajan en Barcelona.

Spoiler

select e.nombre, e.apellido1 || ' ' || e.apellido2 
from empleados e, oficinas o 
where e.codigooficina = o.codigooficina 
and trim(lower(o.ciudad)) = 'barcelona';

select e.nombre, e.apellido1 || ' ' || e.apellido2

from empleados e, oficinas o

where e.codigooficina = o.codigooficina

and trim(lower(o.ciudad)) = 'barcelona';

13. Muestra el código y la cantidad de veces que se ha pedido un producto al menos una vez.

Spoiler

select p.codigoproducto, sum(dp.cantidad) as "cantidad pedida" 
from productos p, detallepedidos dp 
where p.codigoproducto = dp.codigoproducto 
group by p.codigoproducto;

select p.codigoproducto, sum(dp.cantidad) as "cantidad pedida"

from productos p, detallepedidos dp

where p.codigoproducto = dp.codigoproducto

group by p.codigoproducto;

Debe devolver 114 registros.

14. Muestra el nombre de los clientes de Miami que han realizado algún pedido.

Spoiler

select distinct  c.nombrecliente 
from clientes c, pedidos p 
where c.codigocliente=p.codigocliente 
and trim(lower(c.ciudad))='miami';

select distinct c.nombrecliente

from clientes c, pedidos p

where c.codigocliente=p.codigocliente

and trim(lower(c.ciudad))='miami';

15. Mostrar el precio final de cada pedido.

Spoiler

select p.codigopedido, sum(dp.cantidad * dp.PRECIOUNIDAD) as total
from pedidos p, detallepedidos dp
where p.codigopedido = dp.codigopedido
group by p.codigopedido;

select p.codigopedido, sum(dp.cantidad * dp.PRECIOUNIDAD) as total

from pedidos p, detallepedidos dp

where p.codigopedido = dp.codigopedido

group by p.codigopedido;

Debe devolver 88 registros.

16. Mostrar lo que ha pagado cada cliente.

Spoiler

select nombrecliente, sum(p.cantidad) as total_pagado
from clientes c, pagos p
where c.codigocliente=p.codigocliente
group by c.nombrecliente;

select nombrecliente, sum(p.cantidad) as total_pagado

from clientes c, pagos p

where c.codigocliente=p.codigocliente

group by c.nombrecliente;

17. Mostrar el numero de productos de cada gama.

Spoiler

select p.gama, count(*) as numero_productos
from productos p, gamasproductos gp
where p.gama = gp.gama
group by p.gama;

select p.gama, count(*) as numero_productos

from productos p, gamasproductos gp

where p.gama = gp.gama

group by p.gama;

18. Mostrar el código de los pedidos donde se haya vendido el producto de la gama ‘Aromáticas’ mas caro.

Spoiler

select distinct pe.codigopedido
from pedidos pe, detallepedidos dp
where pe.codigopedido = dp.codigopedido
and dp.codigoproducto in (select codigoproducto
                          from productos 
                          where precioventa = (select max(precioventa)
                                              from productos p, gamasproductos g
                                              where p.gama = g.gama
                                              and lower(g.gama) = 'aromáticas'))

select distinct pe.codigopedido

from pedidos pe, detallepedidos dp

where pe.codigopedido = dp.codigopedido

and dp.codigoproducto in (select codigoproducto

from productos

where precioventa = (select max(precioventa)

from productos p, gamasproductos g

where p.gama = g.gama

and lower(g.gama) = 'aromáticas'))

19. Mostrar el código de los pedidos donde se hayan vendido mas de 6 productos.

Spoiler

select pe.codigopedido
from pedidos pe, detallepedidos dp
where pe.codigopedido = dp.codigopedido
group by pe.codigopedido
having count(*)>=6;

select pe.codigopedido

from pedidos pe, detallepedidos dp

where pe.codigopedido = dp.codigopedido

group by pe.codigopedido

having count(*)>=6;

20. Mostrar el codigo de los pedidos donde el precio del pedido sea superior a la media de todos los pedidos.

Spoiler

select pe.codigopedido
from pedidos pe
where 
(select sum(dp.cantidad * dp.PRECIOUNIDAD) as total
      from pedidos p, detallepedidos dp
      where p.codigopedido = dp.codigopedido and pe.codigopedido = p.codigopedido
      group by p.codigopedido)
>
(select avg(t.total)
from (select p.codigopedido, sum(dp.cantidad * dp.PRECIOUNIDAD) as total
      from pedidos p, detallepedidos dp
      where p.codigopedido = dp.codigopedido
      group by p.codigopedido) t);

select pe.codigopedido

from pedidos pe

where

(select sum(dp.cantidad * dp.PRECIOUNIDAD) as total

from pedidos p, detallepedidos dp

where p.codigopedido = dp.codigopedido and pe.codigopedido = p.codigopedido

group by p.codigopedido)

(select avg(t.total)

from (select p.codigopedido, sum(dp.cantidad * dp.PRECIOUNIDAD) as total

from pedidos p, detallepedidos dp

where p.codigopedido = dp.codigopedido

group by p.codigopedido) t);

21. Realiza una vista que muestre los datos de un empleado (nombre, apellidos, ciudad de la oficina) y lo mismo para su jefe (en la misma fila).

Spoiler

create or replace view empleados_jefes as
select e_subor.nombre as nombre_subor, 
        e_subor.apellido1 || ' ' || e_subor.apellido2 as apellidos_subor,
        o_subor.ciudad as ciudad_subor,
        e_jefe.nombre as nombre_jefe, 
        e_jefe.apellido1 || ' ' || e_jefe.apellido2 as apellidos_jefe,
        o_subor.ciudad as ciudad_jefe
from empleados e_subor, empleados e_jefe, oficinas o_subor, oficinas o_jefe
where e_subor.codigojefe = e_jefe.codigoempleado
and o_subor.codigooficina = e_subor.codigooficina
and e_jefe.codigooficina = o_jefe.CODIGOOFICINA;

select * from empleados_jefes;

create or replace view empleados_jefes as

select e_subor.nombre as nombre_subor,

e_subor.apellido1 || ' ' || e_subor.apellido2 as apellidos_subor,

o_subor.ciudad as ciudad_subor,

e_jefe.nombre as nombre_jefe,

e_jefe.apellido1 || ' ' || e_jefe.apellido2 as apellidos_jefe,

o_subor.ciudad as ciudad_jefe

from empleados e_subor, empleados e_jefe, oficinas o_subor, oficinas o_jefe

where e_subor.codigojefe = e_jefe.codigoempleado

and o_subor.codigooficina = e_subor.codigooficina

and e_jefe.codigooficina = o_jefe.CODIGOOFICINA;

select * from empleados_jefes;

22. Realiza una vista que muestre el codigo de pedido y su total en euros.

Spoiler

create or replace view pedidos_total as
select p.codigopedido, sum(dp.cantidad * dp.PRECIOUNIDAD) as total
from pedidos p, detallepedidos dp
where p.codigopedido = dp.codigopedido
group by p.codigopedido;

select * from pedidos_total;

create or replace view pedidos_total as

select p.codigopedido, sum(dp.cantidad * dp.PRECIOUNIDAD) as total

from pedidos p, detallepedidos dp

where p.codigopedido = dp.codigopedido

group by p.codigopedido;

select * from pedidos_total;

Debe devolver 88 registros.

23. Realiza una vista con la información del pedido (codigo, fechapedido, fechaesperada, fechaentrega, nombre cliente y total en euros) ordenado por total de forma descendente.

Spoiler

create or replace view info_pedido as 
select  p.codigopedido, 
        p.fechapedido, 
        p.fechaesperada, 
        p.fechaentrega,
        c.nombrecliente,
        pt.total
from pedidos p, clientes c, PEDIDOS_TOTAL pt
where p.CODIGOCLIENTE = c.CODIGOCLIENTE
and pt.CODIGOPEDIDO = p.CODIGOPEDIDO
order by pt.total desc;

select * from info_pedido;

create or replace view info_pedido as

select p.codigopedido,

p.fechapedido,

p.fechaesperada,

p.fechaentrega,

c.nombrecliente,

pt.total

from pedidos p, clientes c, PEDIDOS_TOTAL pt

where p.CODIGOCLIENTE = c.CODIGOCLIENTE

and pt.CODIGOPEDIDO = p.CODIGOPEDIDO

order by pt.total desc;

select * from info_pedido;

Debe devolver 88 registros.

24. Devolverme la gama de productos mas vendida. Sin vistas

Spoiler

select t.gama, t.cantidad
from (select p.gama, sum(dp.cantidad) as cantidad
      from detallepedidos dp, productos p
      where p.codigoproducto = dp.codigoproducto
      group by p.gama
      order by cantidad desc) t
where rownum = 1;

select t.gama, t.cantidad

from (select p.gama, sum(dp.cantidad) as cantidad

from detallepedidos dp, productos p

where p.codigoproducto = dp.codigoproducto

group by p.gama

order by cantidad desc) t

where rownum = 1;

25. Devolverme la gama de productos mas vendida. Usa vistas

Spoiler

create or replace view gamas_vendidas as 
select p.gama, sum(dp.cantidad) as cantidad
from detallepedidos dp, productos p
where p.codigoproducto = dp.codigoproducto
group by p.gama;

select gv.gama, gv.cantidad
from gamas_vendidas gv
where gv.CANTIDAD = (select max(gv.cantidad) 
                    from gamas_vendidas gv);

create or replace view gamas_vendidas as

select p.gama, sum(dp.cantidad) as cantidad

from detallepedidos dp, productos p

where p.codigoproducto = dp.codigoproducto

group by p.gama;

select gv.gama, gv.cantidad

from gamas_vendidas gv

where gv.CANTIDAD = (select max(gv.cantidad)

from gamas_vendidas gv);

26. Muestra el pais(cliente) donde menos pedidos se hacen.

Spoiler

create or replace view pedidos_paises as
select c.pais, count(*) as num_pedidos
from clientes c, info_pedido ip
where c.nombrecliente = ip.nombrecliente
group by c.pais;

select pp.pais, pp.num_pedidos
from PEDIDOS_PAISES pp
where pp.num_pedidos = (select min(num_pedidos) 
                        from PEDIDOS_PAISES);