Oracle SQL SubConsulta

Navigation
2

I'm doing a table query / modification exercise. I have two tables: 

CREATE TABLE CURSOS
( Codigo NUMBER, 
  Nombre VARCHAR2(20), 
  Cod_Profe NUMBER, 
  Max_Alumn NUMBER, 
  Fecha_Inic DATE, 
  Fecha_Fin DATE, 
  Num_Horas NUMBER,
  CONSTRAINT Cod_Curso_PK PRIMARY KEY (Codigo),    
  CONSTRAINT Cod_Profe_FK
    FOREIGN KEY (Cod_Profe)
    REFERENCES PROFESORADO(Codigo)
);


CREATE TABLE ALUMNADO
( Codigo NUMBER, 
  Nombre VARCHAR2(30), 
  Apellidos VARCHAR2(30), 
  Sexo VARCHAR2(1), 
  Fecha_Nac DATE, 
  Cod_Curso NUMBER,
  CONSTRAINT Cod_alum_PK PRIMARY KEY (Codigo),    
  CONSTRAINT Cod_Curso_FK
    FOREIGN KEY (Cod_Curso)
    REFERENCES CURSOS(Codigo)
);

I have: 

Curso 1 con 22 alumnos
Curso 2 con 18 alumnos
Curso 3 con 17 alumnos
Curso 4 con 14 alumnos

And I need to make a modification that is as follows, "Remove, from the table COURSES, the course record that have less than 15 students."

I have to use the register draft command DELETE.... WHERE... and do the subquery, and it does not work.

This is what I try: 

DELETE CURSOS
WHERE CODIGO NOT IN (SELECT DISTINCT COD_CURSO
                     FROM ALUMNADO
                     WHERE (SELECT COUNT(*) FROM ALUMNADO GROUP BY COD_CURSO) > 15);
    
asked by Ivan Vieites Lores 24.04.2017 в 11:47
source

2 answers

1

Well I can think of a simple way to do it that I hope you fit, try this query: 

DELETE Cursos WHERE (SELECT count(*) FROM Alumnado WHERE COD_CURSO = Cursos.Codigo) < 15;

Without having to do a IN , we directly state the condition with the count and we take advantage of the fact that we are already iterating through the courses to use their code property in the subquery.

The subquery recovers how many students there are for a given course, so then we simply have to check if that value is below our level, which in this case is 15 .

    
answered by 24.04.2017 / 12:47
source
0

I have found the solution thanks to a friend: 

DELETE FROM CURSOS WHERE CODIGO IN (SELECT COD_CURSO
                                    FROM ALUMNADO
                                    GROUP BY COD_CURSO
                                    HAVING COUNT(COD_CURSO)<15);
    
answered by 24.04.2017 в 13:03
Escape CDATA into another CDATA in XML WebControl.Image to Itextsharp.Image