This is CS50’s introduction to databases using a language called SQL. Learn how to create, read, update, and delete data with relational databases, which store data in rows and columns. Learn how to model real-world entities and relationships among them using tables with appropriate types, triggers, and constraints. Learn how to normalize data to eliminate redundancies and reduce potential for errors. Learn how to join tables together using primary and foreign keys. Learn how to automate searches with views and expedite searches with indexes. Learn how to connect SQL with other languages like Python and Java. Course begins with SQLite for portability’s sake and ends with introductions to PostgreSQL and MySQL for scalability’s sake as well. Assignments inspired by real-world datasets.
Tables. Databases. Database Management Systems. SQL. SQLite. SELECT. LIMIT. OFFSET. WHERE. Comparisons. NOT. NULL. Pattern Matching. LIKE. Compound Conditions. Range Conditions. Ordering. Aggregate Functions. ROUND. DISTINCT.
Relational Databases. Relationships: One-to-one, One-to-many, Many-to-many. Entity Relationship Diagrams. Keys: Primary Keys, Foreign Keys. Subqueries. IN. Joins: INNER JOIN, Outer Joins, LEFT JOIN, RIGHT JOIN, FULL JOIN, NATURAL JOIN. Sets: INTERSECT, UNION, EXCEPT. Groups: GROUP BY, HAVING.
Schemas. Normalizing. Data Types. Storage Classes. Type Affinities. Table Constraints: PRIMARY KEY, FOREIGN KEY. Column Constraints: CHECK, DEFAULT, NOT NULL, UNIQUE. Altering Tables: DROP TABLE, ALTER TABLE, ADD COLUMN, RENAME COLUMN, DROP COLUMN. Charlie.
Create, Read, Update, Delete. INSERT INTO. CSVs. .import. DELETE FROM. Foreign Key Constraints. UPDATE. Triggers. Soft Deletions.
Views. CREATE VIEW. Views for Simplifying. Views for Aggregating. Temporary Views. CREATE TEMPORARY VIEW. Common Table Expressions. Views for Partitioning. Views for Securing. Soft Deletions.
Indexes. CREATE INDEX. EXPLAIN QUERY PLAN. Covering Indexes. B-Trees. Partial Indexes. VACUUM. Concurrency. Transactions. ACID: Atomicity, Consistency, Isolation, Durability. BEGIN TRANSACTION. COMMIT. ROLLBACK. Race Conditions. Locks.
Scalability. MySQL: Integers, Strings, Dates, Times, Real Numbers, Floating-Point Imprecision, Fixed Precision, Altering Tables. Stored Procedures. PostgreSQL. Vertical Scaling. Horizontal Scaling. Replication. Read Replicas. Sharding. Access Controls: GRANT, REVOKE. SQL Injection Attacks. Prepared Statements.
A short and simple permissive license with conditions only requiring preservation of copyright and license notices. Licensed works, modifications, and larger works may be distributed under different terms and without source code.
