The Querynomicon
An Introduction to SQL for the Cautious and Weary
Upon first encountering SQL after two decades of Fortran, C, Java, and Python, I thought I had stumbled into hell. I quickly realized that was optimistic: after all, hell has rules.
I have since realized that SQL does too, and that they are no more confusing or contradictory than those of most other programming languages. They only appear so because it draws on a tradition unfamiliar to those of us raised with derivatives of C. To quote Terry Pratchett, it is not mad, just differently sane.
Welcome, then, to a world in which the strange will become familiar, and the familiar, strange. Welcome, thrice welcome, to SQL.
-
Please see the website to view the current version of this tutorial.
-
Contributions are very welcome: please see the contributors' guide for background and the issues in for items where assistance would be most appreciated. Please also see our license for terms of use, and note that all contributors are required to abide by our Code of Conduct.
This lesson notes and working examples for instructors to use as a starting point. We do not expect novices with no prior SQL experience to be able to learn from this on their own. As a musical analogy, these notes are the chord changes and melody; we expect instructors to create an arrangement and/or improvise over the material when delivering it. Please see Teaching Tech Together for background.
Lessons
- Introduction: where we're going and why
- Core Features: getting values from tables
- Tools: Miscellaneous things that make life easier
- Python: using databases from Python
- Advanced Features: when you need 'em, you need 'em
- R: using databases from R
- PostgreSQL: client-server computing and permissions
- Conclusion: where we've been and what comes next
Appendices
Acknowledgments
-
Greg Wilson was the co-founder and first Executive Director of Software Carpentry and received ACM SIGSOFT's Influential Educator Award in 2020.
-
Konstantinos Kitsios is a PhD student at University of Zurich, working on applications of machine learning to software engineering. Previously he worked as a software engineer at Tesla.
-
Pao Corrales has a PhD in Atmospheric Sciences and works on improving severe weather forecasts in Argentina. She loves teaching science and programming using evidence-based techniques centered on the students and their context.
This tutorial would not have been possible without:
- Andi Albrecht's [
sqlparse][sqlparse] module - Dimitri Fontaine's The Art of PostgreSQL
- David Rozenshtein's The Essence of SQL (now sadly out of print)
I would also like to thank the following people for spotting issues, making suggestions, or submitting changes:
- Yanina Bellini Saibene
- Phillip Cloud
- Zoe Daniels
- Conor Flynn
- Andy Goldberg
- Jay Graves
- Sam Hames
- Adam Hawkes
- Robert Kern
- Konstantinos Kitsios
- Olivier Leroy
- Kevin Marshall
- Roy Pardee
- Manos Pitsidianakis
- Daniel Possenriede
- Adam Rosien
- Thomas Sandmann
- Simon Willison
Start where you are
Use what you have
Help who you can