CS50 SQL

• Course
• Gradebook
• VSC for CS50

Index

Week 0 - Querying

• SELECT
• LIMIT
• WHERE
  • =, >, <, >=, <=, != <>, NOT
  • AND
  • OR
  • ()
  • IS NULL, IS NOT NULL
  • LIKE, %, _
  • BETWEEN ... AND ...
• ORDER BY ... ASC / DESC
• Aggregate functions:
  • COUNT
  • AVG
  • MIN
  • MAX
  • SUM
• DISTINCT

Week 1 - Relating

• Relationships:
  • One to one.
  • One to many.
  • Many to many.
• Subqueries.
• Joins.
  • INNER, LEFT, RIGHT, FULL
  • NATURAL JOIN
• Sets.
  • UNION
  • INTERESCT
  • EXCEPT
• Groups.
  • GROUP BY
  • HAVING

Week 2 - Designing

• Commands:
  • .schema
  • .quit
  • .read file.sql
• Statements:
  • CREATE TABLE
  • DROP TABLE
• Data Types vs. Storage Classes
• Type Affinities
• Table Constraints
  • PRIMARY KEY
  • FOREIGN KEY
• Column Constraints
  • CHECK
  • DEFAULT
  • NOT FULL
  • UNIQUE
• Altering tables
  • DROP TABLE
• ALTER TABLE
  • RENAME TO
  • ADD COLUMN
  • RENAME COLUMN