Library › Coding AI › Job seekers

Practice a take-home SQL question on an old MySQL

Practice: Giving the right context. About 5 minutes.

About this exercise

You're practicing for a take-home that runs on MySQL 5.7, which has no window functions and no WITH clauses. Write a query that returns each department's second-highest distinct salary, with columns named exactly department and second_salary; departments with only one distinct salary simply don't appear. One code block, at most 10 lines, then under 25 words outside the code.

Common mistakes when you ask AI for this

  • Not naming the database version. The AI reaches for DENSE_RANK, which MySQL 5.7 doesn't have. Try: Add: "MySQL 5.7: no window functions, no WITH."
  • Forgetting ties. A LIMIT 1 OFFSET 1 approach returns 98000 again for Data. Try: Add: "Second-highest distinct salary; ties count once."
  • Leaving the output open. The AI picks its own column names. Try: Add: "Columns department and second_salary."
  • Getting a menu of options. The AI likes to offer two or three versions of the query, so you're left to pick and test them yourself. Try: Add: "Give one query only, in one code block, no alternatives."
  • Getting a tutorial. By default the AI explains every part of the code at length, which you then have to scroll past. Try: Add: "After the code, one line under 12 words. Nothing else."

What a strong prompt includes

  • Names the version
  • Handles ties
  • Sets the columns
  • Asks for one answer only
  • Caps the code length

Practice this now ↑

Related exercises