Problema:
Un club de dicta clases de distintos deportes a sus socios. Guarda la información de sus socios en
una tabla llamada "socios", la de los deportes que dicta en "deportes" y las inscripciones
en "inscriptos".
1- Elimine las 3 tablas, si existen.
2- Cree las tablas:
create table socios(
documento char(8),
nombre varchar(30),
domicilio varchar(30),
primary key(documento)
);
create table deportes(
codigo tinyint unsigned auto_increment,
nombre varchar(15),
profesor varchar(30),
primary key (codigo)
);
create table inscriptos(
documento char(8) not null,
codigodeporte varchar(20) not null,
año year not null,
cuota char(1), /*'s' o 'n', si esta paga o no*/
primary key(documento,codigodeporte,año)
);
3- Ingrese los siguientes registros para las tablas "socios", "deportes" e "inscriptos":
insert into socios values('22333444','Juan Perez','Colon 123');
insert into socios values('23333444','Ana Lopez','Caseros 984');
insert into socios values('24333444','Marcelo Morales','Sucre 356');
insert into socios values('25333444','Susana Juarez','Sarmiento 723');
insert into deportes (nombre,profesor) values('tenis','Tadeo Torres');
insert into deportes (nombre,profesor) values('natacion','Natalia Nores');
insert into deportes (nombre,profesor) values('basquet','Bautista Pereyra');
insert into deportes (nombre,profesor) values('paddle','Bautista Pereyra');
insert into inscriptos values('22333444',1,'2015','s');
insert into inscriptos values('22333444',1,'2016','n');
insert into inscriptos values('22333444',2,'2015','s');
insert into inscriptos values('23333444',1,'2015','s');
insert into inscriptos values('23333444',2,'2016','s');
insert into inscriptos values('23333444',1,'2016','s');
insert into inscriptos values('24333444',1,'2016','s');
insert into inscriptos values('24333444',3,'2016','n');
4- Se desea saber la cantidad de alumnos inscriptos en cada deporte cada año, sin considerar los
deportes para los cuales no hay inscriptos:
select d.nombre,i.año,count(i.codigodeporte) as cantidad
from deportes as d
join inscriptos as i
on d.codigo=i.codigodeporte
group by d.nombre,i.año;
5- El club quiere almacenar esa información en una tabla llamada "inscriptospordeporteporaño".
Elimine la tabla, si existe.
6- Cree la tabla con la siguiente estructura:
create table inscriptospordeporteporaño(
deporte varchar(15),
año year,
cantidad tinyint unsigned
);
7- Ingrese registros en la tabla creada anteriormente, a partir de la consulta del punto 4:
insert into inscriptospordeporteporaño
select d.nombre,i.año,count(i.codigodeporte)
from deportes as d
join inscriptos as i
on d.codigo=i.codigodeporte
group by d.nombre,i.año;
8- Muestre el nombre del socio, el nombre del deporte y el año de inscripción de todas las
inscripciones con cuota impaga:
select s.nombre as socio,d.nombre as deporte,i.año
from socios as s
join inscriptos as i
on s.documento=i.documento
join deportes as d
on i.codigodeporte=d.codigo
where i.cuota='n';
9- Elimine la tabla "sociosdeudores" si existe.
10- Cree la tabla:
create table sociosdeudores(
socio varchar(30),
deporte varchar(15),
año year
);
11- Intente ingresar en la tabla anteriormente creada los registros resultado de la consulta del
punto 8, colocando los campos "socio" y "deporte" en la lista de campos a cargar:
insert into sociosdeudores (socio,deporte)
select s.nombre,d.nombre,i.año
from socios as s
join inscriptos as i
on s.documento=i.documento
join deportes as d
on i.codigodeporte=d.codigo
where i.cuota='n';
La sentencia no se ejecuta porque los campos listados son 2 y la consulta retorna 3 columnas.
12- Ingrese en la tabla anteriormente creada los registros resultado de la consulta del punto 8,
correctamente:
insert into sociosdeudores
select s.nombre,d.nombre,i.año
from socios as s
join inscriptos as i
on s.documento=i.documento
join deportes as d
on i.codigodeporte=d.codigo
where i.cuota='n';
13- Vea los registros de "sociosdeudores".
14- Agrupe por nombre del profesor y deporte y cuente la cantidad de alumnos por profesor y
deporte, considerando los deportes para los que no haya inscriptos:
select d.profesor,d.nombre,count(i.codigodeporte) as alumnos
from deportes as d
left join inscriptos as i
on i.codigodeporte=d.codigo
group by d.profesor,d.nombre;
15- Elimine la tabla "alumnosporprofesor" si existe.
16- Cree la tabla "alumnosporprofesor":
create table alumnosporprofesor(
profesor varchar(30),
deporte varchar(15),
cantidad tinyint unsigned
);
17- Ingrese en la tabla creada en el punto anterior con el resultado de la consulta del punto 14:
insert into alumnosporprofesor
select d.profesor,d.nombre,count(i.codigodeporte)
from deportes as d
left join inscriptos as i
on i.codigodeporte=d.codigo
group by d.profesor,d.nombre;
18- Muestre todos los registros de la nueva tabla.
Otros problemas:
A) Un profesor guarda en una tabla llamada "alumnos" los datos personales de sus alumnos y en otra
tabla llamada "notas" las calificaciones de los mismos.
1- Elimine las tablas si existen.
2- Cree las tablas con las siguientes estructuras:
create table alumnos(
documento char(8),
nombre varchar(30),
domicilio varchar(30)
);
create table notas(
documento char(8) not null,
nota decimal(4,2) unsigned
);
Algunos alumnos tienen más notas que otros porque presentaron trabajos especiales.
3- Ingrese los siguientes registros:
insert into alumnos (documento,nombre) values('22333444','Juan Perez');
insert into alumnos (documento,nombre) values('23333444','Marta Molina');
insert into alumnos (documento,nombre) values('24333444','Carlos Fuentes');
insert into alumnos (documento,nombre) values('25333444','Sandra Lopez');
insert into notas values('22333444',8);
insert into notas values('23333444',3);
insert into notas values('24333444',6);
insert into notas values('25333444',9);
insert into notas values('23333444',2);
insert into notas values('24333444',5);
insert into notas values('25333444',8);
insert into notas values('25333444',10);
4- El profesor necesita conocer el documento, el nombre del alumno y el promedio de sus notas:
select a.documento,nombre,avg(n.nota) as promedio
from alumnos as a
join notas as n
on a.documento = n.documento
group by a.documento;
5- Elimine la tabla "promedios" si existe.
6- Cree la tabla "promedios" con la siguiente estructura:
create table promedios(
documento char(8),
nombre varchar(30),
promedio decimal(4,2) unsigned
);
7- Ingrese los registros resultado de la consulta del punto 4 en la tabla "promedios":
insert into promedios
select a.documento,nombre,avg(n.nota)
from alumnos as a
join notas as n
on a.documento = n.documento
group by a.documento;
8- Muestre el documento, nombre y promedio de los alumnos aprobados:
select a.documento,nombre,avg(n.nota) as promedio
from alumnos as a
join notas as n
on a.documento = n.documento
group by a.documento
having promedio>=4;
9- Elimine la tabla "aprobados" si existe.
10- Cree la tabla "aprobados" con esta estructura:
create table aprobados(
documento char(8),
nombre varchar(30),
promedio decimal(4,2) unsigned
);
11- Ingrese los registros resultado de la consulta del punto 8 en la tabla creada en el punto
anterior:
insert into aprobados
select a.documento,nombre,avg(n.nota) as promedio
from alumnos as a
join notas as n
on a.documento = n.documento
group by a.documento
having promedio>=4;
12- Muestre los registros de "aprobados".
B) Un banco tiene registrados los datos de sus clientes en una tabla denominada "clientes" y las
cuentas corrientes de los mismos en una tabla llamada "cuentas".
1- Elimine las tablas, si existen.
2- Cree las tablas:
create table clientes(
documento char(8),
nombre varchar(30),
domicilio varchar(30),
primary key(documento)
);
create table cuentas(
numero int(8) zerofill auto_increment,
documento char(8) not null,
saldo decimal(9,2),
primary key(numero)
);
3- Ingrese los siguientes registros:
insert into clientes (documento,nombre) values ('22333444','Juan Perez');
insert into clientes (documento,nombre) values ('23333444','Maria Pereyra');
insert into clientes (documento,nombre) values ('24333444','Marcos Torres');
insert into clientes (documento,nombre) values ('25333444','Ana Juarez');
insert into cuentas values(1234,'22333444',2000.60);
insert into cuentas values(2566,'23333444',5050);
insert into cuentas values(5987,'24333444',200);
insert into cuentas values(14434,'25333444',8000.60);
insert into cuentas values(28566,'23333444',8050);
insert into cuentas values(35987,'24333444',2000);
insert into cuentas values(35997,'24333444',2000);
4- Agrupe por documento y muestre el documento del cliente, su nombre, en una columna
llamada "cantidad" calcule la cantidad de cuentas de cada cliente y en otra columna llamada "total"
calcule la suma de todas las cuentas de cada cliente:
select cl.documento,nombre,
count(*) as cantidad,
sum(saldo) as total
from cuentas as cu
join clientes as cl
on cl.documento=cu.documento
group by cl.documento;
5- Elimine la tabla "cuentasporcliente", si existe.
6- Cree la tabla "cuentasporcliente" con la siguiente estructura:
create table cuentasporcliente(
numero int unsigned auto_increment,
documento char(8),
nombre varchar(30),
cantidad tinyint unsigned,
total decimal(9,2) unsigned,
primary key(numero)
);
7- Inserte el resultado de la consulta del punto 4 en la tabla creada anteriormente:
insert into cuentasporcliente (documento,nombre,cantidad,total)
select cl.documento,nombre,
count(*) as cantidad,
sum(saldo)
from cuentas as cu
join clientes as cl
on cl.documento=cu.documento
group by cl.documento;
8- Vea los registros de "cuentasporcliente".