Practice basic SQL syntax with Jupyter notebook. SQL is particularly useful in handling structured data where there are relations between different entities/variables of the data. SQL is a very important tool for data scientists to have in their repertoire.
In this tutorial, we will practice
- How to create/connect a SQLite database
- How to create a table from scratch/from an existing table
- How to drop a table
- How to insert data into/update a table
- How to retrieve data
- How to do maths across table columns
- How to aggregate data
- How to join tables
- How to create/drop views to simplify queries
- How to deal with NULLs
- How to convert query results into Pandas's DataFrame
- How to rollback and commit a transaction (this one used the sqlite3 package as ipython_sql does not support transactions).
- (Bonus)How to access a SQLite database directly with Pandas
- Dealing with date and time.
It is worth noting that this tutorial mainly focuses on how to retrieve data from a database, instead of database management. That is also why we slected SQLite rather than more complicated Databases such as MySQL or PostgreSQL, etc.
Moreover, this SQLite tutorial would not cover everything about SQLite. Some important features are left to be explored by users such as triger and tansaction, primary or foreigh index, etc.
The most efficient way to master SQL is to keep practicing through extensive hands-on practices. Hope that you could start using SQLite effectively and comfortably.
April/2021
Window Functions based on the latest version of SQLite and add an extra tutorial
- s03-Window Functions within SQLite