Otro ejercicio más de SQL

[Mauricio]

Para los que no lo sepan, la Universidad de Stanford está dando un curso on line de Introducción a las bases de datos. Cada semana hay que entregar una serie de ejercicios y esta semana me encontré con uno que me hizo pensar bastante.
Primero pondré la estructura de la base de datos.

Código: Seleccionar todo

/* Delete the tables if they already exist */
drop table if exists Highschooler;
drop table if exists Friend;
drop table if exists Likes;

/* Create the schema for our tables */
create table Highschooler(ID int, name text, grade int);
create table Friend(ID1 int, ID2 int);
create table Likes(ID1 int, ID2 int);

/* Populate the tables with our data */
insert into Highschooler values (1510, 'Jordan', 9);
insert into Highschooler values (1689, 'Gabriel', 9);
insert into Highschooler values (1381, 'Tiffany', 9);
insert into Highschooler values (1709, 'Cassandra', 9);
insert into Highschooler values (1101, 'Haley', 10);
insert into Highschooler values (1782, 'Andrew', 10);
insert into Highschooler values (1468, 'Kris', 10);
insert into Highschooler values (1641, 'Brittany', 10);
insert into Highschooler values (1247, 'Alexis', 11);
insert into Highschooler values (1316, 'Austin', 11);
insert into Highschooler values (1911, 'Gabriel', 11);
insert into Highschooler values (1501, 'Jessica', 11);
insert into Highschooler values (1304, 'Jordan', 12);
insert into Highschooler values (1025, 'John', 12);
insert into Highschooler values (1934, 'Kyle', 12);
insert into Highschooler values (1661, 'Logan', 12);

insert into Friend values (1510, 1381);
insert into Friend values (1510, 1689);
insert into Friend values (1689, 1709);
insert into Friend values (1381, 1247);
insert into Friend values (1709, 1247);
insert into Friend values (1689, 1782);
insert into Friend values (1782, 1468);
insert into Friend values (1782, 1316);
insert into Friend values (1782, 1304);
insert into Friend values (1468, 1101);
insert into Friend values (1468, 1641);
insert into Friend values (1101, 1641);
insert into Friend values (1247, 1911);
insert into Friend values (1247, 1501);
insert into Friend values (1911, 1501);
insert into Friend values (1501, 1934);
insert into Friend values (1316, 1934);
insert into Friend values (1934, 1304);
insert into Friend values (1304, 1661);
insert into Friend values (1661, 1025);
insert into Friend select ID2, ID1 from Friend;

insert into Likes values(1689, 1709);
insert into Likes values(1709, 1689);
insert into Likes values(1782, 1709);
insert into Likes values(1911, 1247);
insert into Likes values(1247, 1468);
insert into Likes values(1641, 1468);
insert into Likes values(1316, 1304);
insert into Likes values(1501, 1934);
insert into Likes values(1934, 1501);
insert into Likes values(1025, 1101); 

Son tres tablas, una de alumnos (Highschooler), una de amigos (Friend) y una de "se gustan" (Likes). Si dos personas son amigos entonces hay 2 entradas en la tabla Friend, A-B y B-A. Si, en cambio, 2 personas se gustan, no necesariamente existen 2 entradas porque A puede gustar de B y no darse el caso contrario (me hace acordar a mi adolescencia, me gustaban todas y ninguna me daba bola ).
También puede darse el caso de que A guste de B y que no sean amigos.

El ejercicio consiste en lo siguiente:
Por cada estudiante A al que le gusta B donde los dos no son amigos, encontrar si tienen un amigo C en común (quien puedo presentarlos).
Para esos tríos, retornar nombre y grado de A, B y C.

La solución que encontré me parece algo complicado (y probablemente redundante en algunos campos) así que espero que encuentren algo uds. Luego comparamos.

Saludos.

[anibalponce]

Hola Mauricio, que bueno lo del curso, no sabia. Ahi va una respuesta a ver que tal, repeti bastante el codigo, por ahi haciendo mas temporales se reduce. Pa' pensar.
Abrazo
Anibal


select A,(select name from Highschooler where A=id) NameA,(select grade from Highschooler where A=id) GradeA,
B,(select name from Highschooler where B=id) NameB,(select grade from Highschooler where B=id) GradeB,
AmigosA C,(select name from Highschooler where AmigosA=id) NameC,(select grade from Highschooler where B=id) GradeC
from
(
select l.id1 A,f.id2 AmigosA from likes l, friend f
where not exists (select * from friend where id1=l.id1 and id2=l.id2) and l.id1=f.id1
)elA,
(
select l.id2 B,f.id2 AmigosB from likes l, friend f
where not exists (select * from friend where id1=l.id1 and id2=l.id2) and l.id2=f.id1
)ElB
where AmigosA=AmigosB

[Mauricio]

Sin analizarla, solo la ejecuté en el SQLite, está errada .
El resultado que debe dar, sin los grados de cada uno, es:

Andrew Cassandra Gabriel
Austin Jordan Andrew
Austin Jordan Kyle

La última columna es la de amigos en común.

[anibalponce]

Me faltó la ultima linea, creo que ahi está.

select A,(select name from Highschooler where A=id) NameA,(select grade from Highschooler where A=id) GradeA,
B,(select name from Highschooler where B=id) NameB,(select grade from Highschooler where B=id) GradeB,
AmigosA C,(select name from Highschooler where AmigosA=id) NameC,(select grade from Highschooler where B=id) GradeC
from
(
select l.id1 A,f.id2 AmigosA from likes l, friend f
where not exists (select * from friend where id1=l.id1 and id2=l.id2) and l.id1=f.id1
)elA,
(
select l.id2 B,f.id2 AmigosB from likes l, friend f
where not exists (select * from friend where id1=l.id1 and id2=l.id2) and l.id2=f.id1
)ElB
where AmigosA=AmigosB
and exists (select * from likes where A=id1 and B=Id2)

[Mauricio]

Ahora me gustó más.
Pero no fue fácil, o sí?

[anibalponce]

Si si, tiene su complicacion. Buen ejercicio.
Anibal

[Mauricio]

La solución a la que había llegado es esta:

Código: Seleccionar todo

SELECT H1.name, H1.grade, H2.name, H2.grade, T.NAME, T.GRADE FROM LIKES L INNER JOIN Highschooler H1 ON H1.ID = L.ID1 INNER JOIN Highschooler H2 ON H2.ID = L.ID2 INNER JOIN (SELECT F1.ID1 ID1, F2.ID1 ID2,F1.ID2 ID3, H.NAME NAME, H.GRADE GRADE FROM FRIEND F1, FRIEND F2, HIGHSCHOOLER H WHERE F1.ID2 = F2.ID2 AND H.ID =F1.ID2 AND F1.ID1 <> F2.ID1) T ON H1.ID = T.ID1 AND H2.ID = T.ID2 WHERE NOT EXISTS (SELECT * FROM FRIEND F WHERE F.ID1 = L.ID1 AND F.ID2 = L.ID2) AND NOT EXISTS (SELECT * FROM LIKES L WHERE L.ID1 = L.ID2 AND L.ID2 = L.ID1) 

No sé si es mejor o peor, habría que ver el plan de ejecución pero mucho no dirá principalmente porque así como están las tablas no tienen índices ni nada.
Todos los ejercicios los hago bajo SQLite, con el cual no había tenido ningún contacto, pero que está bueno.