Best way to learn SQL from scratch?

[DezUk]

I am a Business Analyst and believe it or not in my career I have never had the need to use SQL to query databases and pull out data in order to manipulate it and do data analysis. I have a project coming up that will require me to do some user usage stats and this means pulling data/stats from our SQL database. I want to learn how to do this and want to it it to my skill set.

Now I know I can go and watch YouTube videos and such like but its where is the best place to start. Speaking with colleagues in the office I've been advised to pick a topic I am interested in and build a project from there. I love my music and like to Dj in my spare time so I was thinking I could export al;l my music tracks and build some sort of database to then query. My question is what is the best way to go about this and learn SQL from the ground up?

After that I need to move on to how to use excel to manipulate data, using functions like vlookup and picot tables, so any suggestions on that would also be appreciated.

Thanks!

 

[AHarvey]

I'm starting an SQL analyst job next week, starting at the bottom and working up. They know I have limited experience but have said there is no limit to where it can go with them. They also sent me over a few links to get up to speed with things:

https://www.codecademy.com/en/courses/learn-sql

https://www.codeschool.com/courses/try-sql

http://www.sqlcourse.com/

http://sqlzoo.net/


Also, get SoloLearn on your phone. It blasts through the SQL but I find it easier to take in sometimes like that

If you want to learn it hands on, Google "free webspace", pick one, sign up and play around with MySQL and PHPMyAdmin

As for Excel, http://www.homeandlearn.co.uk/ ignore the bad design, the content is good for a basic understanding, then you'll need to move on to googling more "data analysis with Excel" for more indepth stuff.

 

[DezUk]

Thanks for the info mate, much appreciated and good luck with the new job.

 

[HungryHippos]

I tend to learn best by applying a solution to a problem, just googling about and fiddling until I get it right, some of it stays with me, some of it I have to lookup if I need to use it again as the syntax is not simple.

But for starters I'd recommend learning about how to do a few things that are generally useful.

Simple Select statements with field Aliasing, i.e.

SELECT
Field1 As [MyFieldName],
Field2 as [AnotherFieldName]
FROM Table1

Table aliasing.

SELECT
t1.Field1,
t1.Field2
FROM Table1 AS t1

Simple WHERE statements.

SELECT
t1.Field1,
t1.Field2
FROM Table1 AS t1
WHERE t1.Field3 IS NOT NULL

Simple GROUP statements.

SELECT
t1.Field1,
COUNT(t1.Field1) as [Field1Count]
FROM Table1 AS t1
WHERE t1.Field3 IS NOT NULL
GROUP BY t1.Field1

Table Joins. There are several types.

I use INNER JOIN (return only results where both tables have results) or LEFT JOIN/RIGHT JOIN (the same as one another, but reversed. example of LEFT JOIN is all results from Left table of the join, and whatever it can get from the right table that match the join criteria) most often.

SELECT
t1.Field1,
t2.Field1
FROM
Table1 AS t1
INNER JOIN Table2 AS t2
ON t2.id_field = t1.id_field

Data types and when they are used, i.e

varchar/nvarchar - text of specific length
int/tinyint,bigint - integers only
numeric - numbers with decimal places
date/datetime - dates/times
uniqueidentifier - guids

CAST statements. The one below would return a datetime field value (normally yyyy-MM-dd HH:MM:ss) as just the date part (yyyy-MM-dd).

SELECT
CAST(t1.DateTime AS date)
FROM Table1 AS t1

CASE statements.

SELECT
CASE WHEN t1.Field1 > 10 THEN 'BigValue' ELSE 'LittleValue' END AS [Field1Case]
FROM Table1 AS t1

You can do quite a lot with some of these basic principles. Most data querying is just understanding how various tables join together and how you can query for the appropriate joins. You can selectively return fields from one or more of the tables in the query.

 

[touch]

I love my music and like to Dj in my spare time so I was thinking I could export al;l my music tracks and build some sort of database to then query. My question is what is the best way to go about this and learn SQL from the ground up?

That might give a good start at designing databases and creating tables but it's going to be far too simple for writing proper queries.
Ideally, get a copy of the actual database that you're going to be working with and play about with that. If that's not possible, try the NorthWind database - lots of tutorials are based on this.

 

[AHarvey]

That might give a good start at designing databases and creating tables but it's going to be far too simple for writing proper queries.
Ideally, get a copy of the actual database that you're going to be working with and play about with that. If that's not possible, try the NorthWind database - lots of tutorials are based on this.

following on from this, go into the careers subforum in GD, there is a thread in there titled "database qualifications" or something like there, there are links etc to test databases you can practice on.

 

[DezUk]

Thanks for all the suggestions folks, much appreciated

 

[tom_e]

Sql express is free iirc download that get the northwind database and go nuts.

Simple SQL is easy to pick up.

 

[Ergates]

Pedant Mode Initiated

varchar/nvarchar - text of specific length

VARCHAR is for variable length text. CHAR is for specific length.

 

[Spacedeck]

I've just started learning this in my free time.

I'm starting from scratch and so far, I'm finding this (https://www.udemy.com/the-complete-sql-bootcamp/) course pretty decent.

They often have sales on everything, so keep an eye out and I recently bought the course for £10.

 

[HungryHippos]

Pedant Mode Initiated


VARCHAR is for variable length text. CHAR is for specific length.

Yes sorry, I meant that you need to specify the field length on creation, i.e. NVARCHAR(255) is a variable length text field of up to 255 characters long. Obviously the length of the data can be shorter than the field length.

 

[Ergates]

Yes sorry, I meant that you need to specify the field length on creation, i.e. NVARCHAR(255) is a variable length text field of up to 255 characters long. Obviously the length of the data can be shorter than the field length.

 

[HungryHippos]

On a serious note, is there much purpose to really use CHAR over VAR/NVARCHAR? I'd assume even storing say 20 character long strings in a VARCHAR isn't far behind a CHAR in terms of efficiency?

 

[madman045]

Sql express is free iirc download that get the northwind database and go nuts.

Simple SQL is easy to pick up.

Just to add to this, get SQL Server Developer edition, its free and has the same functionality as Enterprise edition, ready for when you get to advanced SQL

https://www.microsoft.com/en-us/sql-server/developer-tools

Also have a look at the free Microsoft training

https://mva.microsoft.com/#data-pros

 

[Ergates]

On a serious note, is there much purpose to really use CHAR over VAR/NVARCHAR? I'd assume even storing say 20 character long strings in a VARCHAR isn't far behind a CHAR in terms of efficiency?

99.9% of the time, no. on paper, CHAR is *fractionally* more space efficient efficient and, reportedly, *fractionally* more processing efficient. However, unless you've fallen through a wormhole to the 1990s, you're not going to find any real-world benefit. E.g. If your pay-roll system isn't hitting it's 8am Monday deadline, then don't start your performance tuning by looking for places you can change VARCHAR to CHAR.

The only places I've ever really seen them used is for flags, where valid data value can only ever be 1 character - and even then it's more of a convention than something that gives a benefit.

 

[OspreyO]

I have always found it easier to learn something trying to solve a problem with data I know well (like music) than I would some demo database which bores me to tears.

It will never be as complex as something designed in the real world where programmers make things complicated for no good reason. But it will get you started.

 

[samcat]

99.9% of the time, no. on paper, CHAR is *fractionally* more space efficient efficient and, reportedly, *fractionally* more processing efficient. However, unless you've fallen through a wormhole to the 1990s, you're not going to find any real-world benefit. E.g. If your pay-roll system isn't hitting it's 8am Monday deadline, then don't start your performance tuning by looking for places you can change VARCHAR to CHAR.

This day and age you should really be using nvarchar/nchar instead of varchar/char... Unicode data is all over the place and we are seeing it happen more and more.. If you want to use a varchar/char just use nvarchar/nchar in its place.

The above learning links are good, but also/after do some delving into query optimisation (such as not using functions in your where clause, good use of indexes, working out why your query is slow), good practices (commenting, code layout, when to use views/stored procedures, temp tables etc) and learn some basic DBA skills.. Should mean that you will be less likely to do things that will upset a grumpy DBA.

A developer edition of SQL is now free, so you have no excuse not to install it at home and start learning today.. And knowing how to setup a database and doing basic server admin stuff is useful. Your day job may not directly call for it (yet), but its very very useful to have the foundations and to understand where the data goes, why certain design choices might have been made etc.

Its a fun job (imo) and as your skills grow there is a HUGE amount of work out there for good SQL people (especially if you have some maths/stats background as well).

(I work as a DBA/Senior Business Analyst for a large insurance company)

 

[snips86x]

This is where I would like to end up being. I've been using SQL for 5+ years now and I love sitting and writing queries. I've not moved into a SQL specific role yet as I feel I have more learning to do - of which I don't feel compelled to do after work as my days are long!

Nice to see some resources being posted though, will be useful for sure!

 

[Ayahuasca]

I've just started learning this in my free time.

I'm starting from scratch and so far, I'm finding this (https://www.udemy.com/the-complete-sql-bootcamp/) course pretty decent.

They often have sales on everything, so keep an eye out and I recently bought the course for £10.

It's on offer again for £10 along with a whole host of other coding courses for the same price.

 

[Badg3r]

I have found some of the free microsoft academy videos pretty helpfull for the basics. This site has some good tutorials as well - https://academy.vertabelo.com/