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

["Shea Tisdale" <shea AT sheatisdale.com>]

Personally, I’m enjoying the sql lesson here.  And I’m glad that people who do this for a living, i.e. Michael, Diana and Steve (Steve, you do have mySQL dba down on your resume don’t you?) are posting such informative stuff about this topic.

 

 

-----Original Message-----
From: internetworkers-bounces AT lists.ibiblio.org [mailto:internetworkers-bounces AT lists.ibiblio.org] On Behalf Of DanA Wilson
Sent: Wednesday, October 15, 2003 10:54 AM
To: internetworkers AT lists.ibiblio.org; art2mis AT nc.rr.com
Subject: Re: [internetworkers] mySQL learning curve

 

I think this issue has been beat to death. The original question was about the learning curve of mySQL. Then a friendly little metaphor about guitars and a simplistic view of basic SQL intended to demystify SQL. Then a series of flames about the mistakes in this imaginative guitar based illustration, backed with /corrections/. Now we are discussing the minutiae of SQL ad nauseam.

 

Shall we move ahead with our regularly scheduled programming?

 

dw

 

 

 

 

 

>>> art2mis AT nc.rr.com 10/15/03 10:46AM >>>
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

---
Come and play at the InterNetWorkers Web site!  http://www.ibiblio.org/internetworkers/
You are currently subscribed to InterNetWorkers mailing list
To unsubscribe visit http://lists.ibiblio.org/mailman/listinfo/internetworkers