### File of examples to be run for lecture slides
date()
rm(list=ls())
opSys <- Sys.info()[1]
if (opSys=="Windows"){
  filePrefix <- "C:/dscott/Teaching/782/Lectures/MySQLEXamples"
}else{
  filePrefix <- "~/Teaching/782/Lectures/MySQLExamples/"
} 
setwd(filePrefix)

library(RODBC)

### Read in mySummary.R
###source("mySummary.R")

### Make connection and get some data
### Windows version
channel <- odbcConnect("stats782", uid = "root", pwd = "******")

### Retrieve the first record of the table and check it
query <- "SELECT * 
          FROM cellar
          LIMIT 1"
queryResult <- sqlQuery(channel, query)
str(queryResult)

### Retrieve a table from MySQL
query <- "SELECT Name, Type, AVG(Price) AS AveragePrice
          FROM cellar
          WHERE Winery ='Te Mata'
          GROUP BY Name ORDER BY AveragePrice DESC"
queryResult <- sqlQuery(channel, query)
str(queryResult)
queryResult

### Retrieve data and construct the table in R
query <- "SELECT Name, Type, Price
          FROM cellar
          WHERE Winery ='Te Mata'"
queryResult <- sqlQuery(channel, query)
str(queryResult)
TeMata <- queryResult[order(queryResult$Name,queryResult$Type),]
AveragePrice <- tapply(TeMata$Price,TeMata$Name,mean)
WineType <- unique(TeMata[,1:2])
WineType
data.frame(WineType,AveragePrice=as.numeric(AveragePrice))


q(save="no")