SPOILER/CHEAT: SQL solution

Nicholas Leippe nick at byu.edu
Fri Mar 10 16:00:02 MST 2006


>   * suppose the only tool you were allowed to use to solve the above
> problem was MySQL. You could not use any of the programming languages or
> the shell - only MySQL command line client connected to a MySQL server.
> Could you do it?

Absolutely--(I'd better be able to, or Sasha would smack me ;) (well, he'd
probably make me run a marathon--that's more his style ;)

DROP TABLE IF EXISTS english_dict;
CREATE TABLE english_dict (
word CHAR(40) NOT NULL PRIMARY KEY
);

DROP TABLE IF EXISTS words;
CREATE TABLE words (
word CHAR(40) NOT NULL,
INDEX(word)
);

LOAD DATA LOCAL INFILE '/usr/share/dict/words' IGNORE INTO TABLE english_dict;
LOAD DATA LOCAL INFILE '/path/to/words/file'   IGNORE INTO TABLE words;

#
#  words in dictionary
#
SELECT
w.word,
COUNT(*) AS count
FROM       words         w
INNER JOIN english_dict ed USING(word)
GROUP BY w.word;

#
#  words not dictionary (not asked for in the problem)
#
SELECT
w.word,
COUNT(*) AS count
FROM       words         w
LEFT JOIN english_dict ed USING(word)
WHERE ed.word IS NULL
GROUP BY w.word


-- 
Respectfully,

Nicholas Leippe
Sales Team Automation, LLC
1335 West 1650 North, Suite C
Springville, UT  84663 +1 801.853.4090
http://www.salesteamautomation.com



More information about the PLUG mailing list