🖥️ Week 03, Day 02 - Lecture
Demos and Recipes: SQL and Joining Data
By the end of today’s lecture, you should be able to: i) Connect to a SQLite database and inspect what tables it contains, ii) Write SELECT, WHERE, GROUP BY, and ORDER BY queries, and recognise their pandas equivalents, iii) Explain what an INNER JOIN and a LEFT JOIN each keep, using a real join where one row can match several, iv) Use .pipe() to name each step in a pandas chain so the code reads as a pipeline.
⏰ Tuesday, 28 July 2026 | 10:00am - 1:00pm 📍 CBG.2.01 (see LSE’s 🗺️ campus map)
If you work with data at almost any organisation, chances are that most of the structured data you use is stored in a relational database, not a CSV file. That doesn’t make the CSV skills you’ve built so far wasted: a table in a database and a DataFrame built from a CSV are organised the same way, rows and columns with fixed types, and some tools only ever let you export a CSV in the end. SQL is how you ask a database questions directly, and pandas mirrors its vocabulary closely: groupby is GROUP BY, a merge is a JOIN. Today you learn to ask the same questions both ways.
Most of the material in Week 03 is not required in your final project. Treat it as a toolkit you can draw on if it fits your data and your question. We also reserve time each morning for you to work on your final project and get help from me and your class teacher.
🗣️ Lecture Overview
- Final project support: work on your project with help from me and your class teacher
- Coffee break
- SQL is a different way to work with data: how a list, a dict of lists, a DataFrame, and a SQL table each enforce structure more strictly than the last, connecting with
sqlite3, writing a DataFrame to a database withdf.to_sql, and inspecting a database withsqlite_masterandPRAGMA table_info - Asking questions in SQL: SELECT, FROM, WHERE, GROUP BY, and ORDER BY introduced one clause at a time, with the pandas equivalent alongside each one, and reading query results back into a DataFrame with
pd.read_sql - Joining two tables: why a city name matches more than one place, INNER JOIN and LEFT JOIN on real geocoding data with colour-coded tables,
pd.mergenext to the equivalent SQL JOIN, and.pipe()for naming each step in a pandas chain - Wrap-up: what the lab covers this afternoon and the final project deadline
🎬 Lecture Slides
Use keyboard arrows to navigate. Select the slides below or view fullscreen.
After the Lecture
This afternoon you practise querying and joining data in SQLite, and your class teacher can also help you with your final project.
Questions?
🔗 SQL Resources