Sudoku Time
Posted by Stephen Waits Fri, 31 Mar 2006 18:13:02 GMT
3/31 in 3:32.
Lost maps
Posted by Stephen Waits Fri, 31 Mar 2006 17:54:25 GMT
Yes, Lost just got great again. Wow.
Check out some close-ups and decodes of “the map” over at TVSquad.
Buy an Enigma
Posted by Stephen Waits Fri, 31 Mar 2006 17:48:00 GMT
Yes, there is an actual Nazi Enigma on eBay.
Via Engadget.
Understanding JOINs
Posted by Stephen Waits Wed, 29 Mar 2006 06:31:55 GMT
Understanding JOINs
Introduction
As my wife was taking a class on SQL, it occurred to me that some people may use JOINs without understanding how they work. When she began her class, I told her the toughest concept to grasp would be JOINs. Then one day she came home and said that they’d done JOINs that afternoon. They’d spent an hour or two out of a full five day course on one of the most conceptually difficult areas within all of SQL.
I believe understanding how something works makes it much easier to use. So, with that, to help her, and others, I decided to write this little tutorial. It’ll be written the way I came to understand joins.
The Cartesian Product
Forget about JOINs for now. We’re going to learn something else first.
It really all boils down to this. Understand what’s going on here, combined with your knowledge of SELECT statements and WHERE clauses, and you’ll have it. Don’t make it too difficult.
What is the Cartesian Product? It’s every possible combination of each of the rows in two or more tables. For example, let’s say we have two tables, T1, and T2.
T1 T2
== ==
a 1
b 2
c 3
Given that, the Cartesian Product of T1 and T2 is:
T1,T2
=====
a 1
a 2
a 3
b 1
b 2
b 3
c 1
c 2
c 3
Now go back to our definition of the Cartesian Product: every possible combination of the rows in two or more tables. See how the rows in T2 (1, 2, 3) are repeated once for each of the rows in T1 (a, b, c)? That’s the Cartesian Product.
Another example that may be more familiar to you:
Rank Suit
==== ====
A Spades
K Hearts
Q Diamonds
J Clubs
10
9
8
7
6
5
4
3
2
And the Cartesian Product?
Rank, Suit
==========
A Spades
A Hearts
A Diamonds
A Clubs
K Spades
K Hearts
K Diamonds
K Clubs
... (removed for brevity)
3 Spades
3 Hearts
3 Diamonds
3 Clubs
2 Spades
2 Hearts
2 Diamonds
... hint, hint, it's a full deck of 52 cards!
Notice the size of the Cartesian Product? 52. How is that? 13 Ranks, 4 Suits. 13 * 4 = 52. That’s right, it’s just the product of the number of rows in each of the tables involved. This isn’t that important.
A more complex example:
T1 T2 T3
== == ==
a 1 +
b 2 $
The Cartesian Product of more than two tables works just the same way as for two tables.
T1,T2,T3
========
a 1 +
a 1 $
a 2 +
a 2 $
b 1 +
b 1 $
b 2 +
b 2 $
Again, it’s just every possible combination of of each row in all of the tables involved. This time it was 8 rows because each of the three tables only had two rows in it. 2 * 2 * 2 = 8.
A Slightly More Useful Cartesian Product
Now let’s look at the Cartesian Product of two tables that might be a little more useful:
People id name
====== -- ----
0 Susan
1 Frank
Phones person_id phone
====== --------- -----
0 222-3456
1 777-8989
Here we’ve laid out two tables with a single relation. Clearly, we’re trying to represent two people stored in the People table, each with a phone number stored in the Phones table. Susan’s number is 222-3456 and Frank’s number is 777-8989.
And what happens when we mix these two tables up into a Cartesian Product?
people.id people.name phones.person_id phones.phone
--------- ----------- ---------------- ------------
0 Susan 0 222-3456
0 Susan 1 777-8989
1 Frank 0 222-3456
1 Frank 1 777-8989
What do we have here? Just another Cartesian Product. That’s each row from People combined with each row from Phones. Specifically, it’s the 1st row from People with the 1st row from Phones, the 1st row from People with the 2nd row from Phones, the 2nd row from People with the 1st row from Phones, and 2nd row from People with the 2nd row from Phones. Study this table until it makes sense to you!
Now do a JOIN
Here’s the thing. I told you not to think about JOINs, but we’ve actually been doing a JOIN this whole time. That’s right. The Cartesian Product is the result of JOINing two or more tables together. It’s actually a “join” in the most literal sense of the word. In fact, we got that combination (Cartesian Product) of People and Phones earlier by using this very SQL statement:
select * from people,phones;
Notice how we selected everything from two tables? Side note: this raw Cartesian Product is technically a “Cross Join”, but don’t worry about that for now. As we’ll see later that there are several types of joins.
Unfortunately, this JOIN is not that useful. It’s clear by looking at the People and Phones table that Susan’s phone number is 222-3456 and Frank’s phone number is 777-8989. But since the raw Cartesian Product (or a “Cross Join”) combines every row in one table with every other row in another table, we’re getting all kinds of bogus unrelated rows in our select.
How can we make it more useful? Let’s look at the data again. Try to pick out the related rows when you look at the data. In other words, which rows do we care about?
SELECT * FROM people,phones;
people.id people.name phones.person_id phones.phone
--------- ----------- ---------------- ------------
0 Susan 0 222-3456
0 Susan 1 777-8989
1 Frank 0 222-3456
1 Frank 1 777-8989
How will we tell the database server to keep the related rows and throw away the unrelated rows for us?
Well, just like most other SELECT statements, we need to add a WHERE clause to tell it exactly which rows we care about. Which rows do we care about in this case? Take a look at the result; we want the rows where “people.id” is the same as “phones.person_id”. In other words, just the rows that make sense. Let’s add that to our statement and run it again:
SELECT * FROM people,phones
WHERE people.id=phones.person_id;
people.id people.name phones.person_id phones.phone
--------- ----------- ---------------- ------------
0 Susan 0 222-3456
1 Frank 1 777-8989
Do you see what we did? We picked a few rows out of the (potentially huge) Cartesian Product. That’s all there is to it. Important Note: This is an INNER JOIN. It’s the alternate syntax and it’s the easiest to understand.
Now let’s make our INNER JOIN even more useful by only specifying the columns we care about in our SELECT statement:
SELECT people.name,phones.phone FROM people,phones
WHERE people.id=phones.person_id;
people.name phones.phone
----------- ------------
Susan 222-3456
Frank 777-8989
Now we’re getting somewhere! All we did this time was choose to only select the columns “people.name” and “phones.phone” instead of “*”.
Before we move on, we can do one more thing to this SELECT to clean it up a bit. In this case, we’ll use some aliases to shorten up our table names and make it a little easier to type
SELECT ppl.name,ph.phone
FROM people AS ppl,phones AS ph
WHERE ppl.id=ph.person_id;
In this case, we simply aliased “people” to “ppl” and “phones” to “ph”. This just makes it a little easier to read and edit.
Syntax
Most SQL programmers like to format their complex SELECT statements a little more nicely than all strung out on a single line. Our SELECT statement from above, when formatted nicely ends up looking like this:
SELECT ppl.name, ph.phone
FROM people AS ppl, phones AS ph
WHERE ppl.id=ph.person_id;
The INNER JOIN syntax we used here is implicit. See how the words INNER and JOIN never appear in this statement? That’s because this is an alternate syntax. In fact, it’s important to realize that the INNER JOIN above is exactly the same as the following INNER JOIN:
SELECT ppl.name, ph.phone
FROM people AS ppl
INNER JOIN
phones AS ph
ON ppl.id=ph.person_id;
This is the official syntax; however, the first syntax is widely accepted. I personally prefer the first, simpler syntax for INNER JOINs.
The most important thing to remember, though, is that these two are identical statements.
More JOINs
There’s actually a little more to JOINs than the basic INNER JOIN. However, it’s very important to remember that they’re all basically variations on this simple JOIN (aka Cartesian Product). In general, the JOIN builds up a huge wad of combined rows, and the WHERE trims it down to just the few rows you’re interested in.
This is the exact same process we followed earlier. Use the JOIN to build up a bunch of data, and use the WHERE to pull out only the useful data.
INNER - The intersection of two tables; a “default” JOIN.
LEFT OUTER - Every row in the “left” table, plus the right table, filling in empty right rows with NULLs as needed. This goes beyond the Cartesian Product because it will join in NULL rows that don’t really exist.
RIGHT OUTER - Like LEFT OUTER, but every row on the “right”, filling in NULLs on the “left”.
For more details on each JOIN type, please see the JOIN entry at Wikipedia.
Summary
JOINs are pretty simple. The actual JOIN itself builds up a large chunk of rows. Many of those rows won’t be useful at all. You use the WHERE clause to pull out only the rows that make sense and that you’re interested in.
The INNER JOIN, the most common, is always a subset of the Cartesian Product. The LEFT OUTER and RIGHT OUTER JOINs take that a little farther, but not much.
I hope I’ve helped you better understand JOINs.
Craftsmanship
Posted by Stephen Waits Sat, 25 Mar 2006 20:12:09 GMT
Holy crap, I don’t think I’ve ever seen such craftsmanship. Take a look at this Enigma-like device. Check out all the pictures of it. Amazing stuff. Look at the other works by this artist here.
Via Schneier on Security.
Sudoku Time
Posted by Stephen Waits Fri, 24 Mar 2006 15:32:22 GMT
Finally a hard one! 3/24 in 10:50.
My first Ruby Quiz!
Posted by Stephen Waits Fri, 24 Mar 2006 14:52:10 GMT
I’m so excited. Today’s my birthday, and I woke up to see my first Ruby Quiz get published!
sCrAmBlEd? HaCkZ!
Posted by Stephen Waits Fri, 24 Mar 2006 04:08:05 GMT
Wow.. This is some serious DSP-fu. Check it out: direct or on YouTube.
Via hack a day.
Black Person, Magic Wand, Emacs
Posted by Stephen Waits Thu, 23 Mar 2006 04:17:00 GMT
Three movies..
Shockerz pull off miracle Tie!
Posted by Stephen Waits Tue, 21 Mar 2006 18:33:00 GMT
Last night was a crazy game. First off, it was against the team with the punk kid who was punching me in the head last time we played. They’re in 4th (Mustangs) and we’re in 7th.
At halftime we had them 3-0! We were playing the best we’ve ever played.
But, in the second half things started to go bad. Within 5 minutes it was 3-3, and before we knew it we were down, 4-5.
With just over two minutes left I switched us up to 3 O, 1 D, and immediately they got another one, making it 4-6. Shit.. I thought, bad move. But we stuck with it. We pulled PJ and put a ton of pressure on them, and Skinner sails a 30’ wrist right by the goalie!
Ok.. 5-6 now, reset. Face-off with less than a minute, pull PJ, Kydd goes out, more pressure, pressure, pressure, we’re counting down the seconds, 5, 4, 3, and dink, Eddy puts one in with 0.2 left for the TIE!!!
We were stoked, to say the least!
MMO Boggle
Posted by Stephen Waits Mon, 20 Mar 2006 04:58:24 GMT
Ajax-powered Boggle played against… the entire Internet….
Via O’Reilly Radar.
What the Whitehouse doesn't want you finding in Google or Yahoo
Posted by Stephen Waits Sun, 19 Mar 2006 04:36:00 GMT
While searching for some information on the robots.txt standard, I ran into an interesting link. See, robots.txt is a standard file web admins can put on their site to help instruct search engines on what they should index. Search engines don’t have to pay attention to it - but, of course, the major ones do follow it. Back in the day an indexer’s crawl could damage your site - but those days are gone. It’s all about attempting to control what gets indexed, not how much.
So, you can find out the pages that a web admin does not want indexed in a search engine by checking out their robots.txt file.
For example, take a look at one of my site’s robots.txt file here. In this case I’m happy to have the SE’s crawl and index my entire site.
Now, check out whitehouse.gov’s robots.txt file. These are all the URLs they don’t want you finding in Google or Yahoo. They don’t mind if you find them, mind you, but it’s interesting nonetheless.
Another small Lisp
Posted by Stephen Waits Thu, 16 Mar 2006 17:03:26 GMT
In digging around Hedgehog, I found a mention of Pico Lisp. I don’t know if either meet my needs, but I’m definitely interested.
Fight the BS
Posted by Stephen Waits Thu, 16 Mar 2006 05:07:33 GMT
When I write an email I care about, I like to reread it several times before sending it. On each pass I’ll usually trim sentences, or clarify sentences. The idea is to maximize the message and minimize the amount of text. People won’t read messages which are more than a few lines long. Seriously.
Enter, Bullfighter. I haven’t used it, but it looks really cool. And it’s free!
Via LifeHacker.
Wow. A full Lisp on top of Python.
Posted by Stephen Waits Wed, 15 Mar 2006 17:12:07 GMT
This is impressive. lython puts a Lisp syntax on top of Python.
Via Lemon Odor.
Tracking down DDoS idiots
Posted by Stephen Waits Sun, 12 Mar 2006 19:26:25 GMT
Here is a long article about a kid from Sacramento who tracked down some DDoS attacks. But it’s much more than that. Read it if you’re into compsec. I enjoyed it.
Opera still best..
Posted by Stephen Waits Sat, 11 Mar 2006 23:47:40 GMT
Opera 9 now passes the ACID2 test! I’m anxious for this to get released.
If you aren’t using Opera yet, you’re shortchanging yourself. Nothing’s faster. And it’s as compliant as its competition, usually more.
Download it today.
Office microwaves everywhere now in danger!
Posted by Stephen Waits Sat, 11 Mar 2006 23:29:47 GMT
Light a toothpick. Stick it in the microwave. Turn on.
Via reddit.
Fucking Windows
Posted by Stephen Waits Fri, 10 Mar 2006 20:43:08 GMT
You know how explorer.exe sometimes won’t let go of a file that you want to delete? So you have to fucking reboot and then delete it? Which then, of course, interrupts everything you’re working on.
Fucking Windows!
Well check out the Unlocker. I just installed and used on my system and it worked like a charm!
All the pinouts you can stand
Posted by Stephen Waits Fri, 10 Mar 2006 16:24:13 GMT
Looking for pinouts? Look no further than pinouts.ru.
Via Simpy.
Older posts: 1 2