This vignette aims to be a brief introduction and overview of using SQL. The tutorial will illustrate common functions by answering questions around Pixar films and their performance.
Load packages.
library(pixarfilms)
library(dplyr)
library(dbplyr)
#>
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#>
#> ident, sqlLoad data into temporary database to interact with.
Develop query.
Let’s execute it now.
DBI::dbGetQuery(conn = con, statement = query)
#> number film release_date run_time film_rating
#> 1 1 Toy Story 9456 81 G
#> 2 2 A Bug's Life 10555 95 G
#> 3 3 Toy Story 2 10919 92 G
#> 4 4 Monsters, Inc. 11628 92 G
#> 5 5 Finding Nemo 12202 100 G
#> 6 6 The Incredibles 12727 115 PG
#> 7 7 Cars 13308 116 G
#> 8 8 Ratatouille 13693 111 G
#> 9 9 WALL-E 14057 98 G
#> 10 10 Up 14393 96 PG
#> 11 11 Toy Story 3 14778 103 G
#> 12 12 Cars 2 15149 106 G
#> 13 13 Brave 15513 93 PG
#> 14 14 Monsters University 15877 104 G
#> 15 15 Inside Out 16605 95 PG
#> 16 16 The Good Dinosaur 16764 93 PG
#> 17 17 Finding Dory 16969 97 PG
#> 18 18 Cars 3 17333 102 G
#> 19 19 Coco 17492 105 PG
#> 20 20 Incredibles 2 17697 118 PG
#> 21 21 Toy Story 4 18068 100 G
#> 22 22 Onward 18327 102 PG
#> 23 23 Soul 18621 100 PG
#> 24 24 Luca 18796 95 PG
#> 25 25 Turning Red 19062 100 PG
#> 26 26 Lightyear 19160 105 PG
#> 27 27 Elemental 19524 101 PG
#> 28 28 Inside Out 2 19888 96 PG
#> plot
#> 1 A cowboy doll is profoundly threatened and jealous when a new spaceman action figure supplants him as top toy in a boy's bedroom.
#> 2 A misfit ant, looking for "warriors" to save his colony from greedy grasshoppers, recruits a group of bugs that turn out to be an inept circus troupe.
#> 3 When Woody is stolen by a toy collector, Buzz and his friends set out on a rescue mission to save Woody before he becomes a museum toy property with his roundup gang Jessie, Prospector, and Bullseye.
#> 4 In order to power the city, monsters have to scare children so that they scream. However, the children are toxic to the monsters, and after a child gets through, two monsters realize things may not be what they think.
#> 5 After his son is captured in the Great Barrier Reef and taken to Sydney, a timid clownfish sets out on a journey to bring him home.
#> 6 While trying to lead a quiet suburban life, a family of undercover superheroes are forced into action to save the world.
#> 7 On the way to the biggest race of his life, a hotshot rookie race car gets stranded in a rundown town and learns that winning isn't everything in life.
#> 8 A rat who can cook makes an unusual alliance with a young kitchen worker at a famous Paris restaurant.
#> 9 A robot who is responsible for cleaning a waste-covered Earth meets another robot and falls in love with her. Together, they set out on a journey that will alter the fate of mankind.
#> 10 78-year-old Carl Fredricksen travels to South America in his house equipped with balloons, inadvertently taking a young stowaway.
#> 11 The toys are mistakenly delivered to a day-care center instead of the attic right before Andy leaves for college, and it's up to Woody to convince the other toys that they weren't abandoned and to return home.
#> 12 Star race car Lightning McQueen and his pal Mater head overseas to compete in the World Grand Prix race. But the road to the championship becomes rocky as Mater gets caught up in an intriguing adventure of his own: international e...
#> 13 Determined to make her own path in life, Princess Merida defies a custom that brings chaos to her kingdom. Granted one wish, Merida must rely on her bravery and her archery skills to undo a beastly curse.
#> 14 A look at the relationship between Mike Wazowski and James P. "Sully" Sullivan during their days at Monsters University, when they weren't necessarily the best of friends.
#> 15 After young Riley is uprooted from her Midwest life and moved to San Francisco, her emotions - Joy, Fear, Anger, Disgust and Sadness - conflict on how best to navigate a new city, house, and school.
#> 16 In a world where dinosaurs and humans live side-by-side, an Apatosaurus named Arlo makes an unlikely human friend.
#> 17 Friendly but forgetful blue tang Dory begins a search for her long-lost parents and everyone learns a few things about the real meaning of family along the way.
#> 18 Lightning McQueen sets out to prove to a new generation of racers that he's still the best race car in the world.
#> 19 Aspiring musician Miguel, confronted with his family's ancestral ban on music, enters the Land of the Dead to find his great-great-grandfather, a legendary singer.
#> 20 The Incredibles family takes on a new mission which involves a change in family roles: Bob Parr (Mr. Incredible) must manage the house while his wife Helen (Elastigirl) goes out to save the world.
#> 21 When a new toy called "Forky" joins Woody and the gang, a road trip alongside old and new friends reveals how big the world can be for a toy.
#> 22 Teenage elf brothers Ian and Barley embark on a magical quest to spend one more day with their late father. Like any good adventure, their journey is filled with cryptic maps, impossible obstacles and unimaginable discoveries.
#> 23 Joe is a middle-school band teacher whose life hasn't quite gone the way he expected. His true passion is jazz. But when he travels to another realm to help someone find their passion, he soon discovers what it means to have soul.
#> 24 On the Italian Riviera, an unlikely but strong friendship grows between a human being and a sea monster disguised as a human.
#> 25 A thirteen-year-old girl named Mei Lee is torn between staying her mother's dutiful daughter and the changes of adolescence. And as if the challenges were not enough, whenever she gets overly excited she transforms into a giant re...
#> 26 While spending years attempting to return home, marooned Space Ranger Buzz Lightyear encounters an army of ruthless robots commanded by Zurg who are attempting to steal his fuel source.
#> 27 Follows Ember and Wade, in a city where fire-, water-, earth- and air-residents live together.
#> 28 A sequel that features Riley entering puberty and experiencing brand new, more complex emotions as a result. As Riley tries to adapt to her teenage years, her old emotions try to adapt to the possibility of being replaced.Here, let’s review the functions of SQL covered above.
SELECT = text that follows this chooses the columns you
want in your query, and doing SELECT * will select all
columnssessionInfo()
#> R version 4.4.2 (2024-10-31)
#> Platform: x86_64-pc-linux-gnu
#> Running under: Ubuntu 24.04.1 LTS
#>
#> Matrix products: default
#> BLAS: /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3
#> LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/libopenblasp-r0.3.26.so; LAPACK version 3.12.0
#>
#> locale:
#> [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
#> [3] LC_TIME=en_US.UTF-8 LC_COLLATE=C
#> [5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8
#> [7] LC_PAPER=en_US.UTF-8 LC_NAME=C
#> [9] LC_ADDRESS=C LC_TELEPHONE=C
#> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
#>
#> time zone: Etc/UTC
#> tzcode source: system (glibc)
#>
#> attached base packages:
#> [1] stats graphics grDevices utils datasets methods base
#>
#> other attached packages:
#> [1] dbplyr_2.5.0 irr_0.84.1 lpSolve_5.6.22 forcats_1.0.0
#> [5] tidyr_1.3.1 patchwork_1.3.0 lubridate_1.9.3 ggplot2_3.5.1
#> [9] dplyr_1.1.4 pixarfilms_0.2.1 rmarkdown_2.29
#>
#> loaded via a namespace (and not attached):
#> [1] sass_0.4.9 utf8_1.2.4 generics_0.1.3 RSQLite_2.3.8
#> [5] digest_0.6.37 magrittr_2.0.3 evaluate_1.0.1 grid_4.4.2
#> [9] timechange_0.3.0 RColorBrewer_1.1-3 blob_1.2.4 fastmap_1.2.0
#> [13] jsonlite_1.8.9 ggrepel_0.9.6 DBI_1.2.3 purrr_1.0.2
#> [17] fansi_1.0.6 scales_1.3.0 jquerylib_0.1.4 cli_3.6.3
#> [21] rlang_1.1.4 bit64_4.5.2 munsell_0.5.1 withr_3.0.2
#> [25] cachem_1.1.0 yaml_2.3.10 ggbeeswarm_0.7.2 tools_4.4.2
#> [29] memoise_2.0.1 colorspace_2.1-1 buildtools_1.0.0 vctrs_0.6.5
#> [33] R6_2.5.1 lifecycle_1.0.4 bit_4.5.0 vipor_0.4.7
#> [37] pkgconfig_2.0.3 beeswarm_0.4.0 pillar_1.9.0 bslib_0.8.0
#> [41] gtable_0.3.6 Rcpp_1.0.13-1 glue_1.8.0 xfun_0.49
#> [45] tibble_3.2.1 tidyselect_1.2.1 sys_3.4.3 knitr_1.49
#> [49] farver_2.1.2 htmltools_0.5.8.1 maketools_1.3.1 labeling_0.4.3
#> [53] compiler_4.4.2