Show HN: SQL Teaching – Codecademy for SQL
sqlteaching.com
sqlteaching.com
Amongst other topics, it covers aggregations, joins, grouping and gives a good well-rounded introduction to performance characteristics and best practices. For those wondering "where to go from here", don't look any further and give the course a shot.
[0] - http://www.padjo.org/
I use the Django ORM most of the time, and it is great for quick stuff.
Say I have to do a bulk update, with a few conditions thrown in. MySQL becomes too much hassle trying to parse the first digit out of a string ,and updating another field with that. A simple script using the Django ORM won't take long to do that and will be far easier than trying to work around MySQL's limited string functions.
On the other hand Django's ORM doesn't handle some pretty common cases (conditional aggregates are the first thing that comes to mind). Or complex joins on more than one field. I looks around, and there are ways of hacking these things into the ORM, but they really don't seem worth the bother, when you can use SQL for the query.
Knowing one makes it easier to learn the other, and there's value to understanding the underlying abstraction layer, but you'll avoid using it if you can.
Or use homegrown database wrapper module(s) and keep compile time checking in the vast majority of the code.
In general, the move to SQL was initially painful, but is now a breeze.
With SQL, I don't have to learn an abstraction that is limited to a specific language. So whether I'm working in Java, Python or JavaScript/Node.js, the SQL is the same.
Also, there are numerous technologies that now provide an SQL-like language. For example, learning SQL made it easy to learn CQL (Cassandra), HiveQL, etc.
I would also pay a little more attention to explaining what something is, and why you do it like that. Instead to me it feels more like "copy and paste this command, click run" and so you're not really learning at all. Why SELECT? Why am I using *?
I realise these explanations may come in the later lessons, but from the very beginning I should know what these are (syntax-wise). Then, as things become progressively different I can understand and follow.
I love sites like this, I think they're so beneficial but my main gripes are usually just that: the results, they really need to feel more real-world, and practical-like. Then, I want to know the why/what/how about everything in what I'm doing.
That's just me though. I may be good at SQL, but I'm damn sure I've probably missed a lot of simple things!
Is there a way to sign my email up for updates? I'd like to follow the progress!
EDIT: Ah there is: https://github.com/rhc2104/sqlteaching
SQL Teaching is designed for non-technical people that want to learn the basics of data analysis. Just learning SELECT with WHERE clauses and basic joins get them pretty far.
The Github repo is: https://github.com/rhc2104/sqlteaching , so I guess following that repo is a way of following progress.
Keep up the great work.
I also think beginners need:
- A strong grasp of DISTINCT. This prepares them for the division of tables into subsets by GROUP BY, which is vital to an understanding of how aggregation and window functions operate. ORDER BY can used to show beginners how they can first organize the data, and see how different values in different fields can be used to segregate records into groups.
- WHERE is important but actually not that hard to communicate. The key things about WHERE are one, you can specify true / false expressions whose evaluation dictates inclusion or exclusion of a record (this is crucial to understanding how a JOIN works), and two, you can use IN to compare a record's value to a _list_ of values. Once someone grasps this, it becomes very easy for them to see how a subquery works.
- A strong grasp of how subqueries work. One of the great strengths of SQL is the ability to encapsulate complexity into a subquery and pass that result on to another query for further use.
- A thorough walk-through on "how" JOINs work. What does it mean to JOIN two records? To JOIN two tables? How do we "filter" that join of two tables? (All of this at a _conceptual_ level, taking care to note that how we might think through a complicated join is _not_ how the operation is implemented by the software.) What is the difference between an INNER JOIN and a LEFT JOIN, and how are these differences useful?
- Some discussion of just what exactly SQL is, what it is used for, and what it isn't used for. The tool has to be placed into some context so that beginners are oriented to where they will encounter it and what they can use it for. In particular they need to understand its interface, and the textual / non-visual nature of that interface. Most people are accustomed to working with computers through GUIs. The textual nature of SQL makes it very different, in profound ways, from the vast majority of tools people use on computers.
DISTINCT leads to expensive SORT operations and effectively poor performance.
The use of DISTINCT everywhere smells of denormalized tables. Now if you are in data warehouse or reporting position, then this likely makes sense. I tend to work in transactional applications and keep the redundancy down to an absolute minimum. I abstract away some of the complicated queries with views, procs, and functions where allowable.
As for performance, different rdbms implement this differently, but the general query plan category for GROUP BY and DISTINCT is SORT. If you are querying something where you have multiple subqueries with DISTINCT and you and ordering the final results, you are adding extra sort operations to the query plan, hence hurting performance.
I tend to design everything to optimize read operations because there tends to be much more read than write in systems I work in. For me this means denormalized and heavily indexed where table/index scans are extremely rare. On smaller data sets, some rdbms always scan tables because it saves operations based on table statistics.
I hope this helps.
Been doing SQL for so long now but only recently have I been exposed to how powerful indices and learning how to read the execution plan is.
I realize this is beginner stuff based on a local browser-based SQLite but I wish more learning resources like this and SQLZOO had an advanced section to discuss performance issues you may encounter once you have a large enough dataset. For years I just assumed we just had so much data that having to wait for the query to complete was natural until I tried figuring out how to resolve a deadlock issue we've been having an accidentally figured out how to tune queries to run instantaneously.
There are probably many people out like my former self who know the basics and also have to maintain huge databases.
By the way, if you're one of them, "Use the Index, Luke"[1] is where I learned everything I needed to know about creating indices (though I don't think it has much about reading execution plans).
[0] http://sqlzoo.net [1] use-the-index-luke.com
Additionally, SQLPass puts out a great DVD every year and in the past year or two they had some great talks about how to read xplans, why they are actually lies, and going into things like statistics io, measuring recompile/cpu time, determining if implicit conversions are causing issues, etc etc etc
I googled "execution plan lie" and the only stuff that comes up is regarding how Oracle's Explain Plan sometimes provided inaccurate information. Is that what you were referring to?
I guess explains why Microsoft labelled their version of Explain Plan "Estimated Execution Plan". I mostly just include the actual execution plan when running the query so I can get the real execution plan and the io stats all in one go.
My current process is: 1. set statistics io on; set nocount on; 2. Grab actual execution plan, throwing it in sql sentry plan explorer (free edition) (holy crap good) 3. Finding which estimates are off in the breakdown in 2 4. Investigating specific code areas where estimates are vastly different than actual, fixing whatever issue (implicit conversions, udfs, old stats, etc) arises 5. Consider adding indexes if needed 6. Paste outputs of both stats into http://www.statisticsparser.com/ (you can have it print things as headers, so I like print 'test a' go exec sp_proc @params go print 'test b' go exec sp_proc2 @params ) 7. Compare if I did it right or not by checking overall reads, cpu time, etc
Update: I have the videos in question on my dropbox, I dont know if they would like a public link, so if you dont want to sign up let me know via my email in my profile and I will send them to you.
From my talks with a few of my great programmer friends, it seems this is an area that more people could use some bolstering.
In the first lesson, after the paragraph "Imagine we have a table...", it might help to display an actual table there. I was a bit confused, reading through the whole page and not visually seeing any table. Only after I started writing the command did I realize I had a table underneath the input box.
Overall, looks really good! Not a bad place to start for developers who haven't used SQL before but want to get a simple working knowledge of it.
I've never played it but it looks interesting.
In my point of view, the main answer to "why relational" is that with an rdbms you can answer ad-hoc questions by writing a query rather than writing a program. Even as a programmer, having the power of SQL on the server is really great for analytics.
Furthermore, as an ORM hater, I would say that query results should be processed in your programming language as a table structure rather than converting to specific objects per table. If you're building a CRUD system and the screens update one or two objects/tables at a time, then an ORM is useful, but if you're doing any kind of analytics, any object structure other than a table or list of dictionaries is cumbersome and encourages code over sql which is a bad thing.
Not saying you are incorrect but you made an assertion here. Can you please elaborate?
Also, it's useful having logic baked into the database rather than your application in the case that you need a different application to connect to your database. It's a problem, for example, if you're maintaining your foreign key relationships through ActiveRecords's relations and ignoring it on the server. It makes it much easier for junk data to get in the database.
I agree about rationale, but probably in a different way. For a great many people, especially in the "data analyst camp" (i.e., people coming from finance/consulting background with mind-blowing Excel skills), SQL is not something they want to learn in the vacuum but more like necessary evil. For them, it's hugely valuable to learn how and why SQL can be useful.
http://blog.treasuredata.com/2014/12/05/learn-sql-by-calcula... is my humble attempt to address that issue, and it seems to have resonated well with the data analyst type folks.
Just offering a non-programmer's perspective.
You might want to be a little careful with ensuring an aggressive query timeout. I ran the following:
select * from family_members f1, family_members f2, family_members f3, family_members f4, family_members f5, family_members f6, family_members f7, family_members f8
This took a while to run - I didn't go any further as I didn't want to damage the site during your launch, but add a few more cartesian products in there and it could hurt.
I actually had no idea sql.js existed up until now. Seems like it could be pretty useful.
The site can be slow sometimes, but I have found queries/puzzles that (a) have shown me use-cases for functions and capabilities I have never used and (b) can be challenging! Later they apparently focus more on optimization, but I haven't gotten there. (Especially since they have hundreds of these more difficult queries). I've done about 70ish so far.
What do you guys think about it? Overall, never going to be useful, or a good resource to get better with SQL?
One thing that would be essential is better feedback on errors. Currently, if I enter an invalid SQL query I’m only told that it was incorrect. For beginners, this can be frustrating.
Something like http://blog.codinghorror.com/a-visual-explanation-of-sql-joi... may help with additional explanations.
I also think that things like sargability need to be discussed early on because while sql/set based stuff can be odd to grok at first, knowing why you are doing the things you do is important in laying the right foundations.
Despite the clunky UI, I've found that www.sql-ex.com provides the best medium to tricky problems for those wanting a refresher. (MS T-SQL centric.)
One thing though, any reason you have Mary making 10% less than Dave in your first data set? Seems like kind of a weird thing to include.