[SOLUCIONADO] Necesito saber que query debo correr en el esquema dado para obtener el resultado en

enehh · #1 (**permalink**) 18/07/2015, 23:36

Mas abajo hay tres tablas para una db que para las pruebas la uso en sqlite
La query que quiero resolver es una que me liste el nombre de todos los alumnos inscriptos en todas las materias.
Entiendo que se puede resolver con una sola consulta pero las que probe no me sirvieron.

Con:

Código SQL:

Ver originalSELECT m.nombre, COUNT(i.id_materia) AS AlumMat
FROM inscripcion AS i 
LEFT JOIN materia AS m
ON m.id=i.id_materia
GROUP BY i.id_materia
ORDER BY AlumMat DESC;

Pude sacar una lista de las materias y cantidad de inscriptos

Comn esta otra.

Código SQL:

Ver originalSELECT a.nombre, COUNT(i.id_materia) AS NumOfMat FROM (alumno AS a
INNER JOIN inscripcion AS i
ON a.id=i.id_alumno)
GROUP BY i.id_materia
HAVING COUNT(i.id_materia) > 4
ORDER BY NumOfMat DESC;

Alumnos incriptos en mas de 4 materias
Se me complico con la lista de alumnos inscriptos en todas las materias.

La idea es que sea SQL lo mas standard posible, si tienen la solucion para algun motor en particular sirve tambien. Logre algo usando VIEWS

Gracias

Código SQL:

Ver originalPRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE 'alumno' (
'id' INT NOT NULL,
'nombre' VARCHAR(45) NULL,
PRIMARY KEY ('id')
);
INSERT INTO "alumno" VALUES(1,'Lara');
INSERT INTO "alumno" VALUES(2,'Nestor');
INSERT INTO "alumno" VALUES(3,'Juan');
INSERT INTO "alumno" VALUES(4,'Pedro');
INSERT INTO "alumno" VALUES(5,'Jose');
INSERT INTO "alumno" VALUES(6,'Igor');
INSERT INTO "alumno" VALUES(7,'Steve');
INSERT INTO "alumno" VALUES(9,'Otto');
INSERT INTO "alumno" VALUES(8,'Bart');
INSERT INTO "alumno" VALUES(10,'Lisa');
COMMIT;
 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE 'materia' (
'id' INT NOT NULL,
'nombre' VARCHAR(45) NULL,
PRIMARY KEY ('id')
);
INSERT INTO "materia" VALUES(1,'Math');
INSERT INTO "materia" VALUES(2,'English');
INSERT INTO "materia" VALUES(3,'Anatomy');
INSERT INTO "materia" VALUES(4,'Chinese');
INSERT INTO "materia" VALUES(5,'Spanish');
INSERT INTO "materia" VALUES(6,'Italian');
INSERT INTO "materia" VALUES(7,'Gym');
INSERT INTO "materia" VALUES(8,'Sociology');
INSERT INTO "materia" VALUES(9,'Magic');
INSERT INTO "materia" VALUES(10,'Blood');
COMMIT;
 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE 'inscripcion' (
'id_alumno' INT NOT NULL,
'id_materia' INT NOT NULL,
PRIMARY KEY ('id_materia', 'id_alumno'));
INSERT INTO "inscripcion" VALUES(1,1);
INSERT INTO "inscripcion" VALUES(1,2);
INSERT INTO "inscripcion" VALUES(1,3);
INSERT INTO "inscripcion" VALUES(1,4);
INSERT INTO "inscripcion" VALUES(1,5);
INSERT INTO "inscripcion" VALUES(1,6);
INSERT INTO "inscripcion" VALUES(1,7);
INSERT INTO "inscripcion" VALUES(1,8);
INSERT INTO "inscripcion" VALUES(1,9);
INSERT INTO "inscripcion" VALUES(1,10);
INSERT INTO "inscripcion" VALUES(2,1);
INSERT INTO "inscripcion" VALUES(2,3);
INSERT INTO "inscripcion" VALUES(3,1);
INSERT INTO "inscripcion" VALUES(3,3);
INSERT INTO "inscripcion" VALUES(4,1);
INSERT INTO "inscripcion" VALUES(5,1);
INSERT INTO "inscripcion" VALUES(5,2);
INSERT INTO "inscripcion" VALUES(5,3);
INSERT INTO "inscripcion" VALUES(5,5);
INSERT INTO "inscripcion" VALUES(5,6);
INSERT INTO "inscripcion" VALUES(6,2);
INSERT INTO "inscripcion" VALUES(6,4);
INSERT INTO "inscripcion" VALUES(6,6);
INSERT INTO "inscripcion" VALUES(7,3);
INSERT INTO "inscripcion" VALUES(7,4);
INSERT INTO "inscripcion" VALUES(8,2);
INSERT INTO "inscripcion" VALUES(8,4);
INSERT INTO "inscripcion" VALUES(8,7);
INSERT INTO "inscripcion" VALUES(9,7);
INSERT INTO "inscripcion" VALUES(10,3);
COMMIT;

Libras · #2 (**permalink**) 19/07/2015, 05:20

si te entendi bien, quieres que nada mas salga el alumno que tiene todas las materias, en este ejemplo Lara vdd??

Código SQL:

Ver originalCREATE TABLE #alumno (
id INT NOT NULL,
nombre VARCHAR(45) NULL,
PRIMARY KEY (id)
);
INSERT INTO #alumno VALUES(1,'Lara');
INSERT INTO #alumno VALUES(2,'Nestor');
INSERT INTO #alumno VALUES(3,'Juan');
INSERT INTO #alumno VALUES(4,'Pedro');
INSERT INTO #alumno VALUES(5,'Jose');
INSERT INTO #alumno VALUES(6,'Igor');
INSERT INTO #alumno VALUES(7,'Steve');
INSERT INTO #alumno VALUES(9,'Otto');
INSERT INTO #alumno VALUES(8,'Bart');
INSERT INTO #alumno VALUES(10,'Lisa');
 
 
CREATE TABLE #materia (
id INT NOT NULL,
nombre VARCHAR(45) NULL,
PRIMARY KEY (id)
);
INSERT INTO #materia VALUES(1,'Math');
INSERT INTO #materia VALUES(2,'English');
INSERT INTO #materia VALUES(3,'Anatomy');
INSERT INTO #materia VALUES(4,'Chinese');
INSERT INTO #materia VALUES(5,'Spanish');
INSERT INTO #materia VALUES(6,'Italian');
INSERT INTO #materia VALUES(7,'Gym');
INSERT INTO #materia VALUES(8,'Sociology');
INSERT INTO #materia VALUES(9,'Magic');
INSERT INTO #materia VALUES(10,'Blood');
 
 
CREATE TABLE #inscripcion (
id_alumno INT NOT NULL,
id_materia INT NOT NULL,
PRIMARY KEY (id_materia, id_alumno));
 
INSERT INTO #inscripcion VALUES(1,1);
INSERT INTO #inscripcion VALUES(1,2);
INSERT INTO #inscripcion VALUES(1,3);
INSERT INTO #inscripcion VALUES(1,4);
INSERT INTO #inscripcion VALUES(1,5);
INSERT INTO #inscripcion VALUES(1,6);
INSERT INTO #inscripcion VALUES(1,7);
INSERT INTO #inscripcion VALUES(1,8);
INSERT INTO #inscripcion VALUES(1,9);
INSERT INTO #inscripcion VALUES(1,10);
INSERT INTO #inscripcion VALUES(2,1);
INSERT INTO #inscripcion VALUES(2,3);
INSERT INTO #inscripcion VALUES(3,1);
INSERT INTO #inscripcion VALUES(3,3);
INSERT INTO #inscripcion VALUES(4,1);
INSERT INTO #inscripcion VALUES(5,1);
INSERT INTO #inscripcion VALUES(5,2);
INSERT INTO #inscripcion VALUES(5,3);
INSERT INTO #inscripcion VALUES(5,5);
INSERT INTO #inscripcion VALUES(5,6);
INSERT INTO #inscripcion VALUES(6,2);
INSERT INTO #inscripcion VALUES(6,4);
INSERT INTO #inscripcion VALUES(6,6);
INSERT INTO #inscripcion VALUES(7,3);
INSERT INTO #inscripcion VALUES(7,4);
INSERT INTO #inscripcion VALUES(8,2);
INSERT INTO #inscripcion VALUES(8,4);
INSERT INTO #inscripcion VALUES(8,7);
INSERT INTO #inscripcion VALUES(9,7);
INSERT INTO #inscripcion VALUES(10,3);
 
 
SELECT nombre FROM(
SELECT t2.nombre,COUNT(t3.nombre) total FROM #inscripcion AS t1
LEFT JOIN #alumno AS t2 ON (t1.id_alumno=t2.id)
LEFT JOIN #materia AS t3 ON (t1.id_materia=t3.id)
GROUP BY t2.nombre) AS t1 WHERE total=(SELECT COUNT(*) FROM #materia)

Con ese query obtenemos lo que necesitas solamente al alumno que tiene tooodas las materias en este caso Lara :)

enehh · #3 (**permalink**) 21/07/2015, 06:03

Excelente.
Muchas gracias.
Lo estoy viendo para entender mejor y aplicarlo la próxima.
Que usas para tener que poner el "#" delante del nombre de las tablas?
Nuevamente muchas gracias, pensé que nadie iba a responder.
Saludos

gnzsoloyo · #4 (**permalink**) 21/07/2015, 06:11

Libras te pone ejemplos en SQL Server para que tu los tomes como referencia y luego los adaptes a tu DBMS (Si no me equivoco estás usando SQLite).
Fijate en el manual de referencia cómo se hace en tu caso, para tu DBMS. Cada uno de ellos tiene sus propias características (Oracle y MySQL lo hacen de otras formas, por ejemplo).

Libras · #5 (**permalink**) 22/07/2015, 12:53

Cita:

Iniciado por enehh

Excelente.
Muchas gracias.
Lo estoy viendo para entender mejor y aplicarlo la próxima.
Que usas para tener que poner el "#" delante del nombre de las tablas?
Nuevamente muchas gracias, pensé que nadie iba a responder.
Saludos

Son tablas temporales en sql server, no tengo tus datos por lo que hice tablas temporales......