Internetworkers: http://www.ibiblio.org/internetworkers/

[Diana Duncan <art2mis AT nc.rr.com>]

Sorry this is so late in coming, but I can't let the technical 
inconsistencies in this go...even though I quite like the analogy, I 
just feel compelled to fix the code.

On Thursday, Oct 9, 2003, at 23:41 US/Eastern, Michael D. Thomas wrote:
<snip>

To play along with this example, each row in our table would represent a
> guitar.
>
> So, let's define some columns for our guitar table. Here are some
> attributes of Guitar:
>
> * name -- the name of the guitar.
> * maker_id -- the id of who made it
> * type -- electric or acoustic?
> * tuned -- Is it tuned?
> * guitar_id -- a random number to uniquely identify the guitar. It's 
> the
> 'primary key' for the guitar.
OK, first, we need to define datatypes for these columns, which Michael 
did further on in his post, so I'm going to put that here for reference.

> CREATE TABLE guitar
>    (guitar_id int primary key,
>    name varchar(100),
>    maker_id int,
>    type varchar(100),
>    tuned char(3));

Let's also quickly define the guitar_maker table as follows:

CREATE TABLE guitar_maker
        (guitar_maker_id        int     primary key,
        name    varchar(100));

The SELECT analogy was all correct, so let's skip down to the UPDATE

> > If you can tune your guitar, that's an UPDATE.
>
> Sure. The tuned attribute is updated from false to true.
>
> UPDATE guitar SET tuned='yes' WHERE guitar_id=2346;
>
> Don't forget the WHERE clause, or else you'll set tuned to 'yes' for 
> all
> of the rows!
Just wanted to point out that Michael is using 'yes' and 'no' here 
rather than actual booleans.  This is because most databases don't 
support boolean as a column type, for some reason.
> > If you can put your guitar back in its case, that's an INSERT.
>
> Um, no. An INSERT inserts a brand new row into the database. Thus, 
> you'd
> use an INSERT when you want to put a new guitar on your rack. Here's an
> insert:
>
> INSERT INTO guitar
>   VALUES ('Les Paul blue and white','Les Paul', 'electric', 'classic',
>
>           false);
So this INSERT statement is inconsistent with the definitions.  To do 
this correctly, we first have to insert 'Let Paul' as a maker, then 
reference the maker_id.  We also need a guitar_id, and what is the 
'classic'?.  Also, as pointed out above, we're not using booleans.  
Also, it is good practice in an INSERT statement to always, always, 
always specify the columns, just in case the column order changes in 
the future.  (It can happen!)  So, to fix this:

INSERT INTO guitar_maker (guitar_maker_id, name)
VALUES (1, 'Les Paul');

INSERT INTO guitar (guitar_id, name, maker_id, type, tuned)
VALUES (1, 'Les Paul blue and white', 1, 'electric', 'no');

> > If you can dash your guitar against a speaker in Townshendesque
> mock-rage,
> > that's a DELETE.
>
> Yup. Remember your WHERE clause or you'll delete everything in your
> table:
>
> DELETE FROM guitar
>    WHERE maker='Harmony';
>
> This deletes all the guitars made by Harmony.

Again, this is inconsistent, as there is no maker column, but there is 
a maker_id column.  To perform this delete correctly, do the following:

DELETE FROM guitar
WHERE maker_id = (SELECT guitar_maker_id
        FROM guitar_maker
        WHERE name = 'Harmony');
>
Anyway, that's enough.  I guess my point is that while SQL is not 
inherently difficult, even experts can make mistakes and it's maybe not 
the simplest thing in the world. :)  I wouldn't be surprised if I made 
a mistake here myself!

Diana