Learn SQL - Recommendations?

[LondonManc]

No self-respecting developer is an sql developer.

No self-respecting developer admits to being a developer.

[jayn200]

But understanding how tables are organised in a proper database will help navigate where something has been fully normalised and you have to do multiple joins (and work out which type) in order to get a "sensible" looking output.

What's fully normalized? 3NF? 4NF? Probably most would say 3NF but when you look at databases they're mostly 2NF with a bit of 3NF and some 1NF and some non normalized.

None of it is deliberate by the way.. no one designing these systems says we are going to put x, y, z tables in 2NF and a, b, c tables in 3NF and d, e, f tables are a combination, etc.

When was the last time you looked at a system and saw an address table for example?

Teaching people who only need to query data about normalisation just gets them worrying about theory that isn't consistent in practice and isn't even really applicable to their select query anyway.

[LondonManc]

I think another important thing to understand is the difference between OLTP and reporting databases.
OLTP are designed for getting data in and are almost a table per user screen, while reporting databases have been structured so that data is easier to analyse.

Personally, if you're starting of with databases, I'd recommending downloading and installing MS SQL Server - it's free and easy to install and is very popular with on-premises. If cloud is your thing, then Postgres or MySQL would be more appropriate. All flavours of SQL are fairly similar but have nuances around things like dates and string manipulation.

As others have said, production code shouldn't ever contain SELECT *

[woohoo]

When was the last time you looked at a system and saw an address table for example?

Today.

[jayn200]

Today.

Did you create it?

I have seen it split out probably less than 10% of the time in ERP and CRM systems both off the shelf and bespoke.

Point is they're hardly ever normalized to any consistent standard. Occasionally you will see an ERP system that's mostly normalized to 3NF and it's absolute hell to deal with for any type of data integration and impossible to work with without a full data dictionary.

[woohoo]

Did you create it?

I have seen it split out probably less than 10% of the time in ERP and CRM systems both off the shelf and bespoke.

Point is they're hardly ever normalized to any consistent standard. Occasionally you will see an ERP system that's mostly normalized to 3NF and it's absolute hell to deal with for any type of data integration and impossible to work with without a full data dictionary.

Yep i created it. But its not something that is rare, i dont see a database with an address table and buckle at the knees.

But i take your point.

[ladymuck]

Last client went to a level of overkill on table design that made getting data out very difficult. Over normalised, if such a thing is possible.

My point was less about the nuts and bolts of database design but more about understanding how the design affects the type of join you need to do in order to access the data you need.

[Paralytic]

When I did some DB design in previous life, I used to normalise to Boyce-Codd Normal Form, just because I liked the name.

Well, it was DB design - something had to keep me entertained.

[WTFH]

No self-respecting developer admits to being a developer.

I've developed using C-41 in the past. Does that count?