CREATE TABLE gaasid(
   aasta INT,
   koht VARCHAR(40),
   SO2 FLOAT,
   NO2 FLOAT,
   CO2 FLOAT,
   PRIMARY KEY(aasta, koht)
);

="INSERT INTO gaasid VALUES (" & A2 & ", '" & B2 & "'," & C2 & ", " & D2 &  ", " & E2 & ");"

INSERT INTO gaasid VALUES (2008, 'taimed',58.35902, 2802.4334, 723.27832);
INSERT INTO gaasid VALUES (2008, 'mets',11.51387, 492.92154, 212.50627);
INSERT INTO gaasid VALUES (2008, 'kalad',25.74752, 552.25406, 100.71048);
INSERT INTO gaasid VALUES (2009, 'taimed',29.37142, 2119.75833, 658.94955);
INSERT INTO gaasid VALUES (2009, 'mets',8.50759, 415.07637, 214.95137);
INSERT INTO gaasid VALUES (2009, 'kalad',25.5001, 514.99195, 92.69057);
INSERT INTO gaasid VALUES (2010, 'taimed',20.03561, 2133.60664, 515.2577);
INSERT INTO gaasid VALUES (2010, 'mets',2.53297, 250.85804, 173.38407);
INSERT INTO gaasid VALUES (2010, 'kalad',5.78811, 161.91682, 64.55088);
INSERT INTO gaasid VALUES (2011, 'taimed',28.7144, 2209.63985, 534.98046);
INSERT INTO gaasid VALUES (2011, 'mets',2.9487, 255.98521, 161.43936);
INSERT INTO gaasid VALUES (2011, 'kalad',3.51458, 101.04418, 48.61464);
INSERT INTO gaasid VALUES (2012, 'taimed',44.3308, 3433.36689, 1037.48552);
INSERT INTO gaasid VALUES (2012, 'mets',1.85563, 223.4359, 153.58485);
INSERT INTO gaasid VALUES (2012, 'kalad',4.61474, 190.09127, 56.32136);

+-------+--------+---------+---------+---------+
| aasta | koht   | SO2     | NO2     | CO2     |
+-------+--------+---------+---------+---------+
|  2008 | kalad  | 25.7475 | 552.254 |  100.71 |
|  2008 | mets   | 11.5139 | 492.922 | 212.506 |
|  2008 | taimed |  58.359 | 2802.43 | 723.278 |
|  2009 | kalad  | 25.5001 | 514.992 | 92.6906 |
|  2009 | mets   | 8.50759 | 415.076 | 214.951 |
|  2009 | taimed | 29.3714 | 2119.76 |  658.95 |
|  2010 | kalad  | 5.78811 | 161.917 | 64.5509 |
|  2010 | mets   | 2.53297 | 250.858 | 173.384 |
|  2010 | taimed | 20.0356 | 2133.61 | 515.258 |
|  2011 | kalad  | 3.51458 | 101.044 | 48.6146 |
|  2011 | mets   |  2.9487 | 255.985 | 161.439 |
|  2011 | taimed | 28.7144 | 2209.64 |  534.98 |
|  2012 | kalad  | 4.61474 | 190.091 | 56.3214 |
|  2012 | mets   | 1.85563 | 223.436 | 153.585 |
|  2012 | taimed | 44.3308 | 3433.37 | 1037.49 |
+-------+--------+---------+---------+---------+

mysql> SELECT aasta, SO2 FROM gaasid WHERE koht='kalad';
+-------+---------+
| aasta | SO2     |
+-------+---------+
|  2008 | 25.7475 |
|  2009 | 25.5001 |
|  2010 | 5.78811 |
|  2011 | 3.51458 |
|  2012 | 4.61474 |
+-------+---------+

Kalandusest tekkinud SO2 protsent heitgaaside hulgast aastati
mysql> SELECT aasta, ROUND(SO2/(SO2+NO2+CO2) * 100, 2) as SO2protsent FROM gaasid WHERE koht='kalad';
+-------+-------------+
| aasta | SO2protsent |
+-------+-------------+
|  2008 |        3.79 |
|  2009 |        4.03 |
|  2010 |        2.49 |
|  2011 |        2.29 |
|  2012 |        1.84 |
+-------+-------------+

SELECT MAX(ROUND(SO2/(SO2+NO2+CO2) * 100, 2)) as SO2protsent FROM gaasid WHERE koht='kalad';
SELECT MAX(ROUND(SO2/(SO2+NO2+CO2) * 100, 2)) - 
              MIN(ROUND(SO2/(SO2+NO2+CO2) * 100, 2)) as vahe FROM gaasid WHERE koht='kalad';
+------+
| vahe |
+------+
| 2.19 |
+------+


Kõikumine maksimumi suhtes:
SELECT (MAX(SO2/(SO2+NO2+CO2)) - MIN(SO2/(SO2+NO2+CO2))) /
        MAX(SO2/(SO2+NO2+CO2)) as suhe FROM gaasid WHERE koht='kalad';
		
Leia aastate kaupa ning kokku, milline on lämmastikoksiidi ning 
       süsinikoksiidi emissiooni suhe taimekasvastuse juures.
	   
SELECT aasta, NO2 / CO2 FROM gaasid WHERE koht='taimed';


SELECT SUM(NO2) / SUM(CO2) FROM gaasid WHERE koht='taimed';

Aastate kaupa vääveldioksiidi emissioon kõigi kohtade peale kokku.

mysql> SELECT aasta, SUM(SO2) FROM gaasid GROUP BY aasta;
+-------+--------------------+
| aasta | SUM(SO2)           |
+-------+--------------------+
|  2008 |  95.62041091918945 |
|  2009 | 63.379109382629395 |
|  2010 | 28.356689929962158 |
|  2011 |  35.17767930030823 |
|  2012 |  50.80116903781891 |
+-------+--------------------+


Leidke tegevuskohtade kaupa SO2 emissiooni summad.

SELECT koht, SUM(SO2) FROM gaasid GROUP BY koht;


mysql> SELECT koht, SUM(SO2) FROM gaasid GROUP BY koht;
+--------+--------------------+
| koht   | SUM(SO2)           |
+--------+--------------------+
| kalad  |   65.1650493144989 |
| mets   | 27.358760476112366 |
| taimed | 180.81124877929688 |
+--------+--------------------+


SELECT AVG(SO2) FROM gaasid WHERE koht='kalad';
+-------------------+
| AVG(SO2)          |
+-------------------+
| 13.03300986289978 |
+-------------------+

SELECT aasta, SO2 FROM gaasid WHERE koht='kalad' AND SO2>13.03;


+-------+---------+
| aasta | SO2     |
+-------+---------+
|  2008 | 25.7475 |
|  2009 | 25.5001 |
+-------+---------+

SELECT aasta, SO2 FROM gaasid WHERE koht='kalad' AND
              SO2>(SELECT AVG(SO2) FROM gaasid WHERE koht='kalad');


+-------+---------+
| aasta | SO2     |
+-------+---------+
|  2008 | 25.7475 |
|  2009 | 25.5001 |
+-------+---------+


-- Leidke aastad ja väärtused, kus taimekasvatuses on 
   NO2 emissioon miinimumis või kuni 20% sellest kõrgem.

SELECT aasta, NO2 FROM gaasid WHERE koht='taimed';


+-------+---------+
| aasta | NO2     |
+-------+---------+
|  2008 | 2802.43 |
|  2009 | 2119.76 |
|  2010 | 2133.61 |
|  2011 | 2209.64 |
|  2012 | 3433.37 |
+-------+---------+


SELECT aasta, NO2 FROM gaasid WHERE koht='taimed' 
  AND NO2=(SELECT MIN(NO2) FROM gaasid WHERE koht='taimed');
+-------+---------+
| aasta | NO2     |
+-------+---------+
|  2009 | 2119.76 |
+-------+---------+
  

SELECT aasta, NO2 FROM gaasid WHERE koht='taimed' 
  AND NO2<(SELECT MIN(NO2) FROM gaasid WHERE koht='taimed')*1.2;

+-------+---------+
| aasta | NO2     |
+-------+---------+
|  2009 | 2119.76 |
|  2010 | 2133.61 |
|  2011 | 2209.64 |
+-------+---------+


-- Kuva taimekasvatuse NO2 emissiooni kohta iga aasta puhul, mitu protsenti moodustab see
   NO2 taimekasvatuse keskmisest emissioonist.  (üle saja - ületab, alla saja - alla keskmise)

SELECT aasta, NO2, (NO2/(SELECT AVG(NO2) FROM gaasid WHERE koht='taimed')) FROM gaasid WHERE koht='taimed' 
  
SELECT aasta, NO2,  
  ROUND((NO2/(SELECT AVG(NO2) FROM gaasid WHERE koht='taimed'))*100, 2) as protsenti_keskmisest
  FROM gaasid WHERE koht='taimed' ;  

+-------+---------+----------------------+
| aasta | NO2     | protsenti_keskmisest |
+-------+---------+----------------------+
|  2008 | 2802.43 |               110.34 |
|  2009 | 2119.76 |                83.46 |
|  2010 | 2133.61 |                84.01 |
|  2011 | 2209.64 |                87.00 |
|  2012 | 3433.37 |               135.18 |
+-------+---------+----------------------+
  

  
  -- Kuva taimekasvatuse NO2 emissiooni kohta iga aasta puhul, mitu protsenti ületab see
   NO2 taimekasvatuse keskmist emissiooni. (+ ületab, - jääb alla keskmise)
   
SELECT aasta, NO2,  
  ROUND((NO2/(SELECT AVG(NO2) FROM gaasid WHERE koht='taimed'))*100, 2) as protsenti_keskmisest,
  ROUND((NO2/(SELECT AVG(NO2) FROM gaasid WHERE koht='taimed'))*100, 2)-100 as keskmisega_vorreldes_protsent
  FROM gaasid WHERE koht='taimed' ;  
 

+-------+---------+----------------------+-------------------------------+
| aasta | NO2     | protsenti_keskmisest | keskmisega_vorreldes_protsent |
+-------+---------+----------------------+-------------------------------+
|  2008 | 2802.43 |               110.34 |                         10.34 |
|  2009 | 2119.76 |                83.46 |                        -16.54 |
|  2010 | 2133.61 |                84.01 |                        -15.99 |
|  2011 | 2209.64 |                87.00 |                        -13.00 |
|  2012 | 3433.37 |               135.18 |                         35.18 |
+-------+---------+----------------------+-------------------------------+


SELECT aasta, NO2, pk, 100-pk as vahe FROM (
 SELECT aasta, NO2,  
  ROUND((NO2/(SELECT AVG(NO2) FROM gaasid WHERE koht='taimed'))*100, 2) as pk
  FROM gaasid WHERE koht='taimed') AS tabel1; 
  

+-------+---------+--------+--------+
| aasta | NO2     | pk     | vahe   |
+-------+---------+--------+--------+
|  2008 | 2802.43 | 110.34 | -10.34 |
|  2009 | 2119.76 |  83.46 |  16.54 |
|  2010 | 2133.61 |  84.01 |  15.99 |
|  2011 | 2209.64 |  87.00 |  13.00 |
|  2012 | 3433.37 | 135.18 | -35.18 |
+-------+---------+--------+--------+


SELECT koht, aasta, NO2,  
  ROUND((NO2/(SELECT AVG(NO2) FROM gaasid as g1 WHERE g1.koht=g2.koht))*100, 2) as protsenti_keskmisest
  FROM gaasid as g2 ; 

+--------+-------+---------+----------------------+
| koht   | aasta | NO2     | protsenti_keskmisest |
+--------+-------+---------+----------------------+
| kalad  |  2008 | 552.254 |               181.63 |
| mets   |  2008 | 492.922 |               150.44 |
| taimed |  2008 | 2802.43 |               110.34 |
| kalad  |  2009 | 514.992 |               169.37 |
| mets   |  2009 | 415.076 |               126.68 |
| taimed |  2009 | 2119.76 |                83.46 |
| kalad  |  2010 | 161.917 |                53.25 |
| mets   |  2010 | 250.858 |                76.56 |
| taimed |  2010 | 2133.61 |                84.01 |
| kalad  |  2011 | 101.044 |                33.23 |
| mets   |  2011 | 255.985 |                78.13 |
| taimed |  2011 | 2209.64 |                87.00 |
| kalad  |  2012 | 190.091 |                62.52 |
| mets   |  2012 | 223.436 |                68.19 |
| taimed |  2012 | 3433.37 |               135.18 |
+--------+-------+---------+----------------------+