/* Showing results for Golf2.xlsx */

/* CREATE TABLE */
CREATE TABLE DavidGolf(
ID DOUBLE,
Outlook VARCHAR(100),
Temperature DOUBLE,
Humidity DOUBLE,
Windy DOUBLE,
Play VARCHAR(100)
);

/* INSERT QUERY NO: 1 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
1, 'overcast', 83, 86, 23, 'yes'
);

/* INSERT QUERY NO: 2 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
2, 'overcast', 64, 65, 26, 'yes'
);

/* INSERT QUERY NO: 3 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
3, 'overcast', 72, 90, 28, 'yes'
);

/* INSERT QUERY NO: 4 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
4, 'overcast', 81, 75, 15, 'yes'
);

/* INSERT QUERY NO: 5 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
5, 'rainy', 70, 96, 18, 'yes'
);

/* INSERT QUERY NO: 6 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
6, 'rainy', 68, 80, 22, 'yes'
);

/* INSERT QUERY NO: 7 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
7, 'rainy', 65, 70, 30, 'no'
);

/* INSERT QUERY NO: 8 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
8, 'rainy', 75, 80, 19, 'yes'
);

/* INSERT QUERY NO: 9 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
9, 'rainy', 71, 91, 26, 'no'
);

/* INSERT QUERY NO: 10 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
10, 'sunny', 85, 85, 12, 'no'
);

/* INSERT QUERY NO: 11 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
11, 'sunny', 80, 90, 31, 'no'
);

/* INSERT QUERY NO: 12 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
12, 'sunny', 72, 95, 24, 'no'
);

/* INSERT QUERY NO: 13 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
13, 'sunny', 69, 70, 22, 'yes'
);

/* INSERT QUERY NO: 14 */
INSERT INTO DavidGolf(ID, Outlook, Temperature, Humidity, Windy, Play)
VALUES
(
14, 'sunny', 75, 70, 32, 'yes'
);




/*One hot encoding*/
SELECT `ID`, 
COUNT(CASE WHEN Outlook = 'overcast' THEN 1 END) AS Overcast, 
COUNT(CASE WHEN Outlook = 'rainy' THEN 1 END) AS Rainy, 
COUNT(CASE WHEN Outlook = 'sunny' THEN 1 END) AS Sunny,
ROUND(((`Temperature`-40)/
(100-40)),2) AS Temperature,
ROUND(((`Humidity`-60)/
(100-60)),2) AS Humidity,
ROUND(((`Windy`-1)/
(40-1)),2) AS Windy,
`Play`
FROM davidgolf GROUP BY ID


/*Nominal*/
 SELECT `ID`, Outlook, 
    CASE WHEN (`Temperature`) >= 80
    THEN 'Hot'
    WHEN (`Temperature`) <= 79 AND (`Temperature`) > 70
    THEN 'Mild'
    WHEN (`Temperature`) <= 70
    THEN 'Cool'
    END AS Temperature,
    CASE WHEN (`Humidity`) > 80
    THEN 'High'
    WHEN (`Humidity`) <= 80
    THEN 'Normal'
    END AS Humidity,
    CASE WHEN (`Windy`) > 25
    THEN 'True'
    WHEN (`Windy`) <= 25
    THEN 'False'
    END AS Windy,
    `Play`
    FROM davidgolf


/*One hot encoding with Normalize*/

select `ID`, 
count(case when Outlook = 'overcast' then 1 end) as Overcast, 
count(case when Outlook = 'rainy' then 1 end) as Rainy, 
count(case when Outlook = 'sunny' then 1 end) as Sunny,
ROUND(((`Temperature`-(Select MIN(`Temperature`) from davidgolf))/
((Select MAX(`Temperature`) from davidgolf)-(Select MIN(`Temperature`) from davidgolf))),2) AS Temperature,
ROUND(((`Humidity`-(Select MIN(`Humidity`) from davidgolf))/
((Select MAX(`Humidity`) from davidgolf)-(Select MIN(`Humidity`) from davidgolf))),2) AS Humidity,
ROUND(((`Windy`-(Select MIN(`Windy`) from davidgolf))/
((Select MAX(`Windy`) from davidgolf)-(Select MIN(`Windy`) from davidgolf))),2) AS Windy,
`Play`
from davidgolf group by ID



/*kNN Query*/
SELECT TMP.Play, TMP.vote FROM (SELECT neigh.Play, COUNT(neigh.Play) AS vote FROM 
(
SELECT 
normalizegolf.ID, 
normalizegolf.Overcast, 
normalizegolf.Rainy,
normalizegolf.Sunny,
normalizegolf.Temperature,
normalizegolf.Humidity,
normalizegolf.Windy,
normalizegolf.Play,
ROUND(SQRT(POWER(normalizegolf.Overcast-querygolf.Overcast,2)+
POWER(normalizegolf.Rainy-querygolf.Rainy,2)+
POWER(normalizegolf.Sunny-querygolf.Sunny,2)+
POWER(normalizegolf.Temperature-querygolf.Temperature,2)+
POWER(normalizegolf.Humidity-querygolf.Humidity,2)+
POWER(normalizegolf.Windy-querygolf.Windy,2)+
POWER(normalizegolf.Play-querygolf.Play,2)),2) AS distance
FROM normalizegolf, querygolf WHERE querygolf.ID='1' ORDER BY distance LIMIT 5) neigh

 GROUP BY Play ORDER BY vote DESC LIMIT 1)TMP


/*NB Query*/


CREATE VIEW PRIOR AS 
SELECT Play, COUNT(1) pfreq, SUM(1.0)/(SELECT COUNT(1) FROM nominalgolf) pprob 
FROM nominalgolf GROUP BY Play ;


CREATE VIEW AttrClass AS 
SELECT attributes,value, p.Play,acfreq, acfreq/pfreq acprob 
FROM
	(SELECT 'Outlook' attributes, Outlook value, Play, SUM(1.0) acfreq 
	FROM nominalgolf GROUP BY Outlook,Play
	UNION ALL
		SELECT 'Temperature' attributes, Temperature,Play, SUM(1.0) 
		FROM nominalgolf GROUP BY Temperature,Play
		UNION ALL
			SELECT 'Humidity' attributes,Humidity,Play, SUM(1.0) 
			FROM nominalgolf GROUP BY Humidity,Play
			UNION ALL
				SELECT 'Windy' attributes,Windy,Play, SUM(1.0) 
				FROM nominalgolf GROUP BY Windy,Play) rd 
JOIN PRIOR p ON rd.Play=p.Play



/* Query Prob*/
CREATE VIEW fit AS 


SELECT id,acs.class, pprob * EXP(SUM(LOG(fprob))) score 
FROM test t 
JOIN (
	SELECT sp.attribute, sp.value, sp.class, sp.pprob, s.acprob fprob 
	FROM (
		SELECT s.attribute,s.value, p.class, p.pprob 
		FROM attrClass s, prior p 
		GROUP BY s.attribute,s.value, p.class, p.pprob) sp 
	LEFT JOIN attrClass s 
	ON sp.attribute=s.attribute 
	AND sp.value = s.value AND sp.class=s.class) acs 
ON t.attribute = acs.attribute 
AND t.value = acs.value 
GROUP BY id,acs.class,pprob



/* Compare Prob*/

SELECT f.id, MAX(class) pclass 
FROM fit f JOIN
	(SELECT id, MAX(score) mscore 
	FROM fit GROUP BY id) mq
ON f.id = mq.id AND score = mscore GROUP BY f.id



