==================================================================
Universidad Nacional de Lujan
Departamento de Ciencias Basicas
Division Estadistica y Sistemas
11165 Base de Datos - 10165 Programacion Aplicada
Lic. Guillermo Cherencio
Consultas en SQL
===================================================================

DADAS LAS SIGUIENTES RELACIONES:
===============================

CLIENTE(IDC,APENOM,SALDO,FSALDO)
PRODUCTO(IDP,DESCR,PRECIOB,PRECIOC)
SUCURSAL(IDS,DESCR)
FACTURA(IDS,NRO,IDC,FECHA)
DFACTURA(IDS,NRO,IDP,CANT,PRECIO)

-- Consultas pregrabadas para ser reutilizadas en distintas consultas

CREATE VIEW TFACTURA AS -- CALCULO MONTO TOTAL DE CADA FACTURA
SELECT FF.IDS AS IDS,FF.NRO AS NRO,SUM(DF.CANT*DF.PRECIO) AS TOTAL
FROM FACTURA AS FF INNER JOIN DFACTURA AS DF ON FF.IDS = DF.IDS AND FF.NRO = DF.NRO
GROUP BY FF.IDS, FF.NRO

CREATE VIEW PSUCURSAL AS -- PROMEDIO DE FACTURACION POR SUCURSAL
SELECT TF.IDS AS IDS,AVG(TF.TOTAL) AS PSUC
FROM TFACTURA AS TF
GROUP BY TF.IDS

CREATE VIEW TSUCURSAL AS -- TOTAL DE FACTURACION POR SUCURSAL
SELECT TF.IDS AS IDS,SUM(TF.TOTAL) AS TSUC
FROM TFACTURA AS TF
GROUP BY TF.IDS

CREATE VIEW TCLIENTE AS -- TOTAL DE FACTURACION POR CLIENTE
SELECT FC.IDC AS IDC,SUM(TF.TOTAL) AS TCLI
FROM TFACTURA AS TF INNER JOIN FACTURA AS FC ON TF.IDS = FC.IDS AND TF.NRO = FC.NRO
GROUP BY FC.IDC

AGRUPAMIENTOS
=============

1. cantidad de facturas

SELECT COUNT(*) FROM FACTURA

2. cantidad de items por facturas

SELECT IDS,NRO,COUNT(IDP) AS CANTIDAD FROM DFACTURA
GROUP BY IDS,NRO

3. monto total de facturas

IDEM VIEW TFACTURA

4. cantidad de facturas por sucursal

SELECT IDS,COUNT(NRO) AS CANTIDAD
FROM FACTURA
GROUP BY IDS

5. total de facturacion por sucursal

SELECT IDS,SUM(CANT*PRECIO) AS TOTAL
FROM DFACTURA
GROUP BY IDS

CONSULTAS NO CORRELACIONADAS
============================
1. facturas de los clientes con saldo < a 1500

SELECT * FROM FACTURA AS FAC
WHERE FAC.IDC IN ( SELECT CL.IDC FROM CLIENTE AS CL
                   WHERE CL.SALDO < 1500 )

2. facturas con monto por encima del promedio general
   de facturacion

SELECT * FROM TFACTURA AS TFAC
WHERE TFAC.TOTAL > ( SELECT AVG(TF.TOTAL) FROM TFACTURA AS TF )

3. facturas con monto por encima del promedio general
   de facturacion de la sucursal

SELECT TFAC.* FROM TFACTURA AS TFAC INNER JOIN PSUCURSAL AS PS ON TFAC.IDS = PS.IDS
WHERE TFAC.TOTAL > PS.PSUC


CONSULTAS CORRELACIONADAS
=========================
1. facturas que contengan al menos 1 producto que haya sido
   facturado con un precio menor que el precio base

SELECT FAC.IDS,FAC.NRO,FAC.FECHA,CL.APENOM
FROM FACTURA AS FAC INNER JOIN CLIENTE AS CL ON FAC.IDC = CL.IDC
WHERE EXISTS ( SELECT * FROM DFACTURA AS DF INNER JOIN PRODUCTO AS PR 
                             ON DF.IDP = PR.IDP
               WHERE DF.IDS = FAC.IDS AND DF.NRO = FAC.NRO AND DF.PRECIO < PR.PRECIOB )

2. facturas que contengan mas de 3 productos facturados

SELECT FAC.IDS,FAC.NRO,FAC.FECHA,CL.APENOM
FROM FACTURA AS FAC INNER JOIN CLIENTE AS CL ON FAC.IDC = CL.IDC
WHERE ( SELECT COUNT(*) FROM DFACTURA AS DF
            WHERE DF.IDS = FAC.IDS AND DF.NRO = FAC.NRO ) > 3

3. clientes con facturas que esten por encima del promedio
   de facturacion de la sucursal

SELECT CL.APENOM
FROM   CLIENTE AS CL
WHERE  EXISTS (
   SELECT * FROM TFACTURA AS FAC
   WHERE FAC.IDC = CL.IDC AND EXISTS (
      SELECT * FROM PSUCURSAL AS PS
      WHERE PS.IDS = FAC.IDS AND FAC.TOTAL > PS.PSUC
   )
)


CONSULTAS VARIAS
================

1. cual es la sucursal que mas vendio

SELECT S.DESCR FROM SUCURSAL AS S
WHERE EXISTS ( SELECT * FROM TSUCURSAL AS TTS
               WHERE TTS.IDS = S.IDS AND 
                     TTS.TSUC IN ( SELECT MAX(TS.TSUC) FROM TSUCURSAL AS TS )
             )

2. que producto aun no se vendio nunca

SELECT P.DESCR FROM PRODUCTO AS P
WHERE P.IDP NOT IN ( SELECT DISTINCT IDP FROM DFACTURA )

3. que producto se vendio solo 2 veces

SELECT P.DESCR FROM PRODUCTO AS P
WHERE 2 = ( SELECT COUNT(DF.IDP) FROM DFACTURA AS DF WHERE DF.IDP = P.IDP )

4. cuanto dinero hemos ganado

SELECT SUM(DF.CANT*(DF.PRECIO-PR.PRECIOC)) AS GANANCIA
FROM DFACTURA AS DF INNER JOIN PRODUCTO AS PR ON DF.IDP = PR.IDP

5. cuanto dinero ganamos si vendemos 25 pinzas a $ 50 c/u

SELECT ((50*25)-(PR.PRECIOC*25)) AS GANAMOS
FROM PRODUCTO AS PR
WHERE PR.DESCR = "PINZA"

6. de todos los productos que se vendieron, cual es el de menor costo

SELECT PR.DESCR FROM PRODUCTO AS PR
WHERE EXISTS ( SELECT DISTINCT DF.IDP FROM DFACTURA AS DF
               WHERE DF.IDP = PR.IDP AND PR.PRECIOC IN (
                  SELECT MIN(PR2.PRECIOC) 
                  FROM DFACTURA AS DF2 INNER JOIN PRODUCTO AS PR2 ON DF2.IDP = PR2.IDP )
             )

7. si comparamos el total de compra de los clientes vs su saldo deudor,
   cual o cuales son los clientes que tienen la mejor relacion, es decir,
   un monto alto de compras con respecto a su bajo saldo deudor

CREATE VIEW TSCLIENTE AS -- TOTAL DE FACTURACION POR CLIENTE MENOS SALDO
SELECT TC.IDC AS IDC,(TC.TCLI - CL.SALDO) AS FACTOR
FROM CLIENTE AS CL INNER JOIN TCLIENTE AS TC ON TC.IDC = CL.IDC

SELECT CL.APENOM FROM CLIENTE AS CL
WHERE EXISTS ( SELECT * FROM TSCLIENTE AS TSC
               WHERE TSC.IDC = CL.IDC AND TSC.FACTOR IN (
                 SELECT MAX(TSC2.FACTOR) FROM TSCLIENTE AS TSC2 )
             ) 

8. teniendo en cuenta el costo y las cantidades vendidas, cual o cuales fueron
   los productos que nos dieron mayor ganancia

CREATE VIEW TGPRODUCTO AS -- GANANCIA POR PRODUCTO
SELECT DF.IDP AS IDP,SUM(DF.CANT*(DF.PRECIO-PR.PRECIOC)) AS GANANCIA
FROM DFACTURA AS DF INNER JOIN PRODUCTO AS PR ON DF.IDP = PR.IDP
GROUP BY DF.IDP

SELECT PRO.DESCR FROM PRODUCTO AS PRO
WHERE EXISTS ( SELECT TG.IDP FROM TGPRODUCTO AS TG 
               WHERE TG.IDP = PRO.IDP AND TG.GANANCIA IN (
                  SELECT MAX(TG2.GANANCIA) FROM TGPRODUCTO AS TG2 )
             )

