/* @PregProd1_DATE.sql */
SET LINESIZE 132
SET PAGESIZE 60
SET ECHO ON

DESCRIBE Suministradores;
DESCRIBE Piezas;
DESCRIBE Proyectos;
DESCRIBE SuminPiezasProy;

/*
  Obtener la ref. y el NIF de todos los proveedores de París
 */
SELECT rfSum, NIF FROM Suministradores WHERE ciudad='Paris';

/*
  Obtener los Suministradores y las Piezas de la misma ciudad (equijoin)
 */
SELECT S.* , P.* 
 FROM Suministradores S, Piezas P
 WHERE S.ciudad=P.ciudad;

/*
  Obtener la ref. y ciudad de los Suministradores y las Piezas relacionadas
  (es decir, de las piezas y sus suministradores)
 */
SELECT distinct S.rfSum, S.ciudad , P.rfPza, P.ciudad 
 FROM Suministradores S, Piezas P, SuminPiezasProy SPP
 WHERE S.rfSum=SPP.rfSum AND P.rfPza=SPP.rfPza;

SELECT distinct S.rfSum, S.ciudad , P.rfPza, P.ciudad 
 FROM Suministradores S, Piezas P
 WHERE EXISTS
       (SELECT *
         FROM SuminPiezasProy
         WHERE rfSum=S.rfSum AND rfPza=P.rfPza);

SELECT distinct S.rfSum, S.ciudad , P.rfPza, P.ciudad 
 FROM Suministradores S, Piezas P
 WHERE (S.rfSum, P.rfPza) IN (SELECT rfSum, rfPza FROM SuminPiezasProy);

/*
  Obtener el nombre de los Suministradores que no suministran la pieza 'P2'
 */
SELECT nombSum
 FROM Suministradores S
 WHERE NOT EXISTS
      (SELECT * FROM SuminPiezasProy
       WHERE rfSum = S.rfSum AND rfPza = 'P2');

SELECT nombSum
 FROM Suministradores S
 WHERE rfSum NOT IN
      (SELECT rfSum FROM SuminPiezasProy
       WHERE  rfPza = 'P2');

SELECT nombSum FROM Suministradores
MINUS
SELECT nombSum
 FROM Suministradores S, SuminPiezasProy SPP
 WHERE S.rfSum = SPP.rfSum AND rfPza = 'P2';

SELECT nombSum FROM Suministradores
MINUS
SELECT nombSum
 FROM Suministradores S
 WHERE S.rfSum IN
       (SELECT rfSum FROM SuminPiezasProy WHERE rfPza = 'P2');

/*
  Obtener el nombre de los Suministradores que suministran todas las piezas
  (es decir, de aquellos tales que no hay ninguna pieza que no suministren
 */
SELECT nombSum
 FROM Suministradores S
 WHERE NOT EXISTS
      (SELECT * FROM Piezas P
        WHERE NOT EXISTS
             (SELECT * FROM SuminPiezasProy
              WHERE rfSum = S.rfSum AND rfPza = P.rfPza));

COLUMN "Gramos"  FORMAT 99999
/*
  Obtener la ref. de las piezas, su peso, color y cantidad máxima suministrada, de las
  piezas de color 'rojo' o 'azul' tales que la cantidad total suministrada sea mayor que
  350, excluyendo en este computo los envíos de cantidades menores o iguales que 200.
  El listado deberá estar ordenado por cantidades máximas en sentido creciente, y para
  valores iguales, por la referencia de la pieza, en sentido descendiente.
 */
SELECT P.rfPza "rf.", p.Peso * 1000 "Gramos",
       P.Color "Color", 'Cant. max. enviada' " ", max(SPP.cant)
FROM Piezas P, SuminPiezasProy SPP
WHERE P.rfPza = SPP.rfPza         AND
      P.color IN ('Rojo', 'Azul') AND
      SPP.Cant > 200
GROUP BY P.rfPza, P.Peso, P.Color
HAVING SUM(Cant) > 350
ORDER BY 5, P.rfPza DESC;

COLUMN gramos  HEADING "peso" FORMAT 99999
COLUMN maxCant HEADING "max." FORMAT 9999

/* también se prodrá poner el nombre de la columna en vez de su número */
SELECT P.rfPza "ref.", p.Peso * 1000 gramos, 'gramos, color' " ",
       P.Color " ", 'Cant. max. enviada' " ", max(SPP.cant) maxCant
FROM Piezas P, SuminPiezasProy SPP
WHERE P.rfPza = SPP.rfPza         AND
      P.color IN ('Rojo', 'Azul') AND
      SPP.Cant > 200
GROUP BY P.rfPza, P.Peso, P.Color
HAVING SUM(Cant) > 350
ORDER BY maxCant, P.rfPza DESC;