PREPROCESS.sql

Use mydb;
drop table IF EXISTS MaxScorers;
DROP TABLE IF EXISTS TEMP1;
CREATE TABLE TEMP1 SELECT Matches.*, SUM(A_RUNS) AS SUM FROM Batting, Matches WHERE M_DATE = A_DATE AND M_HOMENUM = A_COUNTRY AND M_OPPNUM = A_OPPOSITION AND M_RESULT = 1 GROUP BY M_ID;
create table MaxScorers SELECT M_ID , A_NAME, A_COUNTRY, MAX(A_RUNS/SUM) AS BESTSCORE FROM Batting, TEMP1 WHERE M_DATE = A_DATE AND M_HOMENUM = A_COUNTRY AND M_OPPNUM = A_OPPOSITION AND M_RESULT = 1 GROUP BY M_ID;
DROP TABLE TEMP1;

drop table IF EXISTS BestPlayers;
DROP TABLE IF EXISTS TEMP;
CREATE TABLE TEMP SELECT A_NAME, A_COUNTRY, SUM(BESTSCORE) AS SUMS FROM MaxScorers GROUP BY A_NAME;
create table BestPlayers SELECT A_NAME AS PNAME,  A_COUNTRY AS COUNTRY, MAX(SUMS) AS SUM FROM TEMP GROUP BY A_COUNTRY;
DROP TABLE TEMP;

DROP TABLE IF EXISTS TotalAverages;
DROP TABLE IF EXISTS TEMP2;
CREATE TABLE TEMP2 SELECT Matches.*, SUM(A_RUNS) AS SUM FROM Batting, Matches WHERE M_DATE = A_DATE AND M_HOMENUM = A_COUNTRY AND M_OPPNUM = A_OPPOSITION GROUP BY M_ID;
create table TotalAverages SELECT  A_NAME, AVG(A_RUNS/SUM) AS AVERAGES FROM Batting, TEMP2 WHERE M_DATE = A_DATE AND M_HOMENUM = A_COUNTRY AND M_OPPNUM = A_OPPOSITION GROUP BY A_NAME;
DROP TABLE TEMP2;

DROP TABLE IF EXISTS BenchStrength;
DROP TABLE IF EXISTS TEMP3;
CREATE TABLE TEMP3 SELECT M_ID AS NEWID, A_NAME AS NEWNAME FROM Matches, Batting WHERE M_DATE = A_DATE AND M_HOMENUM = A_COUNTRY AND M_OPPNUM = A_OPPOSITION;
CREATE TABLE BenchStrength SELECT NEWID, SUM(AVERAGES) AS STRENGTH FROM TotalAverages, TEMP3 WHERE NEWNAME = A_NAME GROUP BY NEWID;
DROP TABLE  TEMP3;