Week 03: SQL – Data Modification & Advanced Queries (under construction)

SLOs for Week 03

Students will be able to:

1. Use SQL aggregate and scalar functions and create expressions.
2. Work with data types, including dates and strings, using LIKE, GLOB,DATE, TIME, DATETIME, JULIANDAY and STRFTIME. 📔 Notebook 3.2
3. Manipulate the records stored in a database using INSERT INTO, UPDATE and DELETE FROM , REPLACE, UPSERT and RETURNING.
4. Table design principles using DDL commands, such as CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX, DROP INDEX, CREATE VIEW, DROP VIEW.
5. Control columns values using constraints: primary keys, foreign keys, delete with CASCADE, CHECK, UNIQUE, NOT NULL, AUTOINCREMENT.
6. Create and manage indexes to optimize query performance using performance and debug commands, such as CREATE INDEX, ANALYZE and EXPLAIN. 📔 Notebook 3.6
7. Optimizing database files with VACUUM.

Slides, videos, and application exercises

Week 03: Slides

Mark Simon. (2023). Getting Started with SQL and Databases : Managing and Manipulating Data with SQL. Apress. - Chapters 7, 8 and 9

Homework 03:

Instructions

Project 03: Classes

Instructions