Billions and billions of rows

December 9, 2005 8:38 AM

Learn SQL by playing inside a galaxy of stars.
posted by Rothko (31 comments total)

You can't tell me what to do
posted by poppo at 8:41 AM on December 9, 2005

That looks pretty nifty, although I haven't tried it yet. I'm not sure how the star display will reinforce your knowledge of SQL, though.
posted by me & my monkey at 8:46 AM on December 9, 2005

I learnt SQL by playing with a lot more than a galaxy.
posted by edd at 8:57 AM on December 9, 2005 [1 favorite]

Cool. Hopefully this will work well for my learning style. Thankya.
posted by sciurus at 9:01 AM on December 9, 2005

I learnt SQL by playing with a lot more than a galaxy.

SELECT object FROM universe 
     WHERE image LIKE 'really cool' 
     ORDER BY right_ascension;

posted by eriko at 9:04 AM on December 9, 2005

This looks neat, and I'm gonna try it. For whatever that's worth.
posted by OmieWise at 9:07 AM on December 9, 2005

This looks good; I'm bookmarking it for later. I've always wanted to learn SQL, but as I've only found dry, complex tutorials out there, I've never bothered.
posted by rolypolyman at 9:08 AM on December 9, 2005

eriko: unless your database is using a case-sensitive collation, I don't think you need to use a LIKE operator there. And even if the universe table is indexed appropriately, that's gotta be a long-running query.
posted by me & my monkey at 9:10 AM on December 9, 2005

Sweet! Like rolypolyman, I've always wanted to learn SQL (along with another half-dozen programming languages....), and this looks like it'll work well with my learning style.
posted by kalimac at 9:10 AM on December 9, 2005

Be warned that it sometimes gives the incorrect reason for an error: it complained "some stars are not of class seven" when my error had been to insert too many stars into a particular table.

(I typed "insert into hilight select starid from stars where starid between 5000 and 15000 and limit 7" when I should have ended with "limit 5". Oh, and never use the misbegotten "between" operator in real work, I just wanted to see if SQLite supported the between operator.)

Also, errors aren't always cleared properly; to get the error cleared, it's sometimes necessary to reexecute the sql more than once.

For a beginning tutorial, the coverage is not incomplete, although the coverage of joins is a bit sparse. To its credit, calculated columns are covered from the outset.
posted by orthogonality at 9:19 AM on December 9, 2005

"insert into hilight select starid from stars where starid between 5000 and 15000 and limit 7"

Er, "insert into hilight select starid from stars where starid between 5000 and 15000 and class=7 limit 7"
posted by orthogonality at 9:26 AM on December 9, 2005

msvcr711.dll tells me that "They" built a SQL parser into this thing? That's pretty kewl. I'm running through the chapters now and it is pretty neat.. I may just throw this out to my younger padwans!
posted by cavalier at 9:29 AM on December 9, 2005

Thanks, I love it.
posted by tcp at 9:31 AM on December 9, 2005

Oh, and never use the misbegotten "between" operator in real work

What's wrong with BETWEEN?
posted by me & my monkey at 9:32 AM on December 9, 2005

eriko: unless your database is using a case-sensitive collation, I don't think you need to use a LIKE operator there.

True, but assuming that it isn't case sensitive is dangerous. God's DBA moves in mysterious ways.

And even if the universe table is indexed appropriately, that's gotta be a long-running query.

;)

To steal a line : "Not my ghoddamned server, monkey boy!"
posted by eriko at 9:40 AM on December 9, 2005

It's full of stars!
posted by Four Flavors at 9:41 AM on December 9, 2005

To steal a line : "Not my ghoddamned server, monkey boy!"

Yeah, but you really don't want to piss off God's DBA. Based on the DBAs I've met, that would be Satan.
posted by me & my monkey at 9:46 AM on December 9, 2005

eriko, thank you, thank you, thank you. I'll have Buckaroo in my head the rest of the day now.
posted by cavalier at 10:02 AM on December 9, 2005

Oh, this is great. I also have been meaning to learn some SQL and this looks perfect. Thank you.

Something slightly similar I've been playing with is this in-browser Ruby tutorial.
posted by thatwhichfalls at 10:19 AM on December 9, 2005

This is awesome. Hopefully it'll help me finally figure out JOINs which I'm always terrible about.
posted by mathowie at 10:42 AM on December 9, 2005

cavalier writes "msvcr711.dll tells me that 'They' built a SQL parser into this thing? "

It's the free embedded SQLite database.


me & my monkey writes "What's wrong with BETWEEN?"

"between" is a ternary operator, the second token of which is "and": "between X AND Y"; but "and" is also used as the binary conjunction operator. Anyone reading your sql is going to initially parse (in his head) the "and" token as the "and" operator, and the beginning of a new sub-expression.

Example "where a between 1 and 2 and b = 3" will initially be read as
"where
a between 1
and 2
and b = 3"

It's just unnecessarily confusing.


mathowie writes "This is awesome. Hopefully it'll help me finally figure out JOINs which I'm always terrible about."

It's all sets. Think of Venn diagrams.

A table is just a set. An unrestricted join of two tables just gives the product of the two sets, so you get you every row in the second table for each row of the first. It's often called the "cross product" or the "Cartesian product", and if you imagine the one set as the x direction on a Cartesian graph, and the second table the y direction, the unrestricted join is just every point on the graph.

Starting from the cross product, you restrict which rows in one table can join which rows in the second table: generally that's done on a shared key: only rows with the same value in the shared key are joined.

An outer join just opens up the restriction (makes it less restrictive) so that you also get back rows in the "outer" table which have no corresponding row in the second, "inner" table. You might do this for example, for user's comments: you'd do the outer join on users, to get all users, even those who had no comments.

Of course, you can also restrict rows based on attribute data, as well as key data: e.g., all users whose names start with 'a'.

Again, it's all just sets. when you're not sure what you need to ask for, draw (or imagine) a Venn diagram. Or imagine the cross-product of N tables as an N-dimensional space, and carve out a partition of that space that contains only the data you want. A two table join is just your standard flat Cartesian graph.
posted by orthogonality at 11:50 AM on December 9, 2005

Wow. Fantastic stuff. I should change my username to SQLGazer.

Edd: That skyserver is awesome as well. Thx!
posted by Skygazer at 11:54 AM on December 9, 2005

Anyone reading your sql is going to initially parse (in his head) the "and" token as the "and" operator

I don't know about anyone. I can see where that might be a problem for some but, I've never had a problem mentally parsing BETWEEN statements. Then again, as a DBA I work exclusively with databases and SQL so I'm probably not the anyone you're referring too so... carry on, nothing to see here.

I do think this looks like an excellent tutorial to pass on to anyone looking to learn SQL, especially a few of the developers I've worked with who could stand to learn some.
posted by srw12 at 12:07 PM on December 9, 2005

SuperProfundo!

Thanks. This solves the biggest problem I've encountered trying to learn SQL: lack of interesting data to play with and a groovy front end to stimulate my reward centers with pretty results.
posted by Freen at 12:10 PM on December 9, 2005

Great link! Thanks so much!
posted by jasper411 at 12:20 PM on December 9, 2005

[BETWEEN is] just unnecessarily confusing.

Unlike srw12, I'm not a DBA, and I find BETWEEN to be less confusing than two separate conditions. It provides more information upon an initial reading than having two separate conditions. SQL seems to be a lot like English in the way that you describe the set of things that you want.
posted by me & my monkey at 1:47 PM on December 9, 2005

Based on the DBAs I've met, that would be Satan.

Yeah. A dethroned sysadmin, but not dumb enough to be a developer. ;)

(I keed, I keed....)

And, hey, if Satan can't figure out to optimize the database allocations based on the image column, his fault. Like we're gonna have "WHERE image LIKE 'really boring'" clauses.

(It does take "select * from universe" to a very odd level.)
posted by eriko at 5:12 PM on December 9, 2005

(It does take "select * from universe" to a very odd level.)

Would a Cartesian product cause a space-time collapse?
posted by me & my monkey at 5:15 PM on December 9, 2005

Oh, and props to orthagonality for his very accurate descriptions of the properties of sets as spoke in SQL joins. The table metaphor is very good.

Of course, you need to know set terminology to understand, but if you're working with databases, you should run out and learn as much set theory as you can. It's what you're doing.


Would a Cartesian product cause a space-time collapse?

Theoretically, yes -- if the output space-time is sufficiently constrained, the result would be a black hole, due to information density.
posted by eriko at 5:20 PM on December 9, 2005

Oh, and props to orthagonality for his very accurate descriptions of the properties of sets as spoke in SQL joins. The table metaphor is very good.

Of course, you need to know set terminology to understand, but if you're working with databases, you should run out and learn as much set theory as you can. It's what you're doing.
posted by eriko at 5:21 PM on December 9, 2005

Whoops. Back button evil. EVIL.
posted by eriko at 5:22 PM on December 9, 2005