db.sql

DROP DATABASE IF EXISTS GROUP_PROJECT_2014;
CREATE DATABASE GROUP_PROJECT_2014;
USE GROUP_PROJECT_2014;
CREATE TABLE Team (
        team_num DECIMAL(2,0) PRIMARY KEY,
        name CHAR(20)
);
CREATE TABLE Coach(
        team_num DECIMAL(2,0) NOT NULL,
        last_name CHAR(20),
        title CHAR(5),
        FOREIGN KEY (team_num) REFERENCES Team(team_num)
);
CREATE TABLE Player (
        team_num DECIMAL(2,0) NOT NULL,
        phone CHAR(20),
        last_name CHAR(20),
        first_name CHAR(20),
        FOREIGN KEY (team_num) REFERENCES Team(team_num)
);
CREATE TABLE `Game_new` (
	  `game_id` int(11) NOT NULL AUTO_INCREMENT,
	  `game_date` date DEFAULT NULL,
	  `home_team` decimal(2,0) NOT NULL,
	  `away_team` decimal(2,0) NOT NULL,
	  `home_pts` decimal(3,0) DEFAULT NULL,
	  `away_pts` decimal(3,0) DEFAULT NULL,
	  PRIMARY KEY (`game_id`),
	  KEY `home_team` (`home_team`,`away_team`),
	  KEY `away_team` (`away_team`),
	  CONSTRAINT `Game_new_ibfk_1` FOREIGN KEY (`home_team`) REFERENCES `Team` (`team_num`),
	  CONSTRAINT `Game_new_ibfk_2` FOREIGN KEY (`away_team`) REFERENCES `Team` (`team_num`)
        )
INSERT INTO Team
VALUES
(1,'Rockets');
INSERT INTO Team
VALUES
(2,'Avalanche');
INSERT INTO Team
VALUES
(3,'Razors');
INSERT INTO Team
VALUES
(4,'WolfClan');
INSERT INTO Team
VALUES
(5,'Outsiders');
INSERT INTO Team
VALUES
(6,'Aliens');

INSERT INTO Coach
VALUES
(1,'Smith', 'Mr.');
INSERT INTO Coach
VALUES
(1,'Greenly', 'Ms.');
INSERT INTO Coach
VALUES
(2,'Forest', 'Ms.');
INSERT INTO Coach
VALUES
(2,'Hart', 'Ms.');
INSERT INTO Coach
VALUES
(3,'Keely', 'Mr.');
INSERT INTO Coach
VALUES
(4,'Burke', 'Mr.');
INSERT INTO Coach
VALUES
(4,'Smythe', 'Mr.');
INSERT INTO Coach
VALUES
(4,'Braun', 'Mr.');
INSERT INTO Coach
VALUES
(5,'Hinton', 'Ms.');
INSERT INTO Coach
VALUES
(6,'Scott', 'Mr.');

INSERT INTO Player
VALUES
(1,'555-1234', 'Smith', 'John');
INSERT INTO Player
VALUES
(1,'555-2345', 'Folds', 'Ben');
INSERT INTO Player
VALUES
(1,'555-3456', 'Spencer', 'Luke');
INSERT INTO Player
VALUES
(1,'555-4567', 'Spencer', 'Bobbie');
INSERT INTO Player
VALUES
(1,'555-5578', 'Green', 'Jeff');
INSERT INTO Player
VALUES
(1,'555-6789', 'Green', 'Mitchell');
INSERT INTO Player
VALUES
(1,'555-7890', 'Doe', 'Mark');
INSERT INTO Player
VALUES
(1,'555-8901', 'McElven', 'Mike');
INSERT INTO Player
VALUES
(1,'555-9012', 'Johnson', 'Jake');
INSERT INTO Player
VALUES
(1,'555-0987', 'Hughes', 'Maria');
INSERT INTO Player
VALUES
(2,'555-9876', 'Forest', 'Jon');
INSERT INTO Player
VALUES
(2,'555-8765', 'Brown', 'Blake');
INSERT INTO Player
VALUES
(2,'555-7654', 'Buchanan', 'Delilah');
INSERT INTO Player
VALUES
(2,'555-6543', 'Sommers', 'Buffy');
INSERT INTO Player
VALUES
(2,'555-5432', 'Brown', 'Brian');
INSERT INTO Player
VALUES
(2,'555-4321', 'Wearly', 'Dave');
INSERT INTO Player
VALUES
(2,'555-3210', 'Seger', 'Bob');
INSERT INTO Player
VALUES
(2,'555-2109', 'Hurt', 'John');
INSERT INTO Player
VALUES
(2,'555-1098', 'Broonzy', 'Bill');
INSERT INTO Player
VALUES
(2,'555-1123', 'Zell', 'Tony');
INSERT INTO Player
VALUES
(3,'555-1134', 'Jagger', 'Mick');
INSERT INTO Player
VALUES
(3,'555-1145', 'Lennon', 'John');
INSERT INTO Player
VALUES
(3,'555-1156', 'Harrison', 'George');
INSERT INTO Player
VALUES
(3,'555-1167', 'Springfield', 'Rick');
INSERT INTO Player
VALUES
(3,'555-1178', 'Johnson', 'Robert');
INSERT INTO Player
VALUES
(3,'555-1189', 'Edwards', 'HoneyBoy');
INSERT INTO Player
VALUES
(3,'555-1190', 'May', 'Brian');
INSERT INTO Player
VALUES
(3,'555-1101', 'Duncan', 'Randy');
INSERT INTO Player
VALUES
(4,'555-0098', 'Richards', 'Keith');
INSERT INTO Player
VALUES
(4,'555-0087', 'Redman', 'Stu');
INSERT INTO Player
VALUES
(4,'555-0076', 'Flagg', 'Randall');
INSERT INTO Player
VALUES
(4,'555-0065', 'Deschain', 'Roland');
INSERT INTO Player
VALUES
(4,'555-0054', 'Dean', 'Eddie');
INSERT INTO Player
VALUES
(4,'555-0043', 'Chambers', 'Jake');
INSERT INTO Player
VALUES
(4,'555-0032', 'Dean', 'Susannah');
INSERT INTO Player
VALUES
(4,'555-0021', 'Randall', 'Frank');
INSERT INTO Player
VALUES
(4,'555-0010', 'Duncan', 'Geillis');
INSERT INTO Player
VALUES
(5,'555-0009', 'MacKenzie', 'Alexander');
INSERT INTO Player
VALUES
(5,'555-1029', 'Fraser', 'Brian');
INSERT INTO Player
VALUES
(5,'555-2983', 'Randall', 'Claire');
INSERT INTO Player
VALUES
(5,'555-3746', 'White', 'Bukka');
INSERT INTO Player
VALUES
(5,'555-5123', 'Matheson', 'Miles');
INSERT INTO Player
VALUES
(5,'555-5234', 'Monroe', 'Sebastian');
INSERT INTO Player
VALUES
(5,'555-5345', 'Pittman', 'Aaron');
INSERT INTO Player
VALUES
(5,'555-5456', 'Pope', 'John');
INSERT INTO Player
VALUES
(6,'555-5567', 'Kirk', 'Jim');
INSERT INTO Player
VALUES
(6,'555-5678', 'Skywalker', 'Luke');
INSERT INTO Player
VALUES
(6,'555-5789', 'Crusher', 'Wesley');
INSERT INTO Player
VALUES
(6,'555-5890', 'Carter', 'Samantha');
INSERT INTO Player
VALUES
(6,'555-5901', 'Crichton', 'John');
INSERT INTO Player
VALUES
(6,'555-5012', 'Sun', 'Aeryn');
INSERT INTO Player
VALUES
(6,'555-9123', 'O\'Neill', 'Jack');
INSERT INTO Player
VALUES
(6,'555-9234', 'Jackson', 'Daniel');
INSERT INTO Player
VALUES
(6,'555-9345', 'Davis', 'Paul');

INSERT INTO Game(game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-05', 'Rockets', 'Wolfclan', 22, 12);
INSERT INTO Game(game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-05', 'Avalanche', 'Outsiders', 19, 24);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-05', 'Razors', 'Aliens', 17, 16);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-12', 'Wolfclan', 'Avalanche', 35, 13);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-12', 'Outsiders', 'Aliens', 20, 23);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-12', 'Aliens', 'Rockets', 17, 21);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-19', 'Rockets', 'Outsiders', 24, 17);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-19', 'Avalanche', 'Razors', 16, 33);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-19', 'Razors', 'Wolfclan', 12, 20);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-26', 'Wolfclan', 'Razors', 23, 19);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-26', 'Outsiders', 'Avalanche', 21, 19);
INSERT INTO Game (game_date,home_team,away_team,home_pts,away_pts)
VALUES
('2013-01-26', 'Aliens', 'Rockets', 17, 16);