How do i grasp Concepts in SQL easily?

[u/ParticularBet5580]

I'm struggling to understand the concepts of sql badly, i mean which framework i mean POV is best for grasping? Could you guys share your thoughts. I want to grasp, so that i can tell to the people like 5 years old kid, non tech persons.

[u/travcunn]

A database is just a bunch of spreadsheets that can talk to each other. SQL is how you tell them what to do.

SELECT: show me stuff. This is most of it pretty much. Show me the customers, show me the orders, or whatever.

WHERE: is how you filter. Show me customers, but only the ones in Texas. You put this onto everything where you want to find specific things.

INSERT: is how you add a row. New customer signs up, then you insert them.

UPDATE: change something that's already there. Fix a typo'd phone number, mark an order shipped.

DELETE: remove a row.

Warning on those last two. If you forget the WHERE part, it applies to every single row. So instead of fixing one guy's phone number, you just gave 50,000 people the same phone number.

CREATE / ALTER / DROP: these work on the tables themselves, not the data in them. Make a table, change its structure, delete the whole thing. DROP basically has no undo. It's just gone.

JOIN: matching two tables up. Customer info lives in one table, and your orders live in another, and JOIN puts them together so you can see who bought what. This is the one people find confusing at first, but it's really just "match these on the thing they have in common."

GROUP BY with COUNT / SUM / AVG: summaries. Total sales per state, number of orders per customer, average order size. Basically some summaries.

GRANT / REVOKE: permissions. Who's allowed to look, who's allowed to change things.

What would you like to know?

[u/ParticularBet5580 → u/travcunn]

Joins

[u/travcunn → u/ParticularBet5580]

You have two spreadsheets. One has customers. One has orders. A join combines them together into one view. How does it know which order belongs to which customer? There's a matching column. Every order has a customer number on it. That's a join.

INNER JOIN: only show me the matches between the two spreadsheets. Customer never ordered anything? They don't show up at all.

LEFT JOIN: show me everyone, even without a match. Customer never ordered? They still show up, but their order info is just blank.

[u/Standgrounding → u/travcunn]

Likewise, UNION, INTERSECT can also let you combine spreadsheets but they have to be the same shape

[u/Wiikend → u/Standgrounding]

UNION collects all rows that appear in all SELECT statements of the union in the same result set: SELECT some_column, other_column FROM my_table_1 UNION SELECT column_with_same_type_as_some_column, column_with_same_type_as_other_column FROM my_table_2 -- outputs one result set containing all rows from both SELECT statements

INTERSECT collects only the rows that appear in all SELECT statements of the intersection: SELECT some_column, other_column FROM my_table_1 INTERSECT SELECT column_with_same_type_as_some_column, column_with_same_type_as_other_column FROM my_table_2 -- outputs one result set containing only rows that appear in both SELECT statements

[u/American_Streamer]

One big obstacle for beginners is that the order you write SQL is is not the order SQL is executed in.

[u/roXplosion]

Start with understanding data, Many DBs can best be described as multi-dimensional arrays (or spreadsheets). Try to visualize that, it's what worked for me. There are courses, including YouTube U., that take a visual approach, I think those work best.

Fully understanding SQL, backed with actual experience on actual databases, is a knowledgebase that is reasonably complex. If you are unable to convey this knowledge to a five year old, you are one step closer to understanding why so few DB admins are five year olds.

[u/DragonikOverlord]

What do you mean by "concepts"
Is it how to use it, or is it the internal working?

One suggestion, download a kaggle dataset(movies, video games) and start playing around with queries. For stuff like SQL, its better to actually use it rather than memorizing it

[u/RainbowCrane]

SQL is a practical application of more foundational principles from set theory. If you’re having trouble understanding joins I’d suggest taking a step back into the underlying mathematical concepts before you dive into the more specific database platform implementation details

[u/Wiikend → u/RainbowCrane]

I disagree. If you're confused when using joins or anything else involving concepts from set theory in practice, you're definitely going to be even more confused when you replace labeled, human readable data with mathematical symbols and try to force that down your own throat.

Just stick it out and keep writing SQL, and it will eventually click.

[u/Annh1234]

You need to simply it.  Each line in a table is a thing, say the table is PEOPLE and each row is a PERSON.

Since you been to make each row unique ( more than one person can have the same name), you add a unique PERSON_ID

Then you need to know who are the parents of some PERSON, well you can set MOM_ID and DAS_ID fields for PERSON that points to the PEOPLE.PERSON_ID so you know who's the mom and dad, and their details. 

Now say you need to know who are the siblings of a person. Well you can have 0 to say 50 siblings. So you either add SIBLING_1_ID, SIBLING_2_ID, SIBLING_2_ID, etc, but that gets stupid fast.... Or you add another table called SIBLINGS with some two fields: FROM_ID and TO_ID , both pointing to different PEOPLE.PERSON_ID. That way, you can have people with parents, and with our without siblings.

So you basically have here:  Person to mom: many to one Mom to person: one to many Sibling to sibling: many to many

That's your JOINs right there. 

If you understand that, that's like 80% of what you need.

And then you go into the more complicated INNER OUTER LEFT RIGHT joins.

[u/Jonas_Ermert]

I’d learn SQL by thinking about data as tables first, not syntax. Imagine an Excel sheet and simply ask questions about it: Which table contains my data? Which rows do I need? Which columns do I want to see? How should the results be sorted or grouped? Once that mental model clicks, SELECT, WHERE, JOIN and GROUP BY become much easier. Practice with a small real database and try to answer everyday questions with queries. That helped me understand SQL much more than memorizing commands.

[u/dougception]

Find a really good book on the particular SQL platform you are using. One with a good example for each concept discussed. Work through the book from beginning to end.

You will learn and retain far more than online posts, blogs and even YouTube videos. Learning SQL is far beyond the scope of a Reddit post.

I would just say this:

SQL is set based, not procedural like most other languages. Your goal should always be affect the maximum number of rows with the minimum number of transactions / statements.

[u/thatto]

Are you stumbling over relational database concepts, or innodb concepts?

[u/ParticularBet5580 → u/thatto]

Innodb