Video link https://youtu.be/habwU-E6hBw
In this example we will attempt to use the SELECT, INSERT, UPDATE and DELETE commands using SQL.
We will also look at calculating simple statistics like min, max, mean, variance using SQL.
If you do not have these packages already installed on your computer then please install them
using the following command to install the package( if not already installed) install.packages(sqldf)
Once you have your packages installed then we will load the packages in the r environment using the following commands
# Packages used
# Call the package
library(sqldf)We will be using the built in dataset called iris.
data("iris")
head(iris)## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3.0 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5.0 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosadf <- sqldf("Select * from iris")
df## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3.0 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5.0 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## 7 4.6 3.4 1.4 0.3 setosa
## 8 5.0 3.4 1.5 0.2 setosa
## 9 4.4 2.9 1.4 0.2 setosa
## 10 4.9 3.1 1.5 0.1 setosa
## 11 5.4 3.7 1.5 0.2 setosa
## 12 4.8 3.4 1.6 0.2 setosa
## 13 4.8 3.0 1.4 0.1 setosa
## 14 4.3 3.0 1.1 0.1 setosa
## 15 5.8 4.0 1.2 0.2 setosa
## 16 5.7 4.4 1.5 0.4 setosa
## 17 5.4 3.9 1.3 0.4 setosa
## 18 5.1 3.5 1.4 0.3 setosa
## 19 5.7 3.8 1.7 0.3 setosa
## 20 5.1 3.8 1.5 0.3 setosa
## 21 5.4 3.4 1.7 0.2 setosa
## 22 5.1 3.7 1.5 0.4 setosa
## 23 4.6 3.6 1.0 0.2 setosa
## 24 5.1 3.3 1.7 0.5 setosa
## 25 4.8 3.4 1.9 0.2 setosa
## 26 5.0 3.0 1.6 0.2 setosa
## 27 5.0 3.4 1.6 0.4 setosa
## 28 5.2 3.5 1.5 0.2 setosa
## 29 5.2 3.4 1.4 0.2 setosa
## 30 4.7 3.2 1.6 0.2 setosa
## 31 4.8 3.1 1.6 0.2 setosa
## 32 5.4 3.4 1.5 0.4 setosa
## 33 5.2 4.1 1.5 0.1 setosa
## 34 5.5 4.2 1.4 0.2 setosa
## 35 4.9 3.1 1.5 0.2 setosa
## 36 5.0 3.2 1.2 0.2 setosa
## 37 5.5 3.5 1.3 0.2 setosa
## 38 4.9 3.6 1.4 0.1 setosa
## 39 4.4 3.0 1.3 0.2 setosa
## 40 5.1 3.4 1.5 0.2 setosa
## 41 5.0 3.5 1.3 0.3 setosa
## 42 4.5 2.3 1.3 0.3 setosa
## 43 4.4 3.2 1.3 0.2 setosa
## 44 5.0 3.5 1.6 0.6 setosa
## 45 5.1 3.8 1.9 0.4 setosa
## 46 4.8 3.0 1.4 0.3 setosa
## 47 5.1 3.8 1.6 0.2 setosa
## 48 4.6 3.2 1.4 0.2 setosa
## 49 5.3 3.7 1.5 0.2 setosa
## 50 5.0 3.3 1.4 0.2 setosa
## 51 7.0 3.2 4.7 1.4 versicolor
## 52 6.4 3.2 4.5 1.5 versicolor
## 53 6.9 3.1 4.9 1.5 versicolor
## 54 5.5 2.3 4.0 1.3 versicolor
## 55 6.5 2.8 4.6 1.5 versicolor
## 56 5.7 2.8 4.5 1.3 versicolor
## 57 6.3 3.3 4.7 1.6 versicolor
## 58 4.9 2.4 3.3 1.0 versicolor
## 59 6.6 2.9 4.6 1.3 versicolor
## 60 5.2 2.7 3.9 1.4 versicolor
## 61 5.0 2.0 3.5 1.0 versicolor
## 62 5.9 3.0 4.2 1.5 versicolor
## 63 6.0 2.2 4.0 1.0 versicolor
## 64 6.1 2.9 4.7 1.4 versicolor
## 65 5.6 2.9 3.6 1.3 versicolor
## 66 6.7 3.1 4.4 1.4 versicolor
## 67 5.6 3.0 4.5 1.5 versicolor
## 68 5.8 2.7 4.1 1.0 versicolor
## 69 6.2 2.2 4.5 1.5 versicolor
## 70 5.6 2.5 3.9 1.1 versicolor
## 71 5.9 3.2 4.8 1.8 versicolor
## 72 6.1 2.8 4.0 1.3 versicolor
## 73 6.3 2.5 4.9 1.5 versicolor
## 74 6.1 2.8 4.7 1.2 versicolor
## 75 6.4 2.9 4.3 1.3 versicolor
## 76 6.6 3.0 4.4 1.4 versicolor
## 77 6.8 2.8 4.8 1.4 versicolor
## 78 6.7 3.0 5.0 1.7 versicolor
## 79 6.0 2.9 4.5 1.5 versicolor
## 80 5.7 2.6 3.5 1.0 versicolor
## 81 5.5 2.4 3.8 1.1 versicolor
## 82 5.5 2.4 3.7 1.0 versicolor
## 83 5.8 2.7 3.9 1.2 versicolor
## 84 6.0 2.7 5.1 1.6 versicolor
## 85 5.4 3.0 4.5 1.5 versicolor
## 86 6.0 3.4 4.5 1.6 versicolor
## 87 6.7 3.1 4.7 1.5 versicolor
## 88 6.3 2.3 4.4 1.3 versicolor
## 89 5.6 3.0 4.1 1.3 versicolor
## 90 5.5 2.5 4.0 1.3 versicolor
## 91 5.5 2.6 4.4 1.2 versicolor
## 92 6.1 3.0 4.6 1.4 versicolor
## 93 5.8 2.6 4.0 1.2 versicolor
## 94 5.0 2.3 3.3 1.0 versicolor
## 95 5.6 2.7 4.2 1.3 versicolor
## 96 5.7 3.0 4.2 1.2 versicolor
## 97 5.7 2.9 4.2 1.3 versicolor
## 98 6.2 2.9 4.3 1.3 versicolor
## 99 5.1 2.5 3.0 1.1 versicolor
## 100 5.7 2.8 4.1 1.3 versicolor
## 101 6.3 3.3 6.0 2.5 virginica
## 102 5.8 2.7 5.1 1.9 virginica
## 103 7.1 3.0 5.9 2.1 virginica
## 104 6.3 2.9 5.6 1.8 virginica
## 105 6.5 3.0 5.8 2.2 virginica
## 106 7.6 3.0 6.6 2.1 virginica
## 107 4.9 2.5 4.5 1.7 virginica
## 108 7.3 2.9 6.3 1.8 virginica
## 109 6.7 2.5 5.8 1.8 virginica
## 110 7.2 3.6 6.1 2.5 virginica
## 111 6.5 3.2 5.1 2.0 virginica
## 112 6.4 2.7 5.3 1.9 virginica
## 113 6.8 3.0 5.5 2.1 virginica
## 114 5.7 2.5 5.0 2.0 virginica
## 115 5.8 2.8 5.1 2.4 virginica
## 116 6.4 3.2 5.3 2.3 virginica
## 117 6.5 3.0 5.5 1.8 virginica
## 118 7.7 3.8 6.7 2.2 virginica
## 119 7.7 2.6 6.9 2.3 virginica
## 120 6.0 2.2 5.0 1.5 virginica
## 121 6.9 3.2 5.7 2.3 virginica
## 122 5.6 2.8 4.9 2.0 virginica
## 123 7.7 2.8 6.7 2.0 virginica
## 124 6.3 2.7 4.9 1.8 virginica
## 125 6.7 3.3 5.7 2.1 virginica
## 126 7.2 3.2 6.0 1.8 virginica
## 127 6.2 2.8 4.8 1.8 virginica
## 128 6.1 3.0 4.9 1.8 virginica
## 129 6.4 2.8 5.6 2.1 virginica
## 130 7.2 3.0 5.8 1.6 virginica
## 131 7.4 2.8 6.1 1.9 virginica
## 132 7.9 3.8 6.4 2.0 virginica
## 133 6.4 2.8 5.6 2.2 virginica
## 134 6.3 2.8 5.1 1.5 virginica
## 135 6.1 2.6 5.6 1.4 virginica
## 136 7.7 3.0 6.1 2.3 virginica
## 137 6.3 3.4 5.6 2.4 virginica
## 138 6.4 3.1 5.5 1.8 virginica
## 139 6.0 3.0 4.8 1.8 virginica
## 140 6.9 3.1 5.4 2.1 virginica
## 141 6.7 3.1 5.6 2.4 virginica
## 142 6.9 3.1 5.1 2.3 virginica
## 143 5.8 2.7 5.1 1.9 virginica
## 144 6.8 3.2 5.9 2.3 virginica
## 145 6.7 3.3 5.7 2.5 virginica
## 146 6.7 3.0 5.2 2.3 virginica
## 147 6.3 2.5 5.0 1.9 virginica
## 148 6.5 3.0 5.2 2.0 virginica
## 149 6.2 3.4 5.4 2.3 virginica
## 150 5.9 3.0 5.1 1.8 virginicadf <- sqldf("Select [Sepal.Width] as SepalWidth, Species from iris")
df## SepalWidth Species
## 1 3.5 setosa
## 2 3.0 setosa
## 3 3.2 setosa
## 4 3.1 setosa
## 5 3.6 setosa
## 6 3.9 setosa
## 7 3.4 setosa
## 8 3.4 setosa
## 9 2.9 setosa
## 10 3.1 setosa
## 11 3.7 setosa
## 12 3.4 setosa
## 13 3.0 setosa
## 14 3.0 setosa
## 15 4.0 setosa
## 16 4.4 setosa
## 17 3.9 setosa
## 18 3.5 setosa
## 19 3.8 setosa
## 20 3.8 setosa
## 21 3.4 setosa
## 22 3.7 setosa
## 23 3.6 setosa
## 24 3.3 setosa
## 25 3.4 setosa
## 26 3.0 setosa
## 27 3.4 setosa
## 28 3.5 setosa
## 29 3.4 setosa
## 30 3.2 setosa
## 31 3.1 setosa
## 32 3.4 setosa
## 33 4.1 setosa
## 34 4.2 setosa
## 35 3.1 setosa
## 36 3.2 setosa
## 37 3.5 setosa
## 38 3.6 setosa
## 39 3.0 setosa
## 40 3.4 setosa
## 41 3.5 setosa
## 42 2.3 setosa
## 43 3.2 setosa
## 44 3.5 setosa
## 45 3.8 setosa
## 46 3.0 setosa
## 47 3.8 setosa
## 48 3.2 setosa
## 49 3.7 setosa
## 50 3.3 setosa
## 51 3.2 versicolor
## 52 3.2 versicolor
## 53 3.1 versicolor
## 54 2.3 versicolor
## 55 2.8 versicolor
## 56 2.8 versicolor
## 57 3.3 versicolor
## 58 2.4 versicolor
## 59 2.9 versicolor
## 60 2.7 versicolor
## 61 2.0 versicolor
## 62 3.0 versicolor
## 63 2.2 versicolor
## 64 2.9 versicolor
## 65 2.9 versicolor
## 66 3.1 versicolor
## 67 3.0 versicolor
## 68 2.7 versicolor
## 69 2.2 versicolor
## 70 2.5 versicolor
## 71 3.2 versicolor
## 72 2.8 versicolor
## 73 2.5 versicolor
## 74 2.8 versicolor
## 75 2.9 versicolor
## 76 3.0 versicolor
## 77 2.8 versicolor
## 78 3.0 versicolor
## 79 2.9 versicolor
## 80 2.6 versicolor
## 81 2.4 versicolor
## 82 2.4 versicolor
## 83 2.7 versicolor
## 84 2.7 versicolor
## 85 3.0 versicolor
## 86 3.4 versicolor
## 87 3.1 versicolor
## 88 2.3 versicolor
## 89 3.0 versicolor
## 90 2.5 versicolor
## 91 2.6 versicolor
## 92 3.0 versicolor
## 93 2.6 versicolor
## 94 2.3 versicolor
## 95 2.7 versicolor
## 96 3.0 versicolor
## 97 2.9 versicolor
## 98 2.9 versicolor
## 99 2.5 versicolor
## 100 2.8 versicolor
## 101 3.3 virginica
## 102 2.7 virginica
## 103 3.0 virginica
## 104 2.9 virginica
## 105 3.0 virginica
## 106 3.0 virginica
## 107 2.5 virginica
## 108 2.9 virginica
## 109 2.5 virginica
## 110 3.6 virginica
## 111 3.2 virginica
## 112 2.7 virginica
## 113 3.0 virginica
## 114 2.5 virginica
## 115 2.8 virginica
## 116 3.2 virginica
## 117 3.0 virginica
## 118 3.8 virginica
## 119 2.6 virginica
## 120 2.2 virginica
## 121 3.2 virginica
## 122 2.8 virginica
## 123 2.8 virginica
## 124 2.7 virginica
## 125 3.3 virginica
## 126 3.2 virginica
## 127 2.8 virginica
## 128 3.0 virginica
## 129 2.8 virginica
## 130 3.0 virginica
## 131 2.8 virginica
## 132 3.8 virginica
## 133 2.8 virginica
## 134 2.8 virginica
## 135 2.6 virginica
## 136 3.0 virginica
## 137 3.4 virginica
## 138 3.1 virginica
## 139 3.0 virginica
## 140 3.1 virginica
## 141 3.1 virginica
## 142 3.1 virginica
## 143 2.7 virginica
## 144 3.2 virginica
## 145 3.3 virginica
## 146 3.0 virginica
## 147 2.5 virginica
## 148 3.0 virginica
## 149 3.4 virginica
## 150 3.0 virginicaIn this example we only want the first 2 rows to be returned.
df <- sqldf("Select [Sepal.Width] as SepalWidth, Species from iris limit 2")
df## SepalWidth Species
## 1 3.5 setosa
## 2 3.0 setosa# By default data is sorted in ascending order
df <- sqldf("Select [Sepal.Width] as SepalWidth, Species from iris order by SepalWidth")
df## SepalWidth Species
## 1 2.0 versicolor
## 2 2.2 versicolor
## 3 2.2 versicolor
## 4 2.2 virginica
## 5 2.3 setosa
## 6 2.3 versicolor
## 7 2.3 versicolor
## 8 2.3 versicolor
## 9 2.4 versicolor
## 10 2.4 versicolor
## 11 2.4 versicolor
## 12 2.5 versicolor
## 13 2.5 versicolor
## 14 2.5 versicolor
## 15 2.5 versicolor
## 16 2.5 virginica
## 17 2.5 virginica
## 18 2.5 virginica
## 19 2.5 virginica
## 20 2.6 versicolor
## 21 2.6 versicolor
## 22 2.6 versicolor
## 23 2.6 virginica
## 24 2.6 virginica
## 25 2.7 versicolor
## 26 2.7 versicolor
## 27 2.7 versicolor
## 28 2.7 versicolor
## 29 2.7 versicolor
## 30 2.7 virginica
## 31 2.7 virginica
## 32 2.7 virginica
## 33 2.7 virginica
## 34 2.8 versicolor
## 35 2.8 versicolor
## 36 2.8 versicolor
## 37 2.8 versicolor
## 38 2.8 versicolor
## 39 2.8 versicolor
## 40 2.8 virginica
## 41 2.8 virginica
## 42 2.8 virginica
## 43 2.8 virginica
## 44 2.8 virginica
## 45 2.8 virginica
## 46 2.8 virginica
## 47 2.8 virginica
## 48 2.9 setosa
## 49 2.9 versicolor
## 50 2.9 versicolor
## 51 2.9 versicolor
## 52 2.9 versicolor
## 53 2.9 versicolor
## 54 2.9 versicolor
## 55 2.9 versicolor
## 56 2.9 virginica
## 57 2.9 virginica
## 58 3.0 setosa
## 59 3.0 setosa
## 60 3.0 setosa
## 61 3.0 setosa
## 62 3.0 setosa
## 63 3.0 setosa
## 64 3.0 versicolor
## 65 3.0 versicolor
## 66 3.0 versicolor
## 67 3.0 versicolor
## 68 3.0 versicolor
## 69 3.0 versicolor
## 70 3.0 versicolor
## 71 3.0 versicolor
## 72 3.0 virginica
## 73 3.0 virginica
## 74 3.0 virginica
## 75 3.0 virginica
## 76 3.0 virginica
## 77 3.0 virginica
## 78 3.0 virginica
## 79 3.0 virginica
## 80 3.0 virginica
## 81 3.0 virginica
## 82 3.0 virginica
## 83 3.0 virginica
## 84 3.1 setosa
## 85 3.1 setosa
## 86 3.1 setosa
## 87 3.1 setosa
## 88 3.1 versicolor
## 89 3.1 versicolor
## 90 3.1 versicolor
## 91 3.1 virginica
## 92 3.1 virginica
## 93 3.1 virginica
## 94 3.1 virginica
## 95 3.2 setosa
## 96 3.2 setosa
## 97 3.2 setosa
## 98 3.2 setosa
## 99 3.2 setosa
## 100 3.2 versicolor
## 101 3.2 versicolor
## 102 3.2 versicolor
## 103 3.2 virginica
## 104 3.2 virginica
## 105 3.2 virginica
## 106 3.2 virginica
## 107 3.2 virginica
## 108 3.3 setosa
## 109 3.3 setosa
## 110 3.3 versicolor
## 111 3.3 virginica
## 112 3.3 virginica
## 113 3.3 virginica
## 114 3.4 setosa
## 115 3.4 setosa
## 116 3.4 setosa
## 117 3.4 setosa
## 118 3.4 setosa
## 119 3.4 setosa
## 120 3.4 setosa
## 121 3.4 setosa
## 122 3.4 setosa
## 123 3.4 versicolor
## 124 3.4 virginica
## 125 3.4 virginica
## 126 3.5 setosa
## 127 3.5 setosa
## 128 3.5 setosa
## 129 3.5 setosa
## 130 3.5 setosa
## 131 3.5 setosa
## 132 3.6 setosa
## 133 3.6 setosa
## 134 3.6 setosa
## 135 3.6 virginica
## 136 3.7 setosa
## 137 3.7 setosa
## 138 3.7 setosa
## 139 3.8 setosa
## 140 3.8 setosa
## 141 3.8 setosa
## 142 3.8 setosa
## 143 3.8 virginica
## 144 3.8 virginica
## 145 3.9 setosa
## 146 3.9 setosa
## 147 4.0 setosa
## 148 4.1 setosa
## 149 4.2 setosa
## 150 4.4 setosa# Sorted in ascending order
df <- sqldf("Select [Sepal.Width] as SepalWidth, Species from iris order by SepalWidth asc")
df## SepalWidth Species
## 1 2.0 versicolor
## 2 2.2 versicolor
## 3 2.2 versicolor
## 4 2.2 virginica
## 5 2.3 setosa
## 6 2.3 versicolor
## 7 2.3 versicolor
## 8 2.3 versicolor
## 9 2.4 versicolor
## 10 2.4 versicolor
## 11 2.4 versicolor
## 12 2.5 versicolor
## 13 2.5 versicolor
## 14 2.5 versicolor
## 15 2.5 versicolor
## 16 2.5 virginica
## 17 2.5 virginica
## 18 2.5 virginica
## 19 2.5 virginica
## 20 2.6 versicolor
## 21 2.6 versicolor
## 22 2.6 versicolor
## 23 2.6 virginica
## 24 2.6 virginica
## 25 2.7 versicolor
## 26 2.7 versicolor
## 27 2.7 versicolor
## 28 2.7 versicolor
## 29 2.7 versicolor
## 30 2.7 virginica
## 31 2.7 virginica
## 32 2.7 virginica
## 33 2.7 virginica
## 34 2.8 versicolor
## 35 2.8 versicolor
## 36 2.8 versicolor
## 37 2.8 versicolor
## 38 2.8 versicolor
## 39 2.8 versicolor
## 40 2.8 virginica
## 41 2.8 virginica
## 42 2.8 virginica
## 43 2.8 virginica
## 44 2.8 virginica
## 45 2.8 virginica
## 46 2.8 virginica
## 47 2.8 virginica
## 48 2.9 setosa
## 49 2.9 versicolor
## 50 2.9 versicolor
## 51 2.9 versicolor
## 52 2.9 versicolor
## 53 2.9 versicolor
## 54 2.9 versicolor
## 55 2.9 versicolor
## 56 2.9 virginica
## 57 2.9 virginica
## 58 3.0 setosa
## 59 3.0 setosa
## 60 3.0 setosa
## 61 3.0 setosa
## 62 3.0 setosa
## 63 3.0 setosa
## 64 3.0 versicolor
## 65 3.0 versicolor
## 66 3.0 versicolor
## 67 3.0 versicolor
## 68 3.0 versicolor
## 69 3.0 versicolor
## 70 3.0 versicolor
## 71 3.0 versicolor
## 72 3.0 virginica
## 73 3.0 virginica
## 74 3.0 virginica
## 75 3.0 virginica
## 76 3.0 virginica
## 77 3.0 virginica
## 78 3.0 virginica
## 79 3.0 virginica
## 80 3.0 virginica
## 81 3.0 virginica
## 82 3.0 virginica
## 83 3.0 virginica
## 84 3.1 setosa
## 85 3.1 setosa
## 86 3.1 setosa
## 87 3.1 setosa
## 88 3.1 versicolor
## 89 3.1 versicolor
## 90 3.1 versicolor
## 91 3.1 virginica
## 92 3.1 virginica
## 93 3.1 virginica
## 94 3.1 virginica
## 95 3.2 setosa
## 96 3.2 setosa
## 97 3.2 setosa
## 98 3.2 setosa
## 99 3.2 setosa
## 100 3.2 versicolor
## 101 3.2 versicolor
## 102 3.2 versicolor
## 103 3.2 virginica
## 104 3.2 virginica
## 105 3.2 virginica
## 106 3.2 virginica
## 107 3.2 virginica
## 108 3.3 setosa
## 109 3.3 setosa
## 110 3.3 versicolor
## 111 3.3 virginica
## 112 3.3 virginica
## 113 3.3 virginica
## 114 3.4 setosa
## 115 3.4 setosa
## 116 3.4 setosa
## 117 3.4 setosa
## 118 3.4 setosa
## 119 3.4 setosa
## 120 3.4 setosa
## 121 3.4 setosa
## 122 3.4 setosa
## 123 3.4 versicolor
## 124 3.4 virginica
## 125 3.4 virginica
## 126 3.5 setosa
## 127 3.5 setosa
## 128 3.5 setosa
## 129 3.5 setosa
## 130 3.5 setosa
## 131 3.5 setosa
## 132 3.6 setosa
## 133 3.6 setosa
## 134 3.6 setosa
## 135 3.6 virginica
## 136 3.7 setosa
## 137 3.7 setosa
## 138 3.7 setosa
## 139 3.8 setosa
## 140 3.8 setosa
## 141 3.8 setosa
## 142 3.8 setosa
## 143 3.8 virginica
## 144 3.8 virginica
## 145 3.9 setosa
## 146 3.9 setosa
## 147 4.0 setosa
## 148 4.1 setosa
## 149 4.2 setosa
## 150 4.4 setosadf <- sqldf("Select [Sepal.Width] as SepalWidth, Species from iris order by SepalWidth desc")
df## SepalWidth Species
## 1 4.4 setosa
## 2 4.2 setosa
## 3 4.1 setosa
## 4 4.0 setosa
## 5 3.9 setosa
## 6 3.9 setosa
## 7 3.8 setosa
## 8 3.8 setosa
## 9 3.8 setosa
## 10 3.8 setosa
## 11 3.8 virginica
## 12 3.8 virginica
## 13 3.7 setosa
## 14 3.7 setosa
## 15 3.7 setosa
## 16 3.6 setosa
## 17 3.6 setosa
## 18 3.6 setosa
## 19 3.6 virginica
## 20 3.5 setosa
## 21 3.5 setosa
## 22 3.5 setosa
## 23 3.5 setosa
## 24 3.5 setosa
## 25 3.5 setosa
## 26 3.4 setosa
## 27 3.4 setosa
## 28 3.4 setosa
## 29 3.4 setosa
## 30 3.4 setosa
## 31 3.4 setosa
## 32 3.4 setosa
## 33 3.4 setosa
## 34 3.4 setosa
## 35 3.4 versicolor
## 36 3.4 virginica
## 37 3.4 virginica
## 38 3.3 setosa
## 39 3.3 setosa
## 40 3.3 versicolor
## 41 3.3 virginica
## 42 3.3 virginica
## 43 3.3 virginica
## 44 3.2 setosa
## 45 3.2 setosa
## 46 3.2 setosa
## 47 3.2 setosa
## 48 3.2 setosa
## 49 3.2 versicolor
## 50 3.2 versicolor
## 51 3.2 versicolor
## 52 3.2 virginica
## 53 3.2 virginica
## 54 3.2 virginica
## 55 3.2 virginica
## 56 3.2 virginica
## 57 3.1 setosa
## 58 3.1 setosa
## 59 3.1 setosa
## 60 3.1 setosa
## 61 3.1 versicolor
## 62 3.1 versicolor
## 63 3.1 versicolor
## 64 3.1 virginica
## 65 3.1 virginica
## 66 3.1 virginica
## 67 3.1 virginica
## 68 3.0 setosa
## 69 3.0 setosa
## 70 3.0 setosa
## 71 3.0 setosa
## 72 3.0 setosa
## 73 3.0 setosa
## 74 3.0 versicolor
## 75 3.0 versicolor
## 76 3.0 versicolor
## 77 3.0 versicolor
## 78 3.0 versicolor
## 79 3.0 versicolor
## 80 3.0 versicolor
## 81 3.0 versicolor
## 82 3.0 virginica
## 83 3.0 virginica
## 84 3.0 virginica
## 85 3.0 virginica
## 86 3.0 virginica
## 87 3.0 virginica
## 88 3.0 virginica
## 89 3.0 virginica
## 90 3.0 virginica
## 91 3.0 virginica
## 92 3.0 virginica
## 93 3.0 virginica
## 94 2.9 setosa
## 95 2.9 versicolor
## 96 2.9 versicolor
## 97 2.9 versicolor
## 98 2.9 versicolor
## 99 2.9 versicolor
## 100 2.9 versicolor
## 101 2.9 versicolor
## 102 2.9 virginica
## 103 2.9 virginica
## 104 2.8 versicolor
## 105 2.8 versicolor
## 106 2.8 versicolor
## 107 2.8 versicolor
## 108 2.8 versicolor
## 109 2.8 versicolor
## 110 2.8 virginica
## 111 2.8 virginica
## 112 2.8 virginica
## 113 2.8 virginica
## 114 2.8 virginica
## 115 2.8 virginica
## 116 2.8 virginica
## 117 2.8 virginica
## 118 2.7 versicolor
## 119 2.7 versicolor
## 120 2.7 versicolor
## 121 2.7 versicolor
## 122 2.7 versicolor
## 123 2.7 virginica
## 124 2.7 virginica
## 125 2.7 virginica
## 126 2.7 virginica
## 127 2.6 versicolor
## 128 2.6 versicolor
## 129 2.6 versicolor
## 130 2.6 virginica
## 131 2.6 virginica
## 132 2.5 versicolor
## 133 2.5 versicolor
## 134 2.5 versicolor
## 135 2.5 versicolor
## 136 2.5 virginica
## 137 2.5 virginica
## 138 2.5 virginica
## 139 2.5 virginica
## 140 2.4 versicolor
## 141 2.4 versicolor
## 142 2.4 versicolor
## 143 2.3 setosa
## 144 2.3 versicolor
## 145 2.3 versicolor
## 146 2.3 versicolor
## 147 2.2 versicolor
## 148 2.2 versicolor
## 149 2.2 virginica
## 150 2.0 versicolordf <- sqldf("Select max([Sepal.Width]) as MaxSepalWidth, Species from iris")
df## MaxSepalWidth Species
## 1 4.4 setosadf <- sqldf("Select min([Sepal.Width]) as MinSepalWidth, Species from iris")
df## MinSepalWidth Species
## 1 2 versicolordf <- sqldf("Select Count(*) as count, Species from iris group by Species")
df## count Species
## 1 50 setosa
## 2 50 versicolor
## 3 50 virginicadf <- sqldf("Select sum([Sepal.Width]) as SumSepalWidth, Species from iris group by Species")
df## SumSepalWidth Species
## 1 171.4 setosa
## 2 138.5 versicolor
## 3 148.7 virginicadf <- sqldf("Select avg([Sepal.Width]) as MeanSepalWidth, Species from iris group by Species")
df## MeanSepalWidth Species
## 1 3.428 setosa
## 2 2.770 versicolor
## 3 2.974 virginicadf <- sqldf("Select variance([Sepal.Width]) as VarianceSepalWidth, Species from iris group by Species")
df## VarianceSepalWidth Species
## 1 0.14368980 setosa
## 2 0.09846939 versicolor
## 3 0.10400408 virginicadf <- sqldf("Select stdev([Sepal.Width]) as sdSepalWidth, Species from iris group by Species")
df## sdSepalWidth Species
## 1 0.3790644 setosa
## 2 0.3137983 versicolor
## 3 0.3224966 virginicad <- data.frame(a = c('a','b','c')
, b= c(1 ,3 ,1)
)
d## a b
## 1 a 1
## 2 b 3
## 3 c 1# Now add a new row in the dataframe d
d <- sqldf(c("insert into d values('x',999)", "select * from d"))
d## a b
## 1 a 1
## 2 b 3
## 3 c 1
## 4 x 999d <- data.frame(a = c('a','b','c')
, b= c(1 ,3 ,1)
)
d## a b
## 1 a 1
## 2 b 3
## 3 c 1d <- sqldf(c("update d set a = 'yyy', b = 111 where a = 'a'", "select * from d"))
d## a b
## 1 yyy 111
## 2 b 3
## 3 c 1d <- sqldf(c("delete from d where a = 'yyy'", "select * from d"))
d## a b
## 1 b 3
## 2 c 1df1 <- sqldf("Select Count(*)
, min([Sepal.Width]) as MinSepalWidth
, max([Sepal.Width]) as MaxSepalWidth
, avg([Sepal.Width]) as MeanSepalWidth
, median([Sepal.Width]) as MedianSepalWidth
, variance([Sepal.Width]) as VarianceSepalWidth
, stdev([Sepal.Width]) as sdSepalWidth
from iris")
df1## Count(*) MinSepalWidth MaxSepalWidth MeanSepalWidth MedianSepalWidth
## 1 150 2 4.4 3.057333 3
## VarianceSepalWidth sdSepalWidth
## 1 0.1899794 0.4358663Use the appropriate field for example we used Species, then the result will give you the output for each of the Species
df1 <- sqldf("Select Species,Count(*) as count
, min([Sepal.Width]) as MinSepalWidth
, max([Sepal.Width]) as MaxSepalWidth
, avg([Sepal.Width]) as MeanSepalWidth
, median([Sepal.Width]) as MedianSepalWidth
, variance([Sepal.Width]) as VarianceSepalWidth
, stdev([Sepal.Width]) as sdSepalWidth
from iris group by Species
")
df1## Species count MinSepalWidth MaxSepalWidth MeanSepalWidth MedianSepalWidth
## 1 setosa 50 2.3 4.4 3.428 3.4
## 2 versicolor 50 2.0 3.4 2.770 2.8
## 3 virginica 50 2.2 3.8 2.974 3.0
## VarianceSepalWidth sdSepalWidth
## 1 0.14368980 0.3790644
## 2 0.09846939 0.3137983
## 3 0.10400408 0.3224966Video link https://youtu.be/habwU-E6hBw