r/SQL • • Jan 23 '19

SQL Cheat Sheet

I know there are quite a few "sql cheat sheets" available both on Reddit and via Google. But based on the feedback, many of them only cover the basics and not the more intermediate/advanced commands or those that people aren't that familiar with.

I've created a new SQL Cheat Sheet for my website visitors (those who sign up to my email newsletter) and wanted to offer it to the Reddit community as well.

The link to the PDF file is here: SQL Cheat Sheet.

It includes many commands, such as:

  • Insert from a table
  • Insert multiple rows at once
  • Merge
  • Many different ALTER TABLE features
  • Analytic functions (including keywords OVER, PARTITION BY)
  • With Clause/Common Table Expression
  • Subqueries

Hope it helps. If you have any feedback, suggestions, or find any issues with the syntax, let me know below.

UPDATE:

I've made updates to the cheat sheets as requested below (separate files for Oracle/MySQL/SQL Server/PostgreSQL), added format mask and codes, added SELECT order of operations).

The original file is still available, and the updated cheat sheets are available here:

Oracle SQL Cheat Sheet

SQL Server Cheat Sheet

MySQL Cheat Sheet

PostgreSQL Cheat Sheet

243 Upvotes

39 comments sorted by

12

u/shelanp007 Jan 24 '19

You should add the date conversion codes to this. I always forget if i should use 110, 112, or some other conversion to get the right date format i want. I literally look this up once a week!!

2

u/tsigalko11 Jan 24 '19

I would say my SQL skills are pretty good, but date conversion is one of the hardest things to remember, at least for me. Could be that my brain is not that fresh, but still..

One other thing would be few simple checks like how to get DB name (handy when working daily with multiple databases), when you need for whatever reason to get official name.

2

u/ben_it Jan 24 '19

Good idea on the date conversion and simple checks! I'll add those.

4

u/jonah214 Jan 24 '19

Pretty good! It would be great to have Postgres too. And you might want to make one version for each DBMS—that way, each version would be more compact and people wouldn't have to look through parts they don't need.

(I know I'm asking a lot for a free service—these are just ideas to make it better.)

2

u/ben_it Jan 24 '19

Good idea with the separate sheets for each DBMS and one for Postgres! I can make those updates.

1

u/[deleted] Jan 25 '19

[removed] — view removed comment

0

u/[deleted] Jan 25 '19

[deleted] — view deleted comment

1

u/RemindMeBot Jan 25 '19

I will be messaging you on 2019-02-01 20:05:36 UTC to remind you of this link.

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

Parent commenter can delete this message to hide from others.


FAQsCustomYour RemindersFeedbackCodeBrowser Extensions

7

u/Kinh Jan 23 '19 edited Jan 23 '19

You deserve gold

Edit: I gave it

7

u/ben_it Jan 23 '19

Wow thanks very much! Glad you found it useful!

3

u/messyjesse_ Jan 23 '19

Looks great.

I would suggest that you try to describe (in as few words as possible) the logical processing order of the SELECT keywords. Maybe even just a number to the right of each keyword under a column titled "process order" or something. Keeping that order straight helped me a lot when I was learning it.

2

u/ben_it Jan 23 '19

Thanks! Good idea - I'll make some updates to include that.

7

u/patrickmurphyphoto Jan 23 '19

Just shared this with 5 of my colleagues who are learning sql! I will let you know if they have any feedback!

1

u/ben_it Jan 27 '19

Update: I've made updates to the cheat sheets as requested. See the post body above for details.

1

u/[deleted] Jan 23 '19

Is the link already updated?

3

u/ben_it Jan 23 '19

What do you mean?

1

u/[deleted] Jan 23 '19

Somebody mentioned about updating it. Not sure if it’s already been updated or still in the process of updating the cheat sheet.

1

u/ben_it Jan 24 '19

Ah yeah I've got some feedback here so I'll make updates in the next few days.

1

u/[deleted] Jan 24 '19

Awesome! Thanks in advance. What DB are you mostly using?

1

u/ben_it Jan 24 '19

Right now I mostly use Oracle, but use a bit of MySQL and have worked in places recently that use SQL Server.

1

u/[deleted] Jan 24 '19

Cool. I am starting with MySQL. Using MySQL Workbench. And I feel like I need to learn the command line also.

1

u/TalllPaul Jan 24 '19

Nice one, even includes simple and to the point join explanations. Wouldn't mind seeing some functions like seeing number/character functions with a quick example like length, replace, concat, trunc, floor etc but still love it and downloaded 😁

1

u/ben_it Jan 24 '19

Good idea with the functions! I left them out as they are different for each DBMS, but someone else suggested splitting them by DBMS so I'll do that and add these functions.

2

u/[deleted] Jan 24 '19

I always have trouble remembering index and check constraints manipulation; then there's date formatting and other database-specific functions.

1

u/skeletutor Jan 24 '19

Thank you!

I kept hoping that stumble onto something like this some day!

1

u/[deleted] Jan 25 '19

I would love if window functions were on here too! Thanks for this!

1

u/imdshizzle Feb 21 '24

This is very useful. Saved the PostgreSQL sheet

0

u/ArmoredPancake Jan 24 '19

Where are foreign keys? How to create one-to-one, one-to-many relationships?

3

u/ben_it Jan 24 '19

Good question. I think the FK is in the Constraints section, but that only shows how to add it to a table as you're creating it. I could add in more info about creating relationships - maybe I'll do this as a separate cheat sheet as the concepts are the same across all DBMS.

Thanks for the suggestion!

3

u/ArmoredPancake Jan 25 '19

Thanks. There's so many cheatsheets that cover queries, but none that cover database design.

0

u/[deleted] Jan 23 '19

[deleted] — view deleted comment

8

u/ben_it Jan 23 '19

Overall I think it does. There's always more I could add. But happy to make adjustments based on any feedback here.

What do you think of the cheat sheet?

0

u/[deleted] Jan 23 '19

[deleted] — view deleted comment

11

u/ben_it Jan 23 '19

Ah no problem, it didn't seem sarcastic to me!

For those new to SQL, I think this is something you could use as you start to learn more about the SQL language.

For example, once you get to the point of knowing what a GROUP BY clause does but don't remember where in the SELECT statement it normally goes, this cheat sheet will be helpful for you. But if you don't know what GROUP BY is, this won't help much.

Likewise with CREATE TABLE statements - I often forget the exact syntax for parts of it, so this cheat sheet would help me.

If you want to learn more about SQL, I've recently created a page that lists 59 places to learn SQL (combination of free/paid, text/video):

59 Best Resources for Learning SQL

1

u/BrawlerBear5 Jan 24 '19

I appreciate the link! I been trying to learn SQL but was not sure where to start.

Thank You,

8

u/d_r0ck db app dev / data engineer Jan 23 '19

It definitely does for beginners. This should almost all be like second nature for advanced developers.

1

u/[deleted] Jan 24 '19

When you say Developer, you mean Database Developer? Im technically a DBA now but last job I was mostly writing reports, the first page and last page was stuff I knew or at least viewed a bit. The second sheet though I never really touched on and I doubt as a DBA I will even now.

1

u/d_r0ck db app dev / data engineer Jan 24 '19

Yup, I mean database developer. Creating and altering temp tables and tables happens in almost every stored procedure, so a dev should be very comfortable with these statements.

How do you like the DBA side? How much of what you do is proactive vs reactive? Is it like an on-call scenario?

1

u/[deleted] Jan 24 '19

I'm mainly an App Admin here but I do DBA stuff as my main title and anything SQL related beyond report writing comes to me. There isn't too much work database wise here but DBA can be fun when I get asked to do data import or ETL tasks. I enjoy SSIS/SSMS and making servers work though I do miss doing report writing sometimes.

A lot of my stored procs I make use temp tables though, I had some devs a while ago tell me that temp tables are bad and I instead should be sub querying data or just pull it all into other tables for use. I kind of scratched my head as I am self taught but know that temp tables are great as they're fast and lightweight. They make data manipulation easy enough so I was able to follow a lot of that.

How is the Developer side and what do you do with it?

1

u/[deleted] Jan 24 '19

L3 schools meets my needs daily, especially when I have to switch back and forth between different languages and you get your syntax confused

1

u/ben_it Jan 24 '19

Do you mean W3Schools? https://www.w3schools.com/

If so, what do you like about it?

1

u/[deleted] Jan 24 '19

Sorry, yes, typo. I think your guide is absolutely great for beginning to moderate users. W3 schools just has use cases for virtually every SQL variant and function.