mysql_setup.md

Hey there, Ed!

Thanks for your kind offer on Twitter to look into some of the SQL problems I've been having.

I'd love to take you up on it. I wasn't free last night, won't be tonight, but Wednesday evening, your time, I'll ping you and see if you're available.

Just so you know what you're getting into, here's some context on what I've been wrestling with:

The Source Data

I'm working through SQL Queries for Mere Mortals right now. Really enjoying it. The book includes a companion data so the reader can practice the queries mentioned in the book. Here's the readme for the data. The data itself is in a zip available under the "downloads" section here

I've downloaded and unzipped the files, and it looks like all the same data is available in three formats:

• MSAccess
• MSSQLServer
• MySQL

(There's many overlapping files names between those "formats" or DB structures. I'm not using anything Microsoft related, so it "smells right" to leave the MS stuff alone for now.)

That leaves me with the MySQL files. Here's the structure:

Here's a code snippet (the first ~40 of 250 lines) from 00 BowlingLeagueStructureModifyMy.SQL

CREATE DATABASE BowlingLeagueModify;

USE BowlingLeagueModify;

CREATE TABLE Bowler_Scores (
	MatchID int NOT NULL DEFAULT 0 ,
	GameNumber smallint NOT NULL DEFAULT 0 ,
	BowlerID int NOT NULL DEFAULT 0 ,
	RawScore smallint NULL DEFAULT 0 ,
	HandiCapScore smallint NULL DEFAULT 0 ,
	WonGame bit NOT NULL DEFAULT 0 
);

CREATE TABLE Bowler_Scores_Archive (
	MatchID int NOT NULL DEFAULT 0 ,
	GameNumber smallint NOT NULL DEFAULT 0 ,
	BowlerID int NOT NULL DEFAULT 0 ,
	RawScore smallint NULL DEFAULT 0 ,
	HandiCapScore smallint NULL DEFAULT 0 ,
	WonGame bit NOT NULL DEFAULT 0 
);

CREATE TABLE Bowlers (
	BowlerID int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	BowlerLastName nvarchar (50) NULL ,
	BowlerFirstName nvarchar (50) NULL ,
	BowlerMiddleInit nvarchar (1) NULL ,
	BowlerAddress nvarchar (50) NULL ,
	BowlerCity nvarchar (50) NULL ,
	BowlerState nvarchar (2) NULL ,
	BowlerZip nvarchar (10) NULL ,
	BowlerPhoneNumber nvarchar (14) NULL ,
	TeamID int NULL ,
	BowlerTotalPins int NULL DEFAULT 0 ,
	BowlerGamesBowled int NULL DEFAULT 0 ,
	BowlerCurrentAverage smallint NULL DEFAULT 0 ,
	BowlerCurrentHcp smallint NULL DEFAULT 0 
);

What I'm trying to do

I want to be able to play with the data, to write sample SELECT statements, etc. Ideally, following along with the book. I've been using things it's talking about on existing datasets I have availabe to me already on my machine, but I'd prefer to have the data availble to me to work along with the book.

I've got Postgress and SQLite3 available on my machine, and yesterday installed MySQL, but cannot figure out how to get any of them pointed at these files.

I've tried various things, like "converting" from MySQL to Postgress here

Or hunting all over StackExchange and finding things like this

The problem is, I know so little about Database Administration that I cannot even parse most of their recommendedations.

For example, I don't know what values to use instead of these example files in this command:

pgloader mysql://user@localhost/dbname postgresql:///dbname

The farthest it seemed like I got was following the instructions here, and using psql -f file_name. I did that and get errors like this:

MySQL/SQLScripts  psql -f 00_SalesOrdersStructureMy.SQL
Timing is on.
psql:00_SalesOrdersStructureMy.SQL:1: ERROR:  database "salesordersexample" already exists
Time: 15.593 ms
psql:00_SalesOrdersStructureMy.SQL:3: ERROR:  syntax error at or near "use"
LINE 1: use SalesOrdersExample;
        ^
Time: 0.499 ms
psql:00_SalesOrdersStructureMy.SQL:8: ERROR:  type "nvarchar" does not exist
LINE 3:  CategoryDescription nvarchar (75) NULL
                             ^
Time: 8.926 ms
psql:00_SalesOrdersStructureMy.SQL:20: ERROR:  type "nvarchar" does not exist
LINE 3:  CustFirstName nvarchar (25) NULL ,

So, it seems like PSQL is breaking on the MySQL syntax (Syntax error on line 1 at "USE").

I tried getting this up and running in MySql, and got into all sorts of username/password problems, and then still couldn't get the data loaded up.

Finally, I'm not sure where the actual data lives inside of these files. I see plenty of schemas and database structures, but no actual rows of data. I don't know if that's normal, or I won't need it, or I'm missing something.

So... that's the context. If you have any ideas or know guides that are helpful, I'll take a look, and if you think you might be able to get something going via screenhero, I'd be honored.

Sorry for the brain dump, but I hope this helps show that I'm trying to do, and what I've done.