Show the names of clients from Spain that are over the mean age aprox 38.2

48 Views Asked by At

RDBMS enter image description here

I'm trying to gather the information that is required in this question, I have calculated clients age, here are the data for this table

CREATE TABLE `clientes` (
  `id_cliente` int(11) NOT NULL,
  `nombre` varchar(45) COLLATE utf8_bin NOT NULL,
  `apellidos` varchar(45) COLLATE utf8_bin NOT NULL,
  `dni_pasaporte` text COLLATE utf8_bin NOT NULL,
  `telefono` mediumtext COLLATE utf8_bin DEFAULT NULL,
  `sexo` mediumtext COLLATE utf8_bin NOT NULL,
  `fecha_nacimiento` date NOT NULL,
  `pais_nacimiento` varchar(45) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `clientes`
--

INSERT INTO `clientes` (`id_cliente`, `nombre`, `apellidos`, `dni_pasaporte`, `telefono`, `sexo`, `fecha_nacimiento`, `pais_nacimiento`) VALUES
(1, 'Carlos', 'Montero Suarez', '16066793V', '723510602', 'H', '1980-02-14', 'Spain'),
(2, 'Allison', 'Lukianov', '3258879821', '395404877', 'femenino', '1970-12-06', 'China'),
(3, 'Rey', 'Roja', '8298779244', '352682209', 'H', '1965-05-19', 'Spain'),
(4, 'Elton', 'Saintsbury', '446046705', '969547852', 'H', '1987-08-23', 'Tunisia'),
(5, 'Bert', 'Leckey', '732411565', '625573861', 'H', '1990-09-27', 'China'),
(6, 'Carmine', 'Eicke', '300115949', '261559877', 'femenino', '1985-08-03', 'China'),
(7, 'Maria ', 'Sanchez Cardoso', '14180343D', '441952642', 'femenino', '1979-05-21', 'Spain'),
(9, 'John ', 'Lennon', '236147952', '630745219', 'H', '1970-11-14', 'Tunisia'),
(10, 'Karla', 'Watson', '523617420', '923541078', 'femenino', '1980-05-12', 'Tunisia');

ALTER TABLE `clientes`
  ADD PRIMARY KEY (`id_cliente`),
  ADD UNIQUE KEY `Id_cliente_unique` (`id_cliente`) USING BTREE;

ALTER TABLE `clientes`
  MODIFY `id_cliente` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

enter image description here

What I need to calculate the average age between all 10 users on this table which is 38.2. And I need to have all spanish users that are above this age, can you let me know how to get this information?

I have tried the following entry and I'm just getting the age of all clients

SELECT *,
YEAR(CURDATE()) - YEAR(fecha_nacimiento) - (RIGHT(CURDATE(), 5) < RIGHT(fecha_nacimiento, 5)) Age
from clientes

I want to know is how to calculate the average of all 10 clients based on current day and date of birth.

SELECT *,
YEAR(CURDATE()) - YEAR(fecha_nacimiento) - (RIGHT(CURDATE(), 5) < RIGHT(fecha_nacimiento, 5)) Age
(select avg(all(year(now()) - year(fecha_nacimiento)))) as Average  
from clientes

but this only shows one client, how can I get the other two?

0

There are 0 best solutions below