Mastering Databases

In this lesson, students explore how databases are made up of tables. SQL statements are used to access information in a database. Students read data from a database and insert new data into a table and check for correctness.

In this lesson, students create a new database from scratch using SQLite and Python. Students create tables and store data for grocery store items. Multiple items are stored at a time and the output is formatted.

In this lesson, students use the shopping cart database from the previous lesson to filter and sort data for different reporting needs. Students learn how to avoid repeating data in their queries and how to create simple database reports using relational operators.

In this lesson, students create an engaging “to-do list” web application. Python and SQLite are used to create a new database and tables. Data in the list is changed using primary keys and auto incrementing as students complete and add tasks to the “to-do list”.

In this lesson, students complete C.R.U.D. (create, retrieve, update and delete) operations to a database. Students practice performing CRUD operations on a scoreboard table with their favorite athletes and statistics. This lesson adds the final piece to basic database SQL skills.

In this lesson, students apply their knowledge of Python and SQLite commands to develop the first part of a notebook web application. Students create the database structure and add the code to allow for the creation, updating, and deletion of “sticky note” type memos in a useful application.

In this lesson, students draw on past Ajax API skills to create an interface into the notebook database. Students learn how to use the commit statement to save changes to the database. Students complete the portfolio-worthy mid-term notebook web application.

In this lesson, students deal with large amounts of data and learn how to limit results with the offset feature. Students learn about data aggregation and use a large database of music artists and songs to build a variety of queries to manage the data effectively.

In this lesson, students use the || syntax operator to display multiple columns of data as a single column. Students learn how to exclude null records from a query result and how to count the number of records meeting a criteria in a database by using operators.

In this lesson, students use a university database to explore using multiple data tables in a variety of ways. Students learn different join strategies – explicit, implicit, inner, outer, left, and right to meet requirements. They’ll also filter and sort joined data in useful ways.

In part I of this final project, students learn how to create and manage user login accounts as part of a “to do list” website application. Ajax commands and database skills are used to create a username and password database and allow users to log into the application. This will create a multi-user “to do list” application.

In this final lesson, students integrate the user login/logout features created in the previous course with the “to-do list” functionality created in a website application earlier in the class. The result is a scalable, multi-user application that showcases the skills and knowledge learned throughout the class. Students graduate with solid foundational database skills as well as advanced Python and Ajax skills.