Variables and temporary tables in SQL, exercise

Question

Variables and temporary tables in SQL, exercise

Navigation

#1 by (1 votes)

1

This exercise was presented to me:

Show students who have enrolled in more courses than Antoine de Saint-Exupery. Show all the data of the students, the number of courses to which they enrolled and how many times more than Juan Perez.

The tables present are:

Students

  'dni' 
  'nombre' 
  'apellido'
  'tel' 
  'email'
  'direccion'

Registrations

  'nom_plan' 
  'nro_curso'
  'dni' 
  'fecha_inscripcion'

Courses

  'nom_plan' 
  'nro_curso'
  'fecha_ini' 
  'fecha_fin'
  'salon' 
  'cupo'

What I did is this:

select alu.'dni', alu.'nombre', alu.'apellido', alu.'direccion', alu.'email',
alu.'tel', count(*)
from alumnos alu
inner join inscripciones insc
on alu.'dni' = insc.'dni'
inner join cursos cur
on cur.'nro_curso' = insc.'nro_curso'
group by 1

And what I would miss is the part of the subquery that would do it with a HAVING as well as the other count where they are shown how many times more than Juan Perez were registered, but they tell me to do it with variables or temporary tables. And I really do not understand how to do it, someone to help me? Thanks!

mysql sql

asked by Damian Ricobelli 16.12.2016 в 19:14

source

1 answer

How can I not open the same wpf window twice in c #? Get all matches between 2 fixed texts

score 1 · Accepted Answer

One solution would be:

Declare an integer variable, create a query that allows you to obtain the number of Juan Perez inscriptions, and assign this value to the variable, for example:

select @numero_inscripciones_juan_perez := count(*)

In the query you already have, you could only include the column dni and its count:

select alu.'dni', count(alu.'dni') as numero_inscripciones

Then add the having clause, compare that the number of student registrations is greater than the value of the variable:

having count(alu.'dni') > @numero_inscripciones_juan_perez

Investigate how to create a temporary table and fill it with the data of this query.

You already have the students who have registered more times than Juan Perez, and you only have to show the number of extra times:

select dni, numero_inscripciones, numero_inscripciones-@numero_inscripciones_juan_perez from tabla_temporal