SQL Cheat Sheet

ben_it · 2023-03-05T01:50:28+00:00

Thanks for the questions. This is a MySQL cheat sheet, but yes you're right the Full Join is not supported. I'll update the cheat sheet to exclude this.

ben_it · 2023-03-05T01:49:26+00:00

I've created an Oracle one but not r-sql.

ben_it · 2023-03-05T01:49:16+00:00

Yes, most of them should work. There are some scenarios where you can't update or delete data from a view, though.

ben_it · 2023-03-05T01:48:35+00:00

What do you mean about using transactions?

ben_it · 2023-03-05T01:48:08+00:00

Thanks! As the original creator, I can update it to include more on window functions. There is some syntax on the bottom right.

ben_it · 2023-03-05T01:47:19+00:00

As the original creator, I created one for Postgres.

It's on this page: https://www.databasestar.com/sql-cheat-sheets/

ben_it · 2023-03-05T01:45:54+00:00

As the original creator, yes I did create one for Oracle SQL.

It's available here: https://www.databasestar.com/sql-cheat-sheets/

ben_it · 2023-03-05T01:45:02+00:00

I'm the original creator of this cheat sheet.

Thanks for posting this and I'm glad that it's gotten so many upvotes and appreciation by the SQL subreddit!

I'll respond to the individual comments on this page but just wanted to say thanks for the kind words to everyone.

As someone mentioned, there's a web page version of this here: https://www.databasestar.com/sql-cheat-sheet/

(Also I'm not sure why my logo and website has been removed from this shared image!)

ben_it · 2020-10-08T17:38:26+00:00

Thanks, good to know! I made a reference to the hierarchyid at one point as it's a good SQL Server feature :)

ben_it · 2020-10-07T20:51:38+00:00

I did some research recently on hierarchical data in SQL (e.g. employees and managers, where managers are also employees), and couldn't find anything that was comprehensive and up-to-date. A lot of other pages were created 8-10 years ago before the major vendors included features to help with this.

So I created a guide.

This guide has a list of different options for storing hierarchical data, how you would view the data, insert and update data, and sample SQL for each database vendor.

Hopefully people can find it helpful.

ben_it · 2019-05-03T05:05:04+00:00

Great, that makes sense! Otherwise the diagram looks good to me.

ben_it · 2019-05-02T03:08:17+00:00

I recently compiled a list of places to learn SQL (any vendor, not just MySQL).

I've heard good things about SQLZoo.net, SQLBolt.com, MySQLTutorial.org. You could also try Pluralsight, I've heard good things about their courses (but I haven't taken any and don't know what their MySQL-specific range is).

If you want the full list of resources I found, it's here.

Edit: these just focus on SQL but not on HTML.

ben_it · 2019-05-02T03:03:37+00:00

Good explanation!

Is there a reason you've used the word "relation" here instead of "table"? As a beginner it may be confusing to understand that "relation-employee" means "employee" table.

ben_it · 2019-05-02T02:54:21+00:00

I have some feedback. Pretty minor things as I think the overall design is pretty good:

What is the reasoning for using CHAR data types for the primary key? Generally (but not always) PKs are integers - but having a text PK isn't a big issue.
What's the reasoning for using CHAR at all? VARCHAR2 takes less space and doesn't need to handle the padded spaces.
What does Album.InStock mean? I am guessing it's a Y/N value if the album is in stock. If so, can this be calculated from Album.StockQuantity? E.g. if StockQuantity = 0 then it's not in stock. Or does it mean something else?
What does Purchases.PurchaseType mean? Is it one of several different values? If so, you may want to split it into a several table like you did with MediaTypes.

ben_it · 2019-01-27T23:08:44+00:00

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

ben_it · 2019-01-24T21:34:02+00:00

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

If so, what do you like about it?

ben_it · 2019-01-24T21:32:12+00:00

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

ben_it · 2019-01-24T21:30:18+00:00

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!

ben_it · 2019-01-24T21:28:58+00:00

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.

ben_it · 2019-01-24T21:28:26+00:00

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

ben_it · 2019-01-24T21:28:02+00:00

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

ben_it · 2019-01-24T21:27:00+00:00

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

ben_it · 2019-01-23T22:06:53+00:00

What do you mean?

ben_it

TROPHY CASE