• Intro to SQL for Querying Databases
  • Overview
    • Data Disclosure and Appropriate Use
  • 1 Concepts
    • 1.1 What is a Relational Database?
    • 1.2 What is SQL?
      • 1.2.1 What kinds of questions can SQL answer?
    • 1.3 What is a Relational Database Management System?
    • 1.4 Advantages & Disadvantages of SQL
  • 2 The Library Checkouts Database
    • 2.1 Entity Relationship Diagrams
    • 2.2 Data Definitions
  • 3 Database Set Up
    • 3.1 SQLiteStudio
      • 3.1.1 Create a Database
      • 3.1.2 Load the Database
      • 3.1.3 Open the SQL Editor
      • 3.1.4 Saving Scripts
  • 4 Hands-On with SQL Code
    • 4.1 Viewing Data
      • 4.1.1 SELECT & FROM
      • 4.1.2 Selecting Columns
      • 4.1.3 Unique Values
      • 4.1.4 Ordering Results
      • 4.1.5 Limiting Number of Rows
      • 4.1.6 Commenting
    • 4.2 Filtering Data
      • 4.2.1 AND & OR Operators
      • 4.2.2 IN Operator
      • 4.2.3 BETWEEN Operator
      • 4.2.4 LIKE Operator
      • 4.2.5 IS NULL Operator
      • 4.2.6 NOT Operator
    • 4.3 Aggregating Data
      • 4.3.1 Count
      • 4.3.2 Average
      • 4.3.3 Sum
      • 4.3.4 Grouping Data
      • 4.3.5 Having
    • 4.4 Joining Data
      • 4.4.1 JOIN Types
      • 4.4.2 JOIN steps
      • 4.4.3 INNER JOIN
      • 4.4.4 LEFT JOIN
    • 4.5 Subqueries
    • 4.6 Saving Query Results
      • 4.6.1 Connecting Databases to Other Analysis Tools
    • 4.7 Data Management
      • 4.7.1 Update Tables
      • 4.7.2 Add & Populate a Column
  • 5 Conclusion
  • 6 Resources
  • CC BY-SA 4.0

Intro to SQL for Querying Databases

6 Resources

DataLab’s Spatial SQL Workshop

W3Schools SQL Materials - This is an excellent reference for SQL syntax with a fun “try it yourself” feature.

Sofware Carpentry’s SQL Novice Workshop

Clark Fitzgeralds & Nick Ulle’s SQL Workshop

Clark Fitzgeralds & Nick Ulle’s SQL Cheatsheet

Lucidchart’s Entity Relationship Diagrams Overview

Working with SQL databases and queries in R:

  • RStudio’s Database Queries with R

  • University of Michigan’s Stats 701 Class Notes