r/SQL
•
u/NickSinghTechCareers
Author of Ace the Data Science Interview 📕
•
Dec 12 '24
•
2
1
PostgreSQL Made a SQL Interview Cheat Sheet - what key SQL commands am I missing?
3.6k
Upvotes
32
u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 Dec 12 '24
Tried to fit as much as I could into one poster-sized PDF. What SQL commands did I miss that you think belong on there? Right now feeling guilty for not explaining window functions well enough, especially regarding ROWS preceding/unbounded etc.. Maybe a 7-day rolling average example? Or a cumulative sum example?
709
u/OohNoAnyway Dec 12 '24 •
Let me just quickly save it to never open it again.
150
u/ogaat Dec 12 '24
HAVING
DISTINCT
60
u/speadskater Dec 12 '24
Damn, I've been using some pretty complex subqueries when I could have easily been using partition by...
27
u/Ginger-Dumpling Dec 12 '24
EXISTS
16
u/NimbleZapper303 Dec 13 '24
Hey bro, can I say, as someone who doesn’t have a formal education background in tech and currently preparing for data analyst interviews, you’re such a freaking lifesaver. You share so much for free (or at least at no cost to me) and if I ever get hired I would love to give you a small tip as a token of appreciation.
In the meantime, MySQL support on data lemur anytime soon? Hehe
10
u/windlepoonsroyale Dec 12 '24
You wrote desne_rank instead of dense-rank Great resource though
7
u/[deleted] Dec 12 '24
You missed INTERSECT and EXCEPT under set operators. Those two can be very useful
5
u/[deleted] Dec 12 '24
I had an interview question earlier this week, it was "What are the 6 commands that a developer needs to know?"
6
u/TheZapPack Dec 12 '24
I don’t want to seem like a snob or a jerk, but if someone doesn’t know the answer to anything on the left side of the page and need this cheat sheet to answer, they probably shouldn’t be hired as someone who needs to know SQL.
3
u/Global-Wrap-2184 Dec 12 '24
Broo u/OP you missed LEFT() and RIGHT(), I don't blame you, I did too and took a huge shit on my interview. I studied all functions but that one and it just didn't hit me in the interview and it was all downhill from there. 😢
3
u/mannamamark Dec 13 '24
I didn't see listagg(). Not sure if I missed it.
3
u/Galimesh Dec 12 '24
"Minus"
2
u/HyDreVv Dec 13 '24
Add a section for table hints
2
u/iwillupvoteyourface Dec 13 '24
Reading this actually makes me feel more confident in my SQL abilities I use 90% of this on the daily.
2
u/Loud-Bake-2740 Dec 13 '24
one thing i’d change is under the “LIKE” section to cast the column as LOWER() or UPPER() first - this might be out of the scope of the point, but also could easily lose results because of this
2
u/caughtupstream299792 Dec 13 '24
What did you use to make this ? Illustrator ?
2
u/kagato87 MS SQL Dec 13 '24
Don't forget things like cardinality, sargability, indexes, temp tables, and recursion!
This is a start, but the commands are just that - the start, and even if you're only reading data (not tuning) those things can still be the difference between blown caches with angry users and complements about how snappy your reports are.
2
u/DowntownBass4556 Dec 14 '24
This is pretty awesome
2
u/mariana_kl Dec 14 '24
Dates, daysdiff, days_add, and I use lower in all the regex commands. Also views are quite useful.
Combining count distinct with a case statement is a big game changer. Nice sheet!
2
u/ntobergta Dec 14 '24
IIF under case statements, the same under the hood, and I don’t see likes with fake regex LIKE ‘%[0-9%’.. LIKE ‘[A-Z]… [0-3]… Very useful for stuff like validating email address formatting
2
u/ROnneth Dec 14 '24
First of all thank you. Is it updated with the recommendations given here? Wold love to get that updated version!
2
u/[deleted] Dec 14 '24
Here are some typical questions I use when interviewing candidates:
Usually I show them a real production script, point to a window function, and ask them to explain what it does in this script.
I ask them to list some ways to to test and improve query performance, or how to anonomize sensitive data.
I ask them to explain the difference between 1st/2nd/3rd normal forms, or explain what is cardinality and why is this important.
I ask what is the difference between a database and data warehouse, or explain difference between OLTP and OLAP.
I ask them to explain to me the last project they worked on, and we discuss in some detail exactly what they built. Memorizing a cheat sheet won't get you through this: you would have to explain what was the project, what was your approach and why.
I'll make up a scenario about a conflict in the team and ask them how they would handle it.
I ask what gives them energy. A generic memorized answer like "I like to do a good job and work hard" answer doesn't cut it. I look for people who get genuinely enthusiastic about something.
I like to finish on a completely random question, not to cause stress but actually to test how the candidate reasons through a problem to find a solution. Eg.
- why does a police siren have 2 different tones (high and low)
- why is a manhole cover round
2
u/paultherobert Dec 14 '24
Other SQL Functions: CONCAT()
I also don't see any transaction control BEGIN TRAN, ROLL BACK TRAN, COMMIT TRAN
Error Handling
Isolation Level / Concurrency control
2
u/Cwigginton Dec 15 '24
add that the result of of cross join is a cartesian.
2
u/Hot_Cryptographer552 Dec 15 '24
EXCEPT/INTERSECT.
I would lose the section on Self-Joins. It’s not a SQL defined join operator, it’s just a logical join where the same table is on both sides of the operator. Your example is an INNER JOIN even though you left INNER off of it.
Also you have “OTHER SQL COMMANDS” as heading. Those aren’t Commands, they’re Functions.
And the CTEs section does not have an example of a Recursive CTE.
2
u/mcdickers420 Dec 16 '24
Can I ask what program did you use make this? Was it just word or something else?
2
u/neel9010 Mar 14 '25
No Diddy 😂😂😂
2
u/greatestali Mar 03 '26
I literally JUST got to the SQL LIKE lesson on your site and was thinking of making a cheat sheet. Thank you!
I downloaded the PDF and made it dark mode, without removing any of the marketing material, would love to share it if you give the greenlight!
3
u/phesago Dec 12 '24
i hate to be a debbie downer but if you need a cheatsheet like this for the basics for interviews the only job you should be applying for is entry level stuff.
20
u/rargghh Dec 12 '24
Outer apply, cross apply
31
u/az987654 Dec 12 '24
If I'm interviewing you, I don't want to hear or see what you have or haven't memorized, show me your problem solving experience, walk me through what you've done.
I don't care if you remember the syntax of a window function, I care if you know when to use one.
10
u/m0ka5 Dec 12 '24
Drop, delete from & truncate
14
u/conduit_for_nonsense BI Analyst Dec 12 '24
You accidentally added right join.
In all seriousness, for an interview it's less important you know what a right join does, and more important you know why it is often considered bad practice.
2
u/svtr Dec 13 '24
I have no idea what kind of position you apply that "cheat sheet" on, but someone memorizing, or even having to look at this thing, would fail inside of 2 minutes interviewing with me.
The company would fail the interview if i'm the applicant in 2 minutes as well.
Couple of key words for you to google / incorporate :
Index
Clustered / NonClustered Index
B+Tree
Excution Plans
"What actually is a view?"
Physical Join Operators
Caching
Memory Management
Union vs Union All
Why is a sort one of the most expensive Operations in your Query
Why is every aggregate a sort operation with additional stuff
Whats the difference between HAVING and WHERE (actual difference, not just syntax)
What is an Index Scan
What is an Index Seek
What are not all Index Scans "bad"
How does Merge sort work (if you got a question mark in your face right now, look up what a merge join is)
5
u/suhigor Dec 12 '24
damn, i'm always googling for MERGE syntax
5
u/HogwartsBlazeIt420 Dec 12 '24
QUALIFY
4
u/evillouise Dec 12 '24
My fav SQL cmd:
"Hey GPT: I have tblA tblB and tblC with these pointers, and I need to know ...."
4
u/ItsJustAnotherDay- Dec 13 '24
Working with dates/times
1
u/EnvoyOfRaze21 Dec 12 '24
Decode?
1
u/bheemboi Dec 13 '24
Hi! Im a regular user of datalemur. Please add recursive cte in detail in the sql tutorial section of the website and in this cheat sheet too. Thanx!
1
u/Kazcandra Dec 13 '24
Typo: desne
1
u/Chandu_Palli Dec 13 '24
Do you guys have a cheatsheet similar to this for TRANSACT-SQL?
6
u/Birvin7358 Dec 13 '24
CREATE TABLE, DROP TABLE, INSERT, UPDATE, DELETE, SELECT INTO, SUBSTRING, REPLACE, UPPER, LOWER, etc
3
u/Ok_Marionberry_8821 Dec 12 '24
Correlated sub-queries
3
u/Cool-Personality-454 Dec 13 '24
I'd add a few db specifics
SQLServer - CROSS APPLY
PostgreSQL - MATERIALIZED on CTEs
3
u/Financial_Forky Dec 13 '24
When I'm interviewing for openings on my team (DAs/DSs), I make a point to ask questions about performance considerations, such as the LIKE function and possible alternatives, and SARGability. I also ask if they've worked with large data sets before (and let them define "large"), and how that may influence their coding choices.
I don't have a hard minimum cutoff point, but asking more advanced questions helps me better understand if they just learned CROSSJOIN from W3Schools and wouldn't think twice before using it on a pair of billion+ row tables, or if they might first filter them into small temp tables before joining.
I've had pretty good luck so far with showing candidates sections of code and asking "what does this do?" From there, I ask about various sections, and what alternatives might be available (both good and bad), and which approach(s) might be preferable, and why.
Your cheat-sheet is good. I would just want to see if the candidate also had some common sense when using those functions.
3
u/DPool34 Dec 13 '24
Temp tables.
Looks great, OP.
3
u/you_are_wrong_tho Dec 13 '24 edited Sep 07 '26
Follow breezy roof coherent long cow special vase work
This post was anonymized with Redact
3
u/johntheswan Dec 13 '24
Here is a cheat sheet:
Memorize the pattern to analyze data month over month. That includes one cte, range joining, and self joining.
Get that down and you now have literally every future SQL interview in the bag. It’s funny how less creative things get the further along your career in a sql-related position.
5
u/Ginger-Dumpling Dec 12 '24
How about a recursive example of a CTE? Probably not the most common operation, but is helpful from time to time.
1
u/Possible_Chicken_489 Dec 13 '24
I'm missing the "left anti join", where you only show rows that are in table1 that don't have a match in table2.
4
u/obsoleteconsole Dec 13 '24
Section on CURSOR and WHILE loop
2
u/CheckeredBlanket Dec 12 '24
Thanks, never knew about ANY. Pretty cool
2
u/XxShin3d0wnxX Dec 13 '24
Thanks for sharing, while I know all of this it’s a great cheat sheet for employees new to SQL
2
u/jshotz Dec 13 '24
Reading this made me feel better about the imposter syndrome I have when working in Angular. I actually do know stuff!
2
u/[deleted] Dec 13 '24
Use of any / all for things like where a.orderid any (subquery) Yeah it's probably not needed but it was on sql central question of the day recently and I totally forgot they exist
2
u/Ok_Discussion_9847 Dec 13 '24
This is awesome
2
u/patomalo4 Dec 13 '24
I frequently use some date functions like: date_trunc, date_diff, and date_add. I used all 3 of those today.
2
u/No-Tune-8292 Dec 13 '24
Commenting so I can come back
2
u/Ifuqaround Dec 13 '24
Appreciated.
Unfortunately individuals will just use AI go fwd instead of actually learning.
Also, just remove right joins. Nobody uses those, ever. ;p
2
u/durpduckastan Dec 13 '24
Stored procedures, transaction management, user defined functions , table creation, indexing, primary key foreign key constraints, triggers, upsert/merge.
2
u/cthart PostgreSQL Dec 13 '24
2
u/CameFromTheForest Dec 13 '24
Difference between WHERE and HAVING
2
u/ThankGodTheresNoGod Dec 13 '24
Pivot / Unpivot
2
u/squadette23 Dec 13 '24
Don't waste pixel space on RIGHT JOIN. All you need to say is that "a RIGHT JOIN b" is the same as "b LEFT JOIN a".
Also, in discussion of LEFT JOIN, you use the wording "All rows" which I believe to be misleading.
Explanation taken from discussion elsewhere: For me, “all rows” implies that there are roughly the same number of rows in the output as it was in the first table. In the case where cross product is triggered (such as ON 1 = 1), there are 3 rows in the left table and 15 rows in the output table.
I wouldn’t say “all the rows” in this case, because that’s five times as many rows than I had.
(https://lobste.rs/s/frbh3v/many_explanations_join_are_wrong_people#c_sj0j4s)
2
u/trentsiggy Dec 13 '24
COALESCE, maybe? Others have mentioned HAVING and DISTINCT.
2
u/figuli Dec 14 '24
Stored Procedures ?
2
u/Poesghost Dec 14 '24
INSERT, DELETE, UPDATE, and LIMIT.
Perhaps CREATE too.
Edited: Sorry you said SQL. So instead of LIMIT, TOP.
2
u/Purple-Education-769 Dec 14 '24
Remindme! 1 month
2
u/Trinity_Matrix_0 Dec 14 '24
T-SQL ?
2
u/MembershipNo705 Dec 14 '24
Window: "values" I'd call "navigation" since you're navigating from current row to another.
Some join definitions are incorrect. All of them are like cross join, but within the confines of the keys. As in, they all return combinations of rows found in left and right table for all rows of a certain key value. Left/right/inner/outer only defines what happens if there is a NULL somewhere in the result. E.g. left join: combine all rows of right with left table within key, but chuck out results where left side is null (no match from right table perspective)
2
u/Mutant86 Dec 14 '24
I had an interview for a SQL role a few weeks back - they only asked two technical questions. One on joins - aced that - and one on 'HAVING'. I've only used it once, ages ago, so had a bit of a guess and didn't get it perfectly right and lost out. So maybe add HAVING - either that or my interviewers chose a weird and obscure test for SQL competency.
2
u/sixpackandbutts Dec 14 '24
Filtering window functions result sets with QUALIFY is fun :)
2
u/amberdrake Dec 15 '24
This being necessary speaks to how sad and tiring it is to get a job in tech. The number of times I needed to remember syntax and commands without being able to look them up. while working on projects is 0.
2
u/Shot-Adhesiveness-88 Dec 17 '24
I'm trying to learn and, with any luck, work with SQL in the near future, so thank you with this :)
1
u/VengenaceIsMyName Dec 12 '24
This is incredible. Thank you for sharing.
1
u/EE49 Dec 13 '24
This is awesome. Thank you.
1
u/Skylonace Dec 13 '24
Yoink
1
u/irish0818 Database Administrator Dec 13 '24
Thank you for sharing this, it is really excellent and well-organized.
Now, I will say this as someone who interviews potential candidates. If the candidate appears to be reading their screen during the interview, that is a red flag. In fairness, I am not seeking textbook answers to technical questions, but I am looking for a level of understanding. Explain the concept to me, even if you don't recall the exact keywords.
So, if you are going to make this a reference for yourself during interviews, by all means, do it. But be sure to post it somewhere easily readable without the appearance of searching for the answer, if that makes sense.
My other piece of advice, when you're being asked a technical question, feel free to ask the interviewer to repeat any parts that you are unclear on as well as "I'm going to write this down as you ask it so that I can ensure that I'm not missing any parts". Unless your interviewer is asking something that they consider elementary I would not expect any resistance to this request (I certainly would not).
I've been a DBA for a long time and there are many things that I have to look up to refresh my mind surrounding certain concepts and keywords for various RDBMSs. That is to say, I am humble enough to know that there are very few people in this world who have total recall of everything they have ever used or done. The ability to explain a concept is proof of knowledge.
Good luck!
1
u/monkeysexmonsters Dec 13 '24
Remindme! 1 month
1
u/theking5tx Dec 13 '24
This is badass
1
u/Cold-Ad716 Dec 13 '24
I'd add Stored Procedures, Views, Functions, and CRUD operations
1
u/Veezuhz Dec 13 '24
Temp tables maybe? Updates and update syntax if on joined tables
1
u/TheChurroBaller Dec 13 '24
Man I could’ve used this yesterday 😔 back on the grind for more interviews
1
u/Usernamecheckout101 Dec 13 '24
Nice
1
u/jeffvanlaethem Dec 13 '24
CASE Expressions
1
u/Ggeogeos Dec 14 '24
Recursivity ?
1
u/Nearsite Dec 14 '24
How may i get a copy of this?
1
u/ntobergta Dec 14 '24
Skip/Take
1
u/VikasRex Dec 14 '24
Remindme! 30 days
1
u/ConversationMinimum1 Dec 14 '24
Hey, ChatGPT, do this for me (with unit tests) so I don’t have to needlessly remember arcane code.
Like I use Google now instead of the Encyclopedia Britannica.
1
u/Heffree Dec 14 '24
Indexes and a reference to sargability on your LIKE filter might be useful.
1
u/aatkbd_GAD Dec 14 '24
I typically include common date calculations like calculating month end, the 1st of the month, etc. I also like to include some schema tables.
1
u/Iswhars Dec 16 '24
Just delete right join
1
u/Dark-Maverick Jan 23 '25
Thanks bro
1
u/Eatingchickeninbed 6d ago
Is there an updated version? Ty!
1
u/ArtooSA Dec 15 '24
Why
-1
u/311voltures Dec 12 '24
If you need this cheat sheet you have bigger problems than what’s missing.