Ejercicios propuestos y resueltos de consultas Oracle (Jardinería)

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

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

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

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

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

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

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

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

select distinct lower(estado) 
from pedidos

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.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 e.nombre, e.apellido1 || ' ' || e.apellido2 
from empleados e, oficinas o 
where e.codigooficina = o.codigooficina 
and trim(lower(o.ciudad)) = 'barcelona';

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

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

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 nombrecliente, sum(p.cantidad) as total_pagado
from clientes c, pagos p
where c.codigocliente=p.codigocliente
group by c.nombrecliente;

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

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 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
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);

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

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;

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 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;

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 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);