/* @%ORACLE_HOME%\ejOra8\Produccion\Preg2Prod */

/* eliminar temporalmente algunas restricciones
*/
ALTER TABLE suminpiezas drop constraint FK_SP_Sum;
ALTER TABLE suministradores drop constraint PK_Sumin;
ALTER TABLE suministradores drop constraint UN_NIF_S;

/* añadir un suministrador duplicado y otros dos con sólo el NIF distinto
*/
DELETE FROM Suministradores WHERE rfSum='S01' OR rfSum='S02';
INSERT INTO Suministradores values ('S01',     'PEREZ',     'X01',    'ZARAGOZA');
INSERT INTO Suministradores values ('S01',     'PEREZ',     'X01',    'ZARAGOZA');
INSERT INTO Suministradores values ('S02',  'MARTINEZ',     'A12',      'HUESCA');
INSERT INTO Suministradores values ('S02',  'MARTINEZ',     'A11',      'HUESCA');


/* listar los Suministradores con nº de referencia distinto
*/
SELECT DISTINCT rfSum FROM Suministradores;

/* listar los Suministradores distintos
*/
SELECT DISTINCT rfSum, nombSum, NIF, ciudad FROM Suministradores;

/* Observar que la diferencia de ambas tablas es nula
*/
SELECT * FROM Suministradores
 MINUS
SELECT DISTINCT rfSum, nombSum, NIF, ciudad FROM Suministradores;

/* contar los Suministradores que hay por cada nº de referencia
*/
SELECT rfSum, count(rfSum) FROM Suministradores GROUP BY rfSum;

/* contar los números de referencia de Suministradores que hay repetidos
*/
SELECT COUNT(rfSum)-COUNT(distinct rfSum) "rfSum Rep." FROM Suministradores;

/* listar los números de referencia de Suministradores que hay repetidos
*/
SELECT rfSum FROM Suministradores GROUP BY rfSum HAVING count(rfSum) > 1;

/* También se puede obtener con SELECT anidados (observese la necesidad de DISTINCT)
*/
SELECT DISTINCT rfSum, nombSum, NIF, ciudad FROM Suministradores X
 WHERE 1 < (SELECT count(rfSum) FROM suministradores WHERE rfSum=X.rfSum);

/* listar los Suministradores que hay repetidos
*/
SELECT * FROM Suministradores
 GROUP BY rfSum, nombSum, NIF, ciudad
 HAVING count(rfSum) > 1;

/* También se puede obtener con SELECT anidados (observese la necesidad de DISTINCT)
*/
SELECT DISTINCT rfSum, nombSum, NIF, ciudad FROM Suministradores X
 WHERE 1 < ( SELECT count(rfSum)
             FROM suministradores
             WHERE rfSum=X.rfSum AND nombSum=X.nombSum AND NIF=X.NIF AND ciudad=X.ciudad);

DELETE FROM Suministradores WHERE rfSum='S01' OR rfSum='S02';
INSERT INTO Suministradores values ('S01',     'PEREZ',     'X01',    'ZARAGOZA');
INSERT INTO Suministradores values ('S02',  'MARTINEZ',     'A12',      'HUESCA');

/* volver a dejar las restricciones como estaban
*/
ALTER TABLE Suministradores ADD (CONSTRAINT PK_Sumin  PRIMARY KEY (rfSum));
ALTER TABLE Suministradores ADD (CONSTRAINT UN_NIF_S  UNIQUE(NIF));
ALTER TABLE suminpiezas     ADD (CONSTRAINT FK_SP_Sum FOREIGN KEY (rfSum)
                                                      REFERENCES Suministradores);