Ejercicios propuestos : Agrupar registros (group by)(Oracle)

Primer problema:

Un comercio que tiene un stand en una feria registra en una tabla llamada "visitantes" algunos datos de las personas que visitan o compran en su stand para luego enviarle publicidad de sus productos.

1- Elimine la tabla "visitantes" y créela con la siguiente estructura:

 drop table visitantes;
 create table visitantes(
  nombre varchar2(30),
  edad number(2),
  sexo char(1) default 'f',
  domicilio varchar2(30),
  ciudad varchar2(20) default 'Cordoba',
  telefono varchar2(11),
  mail varchar2(30) default 'no tiene',
  montocompra number(6,2)
 );

3- Ingrese algunos registros:

 insert into visitantes
  values ('Susana Molina',35,default,'Colon 123',default,null,null,59.80);
 insert into visitantes
  values ('Marcos Torres',29,'m',default,'Carlos Paz',default,'marcostorres@hotmail.com',150.50);
 insert into visitantes
  values ('Mariana Juarez',45,default,default,'Carlos Paz',null,default,23.90);
 insert into visitantes (nombre, edad,sexo,telefono, mail)
  values ('Fabian Perez',36,'m','4556677','fabianperez@xaxamail.com');
 insert into visitantes (nombre, ciudad, montocompra)
  values ('Alejandra Gonzalez','La Falda',280.50);
 insert into visitantes (nombre, edad,sexo, ciudad, mail,montocompra)
  values ('Gaston Perez',29,'m','Carlos Paz','gastonperez1@gmail.com',95.40);
 insert into visitantes
  values ('Liliana Torres',40,default,'Sarmiento 876',default,default,default,85);
 insert into visitantes
  values ('Gabriela Duarte',21,null,null,'Rio Tercero',default,'gabrielaltorres@hotmail.com',321.50);

4- Queremos saber la cantidad de visitantes de cada ciudad utilizando la cláusula "group by" (4 filas devueltas)

5- Queremos la cantidad visitantes con teléfono no nulo, de cada ciudad (4 filas devueltas)

6- Necesitamos el total del monto de las compras agrupadas por sexo (3 filas)
Note que los registros con valor nulo en el campo "sexo" se procesan como un grupo diferente.

7- Se necesita saber el máximo y mínimo valor de compra agrupados por sexo y ciudad (6 filas)

8- Calcule el promedio del valor de compra agrupados por ciudad (4 filas)

9- Cuente y agrupe por ciudad sin tener en cuenta los visitantes que no tienen mail (3 filas)

Ver solución

 drop table visitantes;

 create table visitantes(
  nombre varchar2(30),
  edad number(2),
  sexo char(1) default 'f',
  domicilio varchar2(30),
  ciudad varchar2(20) default 'Cordoba',
  telefono varchar2(11),
  mail varchar2(30) default 'no tiene',
  montocompra number(6,2)
 );

 insert into visitantes
  values ('Susana Molina',35,default,'Colon 123',default,null,null,59.80);
 insert into visitantes
  values ('Marcos Torres',29,'m',default,'Carlos Paz',default,'marcostorres@hotmail.com',150.50);
 insert into visitantes
  values ('Mariana Juarez',45,default,default,'Carlos Paz',null,default,23.90);
 insert into visitantes (nombre, edad,sexo,telefono, mail)
  values ('Fabian Perez',36,'m','4556677','fabianperez@xaxamail.com');
 insert into visitantes (nombre, ciudad, montocompra)
  values ('Alejandra Gonzalez','La Falda',280.50);
 insert into visitantes (nombre, edad,sexo, ciudad, mail,montocompra)
  values ('Gaston Perez',29,'m','Carlos Paz','gastonperez1@gmail.com',95.40);
 insert into visitantes
  values ('Liliana Torres',40,default,'Sarmiento 876',default,default,default,85);
 insert into visitantes
  values ('Gabriela Duarte',21,null,null,'Rio Tercero',default,'gabrielaltorres@hotmail.com',321.50);

 select ciudad, count(*)
  from visitantes
  group by ciudad;

 select ciudad, count(telefono)
  from visitantes
  group by ciudad;

 select sexo, sum(montocompra)
  from visitantes
  group by sexo;

 select sexo,ciudad,
  max(montocompra) as mayor,
  min(montocompra) as menor
  from visitantes
  group by sexo,ciudad;

 select ciudad,
  avg(montocompra) as "promedio de compras"
  from visitantes
  group by ciudad;

 select ciudad,
  count(*) as "cantidad con mail"
  from visitantes
  where mail is not null and
  mail<>'no tiene'
  group by ciudad;

Segundo problema:

Una empresa almacena los datos de sus empleados en una tabla "empleados".

1- Elimine la tabla y luego créela con la estructura definida a continuación:

 drop table empleados;
 create table empleados(
  nombre varchar2(30),
  documento char(8),
  domicilio varchar2(30),
  seccion varchar2(20),
  sueldo number(6,2),
  cantidadhijos number(2),
  fechaingreso date,
  primary key(documento)
 );

3- Ingrese algunos registros:

 insert into empleados
  values('Juan Perez','22333444','Colon 123','Gerencia',5000,2,'10/05/1980');
 insert into empleados
  values('Ana Acosta','23444555','Caseros 987','Secretaria',2000,0,'12/10/1980');
 insert into empleados
  values('Lucas Duarte','25666777','Sucre 235','Sistemas',4000,1,'25/05/1985');
 insert into empleados
  values('Pamela Gonzalez','26777888','Sarmiento 873','Secretaria',2200,3,'25/06/1990');
 insert into empleados
  values('Marcos Juarez','30000111','Rivadavia 801','Contaduria',3000,0,'01/05/1996');
 insert into empleados
  values('Yolanda Perez','35111222','Colon 180','Administracion',3200,1,'01/05/1996');
 insert into empleados
  values('Rodolfo Perez','35555888','Coronel Olmedo 588','Sistemas',4000,3,'01/05/1996');
 insert into empleados
  values('Martina Rodriguez','30141414','Sarmiento 1234','Administracion',3800,4,'01/09/2000');
 insert into empleados
  values('Andres Costa','28444555',default,'Secretaria',null,null,null);

4- Cuente la cantidad de empleados agrupados por sección (5 filas)

5- Calcule el promedio de hijos por sección (5 filas)

6- Cuente la cantidad de empleados agrupados por año de ingreso (6 filas)

7- Calcule el promedio de sueldo por sección de los empleados con hijos (4 filas)

Ver solución

 drop table empleados;
 create table empleados(
  nombre varchar2(30),
  documento char(8),
  domicilio varchar2(30),
  seccion varchar2(20),
  sueldo number(6,2),
  cantidadhijos number(2),
  fechaingreso date,
  primary key(documento)
 );

 insert into empleados
  values('Juan Perez','22333444','Colon 123','Gerencia',5000,2,'10/05/1980');
 insert into empleados
  values('Ana Acosta','23444555','Caseros 987','Secretaria',2000,0,'12/10/1980');
 insert into empleados
  values('Lucas Duarte','25666777','Sucre 235','Sistemas',4000,1,'25/05/1985');
 insert into empleados
  values('Pamela Gonzalez','26777888','Sarmiento 873','Secretaria',2200,3,'25/06/1990');
 insert into empleados
  values('Marcos Juarez','30000111','Rivadavia 801','Contaduria',3000,0,'01/05/1996');
 insert into empleados
  values('Yolanda Perez','35111222','Colon 180','Administracion',3200,1,'01/05/1996');
 insert into empleados
  values('Rodolfo Perez','35555888','Coronel Olmedo 588','Sistemas',4000,3,'01/05/1996');
 insert into empleados
  values('Martina Rodriguez','30141414','Sarmiento 1234','Administracion',3800,4,'01/09/2000');
 insert into empleados
  values('Andres Costa','28444555',default,'Secretaria',null,null,null);

 select seccion, count(*)
  from empleados
  group by seccion;

  select seccion, avg(cantidadhijos) as "promedio de hijos"
   from empleados
   group by seccion;

 select datepart(year,fechaingreso) as ingreso, count(*)
  from empleados
  group by datepart(year,fechaingreso);

 select seccion, avg(sueldo) as "promedio de sueldo"
  from empleados
  where cantidadhijos>0 and
  cantidadhijos is not null
  group by seccion;

31 - Agrupar registros (group by)

Retornar