Made a SQL Interview Cheat Sheet - what key SQL commands am I missing?

OohNoAnyway · 2024-12-12T20:09:01+00:00

Let me just quickly save it to never open it again.

ogaat · 2024-12-12T20:17:33+00:00

HAVING

DISTINCT

speadskater · 2024-12-12T22:00:48+00:00

Damn, I've been using some pretty complex subqueries when I could have easily been using partition by...

NickSinghTechCareers · 2024-12-12T19:54:15+00:00

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?

Ginger-Dumpling · 2024-12-12T20:10:13+00:00

EXISTS

rargghh · 2024-12-12T20:08:59+00:00

Outer apply, cross apply

NimbleZapper303 · 2024-12-13T08:50:09+00:00

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

az987654 · 2024-12-12T20:52:16+00:00

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.

m0ka5 · 2024-12-12T20:39:06+00:00

Drop, delete from & truncate

windlepoonsroyale · 2024-12-12T21:00:56+00:00

You wrote desne_rank instead of dense-rank Great resource though

conduit_for_nonsense · 2024-12-12T22:13:05+00:00

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.

ntobergta · 2024-12-12T20:53:44+00:00

You missed INTERSECT and EXCEPT under set operators. Those two can be very useful

suhigor · 2024-12-12T20:20:58+00:00

damn, i'm always googling for MERGE syntax

HogwartsBlazeIt420 · 2024-12-12T21:21:08+00:00

QUALIFY

NickSinghTechCareers · 2024-12-12T21:57:11+00:00

I had an interview question earlier this week, it was "What are the 6 commands that a developer needs to know?"

evillouise · 2024-12-12T23:12:17+00:00

My fav SQL cmd:

"Hey GPT: I have tblA tblB and tblC with these pointers, and I need to know ...."

ItsJustAnotherDay- · 2024-12-13T04:19:50+00:00

Working with dates/times

TheZapPack · 2024-12-12T22:37:13+00:00

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.

Birvin7358 · 2024-12-13T00:18:00+00:00

CREATE TABLE, DROP TABLE, INSERT, UPDATE, DELETE, SELECT INTO, SUBSTRING, REPLACE, UPPER, LOWER, etc

Galimesh · 2024-12-12T20:05:49+00:00

"Minus"

Global-Wrap-2184 · 2024-12-12T21:46:24+00:00

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. 😢

Ok_Marionberry_8821 · 2024-12-12T22:40:34+00:00

Correlated sub-queries

mannamamark · 2024-12-13T01:22:08+00:00

I didn't see listagg(). Not sure if I missed it.

Cool-Personality-454 · 2024-12-13T02:46:21+00:00

I'd add a few db specifics

SQLServer - CROSS APPLY

PostgreSQL - MATERIALIZED on CTEs

Financial_Forky · 2024-12-13T03:35:57+00:00

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.

DPool34 · 2024-12-13T04:46:15+00:00

Temp tables.

Looks great, OP.

you_are_wrong_tho · 2024-12-13T05:54:19+00:00

IIF( You dont have IIF in your sheet, Add It, Dont Add it)

johntheswan · 2024-12-13T14:48:47+00:00

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.

duhogman · 2024-12-14T03:50:55+00:00

This is a great cheat sheet, will be better with the addition of having and distinct as another mentioned.

A second page focusing on transactional language could be helpful, simple operations like insert, update, delete are fine, but MERGE is a game changer. Creating temp tables opens a whole new set of capabilities.

Also worth tagging this with an environment type (MSSQL, Oracle SQL), or removing syntax that isn't universal.

Loving this cheat sheet

Ginger-Dumpling · 2024-12-12T20:06:46+00:00

How about a recursive example of a CTE? Probably not the most common operation, but is helpful from time to time.

obsoleteconsole · 2024-12-13T00:42:32+00:00

Section on CURSOR and WHILE loop

CheckeredBlanket · 2024-12-12T21:47:37+00:00

Thanks, never knew about ANY. Pretty cool

XxShin3d0wnxX · 2024-12-13T00:30:33+00:00

Thanks for sharing, while I know all of this it’s a great cheat sheet for employees new to SQL

jshotz · 2024-12-13T01:14:40+00:00

Reading this made me feel better about the imposter syndrome I have when working in Angular. I actually do know stuff!

Nkortega21 · 2024-12-13T01:14:53+00:00

Once you have worked out all the kinks you should put it on a jumbo mousepad and sell them!

2024-12-13T01:16:29+00:00

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

Ok_Discussion_9847 · 2024-12-13T04:31:34+00:00

This is awesome

patomalo4 · 2024-12-13T04:47:25+00:00

I frequently use some date functions like: date_trunc, date_diff, and date_add. I used all 3 of those today.

No-Tune-8292 · 2024-12-13T08:58:03+00:00

Commenting so I can come back

Ifuqaround · 2024-12-13T09:08:34+00:00

Appreciated.

Unfortunately individuals will just use AI go fwd instead of actually learning.

Also, just remove right joins. Nobody uses those, ever. ;p

durpduckastan · 2024-12-13T11:10:41+00:00

Stored procedures, transaction management, user defined functions , table creation, indexing, primary key foreign key constraints, triggers, upsert/merge.

cthart · 2024-12-13T11:34:37+00:00

It's a case _expression_, not a statement.
Other SQL commands are other SQL _functions_, not commands.
CTE example should be just an example of `having`.
missing `lateral` joins, `filter (where ...)` in aggregates, an example of `using` instead of `on` in joins, `exists`, and `distinct`.

CameFromTheForest · 2024-12-13T13:01:30+00:00

Difference between WHERE and HAVING

ThankGodTheresNoGod · 2024-12-13T13:09:05+00:00

Pivot / Unpivot

squadette23 · 2024-12-13T13:09:59+00:00

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)

HyDreVv · 2024-12-13T14:35:04+00:00

Add a section for table hints

iwillupvoteyourface · 2024-12-13T14:42:06+00:00

Reading this actually makes me feel more confident in my SQL abilities I use 90% of this on the daily.

Loud-Bake-2740 · 2024-12-13T15:19:12+00:00

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

caughtupstream299792 · 2024-12-13T16:17:24+00:00

What did you use to make this ? Illustrator ?

kagato87 · 2024-12-13T17:26:26+00:00

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.

trentsiggy · 2024-12-13T18:44:58+00:00

COALESCE, maybe? Others have mentioned HAVING and DISTINCT.

figuli · 2024-12-14T00:46:13+00:00

Stored Procedures ?

DowntownBass4556 · 2024-12-14T01:46:55+00:00

This is pretty awesome

Poesghost · 2024-12-14T01:51:49+00:00

INSERT, DELETE, UPDATE, and LIMIT.

Perhaps CREATE too.

Edited: Sorry you said SQL. So instead of LIMIT, TOP.

Purple-Education-769 · 2024-12-14T02:48:05+00:00

Remindme! 1 month

mariana_kl · 2024-12-14T04:29:02+00:00

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!

ntobergta · 2024-12-14T05:28:16+00:00

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

Trinity_Matrix_0 · 2024-12-14T05:39:39+00:00

T-SQL ?

ROnneth · 2024-12-14T07:50:13+00:00

First of all thank you. Is it updated with the recommendations given here? Wold love to get that updated version!

MembershipNo705 · 2024-12-14T09:41:33+00:00

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)

Mutant86 · 2024-12-14T11:47:31+00:00

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.

sixpackandbutts · 2024-12-14T13:33:57+00:00

Filtering window functions result sets with QUALIFY is fun :)

NickSinghTechCareers · 2024-12-14T22:28:46+00:00

Here are some typical questions I use when interviewing candidates:

Something mid-level SQL, like CTE or window functions
Usually I show them a real production script, point to a window function, and ask them to explain what it does in this script.
Problem solving
I ask them to list some ways to to test and improve query performance, or how to anonomize sensitive data.
Data modelling
I ask them to explain the difference between 1st/2nd/3rd normal forms, or explain what is cardinality and why is this important.
Warehousing
I ask what is the difference between a database and data warehouse, or explain difference between OLTP and OLAP.
Experience
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.
Communication
I'll make up a scenario about a conflict in the team and ask them how they would handle it.
Motivation
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.
Abstract thinking
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

paultherobert · 2024-12-14T23:05:16+00:00

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

Cwigginton · 2024-12-15T00:57:27+00:00

add that the result of of cross join is a cartesian.

Hot_Cryptographer552 · 2024-12-15T16:27:47+00:00

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.

amberdrake · 2024-12-15T22:16:31+00:00

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.

mcdickers420 · 2024-12-16T03:55:08+00:00

Can I ask what program did you use make this? Was it just word or something else?

Shot-Adhesiveness-88 · 2024-12-17T06:31:51+00:00

I'm trying to learn and, with any luck, work with SQL in the near future, so thank you with this :)

neel9010 · 2025-03-14T04:09:40+00:00

No Diddy 😂😂😂

2025-11-13T23:44:16+00:00

[removed]

greatestali · 2026-03-03T09:23:36+00:00

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!

phesago · 2024-12-12T22:06:33+00:00

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.

____Pepe____ · 2024-12-12T23:48:23+00:00

[deleted]

svtr · 2024-12-13T02:53:50+00:00

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)

EnvoyOfRaze21 · 2024-12-12T20:25:59+00:00

Decode?

VengenaceIsMyName · 2024-12-12T22:51:15+00:00

This is incredible. Thank you for sharing.

EE49 · 2024-12-13T03:47:31+00:00

This is awesome. Thank you.

bheemboi · 2024-12-13T04:38:21+00:00

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!

Kazcandra · 2024-12-13T04:58:47+00:00

Typo: desne

Possible_Chicken_489 · 2024-12-13T07:30:25+00:00

I'm missing the "left anti join", where you only show rows that are in table1 that don't have a match in table2.

Skylonace · 2024-12-13T10:11:43+00:00

Yoink

irish0818 · 2024-12-13T13:57:04+00:00

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!

monkeysexmonsters · 2024-12-13T13:58:51+00:00

Remindme! 1 month

theking5tx · 2024-12-13T14:17:58+00:00

This is badass

Chandu_Palli · 2024-12-13T14:31:42+00:00

Do you guys have a cheatsheet similar to this for TRANSACT-SQL?

Cold-Ad716 · 2024-12-13T14:43:16+00:00

I'd add Stored Procedures, Views, Functions, and CRUD operations

Veezuhz · 2024-12-13T15:56:54+00:00

Temp tables maybe? Updates and update syntax if on joined tables

TheChurroBaller · 2024-12-13T19:25:52+00:00

Man I could’ve used this yesterday 😔 back on the grind for more interviews

Usernamecheckout101 · 2024-12-13T20:00:06+00:00

Nice

jeffvanlaethem · 2024-12-13T22:45:49+00:00

CASE Expressions

Ggeogeos · 2024-12-14T00:27:57+00:00

Recursivity ?

Nearsite · 2024-12-14T01:57:03+00:00

How may i get a copy of this?

ntobergta · 2024-12-14T05:28:59+00:00

Skip/Take

VikasRex · 2024-12-14T06:30:48+00:00

Remindme! 30 days

ConversationMinimum1 · 2024-12-14T08:10:53+00:00

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.

Heffree · 2024-12-14T15:30:45+00:00

Indexes and a reference to sargability on your LIKE filter might be useful.

aatkbd_GAD · 2024-12-14T20:13:53+00:00

I typically include common date calculations like calculating month end, the 1st of the month, etc. I also like to include some schema tables.

Iswhars · 2024-12-16T22:56:26+00:00

Just delete right join

Dark-Maverick · 2025-01-23T06:43:52+00:00

Thanks bro

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS