###############################################

# run as the MySQL user named root

DELETE FROM	mysql.user
	WHERE   user='';
FLUSH PRIVILEGES;
quit

###############################################

# run as the MySQL user named root

CREATE DATABASE	maxdb;



SELECT		user, password
	FROM	mysql.user;

###############################################

USE maxdb;

# Following needed only if you rerun this script.
# drop table people; # for script only
# drop table stores; # for script only

CREATE TABLE	people (
		name CHAR(10),
		hired DATE,
		store INTEGER,
		hourly BOOL
		);
SHOW TABLES;

DESCRIBE people;

ALTER TABLE	people
	MODIFY	hourly BOOL DEFAULT TRUE;

DESCRIBE people;

# example of an error
# INSERT INTO	people
# 	VALUES ( topsy, '2012/11/01', 4, FALSE);

INSERT INTO	people
	VALUES ( 'topsy', '2012/11/01', 4, FALSE);

INSERT INTO	people (name, store)
	VALUES	( 'percy', 2 ),
		( 'bailey', 2 );

LOAD DATA LOCAL INFILE '/home/max/addfile'
	INTO TABLE people;

SELECT		*
	FROM	people;

SELECT		*
	FROM	people
		ORDER BY name;

SELECT		name
	FROM	people
	WHERE	store = 4;

SELECT		name
	FROM	people
	WHERE	store > 2;

SELECT		name,
		store
	FROM	people
	WHERE	name LIKE '%m%';

DELETE FROM	people
	WHERE	name='bailey'
		OR name='percy';

SELECT		name
	FROM	people;

UPDATE		people
	SET	hourly = TRUE
	WHERE	name = 'sam' OR
		name = 'topsy';

UPDATE		people
	SET	hired = CURDATE()
	WHERE name = 'max';

SELECT		*
	FROM	people;

CREATE TABLE	stores (
		name VARCHAR(20),
		number INTEGER,
		city VARCHAR(20)
		);

INSERT INTO	stores
	VALUES	( 'headquarters', 4, 'new york' ),
		( 'midwest', 5, 'chicago' ),
		( 'west coast', 6, 'san francisco' );

SELECT		*
	FROM	stores;

SELECT		*
	FROM	people, stores
	WHERE	store = number;

# following generates an ambiguous column name error
# SELECT		*
# 	FROM	people, stores
# 	WHERE	name = 'max';

SELECT		*
	FROM	people, stores
	WHERE	people.name = 'max';

SELECT		*
	FROM	people p JOIN stores s
	ON	p.store = s.number;

SELECT		*
	FROM	people p RIGHT JOIN stores s
	ON	p.store = s.number;

SELECT		*
	FROM	people p RIGHT JOIN stores s
	ON	s.number = p.store;

SELECT		p.name,
		city
	FROM	people p JOIN stores s
	ON	p.store = s.number;

SELECT		name
	FROM	people
	WHERE	store =
       (SELECT		number
		FROM	stores
		WHERE	city = 'new york'
       );
