SQL intro

Steph Locke (@theStephLocke)

2019-02-18

Steph Locke (@theStephLocke)

  • Locke Data (@LockeData itsaLocke.com)
  • Microsoft AI MVP
  • Author

Agenda

  • Databases
  • Getting rows and columns
  • Altering columns
  • Aggregating data
  • Combining datasets

Prerequisites

  • R & RStudio
  • RSQLite & rmarkdown
  • kaggle.com/gabrio/board-games-dataset

Databases

Working with databases in R

library(DBI)
library(RSQLite)
dbConn = dbConnect(SQLite(), "../database.sqlite")

Our database

dbListTables(dbConn)
## [1] "BoardGames"               "bgg.ldaOut.top.documents"
## [3] "bgg.ldaOut.top.terms"     "bgg.ldaOut.topics"       
## [5] "bgg.topics"

SQL Chunks

Use {sql connection=dbConn}!

select 
`details.name`, `attributes.boardgamecategory`
from boardgames 
limit 10
Displaying records 1 - 10
details.name attributes.boardgamecategory
Die Macher Economic,Negotiation,Political
Dragonmaster Card Game,Fantasy
Samurai Abstract Strategy,Medieval
Tal der Könige Ancient
Acquire Economic
Mare Mediterraneum Civilization,Nautical
Cathedral Abstract Strategy
Lords of Creation Civilization,Fantasy
El Caballero Exploration
Elfenland Fantasy,Travel

Terminology

  • A database is a collection of data
  • A schema is a collection of related data
  • A table is a collection of data on a specific event or entity
  • A column is data on an attribute of the event or entity
  • A row is a unique event or entity
  • A key identifies a unique event or row

Keys

  • A key is a unique identifier of an entity or row
  • A business key is one or more columns that uniquely describe an entity e.g. first name + last name + DOB
  • A surrogate key is a numeric column that is easier to reference than the business key
  • A primary key is typically the surrogate key

Exercise

  1. Create a markdown document and connect to the database.
  2. Can you use the dbListColumns() function to find out what columns are in the boardgames table?

Answers

dbListFields(dbConn,"boardgames")
##  [1] "row_names"                               
##  [2] "game.id"                                 
##  [3] "game.type"                               
##  [4] "details.description"                     
##  [5] "details.image"                           
##  [6] "details.maxplayers"                      
##  [7] "details.maxplaytime"                     
##  [8] "details.minage"                          
##  [9] "details.minplayers"                      
## [10] "details.minplaytime"                     
## [11] "details.name"                            
## [12] "details.playingtime"                     
## [13] "details.thumbnail"                       
## [14] "details.yearpublished"                   
## [15] "attributes.boardgameartist"              
## [16] "attributes.boardgamecategory"            
## [17] "attributes.boardgamecompilation"         
## [18] "attributes.boardgamedesigner"            
## [19] "attributes.boardgameexpansion"           
## [20] "attributes.boardgamefamily"              
## [21] "attributes.boardgameimplementation"      
## [22] "attributes.boardgameintegration"         
## [23] "attributes.boardgamemechanic"            
## [24] "attributes.boardgamepublisher"           
## [25] "attributes.total"                        
## [26] "stats.average"                           
## [27] "stats.averageweight"                     
## [28] "stats.bayesaverage"                      
## [29] "stats.family.abstracts.bayesaverage"     
## [30] "stats.family.abstracts.pos"              
## [31] "stats.family.cgs.bayesaverage"           
## [32] "stats.family.cgs.pos"                    
## [33] "stats.family.childrensgames.bayesaverage"
## [34] "stats.family.childrensgames.pos"         
## [35] "stats.family.familygames.bayesaverage"   
## [36] "stats.family.familygames.pos"            
## [37] "stats.family.partygames.bayesaverage"    
## [38] "stats.family.partygames.pos"             
## [39] "stats.family.strategygames.bayesaverage" 
## [40] "stats.family.strategygames.pos"          
## [41] "stats.family.thematic.bayesaverage"      
## [42] "stats.family.thematic.pos"               
## [43] "stats.family.wargames.bayesaverage"      
## [44] "stats.family.wargames.pos"               
## [45] "stats.median"                            
## [46] "stats.numcomments"                       
## [47] "stats.numweights"                        
## [48] "stats.owned"                             
## [49] "stats.stddev"                            
## [50] "stats.subtype.boardgame.bayesaverage"    
## [51] "stats.subtype.boardgame.pos"             
## [52] "stats.trading"                           
## [53] "stats.usersrated"                        
## [54] "stats.wanting"                           
## [55] "stats.wishing"                           
## [56] "polls.language_dependence"               
## [57] "polls.suggested_numplayers.1"            
## [58] "polls.suggested_numplayers.10"           
## [59] "polls.suggested_numplayers.2"            
## [60] "polls.suggested_numplayers.3"            
## [61] "polls.suggested_numplayers.4"            
## [62] "polls.suggested_numplayers.5"            
## [63] "polls.suggested_numplayers.6"            
## [64] "polls.suggested_numplayers.7"            
## [65] "polls.suggested_numplayers.8"            
## [66] "polls.suggested_numplayers.9"            
## [67] "polls.suggested_numplayers.Over"         
## [68] "polls.suggested_playerage"               
## [69] "attributes.t.links.concat.2...."         
## [70] "stats.family.amiga.bayesaverage"         
## [71] "stats.family.amiga.pos"                  
## [72] "stats.family.arcade.bayesaverage"        
## [73] "stats.family.arcade.pos"                 
## [74] "stats.family.atarist.bayesaverage"       
## [75] "stats.family.atarist.pos"                
## [76] "stats.family.commodore64.bayesaverage"   
## [77] "stats.family.commodore64.pos"            
## [78] "stats.subtype.rpgitem.bayesaverage"      
## [79] "stats.subtype.rpgitem.pos"               
## [80] "stats.subtype.videogame.bayesaverage"    
## [81] "stats.subtype.videogame.pos"

Getting rows and columns

The core statement

select *
from boardgames
limit 10
Displaying records 1 - 10
row_names game.id game.type details.description details.image details.maxplayers details.maxplaytime details.minage details.minplayers details.minplaytime details.name details.playingtime details.thumbnail details.yearpublished attributes.boardgameartist attributes.boardgamecategory attributes.boardgamecompilation attributes.boardgamedesigner attributes.boardgameexpansion attributes.boardgamefamily attributes.boardgameimplementation attributes.boardgameintegration attributes.boardgamemechanic attributes.boardgamepublisher attributes.total stats.average stats.averageweight stats.bayesaverage stats.family.abstracts.bayesaverage stats.family.abstracts.pos stats.family.cgs.bayesaverage stats.family.cgs.pos stats.family.childrensgames.bayesaverage stats.family.childrensgames.pos stats.family.familygames.bayesaverage stats.family.familygames.pos stats.family.partygames.bayesaverage stats.family.partygames.pos stats.family.strategygames.bayesaverage stats.family.strategygames.pos stats.family.thematic.bayesaverage stats.family.thematic.pos stats.family.wargames.bayesaverage stats.family.wargames.pos stats.median stats.numcomments stats.numweights stats.owned stats.stddev stats.subtype.boardgame.bayesaverage stats.subtype.boardgame.pos stats.trading stats.usersrated stats.wanting stats.wishing polls.language_dependence polls.suggested_numplayers.1 polls.suggested_numplayers.10 polls.suggested_numplayers.2 polls.suggested_numplayers.3 polls.suggested_numplayers.4 polls.suggested_numplayers.5 polls.suggested_numplayers.6 polls.suggested_numplayers.7 polls.suggested_numplayers.8 polls.suggested_numplayers.9 polls.suggested_numplayers.Over polls.suggested_playerage attributes.t.links.concat.2…. stats.family.amiga.bayesaverage stats.family.amiga.pos stats.family.arcade.bayesaverage stats.family.arcade.pos stats.family.atarist.bayesaverage stats.family.atarist.pos stats.family.commodore64.bayesaverage stats.family.commodore64.pos stats.subtype.rpgitem.bayesaverage stats.subtype.rpgitem.pos stats.subtype.videogame.bayesaverage stats.subtype.videogame.pos
1 1 boardgame Die Macher is a game about seven sequential political races in different regions of Germany. Players are in charge of national political parties, and must manage limited resources to help their party to victory. The winning party will have the most victory points after all the regional elections. There are four different ways of scoring victory points. First, each regional election can supply one to eighty victory points, depending on the size of the region and how well your party does in it. Second, if a party wins a regional election and has some media influence in the region, then the party will receive some media-control victory points. Third, each party has a national party membership which will grow as the game progresses and this will supply a fair number of victory points. Lastly, parties score some victory points if their party platform matches the national opinions at the end of the game. The 1986 edition featured 4 parties from the old West Germany and supported 3-4 players. The 1997 edition supports up to 5 players in the re-united Germany and updated several features of the rules as well. The 2006 edition also supports up to 5 players and adds a shorter 5 round variant and additional rules updates by the original designer. Die Macher is #1 in the Valley Games Classic Line //cf.geekdo-images.com/images/pic159509.jpg 5 240 14 3 240 Die Macher 240 //cf.geekdo-images.com/images/pic159509_t.jpg 1986 Marcus Gschwendtner Economic,Negotiation,Political NA Karl-Heinz Schmiel NA Country: Germany,Valley Games Classic Line NA NA Area Control / Area Influence,Auction/Bidding,Dice Rolling,Hand Management,Simultaneous Action Selection Hans im Glück Verlags-GmbH,Moskito Spiele,Valley Games, Inc. 6 7.66508 4.3477 7.29168 NA NA NA NA NA NA NA NA NA NA 7.39570 85 NA NA NA NA 0 1763 719 5251 1.59321 7.29168 147 170 4498 505 1654 No NotRecommended NA NotRecommended NotRecommended Recommended Best NA NA NA NA NotRecommended 14 NA NA NA NA NA NA NA NA NA NA NA NA NA
2 2 boardgame Dragonmaster is a trick-taking card game based on an older game called Coup d’etat. Each player is given a supply of plastic gems, which represent points. Each player will get to be the dealer for five different hands, with slightly different goals for each hand. After all cards have been dealt out, the dealer decides which hand best suits his or her current cards, and the other players are penalized points (in the form of crystals) for taking certain tricks or cards. For instance, if “first” or “last” is called, then a player is penalized for taking the first or last tricks. All players will get a chance to be dealer for five hands, but other players can steal this opportunity by taking all of the tricks during certain hands. At the end, the biggest pile of gems wins the game. Jewel contents: 11 clear (3 extra) 13 green (1 extra) 22 red (2 extra) 22 blue (2 extra) //cf.geekdo-images.com/images/pic184174.jpg 4 30 12 3 30 Dragonmaster 30 //cf.geekdo-images.com/images/pic184174_t.jpg 1981 Bob Pepper Card Game,Fantasy NA G. W. “Jerry” D’Arcey NA Animals: Dragons Indulgence,Coup d’etat NA Trick-taking E.S. Lowe,Milton Bradley 7 6.60815 1.9423 5.87150 NA NA NA NA NA NA NA NA NA NA 5.91318 1066 NA NA NA NA 0 273 52 1053 1.46282 5.87150 2541 73 478 67 161 Some NotRecommended NA NotRecommended Recommended Best NA NA NA NA NA NotRecommended NA NA NA NA NA NA NA NA NA NA NA NA NA NA
3 3 boardgame Part of the Knizia tile-laying trilogy, Samurai is set in medieval Japan. Players compete to gain the favor of three factions: samurai, peasants, and priests, which are represented by helmet, rice paddy, and Buddha tokens scattered about the board, which features the islands of Japan. The competition is waged through the use of hexagonal tiles, each of which help curry favor of one of the three factions — or all three at once! Players can make lightning-quick strikes with horseback ronin and ships or approach their conquests more methodically. As each token (helmets, rice paddies, and Buddhas) is surrounded, it is awarded to the player who has gained the most favor with the corresponding group. Gameplay continues until all the symbols of one type have been removed from the board or four tokens have been removed from play due to a tie for influence. At the end of the game, players compare captured symbols of each type, competing for majorities in each of the three types. Ties are not uncommon and are broken based on the number of other, “non-majority” symbols each player has collected. //cf.geekdo-images.com/images/pic3211873.jpg 4 60 10 2 30 Samurai 60 //cf.geekdo-images.com/images/pic3211873_t.jpg 1998 Franz Vohwinkel Abstract Strategy,Medieval NA Reiner Knizia NA Asian Theme,Country: Japan,Knizia tile-laying trilogy,Samurai NA NA Area Control / Area Influence,Hand Management,Set Collection,Tile Placement 999 Games,ABACUSSPIELE,Astrel Games,Ceilikan Jogos,Descartes Editeur,Edge Entertainment,Fantasy Flight Games,Galakta,Hans im Glück Verlags-GmbH,Hobby Japan,Lacerta,Lautapelit.fi,Rio Grande Games,Skandinavisk Spil Kompagni,Wargames Club Publishing 6 7.44119 2.5085 7.28295 NA NA NA NA NA NA NA NA NA NA 7.30610 112 NA NA NA NA 0 3281 1355 11870 1.18531 7.28295 150 234 12019 707 2601 No NotRecommended NA Recommended Best Recommended NA NA NA NA NA NotRecommended 10 NA NA NA NA NA NA NA NA NA NA NA NA NA
4 4 boardgame When you see the triangular box and the luxurious, large blocks, you can tell this game was designed to be beautiful as well as functional. The object of the game is to build pyramids out of the different colored blocks. A pyramid scores more points when it’s made from a few colors, but it’s much harder to consistently outbid the other players for the necessary blocks. The game is over when the Pharoah’s Pyramid in the center is completed, which is built using all the blocks that the players don’t use during the course of the game. Final round 1990 Hippodice Spieleautorenwettbewerb. //cf.geekdo-images.com/images/pic285299.jpg 4 60 12 2 60 Tal der Könige 60 //cf.geekdo-images.com/images/pic285299_t.jpg 1992 NA Ancient NA Christian Beierer NA Country: Egypt,Promotional Board Games NA NA Action Point Allowance System,Area Control / Area Influence,Auction/Bidding,Set Collection KOSMOS 5 6.60675 2.6667 5.76636 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 111 30 523 1.21028 5.76636 3191 29 314 61 112 No NotRecommended NA Recommended Best Best NA NA NA NA NA NotRecommended 14 NA NA NA NA NA NA NA NA NA NA NA NA NA
5 5 boardgame In Acquire, each player strategically invests in businesses, trying to retain a majority of stock. As the businesses grow with tile placements, they also start merging, giving the majority stockholders of the acquired business sizable bonuses, which can then be used to reinvest into other chains. All of the investors in the acquired company can then cash in their stocks for current value or trade them 2-for-1 for shares of the newer, larger business. The game is a race to acquire the greatest wealth. This Sid Sackson classic has taken many different forms over the years depending on the publisher. Some versions of the 3M bookshelf edition included rules for a 2-player variant. The original version is part of the 3M Bookshelf Series. Note: many books and websites list this as a 1962 publication. This is incorrect; information from Sid Sackson’s diaries, correspondence, and royalty statements prove that it was published in 1964. However, for some reason admins continue to accept “corrections” of the publication date to 1962. A detailed timeline of the development and publication of the game can be found at https://opinionatedgamers.com/2014/05/29/how-acquire-became-acquire/, for those interested. //cf.geekdo-images.com/images/pic342163.jpg 6 90 12 3 90 Acquire 90 //cf.geekdo-images.com/images/pic342163_t.jpg 1964 Scott Okumura,Peter Whitley Economic NA Sid Sackson NA 3M Bookshelf Series NA NA Hand Management,Stock Holding,Tile Placement 3M,Avalon Hill,Avalon Hill (Hasbro),Dujardin,Grow Jogos e Brinquedos,PS-Games,Schmidt France,Schmidt International,Schmidt Spiele,Selecta Spel en Hobby,Smart Games, Inc. 6 7.35830 2.5089 7.21895 NA NA NA NA NA NA NA NA NA NA 7.21696 141 NA NA NA NA 0 5011 1515 18682 1.33020 7.21895 181 823 15195 516 2219 No NotRecommended NA NotRecommended Recommended Best Recommended Recommended NA NA NA NotRecommended 12 NA NA NA NA NA NA NA NA NA NA NA NA NA
6 6 boardgame In the ancient lands along the Mediterranean, players attempt to satisfy their unique victory conditions via trade, war and construction. This lavishly produced game contains tons of wooden game components and a beautiful roll-out vinyl map. Players produce a score of different commodities to trade with other cities in the hope of creating enough income to fill their capitals with buildings, produce artwork, and fill warehouses with goods. //cf.geekdo-images.com/images/pic28424.jpg 6 240 12 2 240 Mare Mediterraneum 240 //cf.geekdo-images.com/images/pic28424_t.jpg 1989 Jean du Poël Civilization,Nautical NA Jean du Poël NA Cities: Marseille NA NA Dice Rolling Historien Spiele Galerie (Historien Spielegalerie) 6 6.52534 3.0000 5.57250 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 26 6 106 1.65064 5.57250 6621 2 73 39 49 Some NA NA NotRecommended NotRecommended Best Best Recommended NA NA NA NA 14 NA NA NA NA NA NA NA NA NA NA NA NA NA
7 7 boardgame In Cathedral, each player has a set of pieces of a different color. The pieces are in the shapes of buildings, covering from one to five square units. The first player takes the single neutral Cathedral piece and places it onto the board. Players then alternate placing one of their buildings onto the board until neither player can place another building. Players capture territory by surrounding areas that are occupied by at most one opponent or neutral building. A captured piece is removed and captured territory becomes off-limits to the opponent. The player with the fewest ‘square units’ of buildings that can’t be placed wins. //cf.geekdo-images.com/images/pic181642.jpg 2 20 8 2 20 Cathedral 20 //cf.geekdo-images.com/images/pic181642_t.jpg 1978 S. Mattusek Abstract Strategy NA Robert P. Moore NA Combinatorial,Polyominoes NA NA Area Enclosure,Pattern Building,Pattern Recognition,Tile Placement Brightway Products Ltd,Falomir Juegos,Family Games, Inc.,Gazebo Games UK Ltd.,Gigamic,Holzinsel,Lagoon Games,Mattel,Robert P. Moore Games,Yangxin Industrial Company Limited 6 6.50534 1.8217 6.21877 6.32724 109 NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 1076 258 4561 1.31078 6.21877 1352 258 2751 90 400 No NotRecommended NA Best NA NA NA NA NA NA NA NotRecommended 10 NA NA NA NA NA NA NA NA NA NA NA NA NA
8 8 boardgame In this interesting offering from Warfrog, players become Gods seeking to dominate a world with their followers. The first part of the game involves constructing the game board, after which players take turns using cards to determine how many new people should appear and what type of terrain they will inhabit. All tribes start off as barbarians, but soon the calming effect of civilization settles the marauding bands; although civilized tribes score more, they can no longer attack. What they can do, though, is civilize other players’ followers. Sure it gives opponents more points, but at least those followers won’t come after you! //cf.geekdo-images.com/images/pic374320.jpg 5 120 12 2 120 Lords of Creation 120 //cf.geekdo-images.com/images/pic374320_t.jpg 1993 Bryan King Civilization,Fantasy NA Martin Wallace NA NA NA NA Modular Board Vendetta,Warfrog Games 5 6.14538 2.4000 5.60857 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 82 15 460 1.25508 5.60857 5371 26 186 28 46 No NotRecommended NA NotRecommended Recommended Best Recommended NA NA NA NA NotRecommended NA NA NA NA NA NA NA NA NA NA NA NA NA NA
9 9 boardgame Although referred to as a sequel to El Grande, El Caballero shares few aspects with its namesake, being a fun but intense brain-burner in which players explore and attempt to control the lands and waterways of the New World. The players are following Columbus by exploring the islands he discovered. Players slowly explore the islands – by picking and placing land tiles that are most favorable to them – and discover wealth in the form of gold and fish. As they learn about the land and sea areas of this new land, they position their caballeros to try to maintain control of the important regions. Castillos give them a measure of protection from others, and ships allow them to establish trade and to fish for food. Success is measured in the size of land and sea areas they control. Their success is measured twice, and in the end these scores are summed and the winner declared. //cf.geekdo-images.com/images/pic1731731.jpg 4 90 13 2 90 El Caballero 90 //cf.geekdo-images.com/images/pic1731731_t.jpg 1998 Doris Matthäus Exploration NA Wolfgang Kramer,Richard Ulrich NA El Grande NA NA Area Control / Area Influence,Tile Placement 999 Games,Hans im Glück Verlags-GmbH,Rio Grande Games 6 6.51776 3.1958 6.07659 NA NA NA NA NA NA NA NA NA NA 6.16406 833 NA NA NA NA 0 529 143 2263 1.40413 6.07659 1711 141 1263 113 319 No NotRecommended NA Recommended Recommended Recommended NA NA NA NA NA NotRecommended 12 NA NA NA NA NA NA NA NA NA NA NA NA NA
10 10 boardgame Elfenland is a redesign of the original White Wind game Elfenroads. The game is set in the mythical world of the elves. A group of fledgling elves (the players) are charged with visiting as many of the twenty Elfencities as they can over the course of 4 rounds. To accomplish the task they will use various forms of transportation such as Giant Pigs, Elfcarts, Unicorns, Rafts, Magic Clouds, Trollwagons, and Dragons. Gameplay: Players begin in the Elf capitol, draw one face down movement tile, and are dealt eight transport cards and a secret ‘home’ city card that they must reach at the end of the 4th round or lose points for each city space away from ‘home’ they are at the end of the game. Markers of each player’s color are placed in each city on the board and are collected when the player visits that city (each counts as 1 point). The round proceeds in 2 stages. The first part of the round consists of the drawing of Tiles showing the differing types of transport (except rafts) from a combination of face up and face down tiles (if a player doesn’t like the 5 tiles that are face up; they can always draw blind from the face down tiles and hope to get one they need). These transport tiles need to match the Transportation cards in your hand to use them most effectively. After each player has a total of 4 tiles they take turns placing a tile on any one of the roads that run between the elf cities. Only one transport tile may be placed on each road; so players may use other players tiles to travel if they have the matching cards in their hand. This frequently causes a readjustment of planned travel routes as other players tiles can allow you to move farther or shorter than you had first thought. Players can play their tiles to help themselves or hinder others by playing a slow mode of transport on another players (perceived) path. Each mode of transport has certain terrain it can travel through quickly or slowly, and those that it cannot. These are listed on the top of each transportation card by the number terrain symbols. The number of terrain symbols equals how many matching cards you must play to move across a given tile in a given terrain. For example, a Magic Cloud tile placed in a mountain would take one Magic cloud card to travel across (1 mountain symbol on card means Magic clouds are fast in mountains). If the same tile was placed on a road in forest terrain it would require 2 Magic Cloud cards to travel that route (2 Forest symbols on card means Magic Clouds are slow in Forest). Magic Clouds cannot travel in desert terrain at all (no desert symbols on card). All modes of transport are different and Rafts can be used on rivers or lakes without needing tiles. Rafts go slow upstream (2 raft cards needed) and fast downstream (1 card needed). The small lake requires 1 raft card to travel across and the larger lake requires 2 cards to travel across. Players may keep one unused transport counter and up to 4 Transportation cards from one round to the next. The second part of the round begins after all players have finished placing their transportation tiles for the round. Each player plays his cards and moves his elf-boot around the board collecting his tokens from the cities visited. If there is a Transport tile on a route and a player has no matching Transportation card he may ‘Caravan’ across it by playing any 3 Transportation cards from his hand. As a bit of ‘take that’ each player has a trouble tile which can be placed next to any transportation tile during the first part of the round. This counter means that in order to travel that path an additional card of the transport type must be played or 4 cards to ‘Caravan’. Victory: if at the end of round 3 a player has visited all 20 cities he is the winner. If not the game ends after round 4 when ‘Home’ cities are revealed and each player subtracts points for each city he is away from his ‘home’ subtracting that from his collected city tokens. The person with the highest score wins. //cf.geekdo-images.com/images/pic1798136.jpg 6 60 10 2 60 Elfenland 60 //cf.geekdo-images.com/images/pic1798136_t.jpg 1998 Doris Matthäus Fantasy,Travel NA Alan R. Moon Elfengold,Elfenland: Back to the Roads,Elfenland: Der Elfen-Zauberer,Stadt Land Spielt Limitierte Sonderdrucke 2013 Elfen Travel,Fairies, Elves and Pixies Elfenroads,Elfenroads NA Card Drafting,Hand Management,Point to Point Movement,Route/Network Building 999 Games,AMIGO Spiel + Freizeit GmbH,Corfix,Hobby World,Midgaard Games,Rio Grande Games 8 6.74996 2.1649 6.58428 NA NA NA NA NA NA 6.62779 163 NA NA NA NA NA NA NA NA 0 1864 661 7792 1.24616 6.58428 709 267 6729 182 679 No NotRecommended NA NotRecommended Recommended Best Recommended Recommended NA NA NA NotRecommended 8 NA NA NA NA NA NA NA NA NA NA NA NA NA

Selecting columns

Use delimiters for bad names!

select 
`details.name`, 
`attributes.boardgamecategory`
from boardgames
limit 10
Displaying records 1 - 10
details.name attributes.boardgamecategory
Die Macher Economic,Negotiation,Political
Dragonmaster Card Game,Fantasy
Samurai Abstract Strategy,Medieval
Tal der Könige Ancient
Acquire Economic
Mare Mediterraneum Civilization,Nautical
Cathedral Abstract Strategy
Lords of Creation Civilization,Fantasy
El Caballero Exploration
Elfenland Fantasy,Travel

Selecting rows

select 
`details.name`, 
`attributes.boardgamecategory`
from boardgames
where `attributes.boardgamecategory` like '%nautical%'
limit 10
Displaying records 1 - 10
details.name attributes.boardgamecategory
Mare Mediterraneum Civilization,Nautical
Broadsides and Boarding Parties Nautical,Pirates,Wargame
Cape Horn Nautical,Racing
Kontor City Building,Nautical,Renaissance
Serenissima (first edition) Economic,Nautical,Renaissance
Blackbeard Adventure,Age of Reason,Nautical,Pirates,Wargame
Wooden Ships & Iron Men Age of Reason,American Indian Wars,American Revolutionary War,Napoleonic,Nautical,Pirates,Post-Napoleonic,Wargame
Lifeboats Nautical,Negotiation
Mississippi Queen Nautical,Racing
Atlantic Storm Card Game,Nautical,World War II

Selecting rows

select 
`details.name`
,`attributes.boardgamecategory`
,`details.maxplayers`
from boardgames
where `attributes.boardgamecategory` like '%nautical%'
and `details.maxplayers` = 2 
limit 10
Displaying records 1 - 10
details.name attributes.boardgamecategory details.maxplayers
Broadsides and Boarding Parties Nautical,Pirates,Wargame 2
Wooden Ships & Iron Men Age of Reason,American Indian Wars,American Revolutionary War,Napoleonic,Nautical,Pirates,Post-Napoleonic,Wargame 2
Breakthru Abstract Strategy,Nautical 2
Pacific Victory Nautical,Wargame,World War II 2
Midway Nautical,Wargame,World War II 2
Guadalcanal Nautical,Wargame,World War II 2
Attack Sub Card Game,Modern Warfare,Nautical,Wargame 2
War at Sea (second edition) Nautical,Wargame,World War II 2
Victory in the Pacific Nautical,Wargame,World War II 2
Broadside Miniatures,Nautical,Wargame 2

Identifying missings

select 
`details.name`
,`attributes.boardgameartist`
from boardgames
where `attributes.boardgameartist` is null
limit 10
Displaying records 1 - 10
details.name attributes.boardgameartist
Tal der Könige NA
Gateway to the Stars NA
Supremacy NA
Brauerei NA
Conquest NA
Bollox NA
MedFront NA
Quebec 1759 NA
Star Wars: Episode 1 – Clash of the Lightsabers NA
Broadsides and Boarding Parties NA

Exercise

  1. Find all games with a details.minage of 16 or greater
  2. Find all records where either the attributes.boardgamedesigner is missing or the attributes.boardgameartist is missing

Answers

select 
`details.name`
,`details.minage`
from boardgames
where `details.minage` >= 16
Displaying records 1 - 10
details.name details.minage
Frischfleisch 18
Metropolis 16
Junta 16
1835 16
Grass 18
True Colors 18
Kampf um Rom 16
Putsch 16
Minos 16
Ultimate Outburst 18

Answers

select 
 `details.name`
,`attributes.boardgamedesigner`
,`attributes.boardgameartist` 
from boardgames
where `attributes.boardgamedesigner` is null 
or `attributes.boardgameartist` is null
Displaying records 1 - 10
details.name attributes.boardgamedesigner attributes.boardgameartist
Tal der Könige Christian Beierer NA
Gateway to the Stars Mark A. Snowden NA
Supremacy Robert J. Simpson NA
Brauerei Valentin Herman NA
Conquest Donald Benge NA
Bollox Rob Nelson NA
MedFront Craig Besinque NA
Quebec 1759 Steve Brewster,Tom Dalgliesh,Lance Gutteridge NA
Star Wars: Episode 1 – Clash of the Lightsabers Craig Van Ness NA
Broadsides and Boarding Parties Larry Harris, Jr. NA

Altering columns

Renaming columns

select 
`details.name` as `name`
from boardgames
limit 10
Displaying records 1 - 10
name
Die Macher
Dragonmaster
Samurai
Tal der Könige
Acquire
Mare Mediterraneum
Cathedral
Lords of Creation
El Caballero
Elfenland

Calculated columns

select 
`details.name` ,substr(`attributes.boardgamecategory`, 1,10) as short_category
, 1.2 as fixed_colum
, coalesce(`attributes.boardgameartist`,"Unknown") as arttist
,case when `attributes.boardgamecategory` like '%nautical%' then 'Boats, boats, boats!'
when `attributes.boardgamecategory` like '%pirate%' then 'R!' 
else 'Who cares?' end as category
from boardgames
where `attributes.boardgameartist` is null
limit 10
Displaying records 1 - 10
details.name short_category fixed_colum arttist category
Tal der Könige Ancient 1.2 Unknown Who cares?
Gateway to the Stars Civilizati 1.2 Unknown Who cares?
Supremacy Economic,P 1.2 Unknown Who cares?
Brauerei Economic 1.2 Unknown Who cares?
Conquest Abstract S 1.2 Unknown Who cares?
Bollox Abstract S 1.2 Unknown Who cares?
MedFront Wargame,Wo 1.2 Unknown Who cares?
Quebec 1759 Age of Rea 1.2 Unknown Who cares?
Star Wars: Episode 1 – Clash of the Lightsabers Card Game, 1.2 Unknown Who cares?
Broadsides and Boarding Parties Nautical,P 1.2 Unknown Boats, boats, boats!

Aggregating data

Grouping data

select 
`details.maxplayers` as max_players
from boardgames
where `details.maxplayers` is not null
group by `details.maxplayers`
Displaying records 1 - 10
max_players
0
1
2
3
4
5
6
7
8
9

Aggregates

select 
`details.maxplayers` as max_players
,count(*) as n
,avg(`details.playingtime`) as avg_playtime
,avg(`details.playingtime`)/`details.maxplayers` as pp_avg_playtime
from boardgames
where `details.maxplayers` is not null
group by `details.maxplayers`
limit 10
Displaying records 1 - 10
max_players n avg_playtime pp_avg_playtime
0 6706 16.45064 NA
1 1095 45.00365 45.003653
2 18683 63.22983 31.614917
3 1198 63.83472 21.278242
4 26287 42.37920 10.594800
5 7391 56.87674 11.375348
6 17435 54.21480 9.035800
7 1040 92.12019 13.160027
8 5020 59.03008 7.378760
9 326 68.45706 7.606339

Exercises

  1. How many games received ratings of 0,1,2,…,10 etc based on the `stats.average col? (Hint: Use the floor() function)
  2. For records with the game.type of ‘boardgameexpansion’ what is the minimum, mean, and maximum of users who have said they want the expansion (stats.wanted)?

Answers

select 
floor(`stats.average`) as rating
, count(*) as games
from boardgames
group by  floor(`stats.average`)
Displaying records 1 - 10
rating games
0 24662
1 777
2 1362
3 3571
4 8025
5 15401
6 19054
7 14480
8 2425
9 313

Answers

select avg(`stats.wishing` ) as avg_want
,min(`stats.wishing`) as min_want
,max(`stats.wishing`) as max_want
from boardgames
where `game.type`='boardgameexpansion'
1 records
avg_want min_want max_want
38.6148628938156 0 2500

Combining datasets

Combination types - inner join

Use when you need only stuff that matches on both sides

inner join

Combination types - left join

Use when you you need to get potentially optional values from another table

left join

Combination types - union (all)

Use when you you need to combine two separate datasets with the same structure

union

Writing joins

select 
b.`details.name`
,t.`topics(bgg.ldaout)`
from boardgames b
inner join `bgg.ldaOut.topics` t on b.`details.name`=t.row_names
limit 10
Displaying records 1 - 10
details.name topics(bgg.ldaOut)
Dragonmaster 5
Samurai 22
Acquire 4
Cathedral 24
Elfenland 25
Bohnanza 8
Ra 10
Catan 23
Basari 8
MarraCash 8

Exercises

  1. Use a join to return any board games without a topic entry, turn the count of records
  2. Can you transform the board game name column so that more entries get joined together? (Hint: Use the replace() function)

Answers

select 
count(*)
from boardgames b
left join `bgg.ldaOut.topics` t on b.`details.name`=t.row_names
where t.row_names is null
1 records
count(*)
82636

Answers

select 
count(*)
from boardgames b
left join `bgg.ldaOut.topics` t on replace(b.`details.name`,' ','.')=t.row_names
where t.row_names is null
1 records
count(*)
69169

Wrapup

Other keywords to learn

  • order by
  • having
  • cross apply
  • with

Typical execution order of keywords

  1. from
  2. joins
  3. where
  4. group by
  5. having
  6. select
  7. order by
  8. limit

Ways to learn SQL

  1. Writing it
  2. Reading other people’s code
  3. Using dbplyr and checking the code it’s written
  4. The SQL Cookbook geni.us/sqlcookbook

Q&A

Followup

  1. Get the slides itsalocke.com/talks
  2. Contact me @theStephLocke steph@itsalocke.com