¶ BBDD Marketing
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);
¶ Ejemplos preguntas
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;
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;
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;
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;
-- 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%"*/
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;
DROP SCHEMA IF EXISTS examen;
CREATE SCHEMA IF NOT EXISTS examen ;
-- -----------------------------------------------------
-- Table examen.cnae
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.cnae;
CREATE TABLE IF NOT EXISTS examen.cnae (
id BIGSERIAL,
codigo VARCHAR(45) NULL,
descripcion VARCHAR(2000) NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.users
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.users;
CREATE TABLE IF NOT EXISTS examen.users (
id BIGSERIAL,
username VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
intentos INT NULL,
roladmin INT NOT NULL,
password VARCHAR(200) NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.tipo_portal
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.tipo_portal;
CREATE TABLE IF NOT EXISTS examen.tipo_portal (
id BIGSERIAL,
nombre VARCHAR(45) NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.tarifas
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.tarifas;
CREATE TABLE IF NOT EXISTS examen.tarifas (
id BIGSERIAL,
nombre VARCHAR(45) NULL,
coeficiente_maximo FLOAT NULL,
coeficiente_minimo FLOAT NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.portales
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.portales;
CREATE TABLE IF NOT EXISTS examen.portales (
id BIGSERIAL,
dominio VARCHAR(100) NOT NULL,
titulo VARCHAR(150) NULL DEFAULT NULL,
descripcion VARCHAR(500) NULL DEFAULT NULL,
visitas INT NULL DEFAULT NULL,
coeficiente FLOAT NULL,
fk_tipo INT NOT NULL,
fk_tarifa INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_portales_tipo_portal1
FOREIGN KEY (fk_tipo)
REFERENCES examen.tipo_portal (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_portales_tarifas1
FOREIGN KEY (fk_tarifa)
REFERENCES examen.tarifas (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.comarcas
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.comarcas;
CREATE TABLE IF NOT EXISTS examen.comarcas (
id BIGSERIAL,
nombre VARCHAR(100) NOT NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.regiones
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.regiones;
CREATE TABLE IF NOT EXISTS examen.regiones (
id BIGSERIAL,
nombre VARCHAR(100) NOT NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.provincias
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.provincias;
CREATE TABLE IF NOT EXISTS examen.provincias (
id BIGSERIAL,
nombre VARCHAR(100) NOT NULL,
fk_region INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_provincias_regiones1
FOREIGN KEY (fk_region)
REFERENCES examen.regiones (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.prov_comarca
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.prov_comarca;
CREATE TABLE IF NOT EXISTS examen.prov_comarca (
fk_comarca INT NOT NULL,
fk_provincia INT NOT NULL,
PRIMARY KEY (fk_comarca, fk_provincia),
CONSTRAINT fk_comarcas_has_provincias_comarcas1
FOREIGN KEY (fk_comarca)
REFERENCES examen.comarcas (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_comarcas_has_provincias_provincias1
FOREIGN KEY (fk_provincia)
REFERENCES examen.provincias (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.municipios
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.municipios;
CREATE TABLE IF NOT EXISTS examen.municipios (
id BIGSERIAL,
nombre VARCHAR(100) NULL,
latitud FLOAT NULL,
longitud FLOAT NULL,
habitantes INT NULL,
fk_portal INT NOT NULL,
fk_comarca INT NOT NULL,
fk_provincia INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_municipios_portales1
FOREIGN KEY (fk_portal)
REFERENCES examen.portales (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_municipios_prov_comarcas1
FOREIGN KEY (fk_comarca , fk_provincia)
REFERENCES examen.prov_comarca (fk_comarca , fk_provincia)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.localidades
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.localidades;
CREATE TABLE IF NOT EXISTS examen.localidades (
id BIGSERIAL,
nombre VARCHAR(100) NOT NULL,
fk_municipio INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_localidades_municipios1
FOREIGN KEY (fk_municipio)
REFERENCES examen.municipios (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.cpostales
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.cpostales;
CREATE TABLE IF NOT EXISTS examen.cpostales (
codigo VARCHAR(5) NOT NULL,
PRIMARY KEY (codigo));
-- -----------------------------------------------------
-- Table examen.cp_localidad
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.cp_localidad;
CREATE TABLE IF NOT EXISTS examen.cp_localidad (
fk_localidad INT NOT NULL,
fk_cp VARCHAR(5) NOT NULL,
PRIMARY KEY (fk_localidad, fk_cp),
CONSTRAINT fk_localidades_has_cpostales_localidades1
FOREIGN KEY (fk_localidad)
REFERENCES examen.localidades (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_localidades_has_cpostales_cpostales1
FOREIGN KEY (fk_cp)
REFERENCES examen.cpostales (codigo)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.estados
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.estados;
CREATE TABLE IF NOT EXISTS examen.estados (
id BIGSERIAL,
estado VARCHAR(45) NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.comerciales
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.comerciales;
CREATE TABLE IF NOT EXISTS examen.comerciales (
id BIGSERIAL,
nombre VARCHAR(45) NULL,
movil VARCHAR(45) NULL,
email VARCHAR(45) NULL,
username VARCHAR(45) NULL,
password VARCHAR(200) NULL,
fk_comercial INT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_comerciales_comerciales1
FOREIGN KEY (fk_comercial)
REFERENCES examen.comerciales (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.clientes
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.clientes;
CREATE TABLE IF NOT EXISTS examen.clientes (
id BIGSERIAL,
cif VARCHAR(10) NULL,
nombre VARCHAR(150) NULL,
acronimo VARCHAR(100) NULL,
direccion VARCHAR(150) NULL,
web VARCHAR(150) NULL,
email VARCHAR(150) NULL,
telefono VARCHAR(45) NULL,
fax VARCHAR(45) NULL,
latitud FLOAT NULL,
longitud FLOAT NULL,
validado INT NULL,
descripcion TEXT NULL,
fk_cnae INT NOT NULL,
fk_user INT NOT NULL,
fk_localidad INT NULL,
fk_cp VARCHAR(5) NULL,
fk_estado INT NOT NULL,
fk_comercial INT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_negocios_cnae1
FOREIGN KEY (fk_cnae)
REFERENCES examen.cnae (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_clientes_users1
FOREIGN KEY (fk_user)
REFERENCES examen.users (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_clientes_cp_localidades1
FOREIGN KEY (fk_localidad , fk_cp)
REFERENCES examen.cp_localidad (fk_localidad , fk_cp)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_clientes_estados1
FOREIGN KEY (fk_estado)
REFERENCES examen.estados (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_clientes_comercial1
FOREIGN KEY (fk_comercial)
REFERENCES examen.comerciales (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.campanas
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.campanas;
CREATE TABLE IF NOT EXISTS examen.campanas (
id BIGSERIAL,
nombre VARCHAR(45) NULL DEFAULT NULL,
link VARCHAR(150) NULL DEFAULT NULL,
fk_cliente INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_campanas_clientes1
FOREIGN KEY (fk_cliente)
REFERENCES examen.clientes (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.tipos_anuncios
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.tipos_anuncios;
CREATE TABLE IF NOT EXISTS examen.tipos_anuncios (
id BIGSERIAL,
codigo VARCHAR(1) NOT NULL,
ancho INT NOT NULL,
alto INT NOT NULL,
precio_base FLOAT NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.publicidad
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.publicidad;
CREATE TABLE IF NOT EXISTS examen.publicidad (
id BIGSERIAL,
url_imagen VARCHAR(500) NOT NULL,
fk_campana INT NOT NULL,
ppc INT NULL DEFAULT NULL,
ppm INT NULL DEFAULT NULL,
fk_tipo INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_publicidad_campanas1
FOREIGN KEY (fk_campana)
REFERENCES examen.campanas (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_publicidad_tipos_anuncios1
FOREIGN KEY (fk_tipo)
REFERENCES examen.tipos_anuncios (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.anuncios
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.anuncios;
CREATE TABLE IF NOT EXISTS examen.anuncios (
id BIGSERIAL,
fk_publicidad INT NOT NULL,
fk_tipo INT NOT NULL,
fk_portal INT NOT NULL,
posicion INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_anuncios_publicidad1
FOREIGN KEY (fk_publicidad)
REFERENCES examen.publicidad (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_tipos_anuncios_has_portales_portales1
FOREIGN KEY (fk_portal)
REFERENCES examen.portales (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_tipos_anuncios_has_portales_tipos_anuncios
FOREIGN KEY (fk_tipo)
REFERENCES examen.tipos_anuncios (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.visitas
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.visitas;
CREATE TABLE IF NOT EXISTS examen.visitas (
id BIGSERIAL,
fecha DATE NOT NULL,
visitas INT NULL DEFAULT NULL,
fk_portal INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_vistas_portales1
FOREIGN KEY (fk_portal)
REFERENCES examen.portales (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.facturas
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.facturas;
CREATE TABLE IF NOT EXISTS examen.facturas (
id BIGSERIAL,
fecha DATE NULL,
fk_cliente INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_facturas_clientes1
FOREIGN KEY (fk_cliente)
REFERENCES examen.clientes (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.lineas_fac
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.lineas_fac;
CREATE TABLE IF NOT EXISTS examen.lineas_fac (
id BIGSERIAL,
fecha_inicio DATE NULL,
fecha_fin DATE NULL,
fk_factura INT NOT NULL,
fk_campana INT NOT NULL,
precio FLOAT NULL,
fk_anuncio INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_lineas_fac_facturas1
FOREIGN KEY (fk_factura)
REFERENCES examen.facturas (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_lineas_fac_campanas1
FOREIGN KEY (fk_campana)
REFERENCES examen.campanas (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_lineas_fac_anuncios1
FOREIGN KEY (fk_anuncio)
REFERENCES examen.anuncios (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.sectores
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.sectores;
CREATE TABLE IF NOT EXISTS examen.sectores (
id BIGSERIAL,
nombre VARCHAR(45) NULL,
fk_cnae INT NOT NULL,
fk_portal INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_sector_cnae1
FOREIGN KEY (fk_cnae)
REFERENCES examen.cnae (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_sector_portales1
FOREIGN KEY (fk_portal)
REFERENCES examen.portales (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.cliente_sector
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.cliente_sector;
CREATE TABLE IF NOT EXISTS examen.cliente_sector (
fk_sector INT NOT NULL,
fk_cliente INT NOT NULL,
PRIMARY KEY (fk_sector, fk_cliente),
CONSTRAINT fk_sector_has_clientes_sector1
FOREIGN KEY (fk_sector)
REFERENCES examen.sectores (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_sector_has_clientes_clientes1
FOREIGN KEY (fk_cliente)
REFERENCES examen.clientes (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.log_users
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.log_users;
CREATE TABLE IF NOT EXISTS examen.log_users (
id BIGSERIAL,
fecha_hora interval NULL,
ip VARCHAR(45) NULL,
fk_user INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_log_user_users1
FOREIGN KEY (fk_user)
REFERENCES examen.users (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.cargos
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.cargos;
CREATE TABLE IF NOT EXISTS examen.cargos (
id BIGSERIAL,
cargo VARCHAR(100) NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table examen.contactos
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.contactos;
CREATE TABLE IF NOT EXISTS examen.contactos (
id BIGSERIAL,
nombre VARCHAR(45) NULL,
telefono VARCHAR(45) NULL,
email VARCHAR(45) NULL,
fk_cliente INT NOT NULL,
fk_cargo INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_contactos_clientes1
FOREIGN KEY (fk_cliente)
REFERENCES examen.clientes (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_contactos_cargos1
FOREIGN KEY (fk_cargo)
REFERENCES examen.cargos (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.crm
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.crm;
CREATE TABLE IF NOT EXISTS examen.crm (
fecha interval NOT NULL,
fk_comercial INT NOT NULL,
fk_cliente INT NOT NULL,
descripcion TEXT NULL,
PRIMARY KEY (fecha, fk_comercial, fk_cliente),
CONSTRAINT fk_crm_comercial1
FOREIGN KEY (fk_comercial)
REFERENCES examen.comerciales (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_crm_clientes1
FOREIGN KEY (fk_cliente)
REFERENCES examen.clientes (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table examen.log
-- -----------------------------------------------------
DROP TABLE IF EXISTS examen.log;
CREATE TABLE IF NOT EXISTS examen.log (
id BIGSERIAL,
tabla VARCHAR(45) NULL,
accion VARCHAR(45) NULL,
usuario VARCHAR(45) NULL,
fechahora interval NULL,
descripcion VARCHAR(2000) NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Data for table examen.users
-- -----------------------------------------------------
INSERT INTO examen.users (id, username, email, intentos, roladmin, password) VALUES (DEFAULT, 'admin', 'info@localhost.com', 0, 1, NULL);
INSERT INTO examen.users (id, username, email, intentos, roladmin, password) VALUES (DEFAULT, 'user', 'user@localhost.com', 0, 0, NULL);
-- -----------------------------------------------------
-- Data for table examen.tipo_portal
-- -----------------------------------------------------
INSERT INTO examen.tipo_portal (id, nombre) VALUES (DEFAULT, 'sectorial');
INSERT INTO examen.tipo_portal (id, nombre) VALUES (DEFAULT, 'local');
-- -----------------------------------------------------
-- Data for table examen.tarifas
-- -----------------------------------------------------
INSERT INTO examen.tarifas (id, nombre, coeficiente_maximo, coeficiente_minimo) VALUES (DEFAULT, 'económico', 1, 0.1);
INSERT INTO examen.tarifas (id, nombre, coeficiente_maximo, coeficiente_minimo) VALUES (DEFAULT, 'moderado', 2, 0.2);
INSERT INTO examen.tarifas (id, nombre, coeficiente_maximo, coeficiente_minimo) VALUES (DEFAULT, 'caro', 4, 0.5);
INSERT INTO examen.tarifas (id, nombre, coeficiente_maximo, coeficiente_minimo) VALUES (DEFAULT, 'supercaro', 8, 0.8);
-- -----------------------------------------------------
-- Data for table examen.tipos_anuncios
-- -----------------------------------------------------
INSERT INTO examen.tipos_anuncios (id, codigo, ancho, alto, precio_base) VALUES (DEFAULT, 'S', 1920, 500, 200);
INSERT INTO examen.tipos_anuncios (id, codigo, ancho, alto, precio_base) VALUES (DEFAULT, 'L', 160, 600, 150);
INSERT INTO examen.tipos_anuncios (id, codigo, ancho, alto, precio_base) VALUES (DEFAULT, 'F', 300, 250, 100);
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","juan@marketing.com","juan",MD5("juan"),null),
(DEFAULT, "Perico","666 655 644","perico@marketing.com","perico",MD5("perico"),1),
(DEFAULT, "Andrés","633 622 611","andres@marketing.com","andres",MD5("andres"),1),
(DEFAULT, "Amparito","622 655 699","amparito@marketing.com","amparito",MD5("amparito"),2),
(DEFAULT, "Gertrudis","600 600 600","ger@marketing.com","ger",MD5("ger"),3),
(DEFAULT, "Genoveva","643 678 699","genoveva@marketing.com","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)