co-pilots who made the most flights

Asked

Viewed 62 times

-3

I have this question:

Name and number of flight hours of the copilot who made the largest number of flights. It is intended to know the exact number of flights made by each of these copilot

And I tried to solve it like this :

SELECT t.nome , p1.n_horas_voo , COUNT( * ) as n_de_voos
FROM   Tripulante t , Piloto p1 , Voo v
WHERE  t.id = v.id_copiloto
   AND v.id_copiloto = p1.id
        GROUP BY ( t.apelido )
        HAVING COUNT( * ) >= ALL( SELECT COUNT(*)
        FROM Voo v2)

However my output is this

inserir a descrição da imagem aqui

and it should just be Carlos because he is the co-pilot who made the most flights. What am I doing wrong? Or how do I solve this question?

Tables:

inserir a descrição da imagem aqui

sql

  • What is the database?

  • From what I saw two answers came out in the question that would solve the problem or at least refer to a solution. There you said that you cannot use subquery without explaining the reason. You also already have other questions similar to yours on the site, with the same problem of lack of clarity. I think it is the case that you read the [Tour] and [Ask], and [Dit] the question elaborating better and explaining all the necessary conditions beforehand, because by your comments, I understand that the question Is Not Clear enough for people to answer what you expect.

  • Working out better, everyone wins because it increases the chance of some answer coming out that solves your problem, and prevents people from wasting time answering something that will not serve for reasons that were not put in the question.

2 answers

2

If it’s Slqserver it looks like this:

SELECT TOP 1 t.nome , p1.n_horas_voo , COUNT( * ) as n_de_voos
FROM   Tripulante t , Piloto p1 , Voo v
WHERE  t.id = v.id_copiloto
   AND v.id_copiloto = p1.id
        GROUP BY ( t.apelido )
        HAVING COUNT( * ) >= ALL( SELECT COUNT(*)
        FROM Voo v2)

Mysql think that uses limit:

    SELECT t.nome , p1.n_horas_voo , COUNT( * ) as n_de_voos
        FROM   Tripulante t , Piloto p1 , Voo v
        WHERE  t.id = v.id_copiloto
           AND v.id_copiloto = p1.id
                GROUP BY ( t.apelido )
                HAVING COUNT( * ) >= ALL( SELECT COUNT(*)
                FROM Voo v2)
        Limit 1

Just make sure you don’t want to use an Order By

  • I cannot use either TOP or Limit, I have to formulate the querry so that I only get a record without having to make a restriction.

1

To know total flights (cycles):

SELECT TMP.* 
  FROM (SELECT T.Id, T.Nome, Count(*) Voos
          FROM Tripulante T
          JOIN Voo V ON V.Id_CoPiloto = T.Id
         GROUP BY T.Id, T.Nome) TMP
 ORDER BY TMP.Voos DESC

To know total hours:

SELECT TMP.* 
  FROM (SELECT T.Id, T.Nome, Sum(V.Data_Chegada - V.Data_Partita) Horas
          FROM Tripulante T
          JOIN Voo V ON V.Id_CoPiloto = T.Id
         GROUP BY T.Id, T.Nome) TMP
 ORDER BY TMP.Horas DESC

I haven’t tried it, but it should be good to see.

  • I can’t use from with select’s or conditions inside unfortunately

  • @Esteves, out of curiosity, because you can’t?

  • @Esteves, you can run without the subquery, hence you order the result on the application side. No error. :)

  • I am not allowed to use, Anyway I want to output the name and number of flight hours of the pilot who made the largest number of flights so I only need to count the number of flights .

  • I get it, well, like I said, no need, just run the subquery, and the result you sort in php. Answer, @Esteves?

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.