futhead.sql

-- Drops futhead database
DROP DATABASE IF EXISTS FUTHEAD;

-- Creates futhead database
CREATE DATABASE FUTHEAD;

-- Uses futhead database;
USE FUTHEAD;

-- DDL for fifa tables
CREATE TABLE FIFA10
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

CREATE TABLE FIFA11
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

CREATE TABLE FIFA12
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
)CHARACTER SET UTF8;

CREATE TABLE FIFA13
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

CREATE TABLE FIFA14
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

CREATE TABLE FIFA15
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

CREATE TABLE FIFA16
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

CREATE TABLE FIFA17
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

CREATE TABLE FIFA18
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

CREATE TABLE FIFA19
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;


CREATE TABLE FIFA20
(
  NAME      CHAR(30),
  CLUB      VARCHAR(50),
  LEAGUE    VARCHAR(50),
  POSITION  VARCHAR(50),
  TIER      VARCHAR(6),
  RATING    INTEGER(2),
  PACE      INTEGER(2),
  SHOOTING  INTEGER(2),
  PASSING   INTEGER(2),
  DRIBBLING INTEGER(2),
  DEFENDING INTEGER(2),
  PHYSICAL  INTEGER(2),
  LOADDATE  TIMESTAMP
) CHARACTER SET UTF8;

-- Truncates fifa18 table
TRUNCATE TABLE FIFA10;
TRUNCATE TABLE FIFA11;
TRUNCATE TABLE FIFA12;
TRUNCATE TABLE FIFA13;
TRUNCATE TABLE FIFA14;
TRUNCATE TABLE FIFA15;
TRUNCATE TABLE FIFA16;
TRUNCATE TABLE FIFA17;
TRUNCATE TABLE FIFA18;
TRUNCATE TABLE FIFA19;
TRUNCATE TABLE FIFA20;