Best resources for learning SQL for database newbies
August 20, 2014 9:06 AM Subscribe
I am making a transition from an Excel-based reporting analyst to a database-oriented role in which SQL will be the primary necessary skill. I have next to zero database experience. I'm looking for resources that would be effective at teaching me what I need to learn. More within…
I'm a reasonably-close to expert-level Excel user with a lot of experience programming in VBA. I've never gotten into high-level math functions of Excel like regression analysis, forecasting, or any calculus-related things, but I pretty much know the ins and outs of the rest. That's my skill set. Other than a little playing around with basic HTML and VERY basic SQL tinkering, I've no other programming experience. I thought that being pretty capable with VBA might making picking up SQL a cinch, but I think my lack of background/familiarity with database theory is making things kind of difficult to conceptualize.
I'm ultimately expected to make my way into the data warehousing world, so SQL is something that I need to develop expert proficiency in. I've seen a lot of options (sql.learncodethehardway.org, sqlcourse.com, New Horizons, etc.), but I'm asking for help identifying which are good for my situation and which might be a waste of time.
Free online stuff would be great, but if there’s a really well-regarded paid course that would be helpful, I might swing it by my bosses and see if my company would spring for it.
Thanks!
I'm a reasonably-close to expert-level Excel user with a lot of experience programming in VBA. I've never gotten into high-level math functions of Excel like regression analysis, forecasting, or any calculus-related things, but I pretty much know the ins and outs of the rest. That's my skill set. Other than a little playing around with basic HTML and VERY basic SQL tinkering, I've no other programming experience. I thought that being pretty capable with VBA might making picking up SQL a cinch, but I think my lack of background/familiarity with database theory is making things kind of difficult to conceptualize.
I'm ultimately expected to make my way into the data warehousing world, so SQL is something that I need to develop expert proficiency in. I've seen a lot of options (sql.learncodethehardway.org, sqlcourse.com, New Horizons, etc.), but I'm asking for help identifying which are good for my situation and which might be a waste of time.
Free online stuff would be great, but if there’s a really well-regarded paid course that would be helpful, I might swing it by my bosses and see if my company would spring for it.
Thanks!
Definitely something like GalaxQL, where you can get results from trying queries in real time, should be on the menu.
The tricky thing about SQL is that your (logically) wrong queries will get results, just not the results you wanted. A typical case would be that you forgot to consider null values in comparisons (the value of the expression "5==null" is null, not false, so you often need to write conditions like "where someColumn <> 5 or IsNull(SomeColumn)" to get the results you want. Seeing how this plays out in a real example is pretty important, imho.
posted by thelonius at 9:25 AM on August 20, 2014 [1 favorite]
The tricky thing about SQL is that your (logically) wrong queries will get results, just not the results you wanted. A typical case would be that you forgot to consider null values in comparisons (the value of the expression "5==null" is null, not false, so you often need to write conditions like "where someColumn <> 5 or IsNull(SomeColumn)" to get the results you want. Seeing how this plays out in a real example is pretty important, imho.
posted by thelonius at 9:25 AM on August 20, 2014 [1 favorite]
Response by poster: Of course I forgot this part...
Follow-up question: I learn best when I can tinker with something on my own, trying new things, etc. I'd really like to be able to fiddle with SQL at home, but, uh... I don't know how. I'd have to host a database on a server (I think?) and have some sort of client. I'm using Aqua Data Studio at work, but that's a $500/yr license. Nope!
posted by JimBJ9 at 9:25 AM on August 20, 2014
Follow-up question: I learn best when I can tinker with something on my own, trying new things, etc. I'd really like to be able to fiddle with SQL at home, but, uh... I don't know how. I'd have to host a database on a server (I think?) and have some sort of client. I'm using Aqua Data Studio at work, but that's a $500/yr license. Nope!
posted by JimBJ9 at 9:25 AM on August 20, 2014
SQLServer Express is free, and runs fine on just about any PC.
posted by SemiSalt at 9:38 AM on August 20, 2014
posted by SemiSalt at 9:38 AM on August 20, 2014
No, you can run a DB on your local machine. MySQL, Oracle Express, SQL Server Express, and SQLite are all free.
posted by thewumpusisdead at 9:39 AM on August 20, 2014
posted by thewumpusisdead at 9:39 AM on August 20, 2014
PostgreSQL is also free and runs pretty easily and has clear tutorials; that's where I've started and I've liked it, plus they have a fun elephant logo. We can be troubleshooting buddies!
posted by Mrs. Pterodactyl at 10:03 AM on August 20, 2014 [1 favorite]
posted by Mrs. Pterodactyl at 10:03 AM on August 20, 2014 [1 favorite]
BTW, SQL comes in dialects. Microsoft SQL is not the same as Oracle SQL which is not the same as xxx SQL. It's obviously easiest to learn the dialect of the ultimate target system. If you want to get a quick look at the differences, look up the book SQL In A Nutshell. It solve various problems in several different dialects. I don't think it's a particularly good text or reference, though.
There is a common ground called ANSI Standard SQL which should run on just about any DB from a major vendor. Most development is done the dialect of the server, however.
posted by SemiSalt at 12:50 PM on August 20, 2014
There is a common ground called ANSI Standard SQL which should run on just about any DB from a major vendor. Most development is done the dialect of the server, however.
posted by SemiSalt at 12:50 PM on August 20, 2014
Response by poster: Of what I've learned thus far, it's all been standard SQL on a mySQL server.
As to what analyses I'm looking to run, that I don't know. What I can say is that my role that I'll eventually be transitioning to in our Business Intelligence / Data Warehouse area will require my becoming extremely skilled at SQL.
As for SQLite, I downloaded that a few days ago when I was following along with a learncodethehardway lesson and it started having me do stuff in command prompt. I don't think that's what I was going for.
posted by JimBJ9 at 1:40 PM on August 20, 2014
As to what analyses I'm looking to run, that I don't know. What I can say is that my role that I'll eventually be transitioning to in our Business Intelligence / Data Warehouse area will require my becoming extremely skilled at SQL.
As for SQLite, I downloaded that a few days ago when I was following along with a learncodethehardway lesson and it started having me do stuff in command prompt. I don't think that's what I was going for.
posted by JimBJ9 at 1:40 PM on August 20, 2014
Best answer: W3 Schools has a decent SQL tutorial that has interactive examples you can run and tinker with online: http://www.w3schools.com/sql/default.asp. I found this to be a good place to start.
If you are using mySQL you can download and install a free version on your own computer. You can also download a sample database that you can use to tinker with from : http://dev.mysql.com/doc/employee/en/
Once you get the basic hang of querying data in the samples, an important topic to learn more about is Database Normalization. Reading up on this will give you a much better idea of how databases are normally designed and why the data is laid out in ways that sometimes seems more complicated then necessary.
There are some mySQL GUI tools that you can download. Its still all lot of typing commands, but a GUI window to type in lets you copy, paste, and edit much better then a command prompt. You might try the lessions you were doing before with one of those tools instead of doing everything at the prompt and see if that works better for you.
posted by nalyd at 5:19 PM on August 20, 2014 [2 favorites]
If you are using mySQL you can download and install a free version on your own computer. You can also download a sample database that you can use to tinker with from : http://dev.mysql.com/doc/employee/en/
Once you get the basic hang of querying data in the samples, an important topic to learn more about is Database Normalization. Reading up on this will give you a much better idea of how databases are normally designed and why the data is laid out in ways that sometimes seems more complicated then necessary.
There are some mySQL GUI tools that you can download. Its still all lot of typing commands, but a GUI window to type in lets you copy, paste, and edit much better then a command prompt. You might try the lessions you were doing before with one of those tools instead of doing everything at the prompt and see if that works better for you.
posted by nalyd at 5:19 PM on August 20, 2014 [2 favorites]
My husband (who really needs to sign up here) asked me to recommend The Coursera/Stanford "Introduction to Databases" class.
posted by tomboko at 7:52 PM on August 20, 2014 [1 favorite]
posted by tomboko at 7:52 PM on August 20, 2014 [1 favorite]
With your Excel background you probably have Access available. It's a good learning tool. By the sounds of your explanation, however, Business Intelligence doesn't really require SQL (lots of non SQL databases coming online) so you might give alternative analytical tools like Tableau a shout on your way by.
posted by ptm at 12:05 AM on August 21, 2014
posted by ptm at 12:05 AM on August 21, 2014
Response by poster: Man, lots of good answers in here.
I've signed up for Stanford's series of Database mini-courses, bookmarked GalaxQL, SQLzoo, W3 Schools, and PostgreSQL, and I'll get to downloading mySQL at home. Prepare for a flood of "WHAT'S WRONG WITH MY CODE?" You know not what you've unleashed.
Seriously, thanks to all.
posted by JimBJ9 at 6:11 AM on August 21, 2014
I've signed up for Stanford's series of Database mini-courses, bookmarked GalaxQL, SQLzoo, W3 Schools, and PostgreSQL, and I'll get to downloading mySQL at home. Prepare for a flood of "WHAT'S WRONG WITH MY CODE?" You know not what you've unleashed.
Seriously, thanks to all.
posted by JimBJ9 at 6:11 AM on August 21, 2014
So for the basics, SQLite is pretty good, but it's not a fully complete SQL engine. It's designed run on embedded systems; one of their clients is a watch company that wants to store alarms and such in a database. It's also not multiuser, meaning only one person can open it at a time. If you want to get into the multiuser DBs, I recommend setting up VirtualBox + Vagrant to have a sandbox to experiment in. Or you can use a Turnkey Linux mysql VM to get going just a bit faster.
There are GUIs for sqlite, but none are very good. LibreOffice comes with Base, which can talk to databases, or run locally on HSQLDB (a competitor to sqlite), and that might be okay. Most developer IDEs also have a 'connect to database' plugin, if you want to go that route. I use Eclipse. If you write VBA in Visual Studio, there's almost certainly a plugin for writing SQL and connecting to DBs.
As far as learning tools, GalaxSQL is pretty good, as is SQLZoo. If you really, really care about query performance, use the index, luke covers SQL indexing. It may not be frequently useful as a business intelligencer, but you may want to bookmark it for when the query you wrote isn't running as fast as you need it to.
posted by pwnguin at 7:50 PM on August 21, 2014 [1 favorite]
There are GUIs for sqlite, but none are very good. LibreOffice comes with Base, which can talk to databases, or run locally on HSQLDB (a competitor to sqlite), and that might be okay. Most developer IDEs also have a 'connect to database' plugin, if you want to go that route. I use Eclipse. If you write VBA in Visual Studio, there's almost certainly a plugin for writing SQL and connecting to DBs.
As far as learning tools, GalaxSQL is pretty good, as is SQLZoo. If you really, really care about query performance, use the index, luke covers SQL indexing. It may not be frequently useful as a business intelligencer, but you may want to bookmark it for when the query you wrote isn't running as fast as you need it to.
posted by pwnguin at 7:50 PM on August 21, 2014 [1 favorite]
« Older Basic n00b info for international container... | How do I get my toddler to stay in bed? Newer »
This thread is closed to new comments.
After that you can dive into more esoteric topics, but I think GalaxQL is way better than anything else out there. If you're on a Mac, you might have to fire up a VM to run it because it doesn't seem to work on Mac anymore.
posted by thewumpusisdead at 9:15 AM on August 20, 2014 [6 favorites]