I just don't use it enough to keep it in memory as a first class concept. A quick google search with an example is enough to jog my memory.
If I got asked about it on an interview, I'd be completely blank.
"I use [x] all the time and know it well, so [x] will be a good filter for evaluating someone's technical skills."
No. People do different work tasks depending on the project or company.
Sometimes I'm knee-deep in SQL. Others in React. I'm often forced to do PHP. My familiarity with specific SQL syntax ebbs & flows with what I'm doing.
There's no point in keeping something in your brain if you're not going to use it every day. Just remember the general concept and Google the specifics when you need it.
Just one example why interviewing is terrible and interviewers really should be trained on what not to do.
Need me to write an ETL. I probably can't crank it out in an hour like a data scientists, but I can get it done in a reasonable amount of time. I just need to do some major context shifting.
Have me write only ETL's for a week, I'll be cranking them out after the first few.
WHERE filters the data before you aggregate it, HAVING afterwards.
That's all there is to it.
It’s like asking if intel x86 is big-endian or little-endian. It’s trivial to check but if I haven’t had to use that fact in a while, I likely won’t bother to remember because it’s trivial to search for.
That does not mean that I do not know basic concepts about how different databases work, including underlying storage engines etc. To the point that for one product I personally designed actual NoSQL (EAV to be precise) database engine and query engine largely resembling SQL to go along.
So would you fail me in your test?
I can describe for you in detail the different index types on MySQL and Postgres, and go into when/why you would use each. But it's been so long since I crafted a complex join by hand I would definitely have to google it. However I don't doubt that I could write the query in a short time. One of the most realistic interviews I ever had, they gave me a laptop and allowed me to google during my answer. I took the job.
Like in Python if you asked lots of questions with list of numbers ... and then generalized it to infinite lists of numbers. If the first case I'd like to see list comprehensions, and then generator expressions or possibly the use of itertools.
There are a lot of ways to answer it, but HAVING is the simplest.
Usually when you add data to the mix, you'll find that most questions are not significantly correlated with candidate success/failure once you condition on "ability to write any code at all".
A couple years later I found the stack of resumes, some we hired, some we didn't. I realized that some of our best engineers had failed my question, and I had subsequently voted "no" (I was outvoted, fortunately, by my fellow panelists. Another reason I strongly advocate panel interviews but that's a separate discussion).
I soon realized that my question was good at filtering out people who didn't think like me, not at filtering out people who would make good engineers.
My takeaway was that everyone, especially me, could use a healthy dose of humility and self-skepticism. Not advocating swinging the pendulum into Impostor Syndrome (which I now struggle with sometimes), but somewhere in the middle is good and healthy IMHO.
I HATE trivia questions and I’m convinced they are a sign of a lazy interviewer. If you want to test someone’s knowledge of sql, give them a sample schema, an internet connected computer and five minutes to write an appropriate sql query for your use case.
I’ve never met somebody who has spent any amount of time writing raw SQL who didn’t know it.
I’ve met a lot who overly rely on their ORM but “have done a lot of SQL” who don’t.
(+) average= meaning the programmer should be able to say 'solve' the FizzBuzz under comfortable conditions. Or say add the first n items in an integer array etc...
My interviews are conversations to get to know you, your background, your professional interests, how you think about problems and how closely your resume lines up with those conversations. I like to get people talking about their work to see where their energy level goes.
When we start talking through a hypothetical data problem there are people who will describe the problem from the UX perspective, the app code perspective and the database perspective. The question prompts that portion of the conversation.
Precisely. And why not? I have my fellow developers do that ( ask trivia and label the interviewee incompetent, if he cannot answer it) , and most people who have reacted to your post have also probably seen that.
Anyway humor me and tell me how much time it would take?
Using the concepts in how you naturally think about problems?
That will only come from experience. I can't say exactly how much but I'd imagine something in the realm of 6 months minimum of applied usage to different problems.
And I interjected during a long pause and said "It sounds like you're trying to ask a question which could be answered by using a HAVING clause. So something like, only those Customers who have two or more orders, which would be 'GROUP BY Customers HAVING count(Orders) > 2'."
I was right, he was looking for that. Still didn't get the job.
It’s a surprisingly good filter.
EDIT: To clarify since this got more attention than I expected...I don’t disqualify any candidate based on the answer to a single question. I just use that question to assess their actual SQL experience. If you’ve spent any amount time writing raw queries by hand for reports or just to pass through to a web service, you’re going to have run into HAVING. It’s a simple part of the basic SELECT syntax without needing to involve joins, subqueries, index optimizations or specific knowledge of database internals.
It’s a fair question and the only way you wouldn’t have run into it is from working purely with ORMs or NoSQL. There’s a lot you can do without it, but it goes a long way in determining my ability to ask you to open up a query analyzer in different environments. It’s also going to tell me a lot about the way you think about data problems and where you are going to gravitate for certain types of logic.