EvanZhang1
commented
1 year ago Some of my code cannot be read, and I think the problem is quotation marks, because there may be multiple references in my code.
Open EvanZhang1 opened 1 year ago
EvanZhang1
commented
1 year ago Some of my code cannot be read, and I think the problem is quotation marks, because there may be multiple references in my code.
install.packages("RSQLite") library("RSQLite") conn <- dbConnect(RSQLite::SQLite(),"FinalDB.sqlite")
CROP_DATA
df1 <- dbExecute(conn, "CREATE TABLE CROP_DATA ( CD_ID INTEGER NOT NULL, YEAR DATE NOT NULL, CROP_TYPE VARCHAR(20) NOT NULL, GEO VARCHAR(20) NOT NULL, SEEDED_AREA INTEGER NOT NULL, HARVESTED_AREA INTEGER NOT NULL, PRODUCTION INTEGER NOT NULL, AVG_YIELD INTEGER NOT NULL, PRIMARY KEY (CD_ID) )", errors=FALSE) if (df1 == -1){ cat ("An error has occurred.\n") msg <- odbcGetErrMsg(conn) print (msg) } else { cat ("Table was created successfully.\n") }
Farm_Prices
df2<- dbExecute(conn, "CREATE TABLE FARM_PRICES ( CD_ID INTEGER NOT NULL, DATE DATE NOT NULL, CROP_TYPE VARCHAR(20) NOT NULL, GEO VARCHAR(20) NOT NULL, PRICE_PRERMT FLOAT(6), PRIMARY KEY(CD_ID) )", ERRORS= FALSE)
if (df2 == -1){ cat ("An error has occurred.\n") msg <- odbcGetErrMsg(conn) print (msg) } else { cat ("Table was created successfully.\n") }
DAILY_fx
df3 <- dbExecute(conn, "CREATE TABLE DAILY_FX ( DFX_ID INTEGER NOT NULL, DATE DATE NOT NULL, FXUSDCAD FLOAT(6), PRIMARY KEY (DFX_ID) )", errors=FALSE)
if (df3 == -1){ cat ("An error has occurred.\n") msg <- odbcGetErrMsg(conn) print (msg) } else { cat ("Table was created successfully.\n") }
MONTHLY_FX
df4 <- dbExecute(conn, "CREATE TABLE MONTHLY_FX( DFX_ID INTEGER NOT NULL, DATE DATE NOT NULL, FXUSDCAD FLOAT(6), PRIMARY KEY (DFX_ID))", ERRORS= FALSE)
if (df4 == -1){ cat ("An error has occurred.\n") msg <- odbcGetErrMsg(conn) print (msg) } else { cat ("Table was created successfully.\n") }
crop_df <- read.csv('Annual_Crop_Data.csv', colClasses=c(YEAR="character")) FARM_PRICES_df <- read.csv('Monthly_Farm_Prices.csv',colClasses=c(date="Character")) daily_df <- read.csv('Daily_FX.csv', colClasses=c(date="character")) monthly_df<- read.csv('Monthly_FX.csv', colClasses=c(date="Character"))
dbWriteTable(conn, "CROP_DATA", crop_df, overwrite=TRUE, header = TRUE) dbWriteTable(conn, "FARM_PRICES", FARM_PRICES_df, overwrite=TRUE, header = TRUE) dbWriteTable(conn, "DAILY_FX", daily_df, overwrite=TRUE, header = TRUE) dbWriteTable(conn, "MONTHLY_FX", monthly_df, overwrite=TRUE, header = TRUE)
dbListTables(conn)
dbGetQuery(conn, 'SELECT COUNT(CD_ID) FROM CROP_DATA')
dbGetQuery(conn, 'SELECT DISTINCT GEO FROM Farm_Prices')
dbGetQuery(conn, 'SELECT SUM(HARVESTED_AREA) FROM CROP_DATA WHERE GEO= ‘Canada’ and YEAR(YEAR) = ‘1968’ and CROP_type = ‘Rye')
dbGetQuery(conn, 'SELECT Price_Prermt, crop_type FROM CROP_DATA WHERE crop_type = \'Rye\' LIMIT 6')
dbGetQuery(conn, 'SELECT DISTINCT GEO FROM CROP_DATA where crop_type = 'Barley'')
dbGetQuery(conn, 'SELECT Max(Year),Min(Year) from CROP_DATA')
dbGetQuery(conn, 'SELECT DISTINCT CROP_TYPE FROM Farm_Prices where PRICE_PRERMT >=350')
dbGetQuery(conn, "select crop_type, Avg_yield from CROP_DATA where YEAR(YEAR)= '2000' order by avg_yield;")
dbGetQuery(conn, 'select Distinct GEO, (select sum(HARVESTED_AREA) from crop_type )as Sum_HARVESTED from crop_type where GEO = 'Canada'';