What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 134 Stack Exchange communities.

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?

share|improve this question
1  
what would be the right place to ask technical questions about the Data Explorer and underlying data? –  Quassnoi May 27 '10 at 11:30
1  
Wouldn't the fact that the data is in flux interfere with learning? Even if we use a "particular" data dump, the sheer variability of the data would make it complicated. –  devinb May 27 '10 at 11:52
    
Ignoring the issue that @devinb pointed out... I can see the huge benefit of learning with a data set that I already know (or at least have a basic understanding of). –  brainimus May 27 '10 at 20:32
3  
@quass you can ask those questions here, just tag them [data-explorer] –  Jeff Atwood May 27 '10 at 21:35
    
What's strange is just before I came across this(like earlier today!) I was thinking there should be a jsbin equivalent for SQL queries. –  Earlz May 27 '10 at 21:49

15 Answers 15

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.

share|improve this answer
3  
perfect! As an experiment, could you try illustrating some of your blog examples with queries constructed in the Data Explorer and let us know how well it does (or doesn't) work? That's definitely the goal here! –  Jeff Atwood May 27 '10 at 11:15
2  
@Jeff: sure! The very next SQL Server entry will be illustrated with SO data. –  Quassnoi May 27 '10 at 11:21
2  
It would enhance readability if all queries were formatted in the same way, perhaps there could be an "AutoFormat" button for Quassnoi's formatting standard –  Andomar May 27 '10 at 16:01
    
Quass: "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)." Im not following this .. can you explain? –  waffles May 31 '10 at 2:09
    
@waffles: SQL is the only way to interoperate with SQL Server. In order to get some data from there, you should write a query. In order to get data in an efficient way, you sometimes should use some tricks, like a recursive CTE instead of DISTINCT, CROSS APPLY instead of JOIN etc. Sometimes you would even need complex TransactSQL batches with variables and flow control etc. Can OData send arbitrary code to SQL Server, make sure it reaches it verbatim and return the results? –  Quassnoi May 31 '10 at 14:37
    
@Quass, see my thoughts on odata here: meta.stackexchange.com/questions/51722/… , odata is just meant as an added bonus, personally I see it as a pointless added bonus cause the data is not live. Also we already have a proper json endpoint to run any query against se sites, so the odata stuff is even more pointless in light of that –  waffles Jun 1 '10 at 5:27

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.

share|improve this answer
1  
TOTALLY AGREE!!! I need to figure out a technical way of integrating this, perhaps add tabs to the grid and a checkbox for show plan? Maybe open a separate topic on this. –  waffles May 27 '10 at 14:17
2  
I have a small perl script to aid writing my blog entries. By default, it outputs the query results, I/O time and statistics and the query plan in SHOWPLAN_TEXT format. It would be a good thing to generate an XML plan for downloading too. –  Quassnoi May 27 '10 at 16:36
    
grr ... i need a proper sql parser to handle a batch seperator ... having a look microsoft actually have one –  waffles May 31 '10 at 1:13
    
related: stackoverflow.com/questions/2940957/… –  waffles May 31 '10 at 1:22
    
status-completed cloudexchange.cloudapp.net/stackoverflow/q/1978/text –  waffles Jun 1 '10 at 5:23
    
@waffles so how did you do it? Seems like it would have been easier (and more discoverable an affordance for users) just to offer a check box and do it internally before you submit the query. –  Cade Roux Jun 3 '10 at 2:58
    
@Cade My implementation is pretty trivial, it just looks for a single GO in a line and treats it as a seperator, sure, its easy to break. But so what. That does not matter that much. I preferred this solution cause it is way more flexible. now we can showplan_xml and set all sorts of other things in the batch. –  waffles Jun 3 '10 at 3:06
    
@Cade also I was trying to demonstrate some of this stuff and it is fairly clear we need a sandbox DB where you can create objects, cause lots of things do not work with table vars (like indexes and so on), if you have any ideas on how to design it (also security wise) can you post a question? –  waffles Jun 3 '10 at 3:08
    
@waffles Will see what I can come up with - biggest problem I can see is cleaning up and quotas. –  Cade Roux Jun 3 '10 at 3:36
    
@waffles here meta.stackexchange.com/questions/52287/… –  Cade Roux Jun 3 '10 at 3:59

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.

share|improve this answer
2  
Very insightful, you know the way this is designed we could just add an additional "Site" on the front page with static clean sample data –  waffles May 27 '10 at 14:35
    
do you think a separate site that allows "write" access to anonUser and sweeps it clean on a regular basis, could do the trick? That way before you ask a sql question on SO you could set up the demo tables on stack explorer and then ask the question pointing to that db. –  waffles Jun 3 '10 at 3:12
    
@waffles - no I don't, because there's no way to set a limiter on the amount of data changed by a single query. It would be real easy to accidentally double the size of your data, and since it's a teaching site it might not even be malicious. –  Joel Coehoorn Jun 3 '10 at 4:00
    
@waffles - this might be something you could on the "standard data sample" i've proposed, though. Perhaps give the ability for each user to create their own private instance of that sample. –  Joel Coehoorn Jun 3 '10 at 4:03

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.

share|improve this answer
    
Creating the custom indexes is a good point. –  Quassnoi May 27 '10 at 11:57
1  
you can create table vars, so update / modify examples are very doable, also we could look at providing access to a custom sandbox / everything goes db –  waffles May 27 '10 at 11:59
1  
@waffles: since you are here: it would be a good idea to show the existing indexes on the tables as well. I was surprised to see that posts.body is not fulltext indexed. –  Quassnoi May 27 '10 at 12:01
    
@Quassnoi, sorry fulltext indexes are not supported on SQL Azure which is a bit of a bummer, but we could look at allowing a Lucene index over the data which could give interesting cross site searches. wrt indexes I think its a great idea to list them, and work together to tune them. –  waffles May 27 '10 at 12:05
    
@waffles - table vars would work, but it's awkward to need to create them –  Joel Coehoorn May 27 '10 at 14:49
    
Can you wrap the whole batch in a begin trans / rollback? Then you could alter tables, insert, etc, and as long as you did your querying in the same batch you'd be ok. –  Blorgbeard May 27 '10 at 23:15
    
@Blorgbeard, that does not work on quite a few levels (locking and security)... someone will type in the query commit delete from posts begin tran –  waffles May 27 '10 at 23:47

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]

share|improve this answer
10  
A picture of Wordsworth. –  Bill the Lizard May 27 '10 at 12:37
2  
Not just an ERD but also an explanation of some of the less-obvious fields. For example, it's not immediately obvious what's inside the denormalized tags field. –  Aarobot May 27 '10 at 13:52
    
note, the denormalized tags field is kind of superfluous since our data dump importer splits it up. Agree on the documentation front, any ideas of how to include better documentation (with circles and arrows) would be very welcome (perhaps open new data explorer topics) –  waffles May 27 '10 at 14:15

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.

share|improve this answer
    
SO is definitely more real-life than Northwind. The only thing it lacks (as Northwind does too) is SPATIAL data that could be queried using spatial abilities. –  Quassnoi May 27 '10 at 11:10
    
SQL (and relational database practice) is probably THE single most portable hard technical skill. I have a problem with widening the scope of "training people" to include too much beyond SQL and relational databases. SQL and the relational model are well-defined, yet people definitely have difficulty with it (hence the "only use LINQ" and "do it in the application camps"). Beyond that, everything gets very more fragmented (and less portable), and it's difficult to see the bang for the buck in terms of audience. –  Cade Roux May 27 '10 at 12:59
3  
@Quassnoi: It also lacks hierarchical data. Some of the things I see people doing/attempting to do with adjacency lists make me weep for the future of humanity. I don't know if it's as common as spatial data, but going by the number of SO questions and Google hits, it's not exactly rare either. –  Aarobot May 27 '10 at 13:55
    
@Aarobot - which relates to a suggestion I have for tags (they sorely need hierarchies in the SQL tags) which I will submit here. –  Cade Roux May 27 '10 at 14:05
    
@Aarobot - There is a suggestion for hierarchy - we get an implementation and data and we're good - meta.stackexchange.com/questions/45438/… –  Cade Roux May 27 '10 at 15:11

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:

share|improve this answer
    
All of these are great points, the sortable grid is a bit of an adventure ... I'm going to work on better schema descriptions tomorrow. So hard to keep track of these bugs as a response to Jeffs post would be nicer to have separate ones –  waffles Jun 1 '10 at 5:31
    
@waffles: Ok, I'll make separate posts. –  Juha Syrjälä Jun 1 '10 at 15:02

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).

share|improve this answer
    
Yes totally, I need to figure out the technical side of this, its a fairly massive change, I think first priority would be some way of getting at the execution plan though –  waffles May 27 '10 at 14:36
    
@waffles - SqlDataReader supports multiple results sets from one .ExecuteReader() call via the NextResult() method. –  Joel Coehoorn May 27 '10 at 14:44
    
@Joel, it more the javascript / display multiple result thing that scares me ... do I make it collapsible, how do I deal with PRINT do I make it tabs and so on ... –  waffles May 27 '10 at 15:06
1  
@waffles: I see multiple results in the JSON, the Messages, and the Text results but not the grid results. Is that not complete yet or did someone miss a checkin? :) –  Jason Punyon Jun 1 '10 at 6:42
    
@Jason, nobody missed a checkin, its just the implementation that is :) should finish it tomorrow –  waffles Jun 1 '10 at 10:30
    
@waffles: 10-4. –  Jason Punyon Jun 1 '10 at 10:51

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 simliar 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 alot messier in terms of data inputs.

SQL comes in flavors though. And the best performing SQL is often specfic 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 changesd 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 commiting 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 avaiable 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 http://lessthandot.com/ 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.

share|improve this answer

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! :-)

share|improve this answer

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)

share|improve this answer

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.

share|improve this answer
    
agree about the anemia to a degree, but as we control this, we could expand and normalize portions or add additional tables if you have any specific needs. additionally you can always do this: cloudexchange.cloudapp.net/stackoverflow/q/1204 –  waffles May 27 '10 at 14:32
    
@waffles - Yes, exactly! Just an online runnable environment is a huge plus. If we could encourage users to post their questons that way, all the better! –  Cade Roux May 27 '10 at 14:51

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)

share|improve this answer
    
ie6 is the spawn of the devil :) I will dig up a VM and do a bit of testing. data types on schema makes total sense (on hover) will have a think about the FK problem, will need to adjust the schema as well as FKs are missing. All queries run are kept in 2 places, in your user page there is full history of everything you did, and there is also a recent query list (which i may end up limiting to saved queries) Im not sure if I can get the DOM to capture F5, will have a look. I could save checkpoints but that is a massive bit of work. –  waffles May 27 '10 at 14:22
    
@waffles - now that I am logged in, it is saving my queries and I was eventually able to wide the columns (probably hovering in not quite the right place). –  Cade Roux May 27 '10 at 15:42

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

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

share|improve this answer
    
Thanks, I sorted out the badge creation date and also just added BountyAmount to the vote table ... which I will pick with the next dump, I deleted the OwnerDisplayName column as its missing from the dump –  waffles May 28 '10 at 21:10

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .