R

use yelp_db; sELECT count(DISTINCT(STATE)) FROM business; SELECT count(DISTINCT(CITY)) FROM business; 51*963 all possible combinations Existing combinations select distinct state,city from business; /* SQL QUERY2 */ SELECT distinct(name) ,review_count, is_open as is_active FROM business WHERE STATE ='ON' order by review_count desc limit 10; 10 ROWS SELECT distinct(name) ,review_count , is_open as is_active FROM business WHERE STATE ='ON'  order by review_count desc limit 10; /*SQL Query 3 */ select  id, text, (useful + funny + cool ) as  Review_Score from review limit 100; /*SQL Query 4 */ select  id, text, (useful + funny + cool ) as  Review_Score from review limit 10000; /*SQL Query 5 */ select character_length(text) from review;   SELECT POSITION("X" IN "Harry Potter and the Order of the Phoenix") AS MatchPosition;                 

use yelp_db; /* SQL Query 1 */ select ID, NAME,STATE ,count(state) from business group by STATE ORDER BY count(state) DESC; 51 Rows /*SQL Query 2 */ select STATE,count(state),AVG(review_count),AVG(stars) from business group by 1 order by 2 DESC ; 51 Rows /*SQL Query 3 */ select STATE,count(state),AVG(review_count),AVG(stars) from business group by 1 having count(state)>='1000' order by 2 DESC ; 11 Rows /*SQL Query 4 */ select state,avg(stars), avg(review_count) from business group by state order by count(state) ; 51 Rows /*SQL Query 5 */ select state,avg(stars), avg(review_count) from business GROUP BY state having  avg(stars) >= 3 order by count(state);  50 Rows ;         order by count(state) ; select * FROM business where state ='ABE'; select * from business; Group by STARS;

SELECT * FROM yelp_db.category; businesss - review count category- restaurant; use yelp_db; select * from category c , business b where (c.category = 'Restaurants'  AND b.id = c.business_id and b.review_count >1000); select b.name,b.city,b.state,b.review_count,c.category from category c , business b where (c.category = 'Restaurants'  AND b.id = c.business_id ) group  by b.state order by b.review_count; select b.name,b.city,b.state,b.review_count,c.category from category c , business b where (c.category = 'Restaurants'  AND b.id = c.business_id ) group  by b.city order by b.review_count; select b.id from review r, business b, category c where r.date > '2017-05-01 00:00:00' and b.id =r.id;

library(twitteR) library(httr) chennai = searchTwitter("#chennai", n=50) chennai[[1]]$text f=function(k){ for(i in 1:50) {   if(chennai[[i]]$isRetweet)     {k=k+1} } return(k)} f(0) if(chennai[[10]]$isRetweet==T){print(TRUE)} else {print(FALSE)} vec=c(1,2,3) sum_vec=function(vec){   sum=0   for(i in 1:length(vec))   {     sum=sum+vec[i]   }   return(sum)} sum_vec(vec) library(RMySQL) con=dbConnect(MySQL(),user="itlab",password="itlab@321", host="10.21.33.3",dbname="yelp_db") business_data = dbGetQuery(conn = con, statement = "select * from business") class(business_data) city_count= dbGetQuery(conn = con, statement = "select count(*),state,city from business  group by state,city order by count(*) desc") city_count=aggregate(x=business_data$id, by=list(business_data$state,business_data$city),FUN =length) ordered_city_count=city_count[order(city_count$x,city_count$Group.2, decr

#create a numeric vector vector1<-rep(1,10) vector1  #[1] 1 1 1 1 1 1 1 1 1 1 #create a character vector - 'letters' and 'LETTERS' are predefined in R vector2<-letters[1:7] vector2 #[1] "a" "b" "c" "d" "e" #create list of two vectors L1=list(vector1,vector2) #Accessing elements of a list L1[[1]] # [1] 1 1 1 1 1 1 1 1 1 1 L1[[2]] #[1] "a" "b" "c" "d" "e" #Creating list dynamically L2<-list();L2 #list() L2[[1]]<-vector1; L2[[1]] # [1] 1 1 1 1 1 1 1 1 1 1 L2[[2]]<-vector2; L2[[1]] # [1] 1 1 1 1 1 1 1 1 1 1 L2[[2]] #[1] "a" "b" "c" "d" "e" #accessing an element in the list L2[[2]] #[1] "a" "b" "c" "d" "e" L2[[2]][2] #[1] "b" #creating list with names of elements L3=list(vector1=vector1,vector2=vector2) L3$vec

x<-matrix(1:10,2,5) x #     [,1] [,2] [,3] [,4] [,5] # [1,]    1    3    5    7    9 # [2,]    2    4    6    8   10 y<-data.frame(x) y #  X1 X2 X3 X4 X5 #1  1  3  5  7  9 # 2  2  4  6  8 10 #check class of data frame class(y) #[1] "data.frame" #check column names names(y) #[1] "X1" "X2" "X3" "X4" "X5" #update column names names(y)[1] #[1] "X1" names(y)[1]<-"column1" names(y) #[1] "column1" "X2"      "X3"      "X4"      "X5" #creating data frame from columns zz<-data.frame(col1=rep(1,10),col2=rep(10,10)) zz #   col1 col2 #1     1   10 #2     1   10 #3     1   10 #4     1   10 #5     1   10 #6     1   10 #7     1   10 #8     1   10 #9     1   10 #10    1   10 #row names of a data frame rownames(zz)  #[1] "1"  "2"  "3"  "4"  "5"  "6"  "7&qu