/* @%ORACLE_HOME%\ejOra8\Produccion\preg1Prod */
SET ECHO ON

SELECT          rfSum  FROM SuminPiezas;
SELECT DISTINCT rfSum  FROM SuminPiezas;
SELECT DISTINCT rfSum  FROM SuminPiezas WHERE rfSum NOT IN ('S01','S03');
SELECT DISTINCT rfSum  FROM SuminPiezas where rfSum=any('S01','S03');
SELECT DISTINCT rfSum  FROM SuminPiezas where rfSum<>all('S01','S03');

/* Obtención de las ciudades donde sólo hay suministradores */
SELECT DISTINCT ciudad FROM Suministradores
 MINUS
SELECT DISTINCT ciudad FROM Piezas;

/* Creación de una tabla con las ref. de las piezas llamadas 'TAPON' */
CREATE TABLE tapones (rfPza CHAR(4));
INSERT INTO tapones SELECT DISTINCT rfPza FROM Piezas WHERE nombPza='TAPON';
SELECT * FROM tapones;

/* Obtención de los suministradores de tapones de todos los tipos. Estos se pueden
   obtener como (SuminPiezas : tapones). Para implementar la división, se opta por
   crear una vista auxiliar on los pares (rfSum,rfPza) que no existen. La solución
   se obtiene restando de los suministradores existentes los de la vista obtenida.
 */
CREATE VIEW aux AS
  SELECT DISTINCT rfSum,t.rfPza FROM SuminPiezas,tapones t
   MINUS
  SELECT rfSum,rfPza FROM SuminPiezas;
SELECT * FROM aux;
SELECT DISTINCT rfSum FROM SuminPiezas
  MINUS
SELECT DISTINCT rfSum FROM aux;
DROP VIEW aux;

/* información de las piezas de cada suministrador */
SELECT rfSum,P.* FROM SuminPiezas SP, piezas P WHERE sp.rfPza=p.rfPza;

/* información de los suministradores de piezas en su misma ciudad */
SELECT * FROM Suministradores
WHERE (rfSum,ciudad) IN
      (SELECT rfSum,ciudad
       FROM SuminPiezas SP, piezas P
       WHERE sp.rfPza=p.rfPza);

/* información completa de los suministradores y piezas de la misma ciudad */
SELECT S.*, P.*
FROM   Suministradores S, SuminPiezas SP, Piezas P
WHERE  S.rfSum=SP.rfSum AND SP.rfPza=P.rfPza AND S.ciudad=P.ciudad;

/* total de piezas, piezas distintas y nº medio de piezas distintas de cada suministrador
   que suministra algún tipo de pieza
*/
SELECT rfSum, COUNT(*) "tipos", SUM(cant) "total", AVG(cant) "media"
 FROM SuminPiezas
 GROUP BY rfSum;

/* total de piezas y piezas distintas de cada suministrador (todos)
*/
SELECT rfSum, COUNT(*) "tipos", SUM(cant) "total"
 FROM SuminPiezas
 GROUP BY rfSum
UNION
  SELECT rfSum, 0, 0
  FROM   Suministradores
 MINUS
  SELECT rfSum, 0, 0
  FROM SuminPiezas;

SELECT S.rfSum, COUNT(DISTINCT SP.rfPza) "tipos", SUM(cant) "total"
 FROM Suministradores S, SuminPiezas SP
 WHERE S.rfSum = SP.rfSum (+)
 GROUP BY S.rfSum;

/* suministradores de más de 1 piezas distintas */
SELECT rfSum, COUNT(*) dif FROM SuminPiezas
GROUP BY rfSum
HAVING COUNT(*) > 1;

/* Obtención del total de proveedores que han suministrado piezas */
SELECT COUNT(DISTINCT rfSum) "tot. Sumin." FROM SuminPiezas;

/* suministradores de más de 1 piezas distintas y cantidad > 60 */
SELECT rfSum, COUNT(*) dif FROM SuminPiezas
WHERE cant > 60
GROUP BY rfSum
HAVING COUNT(*) > 1;

/* proveedores distintos de la pieza 'P01R', y total de piezas recibidas */
SELECT COUNT(*) "nº Sumin.", SUM(cant) "tot. Pzas." FROM SuminPiezas
WHERE rfPza = 'P01R';

DROP   TABLE tapones;