Newbie question RE: Table creation

In my SQL class, we are using the book, "SQL: A Beginner's Guide: Second
Edition", by Robert Sheldon. However, we are also mostly using MySQL, which
has some differences, it seems, fromt the SQL described in the book.

We are being told, very early in the book, to create a database (actually,
schema) via the following command:

CREATE SCHEMA Inventory AUTHORIZATION Mngr
DEFAULT CHARACTER SET Latin1
CREATE TABLE Artists
( ArtistID INTEGER, ArtistName CHARACTER (20) ) ;

I have already figured out, via the MySQL 5.0 Reference Manual (really nice,
much better than Microsoft's documentation for MS SQL Server Express), that
AUTHORIZATION is (apparently) not a part of MySQL, although I have no idea
what replaces it. So I am eliminating it, leaving me with:

mysql> CREATE SCHEMA Inventory
-> DEFAULT CHARACTER SET Latin1
-> CREATE TABLE Artists
-> ( ArtistID INTEGER, ArtistName CHARACTER (20) );

This, however, also does not work. After looking it up, I thought that
maybe CHARACTER needed to be changed to CHAR, like so:

mysql> CREATE SCHEMA Inventory
-> DEFAULT CHARACTER SET Latin1
-> CREATE TABLE Artists
-> ( ArtistID INTEGER, ArtistName CHAR (20) );

....Nope, no look. Even removing the space between the CHAR and the (20)
didn't work. The error message I am getting is:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that
corresponds to your MySQL server version for the right syntax to use near
'CREAT
E TABLE Artists
(ArtistID INTEGER, ArtistName CHAR(20) )' at line 3

.... each and every time. That's rather generic -- not helpful at all.
Which word, or character, is it having trouble with? Which precise
location? Newbies like me have no idea! Can ANYONE please help me? The
sooner, the better -- I am going to stay up until I solve this stupid
problem, even if it takes me all night!

Oh, and if anyone has some advice on what to use instead of AUTHORIZATION,
that would be helpful, too.

Thank you!

-- Eric

Re: Newbie question RE: Table creation

Earthlink Newsgroups wrote:
> In my SQL class, we are using the book, "SQL: A Beginner's Guide: Second
> Edition", by Robert Sheldon. However, we are also mostly using MySQL, which
> has some differences, it seems, fromt the SQL described in the book.
>
> We are being told, very early in the book, to create a database (actually,
> schema) via the following command:
>
> CREATE SCHEMA Inventory AUTHORIZATION Mngr
> DEFAULT CHARACTER SET Latin1
> CREATE TABLE Artists
> ( ArtistID INTEGER, ArtistName CHARACTER (20) ) ;
>
> I have already figured out, via the MySQL 5.0 Reference Manual (really nice,
> much better than Microsoft's documentation for MS SQL Server Express), that
> AUTHORIZATION is (apparently) not a part of MySQL, although I have no idea
> what replaces it. So I am eliminating it, leaving me with:
>
> mysql> CREATE SCHEMA Inventory
> -> DEFAULT CHARACTER SET Latin1
> -> CREATE TABLE Artists
> -> ( ArtistID INTEGER, ArtistName CHARACTER (20) );
>
> This, however, also does not work. After looking it up, I thought that
> maybe CHARACTER needed to be changed to CHAR, like so:
>
> mysql> CREATE SCHEMA Inventory
> -> DEFAULT CHARACTER SET Latin1
> -> CREATE TABLE Artists
> -> ( ArtistID INTEGER, ArtistName CHAR (20) );
>
> ...Nope, no look. Even removing the space between the CHAR and the (20)
> didn't work. The error message I am getting is:
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that
> corresponds to your MySQL server version for the right syntax to use near
> 'CREAT
> E TABLE Artists
> (ArtistID INTEGER, ArtistName CHAR(20) )' at line 3
>
> ... each and every time. That's rather generic -- not helpful at all.
> Which word, or character, is it having trouble with? Which precise
> location? Newbies like me have no idea! Can ANYONE please help me? The
> sooner, the better -- I am going to stay up until I solve this stupid
> problem, even if it takes me all night!
>
> Oh, and if anyone has some advice on what to use instead of AUTHORIZATION,
> that would be helpful, too.
>
> Thank you!
>
> -- Eric

Eric,

As the manual to which you refer explains, schema and database are
synonyms in MySQL

Try this:

CREATE TABLE `Artists` (
`ArtistID` INT NOT NULL AUTO_INCREMENT ,
`ArtistName` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `ArtistID` )
) TYPE = MYISAM CHARACTER SET latin1 COLLATE latin1_general_ci;

Note I've used INT instead of INTEGER, VARCHAR instead of CHAR, and
I've defined a few other things like NOT NULL, AUTO_INCREMENT and
PRIMARY KEY. Actually, *I* haven't - this is the query automatically
generated when creating your table using phpmyadmin.

As for the permissions stuff, I guess a start would be to look at GRANT
in the manual

Have fun