Question

Your deliverables should include your SQL scripts and your R Markdown code, posted to GitHub.

This is by design a very open ended assignment. A variety of reasonable approaches are acceptable. You can (and should) blank out your SQL password if your solution requires it; otherwise, full credit requires that your code is “reproducible,” with the assumption that I have the same database server and R software.

You may work in a small group on this assignment. If you work in a group, each group member should indicate who they worked with, and all group members should individually submit their week 2 assignment.

Please start early, and do work that you would want to include in a “presentations portfolio” that you might share in a job interview with a potential employer! You are encouraged to share thoughts, ask, and answer clarifying questions in the “Week 2: R and SQL” forum.

Answer:

Create SQL table and store values. This data is stored in Azure SQL cloud DB.

#IF OBJECT_ID('dbo.MovieReviews', 'U') IS NOT NULL  DROP TABLE dbo.MovieReviews; 

#CREATE TABLE [dbo].[MovieReviews]([ID] [int] IDENTITY(1,1) NOT NULL,   [Name] [varchar](150) NOT NULL, [The_Cleanse] [int] NULL,   [Downsizing] [int] NULL,    [Lady_Bird] [int] NULL, [Get_Out] [int] NULL,   [The_Shape_of_Water] [int] NULL,    [The_Post] [int] NULL, CONSTRAINT [PK_MovieReviews] PRIMARY KEY CLUSTERED ( [ID] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

#INSERT INTO [dbo].[MovieReviews] ([Name] ,[The_Cleanse] ,[Downsizing] ,[Lady_Bird] ,[Get_Out] ,[The_Shape_of_Water] ,[The_Post]) VALUES  ('Carlos' ,1 ,4 ,5 ,3 ,3 ,2)

#INSERT INTO [dbo].[MovieReviews] ([Name] ,[The_Cleanse] ,[Downsizing] ,[Lady_Bird] ,[Get_Out] ,[The_Shape_of_Water] ,[The_Post]) VALUES  ('Victoria' ,3 ,4 ,2 ,1 ,2 ,3)

#INSERT INTO [dbo].[MovieReviews] ([Name] ,[The_Cleanse] ,[Downsizing] ,[Lady_Bird] ,[Get_Out] ,[The_Shape_of_Water] ,[The_Post]) VALUES  ('John' ,2 ,3 ,5 ,4 ,2 ,1)

#INSERT INTO [dbo].[MovieReviews] ([Name] ,[The_Cleanse] ,[Downsizing] ,[Lady_Bird] ,[Get_Out] ,[The_Shape_of_Water] ,[The_Post]) VALUES  ('Ahmed' ,5 ,2 ,5 ,3 ,1 ,3)

#INSERT INTO [dbo].[MovieReviews] ([Name] ,[The_Cleanse] ,[Downsizing] ,[Lady_Bird] ,[Get_Out] ,[The_Shape_of_Water] ,[The_Post]) VALUES  ('Paul' ,4 ,2 ,4 ,3 ,2 ,1)

#INSERT INTO [dbo].[MovieReviews] ([Name] ,[The_Cleanse] ,[Downsizing] ,[Lady_Bird] ,[Get_Out] ,[The_Shape_of_Water] ,[The_Post]) VALUES  ('Lou' ,5 ,4 ,4 ,3 ,2 ,4)

#SELECT ID, [Name] ,[The_Cleanse] ,[Downsizing] ,[Lady_Bird] ,[Get_Out] ,[The_Shape_of_Water] ,[The_Post] FROM [dbo].[MovieReviews]

Read data from Cloud SQL DB

library(odbc)
## Warning: package 'odbc' was built under R version 3.4.4
library(getPass)
## Warning: package 'getPass' was built under R version 3.4.4
con <- dbConnect(odbc(),
                 Driver = "{ODBC Driver 13 for SQL Server}",
                 Server = getPass(msg="Server Name: "),
                 Database = getPass(msg="Database Name: "),
                 UID = getPass(msg="UID: "),
                 PWD = getPass(msg="Password: "),
                 Port = 1433)
## Please enter password in TK window (Alt+Tab)
## Please enter password in TK window (Alt+Tab)
## Please enter password in TK window (Alt+Tab)
## Please enter password in TK window (Alt+Tab)
#You can load using dns name
#con <- dbConnect(odbc::odbc(), "dnsname")

#Check if the table exist
dbListTables(con, table_name = "MovieR%")
## [1] "MovieReviews"
#Read data
query<-paste0("SELECT ID, [Name] ,[The_Cleanse] ,[Downsizing] ,[Lady_Bird] ,[Get_Out] ,[The_Shape_of_Water] ,[The_Post] FROM [dbo].[MovieReviews]")
moviedata<-dbGetQuery(con,query)
moviedata
##   ID     Name The_Cleanse Downsizing Lady_Bird Get_Out The_Shape_of_Water
## 1  1   Carlos           1          4         5       3                  3
## 2  2 Victoria           3          4         2       1                  2
## 3  3     John           2          3         5       4                  2
## 4  4    Ahmed           5          2         5       3                  1
## 5  5     Paul           4          2         4       3                  2
## 6  6      Lou           5          4         4       3                  2
##   The_Post
## 1        2
## 2        3
## 3        1
## 4        3
## 5        1
## 6        4

Data cleanup

#Drop ID
moviedata$ID <- NULL
moviedata
##       Name The_Cleanse Downsizing Lady_Bird Get_Out The_Shape_of_Water
## 1   Carlos           1          4         5       3                  3
## 2 Victoria           3          4         2       1                  2
## 3     John           2          3         5       4                  2
## 4    Ahmed           5          2         5       3                  1
## 5     Paul           4          2         4       3                  2
## 6      Lou           5          4         4       3                  2
##   The_Post
## 1        2
## 2        3
## 3        1
## 4        3
## 5        1
## 6        4
summary(moviedata)
##      Name            The_Cleanse      Downsizing      Lady_Bird    
##  Length:6           Min.   :1.000   Min.   :2.000   Min.   :2.000  
##  Class :character   1st Qu.:2.250   1st Qu.:2.250   1st Qu.:4.000  
##  Mode  :character   Median :3.500   Median :3.500   Median :4.500  
##                     Mean   :3.333   Mean   :3.167   Mean   :4.167  
##                     3rd Qu.:4.750   3rd Qu.:4.000   3rd Qu.:5.000  
##                     Max.   :5.000   Max.   :4.000   Max.   :5.000  
##     Get_Out      The_Shape_of_Water    The_Post    
##  Min.   :1.000   Min.   :1          Min.   :1.000  
##  1st Qu.:3.000   1st Qu.:2          1st Qu.:1.250  
##  Median :3.000   Median :2          Median :2.500  
##  Mean   :2.833   Mean   :2          Mean   :2.333  
##  3rd Qu.:3.000   3rd Qu.:2          3rd Qu.:3.000  
##  Max.   :4.000   Max.   :3          Max.   :4.000