Contactos

--1
SELECT CONCAT(nombre, ' ', apellidos) AS 'Nombre y Apellidos', cabello, ojos_color
FROM contactos
WHERE cabello='Castaño' AND ojos_color='Marrones'
--2
SELECT COUNT(*) AS 'Personas con ojos marrones y pelo castaño o rubio' FROM contactos
WHERE cabello='Castaño' AND ojos_color='Marrones' OR cabello='Rubio' AND ojos_color='Marrones'
--3
SELECT localidad AS 'Ciudad', cabello AS 'Color de pelo', COUNT(*) AS 'Cantidad de personas x color'
FROM contactos
GROUP BY cabello
ORDER BY localidad;
--4
SELECT localidad AS 'Ciudad', cabello AS 'Color de pelo', COUNT(*) AS 'Cantidad de chicas x color'
FROM contactos
WHERE sexo='F'
GROUP BY cabello
ORDER BY localidad;
--5
SELECT nombre, apellidos, localidad, dirección, teléfono
FROM contactos
WHERE nombre LIKE "%J%O%"
ORDER BY localidad, nombre;
--6
SELECT localidad AS 'Ciudad',
 COUNT(*) AS 'Cantidad de habitantes',
 avg(peso_kg) AS 'Peso promedio',
 avg(altura) AS 'Altura promedio',
 avg(hijos) AS 'Promedio de Hijos'
FROM contactos
GROUP BY localidad;
--7
SELECT nombre, apellidos FROM contactos
WHERE (sexo='F' AND cabello='Pelirrojo' AND altura>1.70) OR (sexo='M' AND cabello='Castaño' AND localidad='Madrid')
ORDER BY sexo;
--8.0
SELECT cabello AS 'Color de pelo', ojos_color AS 'Color de ojos', COUNT(nombre) AS 'Personas con estas caracteristicas', AVG(altura) AS 'Promedio de altura'
FROM contactos
GROUP BY cabello, ojos_color
--8.1
SELECT COUNT(*) AS 'Cantidad de personas', altura AS 'Altura'
FROM contactos
WHERE altura>1.70
GROUP BY altura
ORDER BY altura;
--8.2
SELECT cabello AS 'Color de pelo', ojos_color AS 'Color de ojos', COUNT(nombre) AS 'Personas con estas caracteristicas', AVG(altura) AS 'Promedio de altura'
FROM contactos
GROUP BY cabello, ojos_color
HAVING AVG(altura)>1.70
ORDER BY altura;