51 - Subconsulta simil autocombinación

Algunas sentencias en las cuales la consulta interna y la externa emplean la misma tabla pueden reemplazarse por una autocombinación.

Problema resuelto

Trabajamos con la tabla "libros".

La eliminamos si ya existe:

drop table if exists libros;

Creamos la tabla libros con la siguiente estructura:

create table libros(
  codigo integer primary key,
  titulo text,
  autor text,
  editorial text,
  precio real
);

Insertamos algunas filas:

 insert into libros(titulo,autor,editorial,precio)
  values('Alicia en el pais de las maravillas','Lewis Carroll','Emece',20.00);
 insert into libros(titulo,autor,editorial,precio)
  values('Alicia en el pais de las maravillas','Lewis Carroll','Plaza',35.00);
 insert into libros(titulo,autor,editorial,precio)
  values('Aprenda PHP','Mario Molina','Siglo XXI',40.00);
 insert into libros(titulo,autor,editorial,precio)
  values('El aleph','Borges','Emece',10.00);
 insert into libros(titulo,autor,editorial,precio)
  values('Ilusiones','Richard Bach','Planeta',15.00);
 insert into libros(titulo,autor,editorial,precio)
  values('Java en 10 minutos','Mario Molina','Siglo XXI',50.00);
 insert into libros(titulo,autor,editorial,precio)
  values('Martin Fierro','Jose Hernandez','Planeta',20.00);
 insert into libros(titulo,autor,editorial,precio)
 values('Martin Fierro','Jose Hernandez','Emece',30.00);
 insert into libros(titulo,autor,editorial,precio)
  values('Uno','Richard Bach','Planeta',10.00);

Obtenemos la lista de los libros que han sido publicados por distintas editoriales empleando una consulta correlacionada:

select distinct l1.titulo
  from libros as l1
  where l1.titulo in
  (select l2.titulo
    from libros as l2 
    where l1.editorial <> l2.editorial);

En el ejemplo anterior empleamos una subconsulta correlacionada y las consultas interna y externa emplean la misma tabla. La subconsulta devuelve una lista de valores por ello se emplea "in" y sustituye una expresión en una cláusula "where".

El siguiente "join" retorna el mismo resultado:

 select distinct l1.titulo
  from libros as l1
  join libros as l2
  on l1.titulo=l2.titulo
  where l1.editorial<>l2.editorial;

Buscamos todos los libros que tienen el mismo precio que "El aleph" empleando subconsulta:

 select titulo
  from libros
  where titulo<>'El aleph' and
  precio =
   (select precio
     from libros
     where titulo='El aleph');

Obtenemos la misma salida empleando "join":

 select l1.titulo
  from libros as l1
  join libros  as l2
  on l1.precio=l2.precio
  where l2.titulo='El aleph' and
  l1.titulo<>l2.titulo;

Buscamos los libros cuyo precio supera el precio promedio de los libros por editorial:

 select l1.titulo,l1.editorial,l1.precio
  from libros as l1
  where l1.precio >
   (select avg(l2.precio) 
   from libros as l2
    where l1.editorial= l2.editorial);

Obtenemos la misma salida pero empleando un "join" con "having":

 select l1.editorial,l1.titulo,l1.precio
  from libros as l1
  join libros as l2
   on l1.editorial=l2.editorial
   group by l1.editorial, l1.titulo, l1.precio
   having l1.precio > avg(l2.precio);

Problema propuesto

Un club dicta clases de distintos deportes a sus socios. El club tiene una tabla llamada "deportes" en la cual almacena el nombre del deporte, el nombre del profesor que lo dicta, el día de la semana que se dicta y el costo de la cuota mensual.

  1. Borrar la tabla "deportes" si existe.

  2. Crear la tabla "deportes" con la siguiente estructura:

     create table deportes(
  nombre text primary key,
  profesor text,
  dia text,
  cuota real
 );

  3. Almacenar algunos registros de prueba:

     insert into deportes values('tenis','Ana Lopez','lunes',20);
 insert into deportes values('natacion','Ana Lopez','martes',15);
 insert into deportes values('futbol','Carlos Fuentes','miercoles',10);
 insert into deportes values('basquet','Gaston Garcia','jueves',15);
 insert into deportes values('padle','Juan Huerta','lunes',15);
 insert into deportes values('handball','Juan Huerta','martes',10);

  4. Muestre los nombres de los profesores que dictan más de un deporte empleando subconsulta.

  5. Obtenga el mismo resultado empleando join.

  6. Buscamos todos los deportes que se dictan el mismo día que un determinado deporte (natacion) empleando subconsulta.

  7. Obtenga la misma salida empleando "join".

Solución 
drop table if exists deportes;

 create table deportes(
  nombre text primary key,
  profesor text,
  dia text,
  cuota real
 );
 
 insert into deportes values('tenis','Ana Lopez','lunes',20);
 insert into deportes values('natacion','Ana Lopez','martes',15);
 insert into deportes values('futbol','Carlos Fuentes','miercoles',10);
 insert into deportes values('basquet','Gaston Garcia','jueves',15);
 insert into deportes values('padle','Juan Huerta','lunes',15);
 insert into deportes values('handball','Juan Huerta','martes',10); 
 
 select distinct d1.profesor
  from deportes as d1
  where d1.profesor in
  (select d2.profesor
    from deportes as d2 
    where d1.nombre <> d2.nombre); 
	
 select distinct d1.profesor
  from deportes as d1
  join deportes as d2
  on d1.profesor=d2.profesor
  where d1.nombre<>d2.nombre;	
  
 select nombre
  from deportes
  where nombre<>'natacion' and
  dia =
   (select dia
     from deportes
     where nombre='natacion');  
	 
 select d1.nombre
  from deportes as d1
  join deportes as d2
  on d1.dia=d2.dia
  where d2.nombre='natacion' and
  d1.nombre<>d2.nombre;

Ejecución de ejercicios online

Puede ejecutar comandos de SQLite directamente en el sitio sin tener que instalar nada en su computadora.

Resultado.....
Retornar