SQL Resource Guide#
SQL, which is an acronym for Structured Query Language, is a language widely used to interact with most relational databases. You can create, query, update, and delete databases using SQL. While having a simple syntax, SQL is very powerful and allows you to efficiently query large databases.
“Database” is not a synonym of “dataset.” Databases are collections of data typically controlled by database management systems (such as MySQL or SQLite). Databases are common for large quantities of data, often consisting of multiple tables of data, that multiple users or automated scripts need to access efficiently and securely. Databases are particularly useful when you need to add, update, delete, or use individual observations or subsets of data frequently.
It’s important to note that, while SQL is one language, there are multiple dialects. There are standards for SQL , but different vendors of database management systems provide various versions of SQL syntax , such as MySQL, PostgreSQL, and SQLite. However, most of the syntax, particularly basic syntax, is very similar across dialects.
Getting Started#
Select Star SQL
If you’re completely new to SQL, this is a great place to start, particularly if you only need to query an existing database. This is a fun tutorial, which introduces SQL through real-life stories and questions. The explanations are very good.
Best for: those who only need to query an existing database
GeeksforGeeks: SQL Tutorial
This tutorial provides descriptions and examples for common operations using SQL. Similarly to W3Schools, GeeksforGeeks is also a great place to learn about coding.
Best for: those looking to cover the full range of database operations
Khan Academy: Intro to SQL
Guided introduction to SQL. In addition to explanations, it provides exercises to practice and consolidate your learning.
Best for: those who prefer videos
W3Schools: SQL Tutorial
This tutorial comprehensively provides explanations and examples of different aspects of SQL.
Best for: those looking for a reference resource with examples or exercises for specific SQL functions
Getting Some Practice#
W3Schools: SQL Exercises
Practice makes perfect! W3Schools—a great resources for learning about coding—provides easy, fill-in-the-blank type exercises for you to make sure that you’re learning.
Best for: beginner-level practice without writing SQL code directly
w3resource: SQL Exercises
Want more practice? This website provides exercises and a web-based editor to write and execute code for common tasks.
Best for: practice writing and running queries
DataLemur: SQL Problems
The exercises provided above are meant to check your knowledge of specific aspects of SQL—not your ability to solve problems. In contrast, this website provides questions that you need to answer using SQL. Although the questions are meant to practice for data science interviews, you can use them to consolidate your knowledge.
Best for: those ready to extend their practice beyond just SQL syntax
Knight Lab: SQL Murder Mystery
Are you loving SQL and want more practice? This is a fun tutorial developed by Northwestern’s Knight Lab where you need to use SQL to investigate and solve a crime!
Best for: those looking to practice their skills with something other than traditional exercises
SQL Meets R#
Posit: Best Practices in Working with Databases
If you plan to connect to a database with R, this guide will help you get setup. If you’re a user of the dplyr package, you may have already picked up on the similarities to SQL. The dbplyr package allows you to write dplyr code to interact with databases instead of SQL code – the package translates the dplyr code to SQL for you behind the scenes! A good option if you’re an R user.
Best for: using SQL databases from R
Deep Dive into SQL and Databases#
Database Systems: The Complete Book
Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom
This is a detailed and comprehensive book about databases. The book covers the material taught in the database sequence at Stanford, which includes SQL (mostly chapter 6). This is a theoretical, not practical, book. If you want to learn more about database theory, this is a good place to start.
Best for: those looking to understand database theory
SQL for Smarties: Advanced SQL Programming
As the title indicates, this book is an advanced and comprehensive guide to SQL programming, covering topics not included in other tutorials. It is a practical book that uses only standard SQL. Note, however, that more advanced functions and features are more likely to deviate from the standard SQL syntax across database systems.
Best for: those looking to master advanced SQL functions and methods