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
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 '%/07';
|
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);
|
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;
|
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;
|
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 codigo 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 codigo 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
|
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;
|
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;
|
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);
|
Espero que os sea de ayuda. Si tenéis cualquier duda, no dudes en preguntar. Estamos para ayudarte.
