Reply to: Learn SQL - Recommendations?

[WTFH]

No self-respecting developer admits to being a developer.

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

[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.

[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.

[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.

[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]

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

Today.

[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 *

[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]

No self-respecting developer is an sql developer.

No self-respecting developer admits to being a developer.

[ladymuck]

I agree with you for anyone who might be wanting to get into development in the future that they need to learn those fundamentals. I don't think OP wants to become a SQL developer, he just wants to be able to query some data as a BA. I doubt he will design any databases so not sure if it's relevant for him in particular.

I do think for someone who just wants to be able to connect to an existing data source and query a table or 2 to pull some data into excel or a data visualisation tool then they can skip this and just move straight onto some basic SQL like the W3schools tutorial posted earlier. I know plenty of people of data analysts who know just enough SQL to be able to pull a table into excel and it works out fine for what they need. Of course if they were to try to design a database they would fail horribly.

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.

[vetran]

I remember using a monitoring tool that looked at the ten worst performing queries of a legal based app, and I had the CIO on my back asking why it was slow.

All (and more besides) were using select *

The company that owned said app would not entertain or even admit that this was the problem.

qh

send them a copy of apexsql refactor. Wildcard expand is great.

[Paralytic]

Where did I say that you said the OP said that?

Now fook off and have a nice weekend.

Whilst you went into Friday evening in a triggered state, I was having a lovely weekend. Thanks

[quackhandle]

A star * ?
No self-respecting sql developer uses stars. Put the names of the columns you want to use you lazy sod

I remember using a monitoring tool that looked at the ten worst performing queries of a legal based app, and I had the CIO on my back asking why it was slow.

All (and more besides) were using select *

The company that owned said app would not entertain or even admit that this was the problem.

qh

[jamesbrown]

A star * ?
No self-respecting sql developer uses stars. Put the names of the columns you want to use you lazy sod

No self-respecting developer is an sql developer.

[GJABS]

select top 10 * from

A star * ?
No self-respecting sql developer uses stars. Put the names of the columns you want to use you lazy sod