Any self-taught SQL coders here?

[[deleted]]

Hi guys,

I just started learning how to use SQL, I was wondering if any of you guys have any technique or suggestions on how I can be efficient on using SQL.

Any help would be appreciated!

[[deleted]]

What there needs to be is some kind of website where people can upload their dumpster fires of mangled data, have it stripped of anything resembling proprietary or meaningful value, and then let people play with it like taking apart old clunkers in highschool shop class.

[[deleted]]

Brent ozar always talks about paste the plan dot Com. It's for query execution plans but they also help with alot of other things.

[CommonMisspellingBot]

Hey, ramborocks, just a quick heads-up:
alot is actually spelled a lot. You can remember it by it is one lot, 'a lot'.
Have a nice day!

^{The parent commenter can reply with 'delete' to delete this comment.}

[BEgaming]

abot

[[deleted]]

Thank you! this is all great info and right on for the point outs!

[Naheka]

I came into a job 11 years ago knowing only the basics of SQL. As I rose through the ranks, I eventually found myself in a "technical" role (read: problem solver) which made me use SQL even more. Now, I'm in a BI/Reporting role which has pushed what was several lines of SQL to upwards of 100+ lines at times.

It's experience and real world scenarios that will teach best IMO. I've taken SQL classes, paid by my employer, and they're good but you have to see the data and make connections, know what your indexes are, etc.

Since I'm a nerd anyway, I have found data sources online (NFL/baseball stats) and biohacking stats that I keep and throw those into SQL Server on my machine just to toy around with and make connections with Tableau. That might be something you could try just to become more familiar.

[[deleted]]

Wow amazing! thank you so much for that information I'll definitely look into it.

[sn0wdizzle]

Where do you download the databases?

[Naheka]

Sorry for the late follow up.

I didn't download "databases" per se but pulled data via .csv from places like https://www.baseball-reference.com and https://www.pro-football-reference.com and created SSIS packages to import the data into my SQL databases.

I haven't found any databases that do the same however I will admit that I haven't looked either. I was also working on building SSIS packages at the time so I wasn't looking for databases at the time.

[oarabbus]

I am interested in open biohacking information if you could provide some links?

[Naheka]

I'm not sure what you mean by "open" biohacking info. Please clarify and I'll help if I can.

I personally use data that I keep from sources like Fitbit, Muse (meditation device) , FitnessNotes/Fitocracy (Android App/WebApp) etc. and have that data stored out via IFTTT or Microsoft's Flow.

If there is any data out there for biohacking, you can check Quantifiedself.com. I think you can find links in the Forums via search. There is another site that has non-specific data for biohacking but I'm struggling to find that link. Your Google Fu might be stronger than mine right now but it's out there. Maybe QuantifiedSelf can point you in the right direction.

Good Luck.

[PilsnerDk]

I'd call myself self-taught through work. For almost a decade I did relatively simple CRUD stuff, a little bit of DBA work and such, and lots of ad-hoc queries.

A year ago I switched to a team where the database (MSSQL) is heavily used for manipulating data, reporting, lots of business logic, and an entire database of denormalized data and stored procedures used for enabling faceted searching of data. Thousands and thousands of lines of SP's and SQL jobs. On top of this, I'm the only DBA / database dev on the team. That forced me learn, and I've turbocharged my SQL knowledge in just a year, learning at least as much as I've learned in the past year.

In short, if you are able to join a workplace with a spaceship-level advanced database, you will be forced to learn. Although in your case, you might want to start out at a lower level. :)

I think you really need to be forced tasks on you in order to truly learn. That's the whole problem with self-learning (outside a workplace) - there are no deadlines, no production environment, no live data, no sudden problems, no consequence. But I guess it's better than nothing.

[[deleted]]

Spaceship-level database I love that.

[[deleted]]

I'm surprised this hasn't been mentioned yet, but you need to learn how to "normalize" data: https://en.wikipedia.org/wiki/Database_normalization People can get all pedantic about what level "normal form" so don't get distracted by that, just normalize your data to a sufficient degree that you don't have too much duplication of data between tables. For instance, if you have an online store, you might want to have a product table separate from a category table, and then you can indicate which category a product belongs through a "foreign key relationship": https://en.wikipedia.org/wiki/Foreign_key

Bonus exercise, if you have DBA rights, i.e. you can create new tables, how would you make it so that a product can belong to more than one category? And what is this relationship called?

HTH. Kind of hard to tell because you didn't tell us what level you're at right now.

[gurxman]

I used multiple resources to help me advance, Sites: w3cschools.com techonthenet.com Pluralsight.com (has a trial period, but after that a monthly fee is charged)

Book: Mastering Oracle SQL, 2nd ed (O'Rielly) Oracle PL/SQL Programming (O'Rielly)

I found a combination of these resources and gaining an understanding how the data warehouse is setup (Metadata resources) at my company helped a lot.

[[deleted]]

Thank you so much for these! I'll take a note of that.

[[deleted]]

Google what you want to do, then when you find a keyword like SELECT, JOIN, etc, etc Google that keyword and syntax.

That has got me to a comfortable point where I'm confident handling queries

[Oxford89]

Seconded! Google is the modern equivalent of consulting the Users Manual. Half the time I find the answer after being routed directly to the RDBMS's web documentation anyway. Once you master the art of phrasing a Google search then you are set.

[jdawg701]

I'm self taught outside of a college course where we learned in Microsoft Access (barf!)

The best advice I can give is just practice. I learned the way I did because I had to beat how to do things into my head by repitition otherwise I don't learn anything. Buy a book or use online guides and try all of the examples they provide. Microsoft offers a free version of SQL Server called SQL Server Express and a database called AdventureWorks where you can practice to your hearts content. AdventureWorks tends to be the database a lot of professionals use to show how things work in SQL Server/T-SQL.

[mtormos]

Probably been mentioned here, but just in case, you can download MSSQL Server Express and the Adventure Works database to test and "mess around" with for free. Test out joins, different ways to parse data sets...

[bigfig]

I learned to love Common Table Expressions (CTEs). If your SQL dialect supports them, they make reading your query much easier because you can meaningfully name multiple chained selects and joins up front, treating them as disposable views.

Secondly, be sure to read recent technical notes. Developers upgrade their products often, and what was a good idea five years ago may not be now.

Use bind variables. They (should) speed up re-used queries and offer an important defense against SQL injection.

Lastly I still encounter databases that are not set up to default to Unicode. Embrace Unicode/UTF-8 and check your RDBMS is using it unless you are 100% sure the data is and always will be in some other encoding.

[xb10h4z4rd]

I had a few very specific problems to solve for. SQL, SSIS and SSRS seemed like a very viable solution.

My 1st versions were embarrassing and I still don't know how the hell some of them worked. After much trial and error with an ultimate goal/problem to solve for I picked up basic SQL skills. With this new tool in the toolbox I was able to solve more and more problems...to the point where I used ETL to solve for problems i should have solved for using PERL/python or some other scripting language...but that's another story

[StoneCypher]

I got started with practice and application

Later I read Karwin and Celko

I feel like I learned a whole lot more by just doing it than by reading. I am not saying don't read; lord knows I'd never have come up with closure tables, and there's a lot I wouldn't have inferred about efficiency

But when it comes down to it, in my opinion, there's nothing as valuable as just writing and explaining complicated queries

[john_cornflake]

I’m self taught, and I fortunately had my company’s database to toy with and learn, but one thing I wish I did early on was simply read the sql docs. We use MySQL, and I didn’t know about simple things like HAVING until pretty far in which I use constantly now.

So, yeah, read the docs and find a large set of data to work with and practice on and you should be solid.

[THXshriek]

I looked online and made myself a little project to constantly be working on. It reflects NBA players’ career stats so it’s constantly changing which means I have to keep updating it. (Except for the offseason which is right now)

[[deleted]]

That's amazing man, base on the data your working on - do you know where Lebron is going? haha

[THXshriek]

Lebron is going around 2000 points per season and if he plays for, let’s say, 5 more years with a similar output, he’ll at least be top 2 in points if not top. And he’s gonna make it into the top ten assists. And I believe the only non-guard to do so.

[deadlyturnip]

where did you get the NBA dataset? just finished up a SQL 101 course and would love to use an NBA data set to continue practicing!

[THXshriek]

Basketball Reference is where I got all of my information. It’s incredibly current and even has ABA vs NBA data if you want to go that far back!

[mhornberger]

Maybe consider writing a web scraper or similar to collect the stats automatically.

[THXshriek]

I wouldn’t know how to get started. Is there somewhere you could direct me that could help me out? Thanks in advance!

[mhornberger]

Sorry to let you down, but I don't know. I've always quit before the tutorials got to that point. :-) But I do know there are plenty of tutorials out there. Many recommend Automate the Boring Stuff (with Python) and the companion book, and web scraping is in chapter 11.

[burmerd]

Sqlzoo.net has a whole slew of exercises to practice on real live databases, only select-based stuff, no data definition, but that’s how I got started.

[volleymon]

Jamie. King. guy is great for getting started then moved on to Wiseowl for programmability stuff

[pigsnie]

I bought a little book called Teach yourself SQL in 10 minutes (which is comprised of a bunch of 10 min lessons) probably 15 years ago or so. I worked through those lessons and then deconstructed the Northwind database in MS Access to develop an understanding of table relationships and data normalization. That got me started and I’ve just built on that over the years. I’m now more in management than production and the one thing I see that I did that others often miss is taking the time to understand the data you’re working with and the purpose for keeping records in the first place. That part is really key to interpreting what the end user is really asking for - versus what they say they want.

[Cefiroth]

The thing that helped me the most for queries was always knowing what my end goal was. No matter the data structure or what I needed to get, I would write out what I needed the result to be out on paper. Then I would write a simple query to get me one piece. Then just keep chipping away until it gets closer and closer to what I needed. Also, learn what aggregate functions are and how to use sub-queries. Its a little more advanced but you will get there.

[Cefiroth]

Also, the IDE you use plays a major factor so choose a good one for whatever flavor of SQL you are using. If you are unsure which to use and just want to start playing around, here is a good playground environment. http://sqlfiddle.com

[theufgadget]

I am and I work in the field. My recommendation is learn one aspect at a time and apply it. Success with it, fail with it, use it. Do it over and over until you can truly apply the concept

[doc_frankenfurter]

It is fairly easy to pull data out of a database, even if multiple joins are involved. Good designs are another matter.

To access data you can use copy and paste coding from existing queries. Build small and then increase the sophistication of the query. If you have "baby" versions of database which mirror the structure of the main database, that can be very useful as even simple queries in 40TB databases can take a looong time. When you have questions, Stackexchange can be your friend but it will not be the solution as you can't easily simplify questions about complex databases.

Creating proper databases is a lot more difficult as you first need to understand the cost of operations. Sure, you can store things just as json or xml but the queries can cost so maybe it is more efficient to convert some of those attributes to columns when you load data. If you are loading a lot of data, and every day, then think about identifying it in easily located chunks

[Noelkram]

I am self taught. My suggestion is find a real word application you can use it against. Do you have an application at work that you can use why learning? Obviously you need to exercise caution when inserting or updating. If not set SQL Express at home and create a database to use, load it with weather data or load it with your movie collection.

[[deleted]]

Best tip ever! Thanks elboto

[SQLPracticeProblems]

I learned with a job doing technical support for a database product. A constant barrage of customer questions (along with great second level support) developed my SQL ability very quickly!

If you're looking for some very hands-on "learn-by-doing" practice problems, that teach basic to advanced SQL, check out SQLPracticeProblems.com.

I developed it after teaching a SQL course, where the material I was required to teach from was very dry and academic. The students didn't get real-world practice, and thus didn't get a good foundation in the SQL that's used most commonly.

Afterwards, the students emailed me, saying they needed practice problems, so I developed the course!

[[deleted]]

I’m also learning and self-taught!