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 Inside |
SelectShow> |
select ciudad, codigopostal
from OFICINAS
where lower(pais) = 'españa';
|
2. Obtener el nombre y apellidos del jefe de la empresa.
| Spoiler Inside |
SelectShow> |
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 Inside |
SelectShow> |
select nombre, puesto
from empleados
where lower(puesto) <> 'director oficina';
|
4. Muestra el número de empleados que hay en la empresa.
| Spoiler Inside |
SelectShow> |
select count(*) as "Num empleados"
from empleados;
|
5. Muestra el número de clientes norteamericanos.
| Spoiler Inside |
SelectShow> |
select count(*) as "Num clientes"
from clientes
where upper(pais) = 'USA';
|
6. Número de clientes de cada país.
| Spoiler Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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 Inside |
SelectShow> |
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);
|
Os dejo videos de nuestro canal donde realizo estas consultas:
Espero que os sea de ayuda. Si tenéis cualquier duda, no dudes en preguntar. Estamos para ayudarte.
Hola Fernando, primeramente felicitarte y agradecerte por el aporte que haces a esta comunidad que tiene deseos de aprende. Quería solicitarte tu ayuda en la explicación paso a paso del ejercicio 20, me he confundido en los filtros que utilizaste ya que no usas campos específicos para hacer el comparativo, bueno espero me puedas ayudar. Saludos…
La idea es sacar primero la media de todos los pedidos primero:
(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);
y para cada pedido debes sacar su valor.
(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)
Ya simplemente es compararlos, saludos.
Muchas gracias muy buen aporte sería de mucho más ayuda si en la creación de la base de datos explicas el uso de cada comando.
No sabras como se hacen los disparadaores y paquetes
11. Devuelve el nombre, apellido1 y cargo de los empleados que no representen a ningún cliente.
Hola, en caso de, que a este ejercicio quisiera agregarle un trigger que no permita modificar el precio de venta de un producto si es inferior al precio del proveedor, como lo haria?
Puedes ayudarme con esto
Crear paquete que contenga un procedimiento y una función:
El procedimiento debe actualizar el campo de cédula de una tabla ya creada.
La función debe retornar el nombre de la cédula que se envíe cómo parámetro
Obtén un listado con el nombre de cada cliente y el nombre y apellido de su representante de ventas.
podrias hacer este?
Se busca vendedor. Se necesita conocer el nombre del vendedor y la ciudad de su oficina. Lo que conocemos es que vendió un producto que en la descripción de su gama explicaba que se usa para decoración y sus dimensiones están entre 180-200.