❔ How do these SQL queries work?
I'm honestly at a loss and can't figure out the process for these queries and how they work.
(Note: There's a typo with the second where it says 'uverdrivers' but its supposed to be 'uberdrivers')
These questions are provided as practice questions here: https://www.adaface.com/questions/mysql
Adaface
Sample Questions for MySQL
Relevant questions on MySQL to assess your candidates for on-the-job skills. Use custom conversational assessments tailored to your job description to identify the most qualified candidates.
4 Replies
Hi. The first one: You're selecting all the data (*) from the Students table when the subquery result is 2. What's the subquery there? Is a calculation of the different scores that are bigger than the current Student being printed on the screen.
Let's say that you take Karen. Her score is 40. There's only one person with a bigger score. So the result of the subquery is 1. Then Karen won't be shown.
If you check Eden you'll see that he/she (idk the gender for that name) has 3 persons with bigger scores. Is not 2 either. The person you want is Casey. When you're doing that question what you are actually asking is Give me any Student that has the 3rd biggest score of all. Not the 3rd person in the rank. The persons with the third biggest Score.
But that's really messed up. In real life, I don't think you'll ever see such a query. I haven't in +20 years.
I had to think to understand what they want. You have a lot better options to achive the same result.
Thank you for the explanation, that really helps clear things up. Is the distinct clause inside the subquery necessary? What would happen if it was left out?
if you left that out then you won't be able to get that particular 3rd rank set of rows
the distinct is to count the different scores, if you do COUNT(S1.Score) you'll be counting all Scores, even those that are repeated. In your sample table you don't have repeated scores, but it's there to count only different scores
For your table, as it's doesn't add absolutely nothing, but I think they want you to use them just in case.
Anyway, even for exercises, this is done backward: first, you tell the problem, then you write the SQL. None ever gives you a qeuery to explain what they wanted to find.
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.