/* @pregBD_Piezas.sql */
SET ECHO ON

/* Piezas de color 'verde'
*/
SELECT * FROM Pieza  WHERE color = 'VERDE';

/* Nombres de todas las piezas
*/
SELECT DISTINCT nombPieza FROM Pieza;

/* Nombres de todas las piezas que empiezan por 'T'
*/
SELECT DISTINCT nombPieza FROM Pieza WHERE nombPieza LIKE 'T%';


/* Piezas que son 'TUERCA' o 'TORNILLO'
*/
SELECT DISTINCT * FROM Pieza WHERE nombPieza = 'TUERCA' OR nombPieza = 'TORNILLO';

/* Piezas que son 'TUERCA' o 'TORNILLO'
*/
SELECT DISTINCT *
  FROM  Pieza
  WHERE nombPieza = 'TUERCA'
UNION
SELECT DISTINCT  *
   FROM  Pieza
   WHERE nombPieza = 'TORNILLO';

/* Piezas que son 'TUERCA' o 'TORNILLO'
*/
SELECT DISTINCT clvPieza "id Pieza", nombPieza nombre, color
  FROM  Pieza
  WHERE nombPieza IN ('TUERCA', 'TORNILLO');

/* Piezas que son 'TUERCA' o 'TORNILLO'
*/
SELECT DISTINCT *
  FROM  Pieza
  WHERE nombPieza =ANY ('TUERCA', 'TORNILLO');

/* Nombre de los proveedores que suministran al menos una pieza de color 'VERDE'
*/
SELECT DISTINCT nombProv "proveedor", 'suministra piezas de color verde' "función"
FROM  Proveedor V, Suministrar S, Pieza P
WHERE V.clvProv = S.clvProv AND S.clvPieza = P.clvPieza AND P.color = 'VERDE';

/* Nombre de los proveedores que suministran piezas de color 'VERDE' o 'ROJO'
*/
SELECT DISTINCT nombProv "proveedor", 'suministra piezas de color verde' "función"
FROM  Proveedor V, Suministrar S, Pieza P
WHERE V.clvProv = S.clvProv AND S.clvPieza = P.clvPieza AND P.color = 'VERDE'
UNION
SELECT DISTINCT nombProv "proveedor", 'suministra piezas de color rojo' "función"
FROM  Proveedor V, Suministrar S, Pieza P
WHERE V.clvProv = S.clvProv AND S.clvPieza = P.clvPieza AND P.color = 'ROJO';

/* Piezas de las que no se conoce el color
*/
SELECT * FROM Pieza WHERE  color IS NULL;

/* Piezas disponibles en varios colores
*/
SELECT DISTINCT  P1.clvPieza, P1.nombPieza, P1.color
  FROM  Pieza P1, Pieza P2
  WHERE	P1.nombPieza = P2.nombPieza AND P1.color <> P2. color;

/* Proveedores que suministran 'TUERCAS' y 'TORNILLOS'
*/
SELECT DISTINCT S1.clvProv
   FROM  Suministrar S1, Pieza P1, Suministrar S2, Pieza P2
   WHERE S1.clvPieza = P1.clvPieza AND P1.nombPieza = 'TUERCA'
     AND S2.clvPieza = P2.clvPieza AND P2.nombPieza = 'TORNILLO'
     AND S1.clvProv = S2. clvProv;

/* Proveedores que suministran 'TUERCAS' y 'TORNILLOS'
*/
SELECT DISTINCT clvProv
   FROM  Suministrar S, Pieza P
   WHERE S.clvPieza = P.clvPieza AND P.nombPieza = 'TUERCA'
INTERSECT
SELECT DISTINCT clvProv
   FROM  Suministrar S, Pieza P
   WHERE S.clvPieza = P.clvPieza AND P.nombPieza = 'TORNILLO';

/* Clave y nombre de los proveedores que no suministran piezas
*/
CREATE VIEW noSuministran AS
  SELECT clvProv FROM  Proveedor
MINUS
  SELECT clvProv FROM  Suministrar;

SELECT  *
FROM  Proveedor V, noSuministran N
WHERE V.clvProv = N.clvProv;

DROP VIEW noSuministran;

/* Clave y nombre de los proveedores que no suministran piezas
*/
SELECT *
FROM  Proveedor
WHERE clvProv NOT IN (SELECT clvProv FROM Suministrar);

/* Clave y nombre de los proveedores que no suministran piezas
*/
SELECT *
FROM  Proveedor V
WHERE NOT EXISTS (SELECT * FROM Suministrar WHERE clvProv = V.clvProv);

/* Clave y nombre de los proveedores que no suministran tuercas verdes
*/
SELECT *
FROM  Proveedor V
WHERE NOT EXISTS (SELECT * FROM Pieza P
                  WHERE (nombPieza,color) IN (('TUERCA', 'VERDE'))
                    AND EXISTS(SELECT * FROM Suministrar
                               WHERE clvProv = V.clvProv AND clvPieza = P.clvPieza)
                 );

/* Clave y nombre de los proveedores que no suministran tuercas verdes
*/
SELECT *
FROM  Proveedor V
WHERE clvProv IN (SELECT clvProv FROM Proveedor
                  MINUS
                  SELECT clvProv FROM Suministrar S, Pieza P
                  WHERE S.clvPieza = P.clvPieza
                    AND P.nombPieza = 'TUERCA' AND P.color = 'VERDE');


/* Clave y nombre de los proveedores que suministran tuercas de todos los tipos
*/
SELECT *
FROM  Proveedor V
WHERE NOT EXISTS (SELECT * FROM Pieza P
                  WHERE P.nombPieza = 'TUERCA'
                    AND NOT EXISTS (SELECT * FROM Suministrar
                                    WHERE clvProv = V.clvProv AND clvPieza = P.clvPieza) );


/* Clave y nombre de los proveedores que suministran tuercas de todos los tipos
*/
CREATE VIEW NoSumTuercas AS
SELECT clvProv, Pieza.clvPieza
 FROM  Proveedor, Pieza
 WHERE nombPieza = 'TUERCA'
MINUS
SELECT clvProv, P.clvPieza
 FROM  Suministrar S, Pieza P
 WHERE S.clvPieza = P.clvPieza AND P.nombPieza = 'TUERCA';
 
SELECT clvProv, nombProv FROM Proveedor
MINUS
SELECT P.clvProv, nombProv FROM Proveedor P, NoSumTuercas N WHERE P.clvProv = N.clvProv;

DROP VIEW NoSumTuercas;


/* Clave y nombre de los proveedores, ordenados por nombre
*/
SELECT clvProv, nombProv FROM Proveedor ORDER BY nombProv;

/* Clave y nombre de los proveedores, por orden inverso de nombre
*/
SELECT * FROM Proveedor ORDER BY 2 DESC;

/* total de piezas existentes
*/
SELECT count(DISTINCT clvPieza) "tot. Piezas" FROM Pieza;

/* total de piezas distintas existentes
*/
SELECT count(DISTINCT nombPieza) "tipos de Piezas" FROM Pieza;

/* total de proveedores
*/
SELECT count(*) "tot. proveedores" FROM   Proveedor;

/* mayor valor de clvPieza
*/
SELECT max(clvPieza) "max. clvPieza" FROM   Pieza;

/* clave y nombre de las piezas, y el último dígito de la clave
*/
SELECT clvPieza, MOD(clvPieza, 10) "term.", nombPieza "nombre"
 FROM   Pieza;

/* clave y nombre de las piezas con clave impar, y el último dígito de la clave
*/
SELECT clvPieza, MOD(clvPieza, 10) "term.", nombPieza "nombre"
 FROM   Pieza
 WHERE  MOD(clvPieza,2) = 1;

/* nombres de las piezas, y cantidad de cada tipo
*/
SELECT nombPieza "tipo", count(clvPieza) "total"
 FROM   Pieza
 GROUP BY nombPieza;

/* mostrar los valores NULL como 'desconocido' (SQLPLUS)
*/
SET NULL desconocido

/* colores de las piezas y número de piezas de cada color 
*/
SELECT color, count(*) "total", count(color) "colores"
 FROM   Pieza
 GROUP BY color;

/* número medio de piezas de cada color (incluyendo desconocidos) 
*/
SELECT AVG(count(*)) "piezas/color (incluso nulo)"
 FROM   Pieza
 GROUP BY color;

/* número medio de piezas de cada color (sólo los conocidos)
*/
SELECT AVG(count(color)) "piezas/color (conocido)"
 FROM   Pieza
 GROUP BY color
 HAVING color IS NOT NULL;

/* nombre (y cantidad) de los tipos de piezas de las que hay mas de 1,
   ordenadas por nombre 
*/
SELECT nombPieza "tipo", count(clvPieza) "total"
 FROM   Pieza
 GROUP BY nombPieza
 HAVING count(clvPieza) > 1
 ORDER BY nombPieza ASC;

  
/* Colores de las piezas y número de piezas de cada color, pero sólo de
   los colores con menos piezas que la media, ordenados alfabeticamente
*/
SELECT color, COUNT(*) "total" FROM   Pieza
GROUP BY  color
HAVING count(*) < (SELECT AVG(count(*)) FROM Pieza GROUP BY color)
ORDER BY  color ;

/* mostrar los valores NULL como '' (SQLPLUS)
*/
SET NULL ''

/* especificar los formatos de algunas columnas (SQLPLUS)
*/
column nombre                                    format a20;
column Pieza                                     format a16;
column color                                     format a12;

/* indicar que si no cabe en la columna, trunque el valor (SQLPLUS)
*/
SET WRAP OFF

/* clave y nombre de los proveedores, junto con las piezas que suministran 
*/
SELECT V.clvProv "id.", nombProv "nombre", P.clvPieza "ref.", nombPieza "Pieza", color
FROM   Proveedor V, Suministrar S, Pieza P
WHERE  V.clvProv = S.clvProv (+) AND S.clvPieza = P.clvPieza (+);

/* especificar los formatos de las columnas (SQLPLUS)
*/
column nombPieza         heading "Pieza"         format a16;
column nombProv          heading "Vendedor"      format a16;
column clvProv           heading "ID"            format 999;
column clvPieza          heading "ref."          format 999;

/* clave y nombre de los proveedores, junto con las piezas que suministran 
*/
SELECT V.clvProv, nombProv, P.clvPieza, nombPieza, color
FROM   Proveedor V, Suministrar S, Pieza P
WHERE  V.clvProv = S.clvProv AND S.clvPieza = P.clvPieza
UNION
SELECT clvProv, nombProv, TO_NUMBER(NULL), NULL, NULL
FROM   Proveedor
WHERE  clvProv NOT IN (SELECT clvProv FROM Suministrar);

/* añadir las piezas:  'tuerca amarilla', de clave 99, y 'martillo', de clave 98 
*/
INSERT INTO Pieza VALUES (99, 'TUERCA',   'AMARILLO');
INSERT INTO Pieza VALUES (98, 'MARTILLO', '');

/* añadir como piezas la lista de proveedores (aunque no tenga sentido)
*/ 
INSERT INTO Pieza SELECT clvprov, nombprov, TO_CHAR(NULL) FROM Proveedor;
SELECT * FROM Pieza;

/* eliminar todas las tuercas de color amarillo. Como se ha especificado borrado
   en cascada, no es necesario eliminar previamente los suministros de las piezas
   a eliminar para no violar la integridad referencial.
DELETE  FROM Suministrar
        WHERE clvPieza IN (SELECT clvPieza FROM Pieza
                           WHERE nombPieza = 'TUERCA' AND color = 'AMARILLO');
*/
 
DELETE  FROM Pieza WHERE  nombPieza = 'TUERCA' AND color = 'AMARILLO';

/* eliminar todos los suministros de piezas
*/ 
DELETE  FROM Suministrar;

/* eliminar todas los tipos de piezas de las que hay 1 o menos
*/ 
DELETE FROM Pieza P
  WHERE 2 > (SELECT count(*) FROM Pieza  WHERE nombPieza = P.nombPieza);

/* cambiar el color rojo por rojizo
*/ 
UPDATE Pieza SET color = 'ROJIZO'  WHERE  color = 'ROJO';

/* poner a la pieza 91 el color de la pieza 95
*/ 
UPDATE Pieza SET color = (SELECT color FROM Pieza WHERE clvPieza=95)
  WHERE clvPieza=91;

/* añadir las piezas:  'tuerca amarilla', de clave 99, y 'martillo', de clave 98 
*/
INSERT INTO Pieza VALUES (99, 'TUERCA',   'AMARILLO');
INSERT INTO Pieza VALUES (98, 'MARTILLO', '');

/* asignar 'desconocido' al color de las piezas con color no existente
*/ 
UPDATE Pieza SET color = 'desconocido'  WHERE  color IS NULL;

SELECT * FROM Pieza;