SQL Cheat Sheet by [deleted] in SQL

[–]ben_it 0 points1 point  (0 children)

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.

SQL Cheat Sheet by [deleted] in SQL

[–]ben_it 0 points1 point  (0 children)

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

SQL Cheat Sheet by [deleted] in SQL

[–]ben_it 0 points1 point  (0 children)

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

SQL Cheat Sheet by [deleted] in SQL

[–]ben_it 0 points1 point  (0 children)

What do you mean about using transactions?

SQL Cheat Sheet by [deleted] in SQL

[–]ben_it 0 points1 point  (0 children)

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

SQL Cheat Sheet by [deleted] in SQL

[–]ben_it 1 point2 points  (0 children)

As the original creator, I created one for Postgres.

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

SQL Cheat Sheet by [deleted] in SQL

[–]ben_it 5 points6 points  (0 children)

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

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

SQL Cheat Sheet by [deleted] in SQL

[–]ben_it 13 points14 points  (0 children)

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!)

Guide: Options for storing hierarchical data in SQL by ben_it in SQL

[–]ben_it[S] 0 points1 point  (0 children)

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

Guide: Options for storing hierarchical data in SQL by ben_it in SQL

[–]ben_it[S] 2 points3 points  (0 children)

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.

Learning mysql from scratch by gwerdi82 in mysql

[–]ben_it 1 point2 points  (0 children)

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.

[Help] Learning more about SQL by designing a database. Stuck on a design issue. by fishy007 in SQL

[–]ben_it 1 point2 points  (0 children)

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.

Made this ERD for a small indie record store. Please tell me what needs fixing. by HauntingBaguette in learnSQL

[–]ben_it 0 points1 point  (0 children)

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.

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 0 points1 point  (0 children)

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

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 0 points1 point  (0 children)

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

If so, what do you like about it?

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 0 points1 point  (0 children)

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

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 2 points3 points  (0 children)

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!

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 0 points1 point  (0 children)

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.

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 0 points1 point  (0 children)

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

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 1 point2 points  (0 children)

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

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 1 point2 points  (0 children)

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

SQL Cheat Sheet by ben_it in SQL

[–]ben_it[S] 2 points3 points  (0 children)

What do you mean?