Geek asks and answers

MySQL interview that made me disqualify

I was given this question about an interview with MySQL that made me disqualified for work.

I went for an interview and asked a question that I could not answer, and I lost my job.

They asked.

We have two tables, the first table (main table) CANDIDATESwith fields:

  • candidate_id (primary_key)
  • candidate_name

The second table (child table) CANDIDATE_VOTESwith the fields:

  • v_id (primary key)
  • candidate_id (external key)

Each time a vote is given, the candidate candidate_key is placed in the child.

CANDIDATE:

=================================     
|candidate_id | candidate_Name  |  
|-------------------------------|  
| 1           | abc             |  
|-------------------------------|  
| 2           | xyz             |  
|-------------------------------|  
| 3           | etc             |  
|-------------------------------|

CANDIDATE VOTES

==========================     
| votes_id | candidate_id |  
|-------------------------|  
| 1        | 1            |  
|-------------------------|  
| 2        | 1            |  
|-------------------------|  
| 3        | 2            |  
|-------------------------|

Question: How will you announce the winner?

Please help me how to do this.

I tried a lot, but could not find the logic.

+5
source share
4 answers

, :

SELECT   candidates.*
FROM     candidates JOIN candidate_votes USING (candidate_id)
GROUP BY candidate_id
HAVING   COUNT(*) = (
  SELECT   COUNT(*)
  FROM     candidate_votes
  GROUP BY candidate_id
  ORDER BY votes DESC
  LIMIT    1
)

sqlfiddle.

+8

COUNT(), , . , candidate_name, LIMIT ORDER BY , , .

SELECT count(*) winner, c.candidate_Name
FROM candidates c
INNER JOIN candidate_votes cv
   ON c.candidate_id = cv.candidate_id
GROUP BY c.candidate_Name
ORDER BY winner desc
LIMIT 1  -- remove the LIMIT to see all records

SQL Fiddle with Demo

MySQL GROUP BY SELECT

SELECT count(*) winner, , c.candidate_Name
FROM candidates c
INNER JOIN candidate_votes cv
   ON c.candidate_id = cv.candidate_id
GROUP BY cv.candidate_id
ORDER BY winner desc
LIMIT 1 -- remove the LIMIT to see all records

. SQL Fiddle with Demo

+2
SELECT   CANDIDATE_NAME, count(*)
FROM     CANDIDATES, CANDIDATE_VOTES
WHERE    CANDIDATES.CANDIDATE_ID = CANDIDATE_VOTES.CANDIDATE_ID
GROUP BY CANDIDATE_NAME
ORDER BY count(*) DESC
LIMIT    1

SELECT: +

FROM:

:

GROUP BY: , (*) SELECT , 1 (a.k.a. )

ORDER BY: ,

LIMIT: 1 , ,

0

. GROUP BY:

SELECT TOP 1 C.candidate_id, C.candidate_name, COUNT(1) NoOfVotes
FROM CANDIDATES C
INNER JOIN CANDIDATE_VOTES CV ON C.candidate_id = CV.candidate_id
GROUP BY C.candidate_id, , C.candidate_name
ORDER BY 3 DESC

, , . 1 ( ).

: T-SQL (SQL Server). , "LIMIT 1" "TOP 1" MySQL.

-1

More articles:


All Articles