Download - Consultas Complejas - JOIN
EMPLEADO
idempleado
apellidosnombrecargotratamientofechanacimientofechacontrataciondireccionciudadregioncodpostalpaisteldomicilioextensionfotonotasjefe
COMPAÑIA DE ENVIOS
idcompañiaenvios
nombrecompañiatelefono
PEDIDO
idpedido
fechapedidofechaentregafechaenvioformaenviocargodestinatariodirecciondestinatariociudaddestinatarioregiondestinatariocodpostaldestinatariopaisdestinatarioidempleado (FK)idcliente (FK)
CLIENTE
idcliente
nombrecompañianombrecontactocargocontactodireccionciudadregioncodpostalpaistelefonofax
PRODUCTO
idproducto
nombreproductocantidadporunidadpreciounidadunidadesenexistenciaunidadesenpedidonivelnuevopedidosuspendidoidproveedor (FK)idcategoria (FK)
CATEGORIA
idcategoria
nombrecategoriadescripcionimagen
PROVEEDOR
idproveedor
nombrecompañianombrecontactocargocontactodireccionciudadregioncodpostalpaistelefonofaxpaginaprincipal
DETALLES DE PEDIDO
idproducto (FK)idpedido (FK)
preciounidadcantidaddescuento
ENTIDAD RELACIÓN DE LA BASE DE DATOS NEPTUNO
Estructura de Consultas ComplejasEstructura de Consultas Complejas
Consultas Desarrolladas Nivel Básico
(Una tabla – Tablas relacionadas – Funciones de Valor Agregado)
1. Lista de Empleados
Select * from Empleados
2. Lista de clientes que sean de Brasil
Select * from Clientes
Where país=’Brasil'
3. Lista de proveedores cuyo cargo contacto sea Representante
de Ventas
Select * from Proveedores
Where cargocontacto='Representante de Ventas'
4. Lista de productos cuyo precio unidad sea mayor a 50
Select * from Productos
Where preciounidad<50
5. Lista de productos que comiencen con “m”
Select * from Productos
Where nombreproducto like 'm%'
Order by preciounidad
6. Lista de pedidos efectuados entre 1/8/94 and 31/10/94
Select * from Pedidos
Where fechapedido between #1/8/94# and #31/10/94#
7. Lista de proveedores que tengan fax
Select * from Proveedores
Where isnull (fax) =False
8. Lista de proveedores que no tengan fax
Select * from Proveedores
Where isnull (fax) =True
9. Lista de clientes que tengan la palabra “com” en forma
ordenada descendentemente
Select * from Clientes
Where nombrecompañía like ‘%com%’
Order by nombrecontacto desc
10. Lista de productos cuyo proveedor sea de código 3,5 y 7
Select * from Productos
Where idproveedor in (3, 5, 7)
Ing. Carlos Alberto Chirinos Mundaca - 1
Estructura de Consultas ComplejasEstructura de Consultas Complejas
11. Lista de clientes cuyo país sea Alemania, España,
Francia
Select * from Clientes
Where país in ('Alemania','España','Francia')
12. Lista de empleados y edades
Select apellidos, nombre, fechanacimiento,
Int ((now-fechanacimiento)/365) as Edad from Empleados
13. Lista de pedidos realizados en octubre del año 1994
Select * from Pedidos
Where month (fechapedido) =10 and year (fechapedido) =1994
14. Cual fue el producto mas barato
Select Top 1 nombreproducto, preciounidad from Productos
Order by preciounidad asc
15. Cual fue el producto mas caro
Select Top 1 nombreproducto, preciounidad from Productos
Order by preciounidad desc
16. Lista de productos con sus categorías
Select nombreproducto, nombrecategoría
From productos P, categorías C
Where P.idcategoría= C.idcategoría
17. Lista de productos de la categoría bebidas
Select * from productos p, categorías c
Where p.idcategoría=c.idcategoría and nombrecategoría='bebidas'
18. Lista de proveedores de la empresa exotic liquids
Select * from productos p, proveedores pv
Where p.idproveedor=pv.idproveedor
and nombrecompañía='Exotic Liquids'
19. Lista de pedidos y los empleados que los realizaron
Select idpedido, fechapedido, apellidos
From pedidos pd, empleados e
Where pd.idempleado=e.idempleado
20. Lista de pedidos del empleado de apellidos Davolio
Select * from pedidos p, empleados e
Where p.idempleado=e.idempleado and apellidos='Davolio'
21. Lista de pedidos de la compañía Speedy Express
Ing. Carlos Alberto Chirinos Mundaca - 2
Estructura de Consultas ComplejasEstructura de Consultas Complejas
Select * from pedidos p, [compañías de envíos] c
Where p.formaenvío=c.idcompañíaenvíos
and nombrecompañía='Speedy Express'
22. Lista de productos, categorías y el proveedor que los
vende
Select nombreproducto, nombrecategoría, nombrecompañía
From proveedores pv, productos p, categorías c
Where pv.idproveedor=p.idproveedor and
p.idcategoría=c.idcategoría
23. Lista de pedidos, fechapedido,fechaentrega,cliente que
lo compró, empleado que lo vendió y compañía de reparto
que los despacho
Select Idpedido, fechapedido, fechaentrega, c.nombrecompañía,
ce.nombrecompañía, apellidos
From empleados e, pedidos p, [compañías de envíos] ce, clientes c
Where e.idempleado=p.idempleado and
p.formaenvío=ce.idcompañíaenvíos and p.idcliente=c.idcliente
24. Lista de categorias y cuantos productos tiene cada uno,
ordenados por cantidad.
Select nombrecategoría, count (idproducto) as cantidad
From categorías c, productos p
Where c.idcategoría=p.idcategoría
Group by nombrecategoría order by 2
25. Lista de clientes y la cantidad de pedidos que compro
cada uno.
Select nombrecompañía, count (idpedido) as Cantidad
From clientes c, pedidos p
Where c.idcliente=p.idcliente group by nombrecompañía
Order by 2 desc
26. Lista de proveedores y la cantidad de productos que
ofrece cada uno
Select nombrecompañía, count (idproducto) as Total
From proveedores pv, productos p
Where pv.idproveedor=p.idproveedor
Group by nombrecompañía
Ing. Carlos Alberto Chirinos Mundaca - 3
Estructura de Consultas ComplejasEstructura de Consultas Complejas
27. Lista de proveedores que despachan mas de 3
productos.
Select nombrecompañía, count (idproducto) as Total
From proveedores pv, productos p
Where pv.idproveedor=p.idproveedor
Group by nombrecompañía having count (idproducto)>3
28. Lista de pedidos y sus montos ordenados de mayor a
menor.
Select p.idpedido, sum (cantidad*preciounidad) as monto
From pedidos p, [Detalles de pedidos] dp
Where p.idpedido=dp.idpedido group by p.idpedido order by 2 desc
29. Lista de pedidos y sus montos ordenados de mayor a
menor pero solo los que exceden los 3500 soles
Select p.idpedido, sum (cantidad*preciounidad) as monto
From pedidos p, [Detalles de pedidos] dp
Where p.idpedido=dp.idpedido group by p.idpedido
Having sum (cantidad*preciounidad)>3500 order by 2 desc
30. Cual fue el empleado que vendió el pedido con mas
monto
Select Top 1 nombre, dp.idpedido,
Sum (cantidad*preciounidad) as Total
From empleados e, pedidos p, [detalles de pedidos] dp
Where e.idempleado=p.idempleado and p.idpedido=dp.idpedido
Group by nombre, dp.idpedido order by 3 desc
Ing. Carlos Alberto Chirinos Mundaca - 4
Estructura de Consultas ComplejasEstructura de Consultas Complejas
(Usando INNER JOIN 16-30)
16. Lista de productos con sus categorías
Select nombreproducto, nombrecategoría
From productos P inner join Categorías C
On P.idcategoría= C.idcategoría
17. Lista de productos de la categoría bebidas
Select nombreproducto, preciounidad, nombrecategoría
From productos p inner join categorías c
On p.idcategoría=c.idcategoría
Where nombrecategoría='bebidas'
18. Lista de proveedores de la empresa exotic liquids
Select nombreproducto, preciounidad, nombrecompañía
From productos p inner join proveedores pr
On p.idproveedor=pr.idproveedor
And nombrecompañía=’Exotic Liquids'
19. Lista de pedidos y los empleados que los realizaron
Select idpedido, fechapedido, apellidos
From pedidos p inner join empleados e
On p.idempleado=e.idempleado
20. Lista de pedidos del empleado de apellidos Davolio
Select idpedido, fehapedido, apellidos
From pedidos p inner join empleados e
On p.idempleado=e.idempleado and apellidos='Davolio'
21. Lista de pedidos de la compañía Speedy Express
Select idpedido, nombrecompañía
From pedidos p inner join [compañías de envíos] ce
On p.formaenvío=ce.idcompañíaenvíos
Where nombrecompañía='Speedy Express'
22. Lista de productos, categorías y el proveedor que los
vende
Select nombreproducto, nombrecategoría, nombrecompañía
From (productos p inner join categorías c
On p.idcategoría=c.idcategoría)
Inner join proveedores pr On p.idproveedor=pr.idproveedor
Ing. Carlos Alberto Chirinos Mundaca - 5
Estructura de Consultas ComplejasEstructura de Consultas Complejas
23. Lista de pedidos, fechapedido,fechaentrega,cliente que
lo compró, empleado que lo vendió y compañía de reparto
que los despacho
Select Idpedido, fechapedido, fechaentrega, c.nombrecompañía,
ce.nombrecompañía, apellidos
From ((pedidos p inner join cliente c
On p.idcliente=c.idcliente) inner join
[Compañías de envíos] ce On p.formaenvío=ce.idcompañíaenvíos)
Inner join empleados e On p.idempleado=e.idempleado
24. Lista de categorias y cuantos productos tiene cada uno,
ordenados por cantidad.
Select nombrecategoría, count (idproducto) as Nro_Productos
From categorías c inner join productos p
On c.idcategoría=p.idcategoría
Group by nombrecategoría order by 2
25. Lista de clientes y la cantidad de pedidos que compro
cada uno.
Select nombrecompañía, count (idpedido) as Nro_Pedidos
From clientes c inner join pedidos p
On c.idcliente=p.idcliente group by nombrecompañía
Order by 2 desc
26. Lista de proveedores y la cantidad de productos que
ofrece cada uno
Select nombrecompañía, count (idproducto) as Nro_Productos
From proveedores pr inner join productos p
On pr.idproveedor=p.idproveedor
Group by nombrecompañía
27. Lista de proveedores que despachan mas de 3
productos.
Select nombrecompañía, count (idproducto) as Nro_Productos
From proveedores pr inner join productos p
On pr.idproveedor=p.idproveedor
Group by nombrecompañía having count (idproducto)>3
28. Lista de pedidos y sus montos ordenados de mayor a
menor.
Select dp.idpedido, sum (cantidad*preciounidad) as monto
Ing. Carlos Alberto Chirinos Mundaca - 6
Estructura de Consultas ComplejasEstructura de Consultas Complejas
From pedidos p inner join [Detalles de pedidos] dp
On p.idpedido=dp.idpedido group by dp.idpedido
Order by 2 Desc
29. Lista de pedidos y sus montos ordenados de mayor a
menor pero solo los que exceden los 3500 soles
Select dp.idpedido, sum (cantidad*preciounidad) as monto
From pedidos p inner join [Detalles de pedidos] dp
On p.idpedido=dp.idpedido group by dp.idpedido
Having sum (cantidad*preciounidad)>3500 order by 2 desc
30. Cual fue el empleado que vendió el pedido con mas
monto
Select Top 1 apellidos, dp.idpedido,
Sum (cantidad*preciounidad) as Total
From (empleados e inner join pedidos p
On e.idempleado=p.idempleado) inner join
[Detalles de pedidos] dp On p.idpedido=dp.idpedido
Group by apellidos, dp.idpedido order by 3 desc
Ing. Carlos Alberto Chirinos Mundaca - 7
Estructura de Consultas ComplejasEstructura de Consultas Complejas
CONSULTAS DESARROLLADAS NIVEL INTERMEDIO
(Consultas Sólo una Tabla)
1. Listar los empleados que tengan entre 50 y 60 años.
Select idempleado, nombre, apellidos,
Int (year (now)-year (fechanacimiento)) as Edad
From empleados
Where int (year (now)-year (fechanacimiento)) between 50 and 60
2. Lista de productos que no estén suspendidos cuyo idproveedor
sea un número impar.
Select idproducto, nombreproducto, idproveedor
From productos
Where suspendido = false and idproveedor mod 2 =1
3. Lista de proveedores que no tengan página principal.
Select IdProveedor, NombreCompañía, páginaprincipal
From Proveedores
Where PáginaPrincipal is null
4. Mostrar los pedidos efectuados en el segundo trimestre del año
1995.
Select idpedido, fechapedido
From pedidos
Where month (fechapedido) between 4 and 6 and year (fechapedido)
=1995
5. Cual fue la fecha del pedido que tiene mayor cargo.
Select top 1 idpedido, fechapedido, cargo
From pedidos
Order by cargo Desc
6. Lista de pedidos que se vendieron en Norteamérica en el año
1995.
Select idpedido, fecha pedido from pedidos
Where paísdestinatario='estados unidos'
And year (fechapedido) =1995
7. Lista de pedidos que no tienen código postal del destinatario.
Select * from pedidos
Where CódPostalDestinatario is null
Ing. Carlos Alberto Chirinos Mundaca - 8
Estructura de Consultas ComplejasEstructura de Consultas Complejas
8. Lista de clientes brasileños cuyo cargo de contacto sea Asistente
de Ventas.
Select idcliente, nombrecompañía from clientes
Where país='brasil' and cargocontacto='asistente de ventas'
9. Quiénes son los empleados que actualmente tienen entre 10 y
12 años laborando en la empresa.
Select idempleado, nombre,
Int (year (now)-year (fechacontratación)) as Tiempolabor
From empleados
Where int (year (now)-year (fechacontratación)) in (10, 12)
10. Quien es el empleado que tiene menos edad.
Select top 1 nombre, int (year (now)-year (fechanacimiento)) as edad
From empleados
Order by int (year (now)-year (fechanacimiento))
11. Lista de productos de código impar cuyos precios estén
entre 20 y 50 además de ser productos que sean de categoría de
código 1,3 ó 5
Select idproducto, nombreproducto, preciounidad, idcategoría
From productos
Where idcategoría in (1, 3, 5) and preciounidad between 20 and 50
And idproducto mod 2 =1
12. Lista de pedidos que demoraron más de 10 días entre la
fecha que se realizó el pedido y la fecha que se envió.
Select idpedido, (fechaenvío-fechapedido) as Dias
From pedidos
Where (fechaenvío- fechapedido)>10
13. Cuál fue el pedido que más se demoró en atenderse desde
que se realizó el pedido y se envió el mismo.
Select top 1 idpedido, (fechaenvío-fechapedido) as Dias
From pedidos order by (fechaenvío-fechapedido) Desc
14. Lista de clientes que termine su nombre en “n” o “s” que
no tengan fax.
Select * from clientes
Where (nombrecompañía like '%n' or nombrecompañía like '%s')
And fax is null
Ing. Carlos Alberto Chirinos Mundaca - 9
Estructura de Consultas ComplejasEstructura de Consultas Complejas
15. Lista de clientes que sean brasileños o suecos, que no
tengan fax y además su cargo contacto sea Asistente de
Marketing o Propietario.
Select idcliente, nombrecompañía, nombrecontacto, dirección, ciudad,
país
From clientes
Where país in ('Brasil ','suecia') and fax is null and cargocontacto
In ('asistente de marketing','propietario')
16. Quien es el empleado que tiene menos tiempo laborando
en la empresa.
Select top 1 idempleado, nombre, apellidos, fechacontratación
From empleados order by 4 desc
17. Lista de productos cuya forma de presentación viene en
caja o paquete, cuyos precios estén entre 20 y 80.
Select nombreproducto, cantidadporunidad, preciounidad
From productos
Where (cantidadporunidad like '%paq%' or
Cantidadporunidad like '%cajas%') and preciounidad between 20 and
80
18. Lista de todos los clientes franceses y alemanes que
tengan fax.
Select idcliente, nombrecompañía, nombrecontacto, dirección, país
From clientes
Where país in ('alemania','francia') and fax is not null
19. Cual de los pedidos cuyo nombre de destinatario: “Around
the Horn” tuvo mayor cargo.
Select top 1 idpedido, destinatario, cargo
From pedidos
Where destinatario= 'around the horn'
Order by cargo desc
20. Ver todos los pedidos realizados los días lunes o viernes
que demoraron una semana en ser despachados.
Select idpedido, (format (day (fechapedido),'dddd')) as Dia
From pedidos
Where (fechaenvío-fechapedido) =7
And (format (day (fechapedido),'dddd')) in ('lunes','viernes')
Ing. Carlos Alberto Chirinos Mundaca - 10
Estructura de Consultas ComplejasEstructura de Consultas Complejas
Ing. Carlos Alberto Chirinos Mundaca - 11
Estructura de Consultas ComplejasEstructura de Consultas Complejas
(Consultas Con Tablas Relacionadas)
1. Lista de productos con sus categorías.
Select p.idproducto, nombreproducto, c.nombrecategoría
From productos p, categorías c
Where p.idcategoría=c.idcategoría
2. Lista de productos y proveedores que los ofrezcan.
Select idproducto, nombreproducto, nombrecompañía
From productos p, proveedores pv
Where p.idproveedor=pv.idproveedor
3. Lista de productos, a que categoría pertenece y que proveedor
los ofrece.
Select idproducto, nombreproducto, nombrecategoría,
nombrecompañía
From productos p, proveedores pv, categorías c
Where p.idproveedor=pv.idproveedor and
p.idcategoría=c.idcategoría
4. Lista de pedidos y los clientes que lo compraron.
Select idpedido, nombrecompañía
From pedidos p, clientes c
Where p.idcliente=c.idcliente
5. Lista de pedidos y los empleados que lo realizaron.
Select idpedido, nombre, apellidos
From pedidos p, empleados e
Where p.idempleado=e.idempleado
6. Lista de pedidos y la empresa que los despachó.
Select idpedido, nombrecompañía
From pedidos p, [compañías de envíos] c
Where p.formaenvío=c.idcompañíaenvíos
7. Lista de pedidos mostrando que empleado lo realizó, que cliente
lo compró y en que compañía fueron despachados.
Select idpedido, nombre as empleado, c.nombrecompañía as cliente
cm.nombrecompañía as compañía envíos
From pedidos p, [compañías de envíos] cm, clientes c, empleados e
Ing. Carlos Alberto Chirinos Mundaca - 12
Estructura de Consultas ComplejasEstructura de Consultas Complejas
Where p.formaenvío=cm.idcompañíaenvíos and c.idcliente=p.idcliente
and e.idempleado=p.idempleado
8. A qué categoría pertenece el producto más caro
Select top 1 idproducto, nombreproducto, nombrecategoría,
preciounidad
From productos p, categorías c
Where c.idcategoría=p.idcategoría order by 3 desc
9. Qué proveedor ofrece el producto más caro
Select top 1 nombrecompañía, nombreproducto, preciounidad
From productos p, proveedores pv
Where pv.idproveedor=p.idproveedor
Order by 3 desc
10. Lista de clientes alemanes o brasileños que realizaron
pedidos en el 2do trimestre del año 1995, y qué monto se les
cobró por cargo de dicho pedido.
Select c.idcliente, nombrecompañía, fechapedido, país, cargo
From pedidos p, clientes c
Where p.idcliente=c.idcliente and month (fechapedido) between 4
and 6
and year (fechapedido) =1995 and país in ('alemania','brasil')
11. Lista de todos los productos suspendidos, a qué categoría
pertenece y que proveedor los ofrece.
Select p.idproducto, nombreproducto, nombrecategoría,
nombrecompañía
From productos p, proveedores pv, categorías c
Where p.idproveedor=pv.idproveedor and
c.idcategoría=p.idcategoría
and Suspendido=false
12. Lista de todos los pedidos que se tardaron más de una
semana en ser despachados y que compañía de envíos lo
repartió.
Select idpedido, (fechaenvío-fechapedido) as Envío, nombrecompañía
From pedidos p, [compañías de envíos] c
Where p.formaenvío=c.idcompañíaenvíos
and (fechaenvío-fechapedido)>7
Ing. Carlos Alberto Chirinos Mundaca - 13
Estructura de Consultas ComplejasEstructura de Consultas Complejas
13. A qué categorías de producto le pertenece los productos
que aparecen el pedido 10248
Select nombreproducto, nombrecategoría
From categorías c, productos p, [detalles de pedidos] dp,pedidos pd
Where c.idcategoría=p.idcategoría and p.idproducto=dp.idproducto
and dp.idpedido=pd.idpedido and pd.idpedido =10248
14. Qué cliente realizó el pedido que tiene más monto en cargo
Select top 1 idpedido, nombrecompañía, cargo
From clientes c, pedidos p
Where p.idcliente=c.idcliente order by 3 desc
15. A qué categorías pertenecen los productos que ofrece el
proveedor “Leka Trading”
Select nombreproducto, nombrecategoría
From productos p, proveedores pv, categorías c
Where p.idcategoría=c.idcategoría and
pv.idproveedor=p.idproveedor
and nombrecompañía='leka trading'
16. Qué proveedores ofrecen los productos de la categoría:
Bebidas
Select nombrecompañía, nombreproducto
From productos p, proveedores pv, categorías c
Where p.idcategoría=c.idcategoría and
pv.idproveedor=p.idproveedor
and nombrecategoría='bebidas'
17. Listado de productos cuya presentación sea botella o
frasco, a que categoría pertenece y sus precios.
Select nombreproducto, nombrecategoría, cantidadporunidad,
preciounidad
From productos p, categorías c
Where p.idcategoría=c.idcategoría and
(Cantidadporunidad like ('%frasco%') or
Cantidadporunidad like ('%bot%') )
18. A qué proveedores pertenecen los productos que aparecen
en el pedido 10250
Select nombreproducto, nombrecompañía as cliente
From proveedores pv, productos p, [detalles de pedidos] dp, pedidos
Ing. Carlos Alberto Chirinos Mundaca - 14
Estructura de Consultas ComplejasEstructura de Consultas Complejas
pd
Where pv.idproveedor=p.idproveedor and
p.idproducto=dp.idproducto
and dp.idpedido=pd.idpedido and pd.idpedido=10250
19. A qué clientes pertenecen los pedidos despachados por la
empresa “Speedy Express”
Select idpedido, c.nombrecompañía
From pedidos p, [compañías de envíos] cm, clientes c
Where p.idcliente=c.idcliente and
p.formaenvío=cm.idcompañíaenvíos
and cm.nombrecompañía='speedy express'
20. A qué empleados pertenecen los pedidos despachados por
la empresa “United Package”
Select idpedido, nombre, apellidos
From empleados e, [compañías de envíos] c, pedidos p
Where p.idempleado=e.idempleado and
p.formaenvío=c.idcompañíaenvíos
and c.nombrecompañía='united package'
21. Qué empleados atendieron los pedidos realizados por el
cliente: “Around the Horn”
Select idpedido, nombre, apellidos
From empleados e, clientes c, pedidos p
Where p.idempleado=e.idempleado and p.idcliente=c.idcliente and
c.nombrecompañía='around the horn'
22. Lista de clientes que el empleado Anne atendió con algún
pedido.
Select idpedido, nombrecompañía
From empleados e, clientes c, pedidos p
Where p.idempleado=e.idempleado and p.idcliente=c.idcliente
and nombre='anne'
23. Lista de pedidos despachados por la empresa: “Federal
Shipping”, en que fecha se realizaron y que empleado los
realizó.
Select idpedido, fechapedido, nombre
From pedidos p, empleados e, [compañías de envíos] cm, clientes c
Where p.idempleado=e.idempleado and p.idcliente=c.idcliente and
Ing. Carlos Alberto Chirinos Mundaca - 15
Estructura de Consultas ComplejasEstructura de Consultas Complejas
p.formaenvío=cm.idcompañíaenvíos and
cm.nombrecompañía='federal shipping'
24. Qué pedidos que se entregaron a Alemania en el año 1995
y que fueron atendidos por los empleados Nancy o Andrew
Select idpedido, direccióndestinatario, nombre
From pedidos p, empleados e, [compañías de envíos] cm, clientes c
Where p.idempleado=e.idempleado and p.idcliente=c.idcliente and
p.formaenvío=cm.idcompañíaenvíos and c.país='alemania' and
nombre in('nancy','andrew')
25. Lista de los pedidos que se despacharon a “Río de Janeiro”
repartidos por la empresa “United Package” y que cliente los
compró.
Select p.IdPedido, c.NombreCompañía
From pedidos p, clientes c, [compañías de envíos] cm
Where p.IdCliente=c.idcliente and
p.FormaEnvío=cm.idcompañíaenvíos and
p.CiudadDestinatario='Río de Janeiro'
and cm.NombreCompañía='United Package'
26. Lista de pedidos donde aparece el producto ‘Pan fino’
como parte del pedido, en que cantidad se compró, a que precio
y que monto representó dicho pedido.
Select pd.idpedido, cantidad, dp.preciounidad,
(Cantidad*dp.preciounidad) as monto
From pedidos pd, productos p, [detalles de pedidos] dp
Where p.idproducto=dp.idproducto and dp.idpedido=pd.idpedido
and nombreproducto ='pan fino'
27. Lista de pedidos donde aparezca por lo menos un producto
de la categoría ‘Lácteos’ y en que fecha se realizaron.
Select pd.idpedido, nombreproducto
From pedidos pd, productos p, [detalles de pedidos] dp, categorías c
Where p.idproducto=dp.idproducto and dp.idpedido=pd.idpedido
and c.idcategoría=p.idcategoría and nombrecategoría='lácteos'
28. Lista de pedidos donde aparezca por lo menos un producto
ofrecido por el proveedor ‘Tokyo Traders’.
Select pd.idpedido, nombreproducto
From pedidos pd, productos p, [detalles de pedidos] dp, proveedores
Ing. Carlos Alberto Chirinos Mundaca - 16
Estructura de Consultas ComplejasEstructura de Consultas Complejas
pv
Where p.idproducto=dp.idproducto and dp.idpedido=pd.idpedido
and pv.idproveedor=p.idproveedor and nombrecompañía='tokyo
traders'
29. Mostrar el pedido, fecha de pedido, nombre del producto y
el monto, dé un producto que más monto representó en dicho
pedido.
Select top 1 pd.idpedido, fechapedido, nombreproducto,
(Cantidad * dp.preciounidad) as monto
From pedidos pd, productos p, [detalles de pedidos] dp
Where p.idproducto=dp.idproducto and dp.idpedido=pd.idpedido
order by 4 desc
30. Mostrar el pedido, fecha de pedido, nombre del producto y
el monto, dé un producto que más monto se le hizo en
descuento en dicho pedido.
Select top 1 pd.idpedido, fechapedido, nombreproducto,
(Cantidad * dp.preciounidad) as monto
From pedidos pd, productos p, [detalles de pedidos] dp
Where p.idproducto=dp.idproducto and dp.idpedido=pd.idpedido
order by descuento desc
Ing. Carlos Alberto Chirinos Mundaca - 17
Estructura de Consultas ComplejasEstructura de Consultas Complejas
(Usando INNER JOIN 1-15)
1. Lista de productos con sus categorías.
Select nombreproducto, nombrecategoría
From productos p inner join categorías c
On p.idcategoría=c.idcategoría
2. Lista de productos y proveedores que los ofrezcan.
Select nombreproducto, nombrecompañía
From productos p inner join proveedores pv
On p.idproveedor=pv.idproveedor
3. Lista de productos, a que categoría pertenece y que proveedor
los ofrece.
Select nombreproducto, nombrecategoría, nombrecompañía
From (productos p inner join proveedores pv
On p.idproveedor=pv.idproveedor) inner join categorías c
On p.idcategoría=c.idcategoría
4. Lista de pedidos y los clientes que lo compraron.
Select idpedido, nombrecompañía
From pedidos p inner join clientes c
On p.idcliente=c.idcliente
5. Lista de pedidos y los empleados que lo realizaron.
Select idpedido, nombre, apellidos
From pedidos p inner join empleados e
On p.idempleado=e.idempleado
6. Lista de pedidos y la empresa que los despachó.
Select idpedido, nombrecompañía
From pedidos p inner join [compañías de envíos] ce
On p.formaenvío=ce.idcompañíaenvíos
7. Lista de pedidos mostrando que empleado lo realizó, que cliente
lo compró y en que compañía fueron despachados.
Select idpedido, nombre, c.nombrecompañía as cliente,
ce.nombrecompañía as compañíaenvíos
From ((pedidos p inner join empleados e
On p.idempleado=e.idempleado) inner join clientes c
Ing. Carlos Alberto Chirinos Mundaca - 18
Estructura de Consultas ComplejasEstructura de Consultas Complejas
On p.idcliente=c.idcliente) inner join [compañías de envíos] ce
On p.formaenvío=ce.idcompañíaenvíos
8. A qué categoría pertenece el producto más caro
Select top 1 nombreproducto, nombrecategoría, preciounidad
From productos p inner join categorías c
On c.idcategoría=p.idcategoría order by 3 desc
9. Qué proveedor ofrece el producto más caro
Select top 1 nombreproducto, nombrecompañía, preciounidad
From productos p inner join proveedores pv
On p.idproveedor=pv.idproveedor
Order by 3 desc
10. Lista de clientes alemanes o brasileños que realizaron
pedidos en el 2do trimestre del año 1995, y qué monto se les
cobró por cargo de dicho pedido.
Select nombrecompañía, país, fechapedido, cargo
From pedidos p inner join clientes c
On p.idcliente=c.idcliente
Where país in ('alemania','brasil') and
Month (fechapedido) in (4, 5, 6) and year (fechapedido) =1995
11. Lista de todos los productos suspendidos, a qué categoría
pertenece y que proveedor los ofrece.
Select p.idproducto, nombreproducto, nombrecategoría,
nombrecompañía
From (productos p inner join proveedores pv
On p.idproveedor=pv.idproveedor) inner join categorías
On c.idcategoría=p.idcategoría and Suspendido=Trae
12. Lista de todos los pedidos que se tardaron más de una
semana en ser despachados y que compañía de envíos lo
repartió.
Select idpedido, (fechaenvío-fechapedido) as Demora,
nombrecompañía
From pedidos p inner join [compañías de envíos] ce
On p.formaenvío=ce.idcompañíaenvíos
Where (fechaenvío-fechapedido)>7
13. A qué categorías de producto le pertenece los productos
que aparecen el pedido 10248.
Ing. Carlos Alberto Chirinos Mundaca - 19
Estructura de Consultas ComplejasEstructura de Consultas Complejas
Select nombreproducto, nombrecategoría
From (productos p inner join categorías c
On p.idcategoría=c.idcategoría)
Inner join [detalles de pedidos] dp on p.idproducto=dp.idproducto
Where idpedido=10248
14. Qué cliente realizó el pedido que tiene más monto en cargo
Select top 1 nombrecompañía, idpedido, cargo
From clientes c inner join pedidos p
On c.idcliente=p.idcliente
Order by 3 desc
15. A qué categorías pertenecen los productos que ofrece el
proveedor “Leka Trading”
Select nombrecategoría, nombreproducto, nombrecompañía
From (categorías c inner join productos p
on c.idcategoría=p.idcategoría) inner join proveedores pv
on p.idproveedor=pv.idproveedor
Where nombrecompañía='Leka Trading'
Ing. Carlos Alberto Chirinos Mundaca - 20
Estructura de Consultas ComplejasEstructura de Consultas Complejas
CONSULTAS DESARROLLADAS NIVEL AVANZADO
(Con Tablas Relacionadas Usando Funciones de Agregado)
Ing. Carlos Alberto Chirinos Mundaca - 21
Estructura de Consultas ComplejasEstructura de Consultas Complejas
1. Listado de empleados y cuantos pedidos efectuó cada uno de
ellos.
Select nombre, count (idpedido) as total
From empleados e, pedidos p
Where p.idempleado=e.idempleado group by nombre
2. Listado de clientes y cuantos pedidos efectuó cada uno de ellos.
Select nombrecompañía, count (idpedido) as total
From clientes e, pedidos p
Where p.idcliente=e.idcliente group by nombrecompañía
3. Listado de compañías de envíos y cuántos pedidos despacharon
cada uno de ellos.
Select nombrecompañía, count (idpedido) as cantidad
From pedidos p, [compañías de envíos] c
Where p.formaenvío=c.idcompañíaenvíos
Group by nombrecompañía
4. Listado de proveedores y cuantos productos ofrecen cada uno
de ellos.
Select nombrecompañía, count (idproducto) as cantidad
From productos p, proveedores pv
Where p.idproveedor=pv.idproveedor group by nombrecompañía
5. Listado de categorías y cuántos productos contienen cada uno
de ellos.
Select nombrecategoría, count (idproducto) as cantidad
From productos p, categorías c
Where p.idcategoría=c.idcategoría group by nombrecategoría
6. Listado de empleados y el monto acumulado de todos sus
pedidos.
Select nombre, sum (cantidad*preciounidad) as cantidad
From pedidos p, empleados e, [detalles de pedidos] d
Where p.idempleado=e.idempleado and d.idpedido=p.idpedido
Group by nombre
7. Listado de clientes y el monto acumulado de todos sus pedidos.
Select nombrecompañía, sum (cantidad*preciounidad) as monto
From clientes c, pedidos p, [detalles de pedidos] dp
Where p.idcliente=c.idcliente and dp.idpedido=p.idpedido
Group by nombrecompañía
Ing. Carlos Alberto Chirinos Mundaca - 22
Estructura de Consultas ComplejasEstructura de Consultas Complejas
8. Listado de compañías de envíos y el monto acumulado de todos
sus pedidos que despacharon cada uno de ellos.
Select nombrecompañía, sum (cantidad*preciounidad) as monto
From [compañías de envíos] ce, pedidos p, [detalles de pedidos] dp
Where ce.idcompañíaenvíos=p.formaenvío and
dp.idpedido=p.idpedido
Group by nombrecompañía
9. Lista de pedidos y sus montos totales sin descuentos.
Select p.idpedido, sum (cantidad*preciounidad) as monto
From pedidos p, [detalles de pedidos] d
Where p.idpedido=d.idpedido group by p.idpedido
10. Lista de pedidos, montos totales sin descuentos, montos
de descuento y montos netos.
Select p.idpedido, ccur (sum (cantidad*dp.preciounidad))
As montototal,
Ccur (sum (cantidad*dp.preciounidad*descuento)) as descuento,
Ccur (sum (cantidad*dp.preciounidad*(1-descuento))) as montoneto
From [detalles de pedidos] dp, pedidos p
Where dp.idpedido=p.idpedido
Group by p.idpedido
11. Lista de pedidos y los montos netos de cada uno de ellos,
realizados por el empleado Andrew.
Select p.idpedido,
Ccur (sum (cantidad*dp.preciounidad*(1-descuento) )) as montoneto
From [detalles de pedidos] dp, pedidos p, empleados e
Where e.idempleado=p.idempleado and dp.idpedido=p.idpedido
And nombre='Andrew'
Group by p.idpedido
12. Cuánto se vendió en monto (cantidad de dinero), el
producto ‘Pez espada’
Select nombreproducto, sum (cantidad*dp.preciounidad) as Monto
Ing. Carlos Alberto Chirinos Mundaca - 23
Estructura de Consultas ComplejasEstructura de Consultas Complejas
From productos p,[detalles de pedidos] dp
Where p.idproducto=dp.idproducto
And nombreproducto='Pez espada'
Group by nombreproducto
13. En cuántos pedidos aparece el producto ‘Algas Konbu’
como producto vendido
Select nombreproducto, count (p.idpedido) as Cantidad
From pedidos p, productos pr, [detalles de pedidos] dp
Where pr.idproducto=dp.idproducto and dp.idpedido=p.idpedido
And nombreproducto='algas konbu'
Group by nombreproducto
14. Cuál fue el producto que tuvo menos ventas en unidades
Select top 1 nombreproducto, sum (cantidad) as Cantidad
From pedidos p, productos pr, [detalles de pedidos] dp
Where pr.idproducto=dp.idproducto and dp.idpedido=p.idpedido
Group by nombreproducto
Order by sum (cantidad) asc
15. Cuál fue el producto que más se vendió en monto (cantidad
de dinero)
Select top 1 nombreproducto,
Ccur (sum (cantidad*dp.preciounidad)) as Monto
From pedidos p, productos pr, [detalles de pedidos] dp
Where pr.idproducto=dp.idproducto and dp.idpedido=p.idpedido
Group by nombreproducto
Order by sum (cantidad*dp.preciounidad) desc
16. A qué categoría pertenece el producto que más vendió el
empleado Nancy
Select top 1 nombrecategoría, nombreproducto, dp.cantidad
From pedidos pd, productos p, [detalles de pedidos] dp, categorías c,
Empleados e Where c.idcategoría=p.idcategoría
and p.idproducto=dp.idproducto and dp.idpedido=pd.idpedido
And e.idempleado=pd.idempleado and nombre='Nancy'
Order by 3 desc
17. Cuál fue los 5 mejores promedios de ventas (monto)
efectuados por los empleados
Select top 5 sum (cantidad*preciounidad) as monto, nombre
Ing. Carlos Alberto Chirinos Mundaca - 24
Estructura de Consultas ComplejasEstructura de Consultas Complejas
From [detalles de pedidos] dp, pedidos p, empleados e
Where dp.idpedido=p.idpedido and p.idempleado=e.idempleado
Group by nombre order by 1 desc
18. Cuál fue el cliente que menos pedidos hizo
Select top 1 nombrecompañía, count (idpedido) as cantidadpedidos
From clientes c, pedidos p
Where c.idcliente=p.idcliente
Group by nombrecompañía
Order by count (idpedido)
19. Cuál es la compañía de envíos que más despachó en monto
de dinero
Select top 1 nombrecompañía,
Count (p.idpedido) as cantidadpedidos,
Sum (cantidad*preciounidad) as monto
From [compañías de envíos] c, pedidos p, [detalles de pedidos] dp
Where c.idcompañíaenvíos=p.formaenvío and
dp.idpedido=p.idpedido
Group by nombrecompañía order by sum (cantidad*preciounidad)
desc
20. Quién es el empleado que quedó último en ventas
(considere montos vendidos)
Select top 1 nombre, sum (cantidad*preciounidad) as monto
From pedidos p, empleados e, [detalles de pedidos] dp
Where e.idempleado=p.idempleado and dp.idpedido=p.idpedido
Group by nombre order by sum (cantidad*preciounidad)
21. Listado de todos los pedidos que sumen más de S/. 5000
que cliente lo compró y cuánto se demoró en despachar dicho
pedido.
Select p.idpedido, (cantidad*preciounidad) as
Monto, nombrecompañía, fechaentrega
From pedidos p, clientes c, [detalles de pedidos] d
Where p.idpedido=d.idpedido and p.idcliente=c.idcliente
And (cantidad*preciounidad) >5000
22. Qué proveedor ofrece el producto que más vendió Anne
(considere montos de ventas)
Select nombrecompañía, nombreproducto,
Ing. Carlos Alberto Chirinos Mundaca - 25
Estructura de Consultas ComplejasEstructura de Consultas Complejas
(Cantidad*c.preciounidad) as monto
From productos p, proveedores pv, [detalles de pedidos] c,
Pedidos pd, empleados e Where p.idproveedor=pv.idproveedor
And p.idproducto=c.idproducto and e.idempleado=pd.idempleado
And pd.idpedido=c.idpedido and nombre='anne'
23. Cuántos pedidos tiene el producto: “Ravilois Angelo” en
más de 30 unidades por pedido
Select count (pd.idpedido) as cantidad
From pedidos pd, productos p, [detalles de pedidos] d
Where p.idproducto=d.idproducto and d.idpedido=pd.idpedido
And nombreproducto='raviolis angelo' and cantidad >30
24. Cuál es el pedido que tiene más unidades vendidas del
producto: “Chocolate Blanco”
Select top 1 pd.idpedido, d.cantidad
From pedidos pd, productos p, [detalles de pedidos] d
Where p.idproducto=d.idproducto and d.idpedido=pd.idpedido
And nombreproducto='chocolate blanco' order by 2 desc
25. Lista de pedidos que tiene su monto bruto igual a su
monto neto (no se hizo ningún descuento).
Select p.idpedido, cantidad, preciounidad,
(Cantidad*preciounidad) as sueldobruto,
((Cantidad*preciounidad) -descuento) as sueldoneto
From pedidos p, [detalles de pedidos] d
Where d.idpedido=p.idpedido and
(Cantidad*preciounidad) = ((cantidad*preciounidad) -descuento)
26. Lista de pedidos que tiene su monto de descuento entre
$10 y $20.
Select p.idpedido, cantidad, preciounidad,
Int ((cantidad*preciounidad)*descuento) as descuento
From pedidos p, [detalles de pedidos] d
Where d.idpedido=p.idpedido and
((Cantidad*preciounidad)*descuento) between 10 and 20
27. Qué proveedor representa la mayor parte de las ventas
para la empresa
Select top 1 nombrecompañía, count (d.idproducto) as cantproductos
Ing. Carlos Alberto Chirinos Mundaca - 26
Estructura de Consultas ComplejasEstructura de Consultas Complejas
From proveedores pv, productos p, [detalles de pedidos] d,
pedidos pd
Where p.idproveedor=pv.idproveedor and p.idproducto=d.idproducto
And d.idpedido=pd.idpedido group by nombrecompañía
Order by 2 desc
28. Qué categoría de productos es la que representa la menor
mayor parte de las ventas para la empresa
Select top 1 nombrecategoría, count (d.idproducto) as cantproductos
From categorías c, productos p, [detalles de pedidos] d, pedidos pd
Where p.idcategoría=c.idcategoría and p.idproducto=d.idproducto
And d.idpedido=pd.idpedido group by nombrecategoría order by 2
29. Qué empleado vendió más productos de la categoría
‘Bebidas’ (en monto)
Select top 1 nombre, count (d.idproducto) as cantproductos
From categorías c, productos p, [detalles de pedidos] d,
pedidos pd, empleados e Where p.idcategoría=c.idcategoría
And p.idproducto=d.idproducto and d.idpedido=pd.idpedido
And pd.idempleado=e.idempleado and nombrecategoría='bebidas'
Group by nombre order by 2 desc
30. Quién fue el empleado que más compró productos de la
categoría ‘Condimentos’
Select top 1 nombrecompañía,
Count (d.idproducto) as cantproductos
From categorías c, productos p, [detalles de pedidos] d,
Pedidos pd, clientes cl
Where p.idcategoría=c.idcategoría and p.idproducto=d.idproducto
And d.idpedido=pd.idpedido and pd.idcliente=cl.idcliente
And nombrecategoría='condimentos' group by nombrecompañía
Order by 2 desc
Ing. Carlos Alberto Chirinos Mundaca - 27
Estructura de Consultas ComplejasEstructura de Consultas Complejas
(Usando INNER JOIN 1-15)
1. Listado de empleados y cuantos pedidos efectuó cada uno de
ellos.
Select nombre, count (idpedido) as total
From empleados e inner join pedidos p
On p.idempleado=e.idempleado group by nombre
2. Listado de clientes y cuantos pedidos efectuó cada uno de ellos.
Select nombrecompañía, count (idpedido) as total
From clientes e inner join pedidos p
On p.idcliente=e.idcliente group by nombrecompañía
3. Listado de compañías de envíos y cuántos pedidos despacharon
cada uno de ellos.
Select nombrecompañía, count (idpedido) as cantidad
From pedidos p inner join [compañías de envíos] c
On p.formaenvío=c.idcompañíaenvíos
Group by nombrecompañía
4. Listado de proveedores y cuantos productos ofrecen cada uno
de ellos.
Select nombrecompañía, count (idproducto) as cantidad
From productos p inner join proveedores pv
On p.idproveedor=pv.idproveedor group by nombrecompañía
5. Listado de categorías y cuántos productos contienen cada uno
de ellos.
Select nombrecategoría, count (idproducto) as cantidad
From productos p inner join categorías c
On p.idcategoría=c.idcategoría group by nombrecategoría
6. Listado de empleados y el monto acumulado de todos sus
pedidos.
Select nombre, sum (cantidad*preciounidad) as cantidad
From (pedidos p inner join empleados e on
p.idempleado=e.idempleado)
Inner join [detalles de pedidos] d
On d.idpedido=p.idpedido group by nombre
Ing. Carlos Alberto Chirinos Mundaca - 28
Estructura de Consultas ComplejasEstructura de Consultas Complejas
7. Listado de clientes y el monto acumulado de todos sus pedidos.
Select nombrecompañía, sum (cantidad*preciounidad) as monto
From (clientes c inner join pedidos p on p.idcliente=c.idcliente)
Inner join [detalles de pedidos] dp on dp.idpedido=p.idpedido
Group by nombrecompañía
8. Listado de compañías de envíos y el monto acumulado de todos
sus pedidos que despacharon cada uno de ellos.
Select nombrecompañía, sum (cantidad*preciounidad) as monto
From ([compañías de envíos] ce inner join pedidos p on
ce.idcompañíaenvíos=p.formaenvío) inner join [detalles de pedidos]
dp on dp.idpedido=p.idpedido group by nombrecompañía
9. Lista de pedidos y sus montos totales sin descuentos.
Select p.idpedido, sum (cantidad*preciounidad) as monto
From pedidos p inner join [detalles de pedidos] d
On p.idpedido=d.idpedido group by p.idpedido
10. Lista de pedidos, montos totales sin descuentos, montos
de descuento y montos netos.
Select p.idpedido, ccur (sum (cantidad*dp.preciounidad))
As montototal,
Ccur (sum (cantidad*dp.preciounidad*descuento)) as descuento,
Ccur (sum (cantidad*dp.preciounidad*(1-descuento))) as montoneto
From [detalles de pedidos] dp inner join pedidos p
On dp.idpedido=p.idpedido
Group by p.idpedido
11. Lista de pedidos y los montos netos de cada uno de ellos,
realizados por el empleado Andrew.
Select p.idpedido,
Ccur (sum (cantidad*dp.preciounidad*(1-descuento))) as montoneto
From ([detalles de pedidos] dp inner join pedidos p on
e.idempleado=p.idempleado) inner join empleados e on
dp.idpedido=p.idpedido
Where nombre='Andrew'
Group by p.idpedido
12. Cuánto se vendió en monto (cantidad de dinero), el
producto ‘Pez espada’
Select nombreproducto, sum (cantidad*dp.preciounidad) as Monto
Ing. Carlos Alberto Chirinos Mundaca - 29
Estructura de Consultas ComplejasEstructura de Consultas Complejas
From productos p inner join [detalles de pedidos] dp
On p.idproducto=dp.idproducto
Where nombreproducto='Pez espada'
Group by nombreproducto
13. En cuántos pedidos aparece el producto ‘Algas Konbu’
como producto vendido
Select nombreproducto, count (p.idpedido) as Cantidad
From ([detalles de pedidos] dp inner join productos pr on
pr.idproducto=dp.idproducto)
Inner join pedidos p on dp.idpedido=p.idpedido
Where nombreproducto='algas konbu'
Group by nombreproducto
14. Cuál fue el producto que tuvo menos ventas en unidades
Select top 1 nombreproducto, sum (cantidad) as Cantidad
From ([detalles de pedidos] dp inner join productos pr on
pr.idproducto=dp.idproducto)
Inner join pedidos p on dp.idpedido=p.idpedido
Group by nombreproducto order by sum (cantidad) asc
15. Cuál fue el producto que más se vendió en monto (cantidad
de dinero)
Select top 1 nombreproducto,
Ccur (sum (cantidad*dp.preciounidad)) as Monto
From ([detalles de pedidos] dp inner join productos pr on
pr.idproducto=dp.idproducto)
Inner join pedidos p on dp.idpedido=p.idpedido
Group by nombreproducto
Order by sum (cantidad*dp.preciounidad) desc
Ing. Carlos Alberto Chirinos Mundaca - 30