CS50x: Introduction to Computer Science SQL and Databases Syllabus

SQL and Databases syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for SQL and Databases in CS50x: Introduction to Computer Science, not a summary of it.

1. 1

   Database Fundamentals

   3 topics

   • Tables and Schemas
   • SQL Data Types
   • Constraints
2. 2

   Querying and Manipulation

   4 topics

   • SELECT Statements
   • INSERT, UPDATE, and DELETE
   • Keywords and Functions
   • JOIN Operations
3. 3

   Performance and Concurrency

   3 topics

   • Indexes
   • Transactions
   • Race Conditions
4. 4

   Database Security

   2 topics

   • SQL Injection Attacks
   • Parameterized Queries

SQL and Databases flashcards for CS50x: Introduction to Computer Science

18 of 50 cards from the SQL and Databases deck — real questions with worked answers.

1. What is a relational database?

   A database that organizes data into one or more tables of rows and columns, where relationships between tables are expressed through shared key values (e.g., foreign keys referencing primary keys).

2. In database design, what is a schema?

   The formal structure (blueprint) of a database: which tables exist, the columns in each table, each column's data type, and the constraints applied to them. In SQLite it is defined with CREATE TABLE statements and viewable with the .schema command.

3. In a relational table, what do rows and columns each represent?

   Each row (record/tuple) represents one entity or instance of the data (e.g., one student); each column (field/attribute) represents one property shared by all rows (e.g., name, year).

4. What is the general SQL syntax for creating a new table?

   CREATE TABLE table_name (column1 TYPE constraints, column2 TYPE constraints, ...); for example: CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

5. What is SQLite, and why is it commonly used in courses like CS50?

   SQLite is a lightweight, serverless relational database engine that stores an entire database in a single file on disk. It requires no separate database server, making it simple to embed in applications and ideal for learning and small projects.

6. List the five storage classes (data types) supported by SQLite.

   NULL, INTEGER, REAL, TEXT, and BLOB.

7. What is a BLOB in SQL?

   A Binary Large OBject: raw binary data stored exactly as input (e.g., images, audio, files), with no interpretation as text or numbers.

8. What is the difference between the INTEGER and REAL types in SQLite?

   INTEGER stores whole numbers (signed integers); REAL stores floating-point numbers (8-byte IEEE floating point), used for values with decimal parts.

9. What does a NULL value mean in a database column?

   The absence of any value for that field — it is not zero and not an empty string; the data is simply missing or unknown.

10. In SQL databases like MySQL, what is the difference between CHAR(n) and VARCHAR(n)?

    CHAR(n) stores fixed-length strings that always occupy exactly n characters (padded if shorter); VARCHAR(n) stores variable-length strings up to a maximum of n characters, using only the space needed.

11. What is a PRIMARY KEY constraint?

    A column (or set of columns) that uniquely identifies each row in a table. Its values must be unique and non-NULL; a table can have only one primary key, and it is often an auto-incrementing integer id.

12. What is a FOREIGN KEY constraint?

    A column in one table whose values refer to the primary key of another table, linking the two tables and enforcing that referenced rows actually exist (referential integrity).

13. What is the difference between the UNIQUE and NOT NULL column constraints?

    UNIQUE requires that no two rows have the same value in that column (duplicates rejected); NOT NULL requires that every row must have some value in that column (NULL rejected). A column can have both, which is essentially what a primary key enforces.

14. What is referential integrity?

    The guarantee that every foreign key value in a table matches an existing primary key value in the referenced table, so no row can point to a nonexistent record.

15. What SQL statement retrieves all columns of all rows from a table named movies?

    SELECT * FROM movies; — the asterisk selects every column, and with no WHERE clause every row is returned.

16. What is the purpose of the WHERE clause in a SELECT statement?

    It filters rows so that only those satisfying a given condition are returned, e.g., SELECT title FROM movies WHERE year = 2011; Conditions can use =, <, >, AND, OR, NOT, etc.

17. In SQL's LIKE operator, what do the wildcards % and _ match?

    % matches any sequence of zero or more characters; _ matches exactly one character. Example: WHERE title LIKE 'Toy Story%' matches all titles beginning with "Toy Story".

18. What does ORDER BY do, and what is its default sort direction?

    It sorts the result rows by one or more columns. The default is ascending (ASC); add DESC for descending, e.g., SELECT * FROM shows ORDER BY rating DESC;

See more SQL and Databases flashcards →

Planning SQL and Databases for CS50x: Introduction to Computer Science

SQL and Databases (CS50x: Introduction to Computer Science) FAQ