Video by freeCodeCamp.org · taught by David J. Malan · streamed from YouTube. All rights remain with the creator.
Lesson 9: SQL and Databases
About this lesson
The final lecture of this series introduces databases and SQL, the language used to store and retrieve structured data, and connects them to the web applications of the previous week. It begins with the limitations of keeping data in spreadsheets or simple files, and shows the kind of questions that become hard to answer that way. It then introduces relational databases, which keep data in tables with rows and columns, using SQLite, a database that stores everything in one file. You see how to create tables and choose types for their columns, and how the four basic operations of creating, reading, updating and deleting data are expressed in SQL. The lecture demonstrates filtering, sorting and limiting results, matching patterns, and summarising data with functions that count, average and find extremes. Using a larger example database, it shows why data is split across several tables, how primary keys and foreign keys link them, and how joins combine them in a single query. It explains indexes, which make searches dramatically faster at the cost of extra space and slower writes. The lecture then shows how to run SQL from Python inside a Flask application, and discusses two dangers that arise once real users are involved: problems when several people change the same data at the same moment, which transactions solve, and the importance of passing user input through placeholders instead of building queries by hand. Afterwards, read Chapter 9 of the course book, which works through queries on two linked tables.