Examen 2ev | Bases de datos (DAM DAW ASIR)

CNAE (Clasificación Nacional de Actividades Económicas)

Mediante Heidi SQL importa los datos y con PHPMyAdmin concede los permisos al usuario examen para poder hacer SELECT sobre geo_normalizada.

Hacer que el usuario examen tenga permisos de SELECT contra la base de datos geo_normalizada.
En la base de datos TEST importar la tabla de CNAE 2009 CNAE 2009

Examen ZIP con visitas (fichero pesado)
Examen ZIP sin visitas
Municipios con habitantes

Script para actualizar habitantes

UPDATE municipios as m 
SET m.habitantes = (SELECT mh.hab
from test.municipios_hab mh
WHERE mh.nombre = m.nombre);

DDL MySQL

[plegar]

DML

INSERT INTO estados VALUES 
  (DEFAULT,"Activa"),
  (DEFAULT,"Inactiva"),
  (DEFAULT,"Liquidada"),
  (DEFAULT,"Suspendida");
create table examen.semilla(
id int);
insert into examen.semilla values 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
create table examen.numeros(
id int);
set @i:=0;
insert into examen.numeros
select @i:=@i+1 
from semilla s1 cross join semilla s2 
cross join semilla s3 cross join semilla s4
cross join semilla s5 cross join semilla s6;
INSERT INTO cnae VALUES (DEFAULT, "0000","SIN CLASIFICAR");
INSERT INTO comerciales VALUES 
(DEFAULT, "Juan","644 655 666","[email protected]","juan",MD5("juan"),null),
(DEFAULT, "Perico","666 655 644","[email protected]","perico",MD5("perico"),1),
(DEFAULT, "Andrés","633 622 611","[email protected]","andres",MD5("andres"),1),
(DEFAULT, "Amparito","622 655 699","[email protected]","amparito",MD5("amparito"),2),
(DEFAULT, "Gertrudis","600 600 600","[email protected]","ger",MD5("ger"),3),
(DEFAULT, "Genoveva","643 678 699","[email protected]","genoveva",MD5("genoveva"),5);

INSERT INTO clientes 
(cif,nombre,direccion,
web,email,telefono,latitud,
longitud,validado,fk_cnae,
fk_user,fk_estado,fk_comercial)
SELECT 
CONCAT(
  if(n.id%2=0,"A","B"),
  truncate(RAND()*10,0),
  truncate(RAND()*10,0),
  truncate(RAND()*10,0),
  truncate(RAND()*10,0),
  if(n.id>999,n.id,
    if(n.id>99,CONCAT("0",n.id),
      if(n.id>9,CONCAT("00",n.id),
         CONCAT("000",n.id))))) AS cif,
CONCAT("Cliente ",n.id) nombre,
CONCAT("C/ Del Cliente Nº",n.id) direccion,
CONCAT("cliente",n.id,".com") web,
CONCAT("info@cliente",n.id,".com") email,
CONCAT("9",
  truncate(RAND()*10,0),
  truncate(RAND()*10,0),
  truncate(RAND()*10,0),
  truncate(RAND()*10,0),
  if(n.id>999,n.id,
    if(n.id>99,CONCAT("0",n.id),
      if(n.id>9,CONCAT("00",n.id),
         CONCAT("000",n.id))))) AS telefono,
0,
0,
if(truncate(RAND()*5,0)%2=0,true,FALSE) validado,
1,
1,
1,
TRUNCATE(1+RAND()*3,0)
FROM numeros n LIMIT 9876;

INSERT INTO portales 
SELECT 
NULL,
CONCAT("sector",n.id,".com") dominio,
CONCAT("título del sector",n.id) titulo,
CONCAT("descripcion del sector",n.id) descripcion,
0,1,1,1
FROM numeros n LIMIT 1000;
INSERT INTO examen.regiones (nombre)
SELECT nombre FROM geo_normalizada.regiones
ORDER BY id;
INSERT INTO examen.provincias (nombre, fk_region)
SELECT nombre, fk_region FROM geo_normalizada.provincias
ORDER BY id;
INSERT INTO examen.comarcas (nombre)
SELECT nombre  FROM geo_normalizada.comarcas
ORDER BY id;
INSERT INTO examen.prov_comarca 
SELECT fk_comarca, fk_provincia 
FROM geo_normalizada.prov_comarcas;

INSERT INTO portales
SELECT null,
  concat(REPLACE(REPLACE(
       lower(replace(m.nombre,"'","-"))
       ," ","-"),"í","i"),".com") AS dominio,
  CONCAT_WS(" ","Portal de",m.nombre) as titulo,
  CONCAT_WS(" ","Directorio de empresas de",m.nombre) as
  descripcion,  0 visitas,  1 coeficiente, 2 , 1
FROM geo_normalizada.municipios m;
INSERT INTO municipios
SELECT null,
  m.nombre AS dominio,
  m.latitud,
  m.longitud,
  0,
  (select p.id from portales p where 
    substr(p.titulo,11) =m.nombre) AS fk_portal,
  m.fk_comarca,
  m.fk_provincia  
FROM geo_normalizada.municipios m;
INSERT INTO examen.localidades
SELECT NULL, l.nombre, l.fk_municipio
FROM geo_normalizada.localidades l;
INSERT INTO examen.cpostales
SELECT codpostal
FROM geo_normalizada.cod_postales;

INSERT INTO examen.cp_localidad
SELECT CP.fk_localidad, b.codpostal
FROM geo_normalizada.localidades_cp CP join 
geo_normalizada.cod_postales b
ON CP.fk_cod_postal = b.id;

INSERT INTO examen.cnae
SELECT NULL, c.cod_integrado,
c.titulo_cnae2009
FROM geo_normalizada.sector_cnae2009 c;
SET @i:= (SELECT COUNT(*) FROM cnae);
INSERT INTO sectores 
SELECT 
NULL,
CONCAT("sector",n.id) nombre,
truncate(RAND()*@i,0)+1 AS fk_cnae,
n.id AS fk_portal
FROM numeros n LIMIT 1000;
insert INTO campanas VALUES
(DEFAULT,"Default","https://x.com",1);

insert INTO publicidad VALUES
(DEFAULT,"https://x.com/slide.jpg",1,0,0,1),
(DEFAULT,"https://x.com/lateral.jpg",1,0,0,2),
(DEFAULT,"https://x.com/foot.jpg",1,0,0,3);
CREATE TABLE semilla4 
SELECT * FROM semilla LIMIT 4;
INSERT INTO anuncios 
SELECT NULL, 
ta.id AS fk_publicidad,
ta.id AS fk_tipo,
p.id AS fk_portal,
s4.id AS posicion
FROM tipos_anuncios as ta INNER join
publicidad pu
ON ta.id = pu.fk_tipo
cross JOIN
semilla4 s4 cross JOIN portales p ;
CREATE TABLE diccionario
(id INT, texto VARCHAR(100));
INSERT INTO diccionario VALUES
(1,"Navidad 2022"),
(2,"Navidad 2023"),
(3,"Black Friday 2022"),
(4,"Black Friday 2023"),
(5,"Rebajas Enero 2022"),
(6,"Rebajas Enero 2023"),
(7,"Rebajas Agosto 2022"),
(8,"Rebajas Agosto 2023"),
(9,"San Valentín 2022"),
(10,"San Valentín 2023"),
(11,"Carnaval 2022"),
(12,"Carnaval 2023"),
(13,"Publicidad corporativa 2022"),
(14,"Publicidad corporativa 2023");
INSERT INTO campanas
SELECT 
NULL,
CONCAT((SELECT texto
FROM diccionario d where
d.id = 1+(c.id MOD 14))," ",c.nombre) t1,
lower(
CONCAT("http://",replace(c.nombre," ",""),".com","/",
REPLACE((SELECT texto
FROM diccionario d where
d.id = 1+(c.id MOD 14))," ","") 
)
) t2,
c.id as fk
FROM clientes c
WHERE c.validado = TRUE;
INSERT INTO cargos (cargo) VALUES
("Gerente"),("Marketing"),("Financiero"),("Empleado");

UPDATE clientes
SET acronimo =
  if (id<10,
    CONCAT("CLT00",clientes.id),
     if (id<100, CONCAT("CLT0",clientes.id), CONCAT("CLT",clientes.id) )); INSERT INTO publicidad SELECT NULL AS id, concat(c.link,"/imagen-",t.ancho,"-",t.alto,".jpg") as link, c.id AS fk_campana, 0,0, t.id as fk_tipo FROM campanas c cross join tipos_anuncios t WHERE c.fk_cliente !=1; UPDATE anuncios A SET fk_publicidad = (SELECT id FROM publicidad P where A.fk_tipo = P.fk_tipo ORDER BY RAND() LIMIT 1) WHERE A.id MOD 50 = 0; INSERT INTO facturas SELECT DISTINCT NULL, SYSDATE(), c.fk_cliente FROM anuncios a JOIN publicidad p ON a.fk_publicidad = p.id JOIN campanas c ON p.fk_campana = c.id WHERE fk_cliente !=1; UPDATE facturas set fecha = DATE_ADD("2021/01/01",interval RAND()*720 DAY); INSERT INTO lineas_fac SELECT NULL AS id,f.fecha AS fecha_inicio, DATE_ADD(f.fecha,INTERVAL 12+ RAND()*12 MONTH) AS fecha_fin, f.id AS fk_factura, c.id AS fk_campana, tp.precio_base AS precio, a.id AS fk_anuncio FROM tipos_anuncios AS tp JOIN anuncios AS a ON tp.id = a.fk_tipo JOIN publicidad AS p ON a.fk_publicidad = p.id JOIN campanas AS c ON p.fk_campana = c.id JOIN clientes cl ON c.fk_cliente = cl.id JOIN facturas AS f ON cl.id = f.fk_cliente WHERE cl.id != 1; CREATE TABLE nombres ( id INT AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(30), apellido VARCHAR(30)); INSERT INTO nombres VALUES (DEFAULT,"María","Pedraza"), (DEFAULT,"Laura","Martínez"), (DEFAULT,"Felipe","Hermoso"), (DEFAULT,"Ramón","Villanueva"), (DEFAULT,"Lucio","Sobrino"), (DEFAULT,"Armando","Camorra"), (DEFAULT,"Isabel","Ayuso"), (DEFAULT,"Pedro","Piqueras"), (DEFAULT,"Rodolfo","Langostino"), (DEFAULT,"Pablo","Iglesias"), (DEFAULT,"Carmen","López"), (DEFAULT,"Remigio","Carpintero"), (DEFAULT,"Victoria","Abril"), (DEFAULT,"Joselín","Ubrique"), (DEFAULT,"Jesús","Gracias"), (DEFAULT,"Marcelino","Pan y Vino"), (DEFAULT,"Perico","Sánchez"); INSERT INTO contactos (nombre, telefono, fk_cliente, fk_cargo) SELECT CONCAT_WS(" ",(SELECT nombre FROM nombres ORDER BY RAND() LIMIT 1) , (SELECT apellido FROM nombres ORDER BY RAND() LIMIT 1) ) nombre, CONCAT("666",(SELECT id from numeros where id > 99999 ORDER BY RAND() LIMIT 1)) telefono,
    (SELECT id FROM clientes ORDER BY RAND() LIMIT 1) AS fk_clientes,
  (SELECT id FROM cargos ORDER BY RAND() LIMIT 1) AS fk_cargos
FROM numeros LIMIT 500;

UPDATE clientes 
SET fk_cnae = 
(SELECT id FROM cnae
ORDER BY RAND() LIMIT 1),
fk_cp = (SELECT codigo FROM cpostales
ORDER BY RAND() LIMIT 1)
WHERE TRUE;
--
UPDATE clientes C
SET fk_localidad = 
(SELECT CP.fk_localidad FROM cp_localidad CP
WHERE C.fk_cp = CP.fk_cp LIMIT 1);
--
UPDATE clientes C
SET fk_comercial = 
(SELECT id FROM comerciales 
ORDER BY RAND() LIMIT 1);
--
INSERT cliente_sector 
SELECT S.id, C.id FROM clientes C JOIN cnae CN 
ON C.fk_cnae = CN.id JOIN sectores S
ON CN.id = S.fk_cnae;


--
TRUNCATE crm;
INSERT IGNORE crm
SELECT DATE_ADD("2020/01/01",interval RAND()*1080 DAY),fk_comercial, id, 
  CONCAT("llamada telefónica al ", telefono)
FROM clientes 
WHERE validado = TRUE AND
  id MOD 2 =0
union
SELECT DATE_ADD("2020/01/01",interval RAND()*1080 DAY),fk_comercial, id, 
  CONCAT("envío de correo electrónico ", email)
FROM clientes 
WHERE id MOD 3 =0;

INSERT INTO visitas
SELECT NULL,
DATE_ADD("2020/01/01",INTERVAL N.id DAY),
truncate(RAND()*150,0),
P.id
FROM 
numeros N cross join portales P
WHERE N.id <=720;

UPDATE portales SET 
visitas = (SELECT 50+round(RAND()*300,0));

DROP TABLE numeros;

ALTER TABLE localidades
ADD COLUMN altura INT,
ADD COLUMN longitud decimal(10,6),
ADD COLUMN latitud decimal(10,6);
UPDATE localidades l
SET 
  altura = (SELECT altura FROM geo_normalizada.localidades m WHERE m.id = l.id),
  longitud = (SELECT round(longitud,4) FROM geo_normalizada.localidades m WHERE m.id = l.id),
  latitud = (SELECT round(latitud,4) FROM geo_normalizada.localidades m WHERE m.id = l.id);
  
ALTER TABLE municipios
ADD COLUMN altura INT AFTER longitud;

update  municipios m
SET  
  altura = (SELECT round(avg(altura),0) FROM geo_normalizada.localidades l WHERE l.fk_municipio = m.id),
  longitud = (SELECT round(avg(longitud),4) FROM geo_normalizada.localidades l WHERE l.fk_municipio = m.id),
  latitud = (SELECT round(avg(latitud),4) FROM geo_normalizada.localidades l WHERE l.fk_municipio = m.id);
  
UPDATE examen.municipios m
SET m.habitantes = (SELECT n.hab FROM geo_normalizada.municipios_hab n 
WHERE n.nombre = m.nombre)

UPDATE clientes c
SET c.fk_localidad = (SELECT cp.fk_localidad FROM cp_localidad cp WHERE cp.fk_cp=c.fk_cp LIMIT 1)

[plegar]

Preguntas de examen:

Listado de todos los clientes validados que no tienen asociado ningún anuncio a la publicidad de sus campañas:

SELECT * FROM clientes
WHERE id
NOT IN (SELECT DISTINCT C.fk_cliente
FROM anuncios A inner join publicidad P 
ON A.fk_publicidad = P.id INNER JOIN campanas C
ON P.fk_campana = C.id) AND
validado = TRUE;

[plegar]

Suma todas las facturas del cliente 69 por cada ejercicio

SELECT C.nombre, YEAR(F.fecha) AS ejer, 
  SUM(L.precio)
FROM clientes C join facturas F 
ON C.id = F.fk_cliente JOIN lineas_fac L
ON F.id = L.fk_factura
WHERE C.id=69
GROUP BY C.nombre, ejer;

[plegar]

Facturación de la empresa por ejercicios

SELECT YEAR(F.fecha) AS ejer, 
  SUM(L.precio) total
FROM facturas F JOIN lineas_fac L
ON F.id = L.fk_factura
GROUP BY ejer;

[plegar]

Listado con los mejores clientes:

SELECT id FROM
(SELECT C.id miid, SUM(L.precio) AS tot
FROM clientes C  join facturas F 
ON C.id = F.fk_cliente JOIN lineas_fac L
ON F.id = L.fk_factura
GROUP BY 1
HAVING SUM(L.precio)= 1500) AS t;

[plegar]

-- suma la facturación del comercial que más vendió
-- en la empresa sin contar los clientes de la 
-- Comunidad de Madrid (Región like "%Madrid%").
SELECT SUM(lf.precio) total, c.fk_comercial
FROM lineas_fac lf JOIN facturas f
ON lf.fk_factura = f.id RIGHT 
JOIN clientes_no_madrid c
ON f.fk_cliente = c.id
GROUP BY c.fk_comercial
ORDER BY total desc
/*DROP VIEW clientes_no_madrid;
CREATE VIEW clientes_no_madrid as
SELECT DISTINCT cl2.id, cl2.fk_comercial
FROM clientes cl2 JOIN localidades l
ON cl2.fk_localidad = l.id JOIN municipios m
ON l.fk_municipio = m.id JOIN provincias p
ON m.fk_provincia = p.id JOIN regiones r
ON p.fk_region = r.id
WHERE r.nombre NOT LIKE "%madrid%"*/

todo seguido …

SELECT SUM(lf.precio) total, c.fk_comercial
FROM lineas_fac lf JOIN facturas f
ON lf.fk_factura = f.id RIGHT JOIN clientes c
ON c.id = f.fk_cliente  JOIN localidades l
ON c.fk_localidad = l.id JOIN municipios m
ON l.fk_municipio = m.id JOIN provincias p
ON m.fk_provincia = p.id 
JOIN regiones r ON p.fk_region = r.id
WHERE r.nombre NOT like "%madrid%"
GROUP BY c.fk_comercial
ORDER BY total DESC;