Let's start by first creating tables and inserting data into them.
Create tables
CREATE TABLE country (
countryid INTEGER PRIMARY KEY,
countrycode TEXT NOT NULL,
countryname TEXT NOT NULL
);
CREATE TABLE rankcal (
dateid INTEGER PRIMARY KEY,
rankdate DATE NOT NULL,
rankyear INT NOT NULL,
rankmonth INT NOT NULL,
rankday INT NOT NULL,
rankquarter TEXT
);
CREATE TABLE fifarank (
rankid INTEGER PRIMARY KEY,
countryid INTEGER NOT NULL,
dateid INTEGER NOT NULL,
rank INTEGER NOT NULL,
FOREIGN KEY (countryid) REFERENCES country(countryid),
FOREIGN KEY (dateid) REFERENCES rankcal(dateid)
);
Now, let's insert data into the tables. For rankingcal, I used the date column from flat file available in
kaggle and used python script to create insert statements.
from datetime import datetime
# List of dates (you can replace this with your full list of 286 dates)
dates = ['08/08/1993']
def get_quarter(month):
if month in [1, 2, 3]:
return 'Q1'
elif month in [4, 5, 6]:
return 'Q2'
elif month in [7, 8, 9]:
return 'Q3'
else:
return 'Q4'
# Function to generate the SQL insert statements
def generate_insert_statements(dates):
insert_statements = []
for date_str in dates:
# Convert date to datetime object
date_obj = datetime.strptime(date_str, '%m/%d/%Y')
# Extract year, month, day
year = date_obj.year
month = date_obj.month
day = date_obj.day
quarter = get_quarter(month)
# Create insert statement
insert_statement = f"INSERT INTO rankcal(rankdate, rankyear, rankmonth, rankday, rankquarter) " \
f"VALUES ('{date_obj.strftime('%Y-%m-%d')}', {year}, {month}, {day}, '{quarter}');"
insert_statements.append(insert_statement)
return insert_statements
# Generate and print the insert statements
insert_statements = generate_insert_statements(dates)
# Output all insert statements
for statement in insert_statements:
print(statement)
