Can we become our own "Northwind" for teaching SQL / databases?

Question

In the Microsoft ecosystem there are some standard databases like Northwind ...

The Northwind Traders sample database contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world.

... that are always trotted out to demo any particular SQL query -- the foo, bar, and baz of database schemas, if you will.

With that in mind, this is what I'd like to do:

1. Use our own creative commons data dump as a standard reference point to teach other programmers SQL
2. Provide an easy, reliable, officially supported web interface to query our monthly creative commons data dumps -- for all websites in our network.
3. Provide an oData interface for those who want it.
4. Let anyone in the world analyze our data with a minimum of effort

We are now working with Sam Saffron (aka Waffles) to build this out in an official capacity as the Stack Exchange Data Explorer:

http://odata.stackexchange.com/

(the URL isn't final; eventually it will live at http://odata.stackexchange.com)

We'd like your feedback on what we've built, with the above goals specifically in mind. Most of all, we want these queries to be ...

• editable
• saveable
• shareable (permalinked)

... in a straightforward way that lets us use our own data to teach and illustrate database queries.

The goal of our sites is learning, and ultimately making the Internet a slightly better place. If the Stack Exchange Data Explorer can help us teach SQL and relational databases by querying the very data we're creating in the process.. this pleases me greatly.

(Also, the code for Data Explorer will very likely be open sourced, once we get a bit further in. So there's a learning and contribution vector there as well.)

How can the current Stack Exchange Data Explorer tool at http://odata.stackexchange.com/ be changed or improved to make it a better reference point for sample SQL queries?

Answer 1

So: can we become our own "Northwind" for teaching SQL / databases?

Having SO dump as a test database would be a huge benefit for any person learning SQL, because:

1. Unlike Northwind, it's real data, not made up. The data are distributed like those in real world.

2. The database can be queried with almost any kind of real-life queries, including composite indexes, fulltext search etc.

I run a blog in which I post solutions to the SQL problems people are interested in. This usually includes generating random sample data to run the queries against.

I just browsed my latest entries and have found that a majority of questions could be as well illustrated with actual SO data instead of lame random tables.

Unfortunately, I'm not familiar with OData so cannot say if it maps well to SQL (which is required for the queries to be efficient). It probably would be a good idea for people to implement custom stored procedures for the common tasks which could be exposed as entry points for any open interface.

Answer 2

We need to be able to see the execution plan!

(Because that's what we always tell users - right after - "It Depends!")

I think if you add a batch separator feature (like GO), users could do:

SET SHOWPLAN_TEXT ON
GO

YourQuery

Or I guess you could do it internally.

Answer 3

I would love to see an officially supported raw data/query site, and my experience with StackQL taught me that training is this area is something developers sorely need.

One of the things about Northwind is that some of the tables are light on actual data. A real company would expect to have a much larger number of certain records, and the Northwind database doesn't have near that volume.

This is not just because it costs money to make up data, but also because there's a real benefit to smaller tables like this. You want to be able to build simple examples on "large" tables that aren't going to have 100,000 results or more come back. It's nice to be able to write queries to demonstrate a concept where the student can verify the results are accurate by eyeballing the data. The SO system has data like that, but only if you go off the beaten path, so to speak.

Another thing about the Northwind data is that it never changes. An example written in 2001 will still return the same data today, and so you can easily show data in a tutorial and know it will still be accurate for your readers later. StackOverflow's data changes every month.

With these points in mind, it might be useful to create something I like to call "The SO Standard Sample" - pull out a subset of the data with a smaller number of posts in it. Perhaps limit it to the posts associated with 50 (highly-connected) users, of which only one or two are on the first page. Of course share the full data as well, but also have this smaller standard sample you can use for building example queries.

This would also be useful in other ways as well: teach how queries scale over time by having a student first build a query vs the sample and then run it on the full current dataset, or by having a smaller set of data that can be more easily moved around and tested with. It would also help with performance issues on the site itself, since we could use that sample site for testing a query before running it on the larger (and therefore slower and more expensive) real data.

Answer 4

A problem could be that you can't really run any queries that modify the data. So examples including inserts or updates can't be tested with the data explorer. Also, using a local copy of the data to test such queries and experiment on them isn't generally a viable option since it requires importing gigabytes of data first.

That the database is rather big also easily makes queries slow. This might be an advantage, because you see what kind of queries have better performance, but might also lead to timeouts for reasonable queries that would just require an additional index to perform better.

Answer 5

A couple of points:

• Widen the vision: don't think of it as teaching "SQL" and "databases" but "persistence". You've already mentioned odata, and that's a good start. Encourage others to think about ways of storing the data in non-SQL databases... Azure storage, perhaps a Protocol Buffer descriptor, Thrift etc. Maybe have sample AppEngine and Amazon EC2 applications?

• Consider whether your schema actually has enough of the common themes to make it a good sample data set. Are you doing "unusual" things which folks should be explicitly warned about? Do you manage without certain common features which would make this an incomplete tutorial?

Neither of these are show-stoppers by any means - just things to think about.

Answer 6

I realize you have the schema slide-out but if you're trying to reach/teach the programmer with little to no SQL experience I'd think a good old fashioned ERD might be in order.

[Insert some saying about pictures and words and worth]

Answer 7

A great idea!

What technologies you are using for building Data Explorer? This is interesting if this thing gets open sourced. Do you use same stuff as with SO? C#, ASP.NET, MVC?

Is meta.stackoverflow.com the right place to report bugs and submit feature requests about Data Explorer?

Anyways, here are couple bugs/feature requests:

• Microsoft is mispelled on page http://odata.stackexchange.com/about (Microsft)
• Links to stored queries should have a slug like SO question links: e.g. http://odata.stackexchange.com/stackoverflow/q/734/how-long-until-i-get-the-generalist-badge
• Column headers on query results should sort the results when clicked
• If column type is numerical or date, it should be right aligned. Looks nicer that way.
• Schema should show descriptions and possible values for fields. Maybe it should even have some kind of entity-relationship diagram for the tables.

Answer 8

I think having real life data is a great idea. I think this is especially helpful to people who are doing web site work directed to a large audience as they can see how someone else solved a similar problem. I'm not sure the data is helpful to people learning to write internal business applications which tend to be much more complex and a lot messier in terms of data inputs.

SQL comes in flavors though. And the best performing SQL is often specific to the vendor. It's true that this comes up mainly at the more advanced stages of learning SQL, but if this is to be a tool that will help in anything other than learning the basics of joining and selecting, how are we going to account for this?

Just brainstorming the problem here. Are we going to create a translation tool? So the learner can write queries in his or her flavor of SQL and they get translated to the SQL the data store uses? This, by the way, would also be extremely helpful for people moving from one platform to another. Think how much easier it would be to learn PL/SQL if you could translate the T-SQL you already know.

Or could we set up small downloadable data stores in different database backends that are populated by an ETL process. Then people could learn against the database they really need to be able to write queries against. We could even provide links to where people could download the free versions of the various vendor implementations (I think MYSQL, SQL Server and Oracle would be the ones I'd start with.)

As far as the question about people learning the SQL to change data, when we are looking to make a change to fix some bad data, we put it in a transaction, write a select to select the records we are going to change, write the delete, insert or update, put the same select after the SQL to change the data and rollback the change. This way you can see the before and after but not actually change the data. Could the button that runs the query be changed so that it adds these elements (or that the query will fail without them)? That way people can see how their change would affect the data without actually affecting it. It also teaches them good habits in terms of learning to check results before committing to the path.

One thing I would like to see is a structured series of tutorials using the data. People don't tend to learn SQL in any structured way and even experienced SQL writers tend to have knowledge gaps. When I was teaching databases, I found that all the available textbooks were poorly designed for learning SQL. They concentrated first on database design which made no sense as you can't design a database until you first have experience querying it. Then they taught SQL in terms of this is how you do an inner join, this is how you do a left join, etc. They teach how to write a cursor, but not when should write one and, more importantly in this case, when you shouldn't. But really people need to be taught SQL (and other languages as well) in terms of this is problem you will encounter, this how you solve it. I'm currently working on a series of Wiki articles at LessThanDot on how to solve various common SQL problems (or design patterns if you will), these could easily be a start for me to create such a curriculum - something I've long wanted to do and now have the time available to do.

Another idea would be to pose different query problems and have a few experts in each database write queries to solve it. We could try to get several solutions per database for complex problems and then have a discussion as to which to chose under which circumstances. If we did this in a private forum where people had to be invited to join, we could work out some top notch solutions and publish them on the training site. Kind of a we pose the problem to the student, then after he submits a solution, he can see what the experts did for the same problem and more importantly what their thought process about their solutions was.

As usual I've babbled on to a ridiculous extent. Stopping now.

Answer 9

Can we add the ability to show multiple result sets?

It isn't very pedagogically smart to just show someone the end result (some giant honking sql statement no matter how well commented it is) and expect them to get it. You have to be able to show someone how to think about the problem in steps and evolve a solution. If we had the ability to show intermediate results as we build up a query it would better enable us to teach.

A way to label the intermediate results would help too (so a reader doesn't just see 10 grids and can't remember which intermediate result they're looking at).

Answer 10

Great idea! I would add a few more points that should be available for TrilogyOverflow to become the Northwind++ of databases:

• we need to have scripts to create an empty schema - if people want to play around with the database, it would be helpful to be able to create an empty database schema on your own local instance

• there should be an arbitrary, but rather small set of data that can be inserted by means of a SQL script - if you create your database from scratch with the above script, you'll probably also want to have a few thousand records in there - probably not 700'000 rows - but a few hundred

• a slimmed down version (possibly the result of the two scripts above) ought to be available as a *.bak file for SQL Server 2005 / 2008 / 2008 R2 so that folks can grab that BAK and restore it and be up and running in no time

Given all this, and with the support of all the Overflowers' help, we can definitely unseat Northwind! :-)

Answer 11

First, let me say that this is a great idea!

However, I see some cons.

The data model might be a bit too anemic, so that invoice header/detail/customer/cost center/product questions, for instance don't have a good corresponding data set.

While it can support a lot of examples, they might need to depart too far from the users' questions. If the user spends some time putting the question into terms of the SO data, that might be a little easier. Sometimes, I don't find it terribly hard to simply implement their question off the bat and give them a runnable answer.

I'll try to use it to answer some questions and get back to you.

Answer 12

Some problems I can think of:

1. This would only work for one SQL Server version. But there are questions for SQL Server 2000, 2005, 2008, and 2008R2.
2. You'd need an equivalent for MySQL, Oracle, PostrgreSQL, SQLite, DB2, etc.
3. Compared to a typical customer - order - orderline database, the StackOverflow database is a niche model. Relations between tags and questions are less intuitive than between orders and customers.

I'll try to answer some questions with this though, maybe it works :)

EDIT: There should be a way to display XML data like you get with the for xml clause. That's very practical to answer these kind of questions.

EDIT2: The SO database is perhaps a bit large for demonstration purposes. Many queries seem to time out, like an aggregate votecount (ASP.NET error when I clicked on perma link button)

Answer 13

It looks like Azure is supporting the more advanced SQL Server PIVOT and XML constructs, so:

SELECT Id, Val, Measure
FROM(
SELECT *
FROM Users
WHERE DisplayName LIKE'%Cade%Roux%'
) AS u
UNPIVOT(Val For Measure IN ([UpVotes], [DownVotes], [Views])) AS unpvt
FOR XML PATH('')

Works!

So here are some feature suggestions (I'm stuck with IE6 at work) (mostly towards the editable side of things):

I wasn't logged in when I started, but when I hit F5 (run query, you know), I lost everything.

Seems to be fixed (I can't widen the columns in the grid view (IE6 issue?).)

Seems to be working once you have logged in (It would be nice to keep all the queries run (or at least some time period), instead of just manually save queries. Teradata SQL Assistant does this as does SSMSToolsPack - it's sorely missed in SSMS but you can obviously save queries in SSMS a lot more easily thaan in this Data Explorer.)

SQL code-formatting - I don't format my SQL code, I always use the defaults in Red Gate SQL Prompt because I don't care about formatting any more - I have decided life is too short to format code, and I pick settings that seem cleanest and always let it format.

Show data types on the schema (perhaps with a hover/tooltip).

Show FK/PK relationships in the schema (I don't need an E-R diagram, but any FK could simply say REFERENCES TableName.PKName)

Answer 14

I just stumbled across this, about 4 years later, and I think it's a fantastic idea. I'm hoping, as I investigate, that the idea has evolved. A few observations:

Northwind is biased in a few directions. It's designed to help show Access in a good light. It has a small amount of data, as has been noted. This can be useful in a tutorial environment. The business rules for Northwind are mostly unwritten. But they are intuitive, if such a business is within your experience.

And Northwind lacks a body of justifications for the design. At least, this was true last time I looked. This body of justifications ("why did they do it that way?") can be enormously helpful in a tutorial.

The read only SQL interface to the underlying SE data is probably designed for OLAP purposes, while the data itself was design with OLTP in mind. This probably affects the design, making it more useful to illustrate some techniques and less useful for others.

Having said all that, I still think it's a fantastic idea. The biggest problem with the thundering herd of database newbies is that most of them want to learn how to design a database first, before getting any experience at writing queries against an existing database that was well built and contains useful and current data. This gives a good solid base to point them at.

It's hard to learn good database design when you haven't seen any good examples. And I recognize that "goodness" is often a matter of religion in design issues.

Anyway, thanks to those who have advance the idea this far.

Answer 15

I found a couple columns which aren't populated in that database:

http://odata.stackexchange.com/stackoverflow/q/1641