Actividades Complementarias T6

-- Modifica el número de plazas con un valor igual a la mitad en aquellos centros con menos de dos profesores

UPDATE centros
SET num_plazas=num_plazas/2
WHERE cod_centro
IN (SELECT cod_centro FROM profesores GROUP BY cod_centro HAVING COUNT(*)<2)
OR (COD_CENTRO NOT IN (SELECT DISTINCT COD_CENTRO FROM PROFESORES));

-- Elimina los centros que no tengan personal

DELETE centros
WHERE cod_centro
IN (SELECT cod_centro FROM profesores GROUP BY cod_centro HAVING COUNT(*)=0);

-- Añade un nuevo profesor en el centro o en los centros cuyo número de administrativos sea 1 en la especialidad de 'IDIOMA', con DNI 8790055 y de nombre 'Clara Salas'.

INSERT INTO profesores
 (cod_centro, dni, apellidos, especialidad)
 SELECT DISTINCT cod_centro, 8790055, 'CLARA SALAS', 'IDIOMA'
 FROM centros
 WHERE cod_centro IN (SELECT cod_centro FROM personal
 WHERE funcion='ADMINISTRATIVO'
 GROUP BY cod_centro
 HAVING COUNT(*)=1);

 -- Borra al personal que esté en centros de menos de 300 plazas y con menos de dos profesores.
 -- Añadimos los que no tienen personal despues del AND y antes del OR.
DELETE FROM personal
 WHERE cod_centro IN (SELECT cod_centro FROM centros
 WHERE num_plazas < 300) AND (cod_centro NOT IN (SELECT DISTINCT cod_centro FROM profesores)
 OR cod_centro IN
 (SELECT cod_centro FROM profesores
 GROUP BY cod_centro
 HAVING COUNT(*)<2));

 -- Borra a los profesores que estén en la tabla PROFESORES y que no estén en la tabla PERSONAL.

 DELETE FROM profesores
  WHERE dni NOT IN
  (SELECT dni
  FROM personal
  WHERE funcion='PROFESOR');

  -- Da de alta un nuevo artículo de 'Primera' categoría para los fabricantes de 'FRANCIA' y abastece con 5 unidades de ese artículo a todas las tiendas y en la fecha de hoy.

  COMMIT;

INSERT INTO articulos
SELECT 'Tampones', cod_fabricante, 1,'Primera',100,75,35
FROM fabricantes
WHERE pais='FRANCIA'; -- FALTA LA SEGUNDA PARTE.

     -- Proporcionar el numero de tiendas y de existencias mínimas

SELECT 5*(SELECT COUNT(*) FROM TIENDAS) FROM DUAL -- ???????????????? WTF

INSERT INTO ARTICULOS
SELECT 'Tampones', COD_FABRICANTE, 4, 'Primera', 1.20, 1, 5*(SELECT COUNT (*) FROM TIENDAS)
FROM FABRICANTES
WHERE PAIS = 'FRANCIA';

INSERT INTO PEDIDOS
SELECT NIF, 'Tampones', cod_fabricante, 4, 'Primera', SYSDATE, 5
FROM tiendas, faricantes
WHERE PAIS = 'FRANCIA';

 -- Inserta un pedido de 20 unidades en la tienda '1111-A' con el artículo que mayor número de ventas haya realizado.
 -- (poner el distinct y group by articulo, cod_fabricante, peso, categoria)

INSERT INTO PEDIDOS
           SELECT DISTINCT '1111-A', articulo, cod_fabricante, peso, categoria, SYSDATE, 20
           FROM ventas
           WHERE articulo=(SELECT articulo FROM ventas
           GROUP BY articulo, cod_fabricante, peso, categoria
           HAVING SUM(unidades_vendidas) =
           (SELECT (MAX(SUM(unidades_vendidas)))
           FROM ventas
           GROUP BY articulo, cod_fabricante, peso, categoria));

-- Da de alta una tienda en la provincia de 'MADRID' y abastécela con 20 unidades de cada uno de los artículos existentes.

INSERT INTO tiendas
 VALUES ('6755-J','PAPITOmarket','C/Mi Amool 69','Madrid','Madrid',28043);

INSERT INTO pedidos
 SELECT '6755-J',articulo, cod_fabricante, peso,categoria, SYSDATE, 20
 FROM articulos;

-- Da de alta dos tiendas en la provincia de 'SEVILLA' y abastécelas con 30 unidades de artículos de la marca de fabricante 'GALLO'.

INSERT INTO TIENDAS VALUES ('4100-A', 'CACASHOP', 'C/ZURULLO 20', 'Sevilla', 'Sevilla', 41500);
INSERT INTO TIENDAS VALUES ('4545-B', 'MIERDITIENDA', 'C/ESCROTO 30','Sevilla', 'Sevilla', 41500);

INSERT INTO pedidos
    SELECT DISTINCT NIF, ARTICULO, A.COD_FABRICANTE, PESO CATEGORIA, SYSDATE, 30
    FROM TIENDAS, ARTICULOS A, FABRICANTES F
    WHERE F.NOMBRE='GALLO' AND PROVINCIA='SEVILLA' AND A.COD_FABRICANTE = F.COD_FABRICANTE;

-- Realiza una venta para todas las tiendas de 'TOLEDO' de 10 unidades en los artículos de 'Primera' categoría.

INSERT INTO VENTAS
  SELECT NIF, ARTICULO, COD_FABRICANTE, PESO, CATEGORIA, SYSDATE, 10
  FROM articulos,tiendas
  WHERE categoria='Primera'
  AND provincia='TOLEDO';

-- Para aquellos artículos de los que se hayan vendido más de 30 unidades, realiza un pedido de 10 unidades para la  tienda con NIF '5555-B' con la fecha actual. AGRUPAR TAMBIÉN POR COD_FABRICANTE Y PONER SELECT DISTINCT.

INSERT INTO PEDIDOS SELECT DISTINCT '5555-B', ARTICULO, COD_FABRICANTE, PESO, CATEGORIA, SYSDATE, 10 FROM VENTAS WHERE ARTICULO IN
(SELECT ARTICULO FROM VENTAS
GROUP BY ARTICULO, COD_FABRICANTE, PESO, CATEGORIA
HAVING SUM(UNIDADES_VENDIDAS)>30);

        --Otra forma de hacerlo mas sencilla

INSERT INTO PEDIDOS
    SELECT DISTINCT '5555-B', ARTICULO, COD_FABRICANTE, PESO, CATEGORIA, SYSDATE, 10
    FROM VENTAS
    GROUP BY ARTICULO, COD_FABRICANTE, PESO, CATEGORIA
    HAVING SUM(UNIDADES_VENDIDAS)>30;

--  Cambia los datos de la tienda con NIF '1111-A' igualándolos a los de la tienda con NIF '2222-A'.

UPDATE TIENDAS SET (NOMBRE, DIRECCIÓN,POBLACIÓN,PROVINCIA,CODPOSTAL)=
    (SELECT NOMBRE, DIRECCIÓN,POBLACIÓN,PROVINCIA,CODPOSTAL FROM TIENDAS WHERE NIF = '2222-A')
    WHERE NIF='1111-A';

-- Cambiar todos los artículos de ‘primera’ a 'segunda categoría del país ITALIA

UPDATE articulos
 SET categoria='Segunda'
 WHERE categoria='Primera' AND
 cod_fabricante = (SELECT cod_fabricante FROM fabricantes
 WHERE pais='ITALIA');

-Modifica aquellos pedidos en los que la cantidad pedida sea superior a las existencias del artículo, asignando el 20 por 100 de las existencias a la cantidad que se ha pedido.

UPDATE PEDIDOS P SET UNIDADES_PEDIDAS=
(SELECT EXISTENCIAS*0.2 FROM ARTICULOS WHERE
    ARTICULO=P. ARTICULO AND
    COD_FABRICANTE=P. COD_FABRICANTE AND
    PESO=P. PESO AND
    CATEGORIA=P. CATEGORIA )
    WHERE UNIDADES_PEDIDAS >
        (SELECT EXISTENCIAS FROM ARTICULOS WHERE
        ARTICULO=P. ARTICULO AND
        COD_FABRICANTE=P. COD_FABRICANTE AND
        PESO=P. PESO AND
        CATEGORIA=P. CATEGORIA);

-- Eliminar aquellas tiendas que no han realizado ventas.

DELETE FROM TIENDAS WHERE NIF NOT IN (SELECT NIF FROM VENTAS);

-- Eliminar los artículo que no hayan tenido no compras ni ventas.

DELETE FROM ARTICULOS
 WHERE (ARTICULO, COD_FABRICANTE, PESO, CATEGORIA) NOT IN
    (SELECT DISTINCT ARTICULO COD_FABRICANTE, PESO, CATEGORIA FROM VENTAS
     GROUP BY ARTICULO)
 AND (ARTICULO, COD_FABRICANTE, PESO, CATEGORIA) NOT IN
    (SELECT DISTINCT ARTICULO, COD_FABRICANTE, PESO, CATEGORIA FROM PEDIDOS
    GROUP BY ARTICULO);

-- Borrar los pedidos de ‘primera’ categoría cuyo país de procedencia sea BÉLGICA.

DELETE FROM PEDIDOS
 WHERE COD_FABRICANTE = (SELECT COD_FABRICANTE FROM FABRICANTES WHERE PAIS='BELGICA');

-- Borrar los pedidos que no tengan tienda.

 DELETE FROM PEDIDOS
  WHERE NIF NOT IN(SELECT NIF FROM TIENDAS);

-- Restar uno a las unidades de los últimos pedidos de la tienda con nif 555-B

UPDATE PEDIDOS
 SET UNIDADES_PEDIDAS=UNIDADES_PEDIDAS-1
 WHERE NIF='5555-B' AND
 FECHA_PEDIDO= (SELECT MAX(FECHA_PEDIDO) FROM PEDIDOS WHERE NIF='5555-B');