---
title: "Flights"
author: "Kamilla Lukaszewska"
date: "5/22/2019"
output: html_document
---
```{r}
library(RODBC)
connect <- odbcConnect("FlightProject", uid = "dbad_s444483", pwd = "dXKeqA6K25")
```

** zadanie 1: Average arrival delay**
```{r}
sqlQuery(connect, "SELECT  AVG(arr_delay_new) AS 'avg_delay'
	           FROM    Flight_delays;")
```

** zadanie 2: Maximum arrival delay**
```{r}
sqlQuery(connect, "SELECT  MAX(arr_delay_new) AS 'max_delay'
	           FROM    Flight_delays;")
```

** zadanie 3: Flight with the biggest arrival delay**
```{r}
sqlQuery(connect, "SELECT  unique_carrier AS 'carrier',
        	       	   origin_city_name,
        	           dest_city_name,
        	           fl_date,
        	           arr_delay_new
	       	   FROM    Flight_delays
	           WHERE   arr_delay_new >= (SELECT   MAX(arr_delay_new)
                          	             FROM     Flight_delays);")
```

** zadanie 4: Days of week the worst for travelling **
```{r}
sqlQuery(connect, "SELECT   W.weekday_name,
               		    AVG(F.arr_delay_new) AS 'delay'
	       	   FROM     Flight_delays F JOIN Weekdays W ON  W.weekday_id = F.day_of_week
	     	   GROUP BY W.weekday_name
	      	   ORDER BY delay desc;")
```

** zadanie 5: Airlines flying from San Francisco (SFO) with the smallest arrival delay **
```{r}
sqlQuery(connect, "SELECT   DISTINCT airline_name,
         	            avg_delay
	           FROM     (SELECT    A.airline_name,
                    		       F.airline_id,
                    		       AVG(F.arr_delay_new) AS 'avg_delay'
          		     FROM      Flight_delays F JOIN Airlines A ON F.airline_id = A.airline_id
         		     GROUP BY  A.airline_name, F.airline_id) AS T JOIN Flight_delays F ON T.airline_id = F.airline_id
	           WHERE    F.origin LIKE 'SFO'
	           ORDER BY avg_delay desc;")
```

** zadanie 6: What part of airlines have regular arrival delay (more than 10 minutes) **
```{r}
d <- sqlQuery(connect, "SELECT  COUNT (DISTINCT unique_carrier)
                    	 FROM    Flight_delays
                  	 WHERE   unique_carrier IN (SELECT unique_carrier
                                                    FROM    Flight_delays
                                               	    GROUP BY    unique_carrier
                                               	    HAVING  AVG( arr_delay_new) > 10)")

z <- sqlQuery(connect, "SELECT COUNT(DISTINCT unique_carrier)
                        FROM Flight_delays")

i <- (d/z)
i
```

** zadanie 7: How departure delay affect arrival delay **
```{r}
X <- sqlQuery(connect,"SELECT dep_delay_new
                   FROM Flight_delays")

Y <- sqlQuery(connect,"SELECT arr_delay_new
                   FROM Flight_delays")

Wspolczynnik_Pearsona <- cor( X, Y, use= "complete.obs",method= "pearson")

print(Wspolczynnik_Pearsona)
```

** zadanie 8: Airline has the biggest increase of average arrival delay in the month's last week **
```{r}

```

** zadanie 9: Airlines flight from San Francisco (SFO) to Portland (PDX) and also from San Francisco (SFO) to Eugene (EUG) **
```{r}
sqlQuery(connect, "SELECT  DISTINCT airline_name
	           FROM    (SELECT  DISTINCT A.airline_name,
                 	            F.airline_id
         		    FROM    Flight_delays F JOIN Airlines A ON F.airline_id = A.airline_id
         		    WHERE   F.origin LIKE 'SFO' AND F.dest LIKE 'PDX') AS T JOIN Flight_delays F1 ON F1.airline_id = T.airline_id
	           WHERE   F1.origin LIKE 'SFO' AND F1.dest LIKE 'EUG';")
```

** zadanie 10: The quickest way to get from Chicago to Stanford after 2 pm **
```{r}
sqlQuery(connect, "SELECT   origin,
         		    dest,
        	            AVG(arr_delay_new) AS 'avg_delay'
	           FROM     Flight_delays
	           WHERE    crs_dep_time BETWEEN 1401 AND 2359
	           GROUP BY origin, dest
	           HAVING   origin IN ('MDW', 'ORD') AND dest IN ('SFO', 'SJC', 'OAK')
	           ORDER BY avg_delay desc;")
```