1.- Teniendo en cuenta que la publicidad con id = 1, 2 ó 3 es una publicidad genérica indica cuantos anuncios NO son de publicidad genérica
Sol: 2.188
SELECT COUNT(*) FROM anuncios
WHERE fk_publicidad > 3
2.- Indica cual es el importe de la mayor factura y luego indica cuantas facturas llegan a ese importe. Sol: 600 y 5
SET @maximo:=(SELECT SUM(precio)
FROM lineas_fac GROUP BY fk_factura
ORDER BY 1 DESC LIMIT 1);
SELECT COUNT(*) FROM
(SELECT SUM(precio) n
FROM lineas_fac
GROUP BY fk_factura
HAVING SUM(precio)=@maximo) t
3.- Cuenta cuantos portales hay de cada tipo
SELECT COUNT(*), t.nombre
FROM portales p, tipo_portal t
WHERE p.fk_tipo = t.id
GROUP BY fk_tipo
4.- Indica cuantos clientes hay por cada sector muestra también el nombre del sector
SELECT nombre,
COUNT(fk_cliente) as num_clientes
FROM cliente_sector cs,
sectores s
WHERE s.id= cs.fk_sector
GROUP BY fk_sector
ORDER BY s.id
5.- Cuenta cuantos clientes hay por cada municipio
SELECT
m.nombre,
COUNT(c.id) n_clientes
FROM clientes c, localidades l,
municipios m
WHERE c.fk_localidad = l.id
AND m.id = l.fk_municipio
GROUP BY m.id ORDER BY 1;
6.- Cuenta cuantos clientes de las REGIONES de Galicia y Andalucía hay por CNAE, mostrando el código y la descripción del CNAE
SELECT REGI, cnae.codigo, cnae.descripcion,
COUNT(cli.id) as clientes
FROM (SELECT CL.id, CL.FK_CNAE,
RE.NOMBRE AS REGI
FROM clientes cl, localidades lo,
municipios mu, provincias pr, regiones re
WHERE cl.fk_localidad = lo.id AND
lo.fk_municipio = mu.id AND
mu.fk_provincia = pr.id AND
pr.fk_region = re.id AND
re.nombre IN("ANDALUCIA","GALICIA")) cli, cnae
WHERE cli.fk_cnae = cnae.id
GROUP BY REGI, cnae.codigo , cnae.descripcion
ORDER BY 2
/* clientes de las REGIONES de Galicia y Andalucía */
DROP VIEW if exists clientes_galicia_andalucia;
CREATE VIEW CLIENTES_GALICIA_ANDALUCIA AS
SELECT c.*, r.id fk_region FROM clientes c,
localidades l, municipios m, provincias p, regiones r
WHERE c.fk_localidad = l.id AND l.fk_municipio =
m.id AND m.fk_provincia = p.id AND p.fk_region =
r.id AND r.nombre IN ("GALICIA","ANDALUCIA");
-- AND (R.NOMBRE = "GALICIA" OR R.NOMBRE = "ANDALUCIA")
/* Cuenta cuantos clientes de las REGIONES de Galicia y Andalucía hay por CNAE, mostrando el código y la descripción del CNAE */
SELECT COUNT(c.id), cn.descripcion, cn.codigo
FROM clientes_galicia_andalucia c,
cnae cn
WHERE c.fk_cnae = cn.id
GROUP BY c.fk_cnae
7.- consulta que muestre las ventas mensuales, ventas trimestrales y ventas anuales de la empresa ordenados fecha de más próxima a más lejana
SELECT YEAR(fecha),
SUM(precio) FROM lineas_fac lf,
facturas f WHERE lf.fk_factura =
f.id GROUP BY 1
UNION
SELECT concat(YEAR(fecha),"-",
quarter(fecha)),
SUM(precio) FROM lineas_fac lf,
facturas f WHERE lf.fk_factura =
f.id GROUP BY 1
union
SELECT concat(YEAR(fecha),"-",
quarter(fecha),"-",month(fecha)),
SUM(precio) FROM lineas_fac lf,
facturas f WHERE lf.fk_factura =
f.id GROUP BY 1
ORDER BY 1 desc
SELECT YEAR(fecha),
QUARTER(fecha),
MONTH(fecha),
SUM(precio)
FROM lineas_fac lf, facturas f
WHERE lf.fk_factura = f.id
GROUP BY 1, 2, 3 WITH ROLLUP
order BY 1 DESC, 2 DESC, 3 desc
/* Crea una consulta que calcule en tanto por cien (%) el número de localidades que tiene cada provincia respeto a su región y también respeto a España. */
– 1.- calcular el número de localidades de Spain
– 2.- calcular y guardar en una vista el número de localidades de cada provincia
DROP VIEW if exists provincias_loc;
CREATE VIEW provincias_loc as
select m.fk_provincia AS id, p.nombre,
COUNT(l.id) AS num_localidades, p.fk_region
FROM
localidades l, municipios m, provincias p
WHERE
l.fk_municipio = m.id and
p.id = m.fk_provincia
GROUP BY m.fk_provincia;
DROP VIEW if exists regiones_loc;
CREATE VIEW regiones_loc as
SELECT r.id AS id, r.nombre,
COUNT(l.id) AS num_localidades
FROM
localidades l, municipios m, provincias p,
regiones r
WHERE
l.fk_municipio = m.id and
p.id = m.fk_provincia and
p.fk_region = r.id
GROUP BY r.id;
SET @espana:=(SELECT COUNT(*) FROM localidades);
SELECT pl.nombre,concat(PL.num_localidades*100/@espana,"%") AS por,
concat(PL.num_localidades*100/RL.num_localidades,"%") AS por2
FROM provincias_loc PL, regiones_loc RL
WHERE PL.fk_region = RL.id
SET @spain:= (SELECT COUNT(id) FROM localidades);
DROP VIEW if EXISTS provincia_loc ;
CREATE VIEW provincia_loc AS
SELECT
p.id, p.nombre, p.fk_region,
COUNT(l.id) AS num_loc
FROM
provincias p, municipios m, localidades l
WHERE p.id = m.fk_provincia AND
m.id = l.fk_municipio
GROUP BY p.id;
DROP VIEW if EXISTS regiones_loc;
CREATE VIEW regiones_loc AS
SELECT
r.id, r.nombre, COUNT(l.id) AS num_loc
FROM
regiones r, provincias p,
municipios m, localidades l
WHERE r.id = p.fk_region and
p.id = m.fk_provincia AND
m.id = l.fk_municipio
GROUP BY r.id;
SET @spain:= (SELECT COUNT(id) FROM localidades);
DROP VIEW if EXISTS tantos_por_cien;
CREATE VIEW tantos_por_cien as
SELECT p.id, p.nombre, r.id AS id_reg,
r.nombre nombre_reg,
concat(p.num_loc*100/(SELECT COUNT(id) FROM localidades)," %") por_spain,
concat(p.num_loc*100/r.num_loc," %") por_reg
FROM
provincia_loc p, regiones_loc r
WHERE
p.fk_region = r.id
SELECT tc.nombre_reg, round(SUM(por_reg),3)
FROM tantos_por_cien tc
GROUP BY tc.id_reg