CS50x: Introduction to Computer Science SQL and Databases Flashcards

24 sample cards from the SQL and Databases deck

Sampled from the end of the deck, so these are different cards from the ones shown on the syllabus page.

1. What is the difference between the WHERE and HAVING clauses?

   WHERE filters individual rows before grouping and cannot use aggregate functions; HAVING filters groups after GROUP BY and can use aggregates, e.g., HAVING COUNT(*) > 10.

2. What is a nested query (subquery), and how is it used with IN?

   A query placed inside another query, with the inner result feeding the outer one. Example: SELECT title FROM shows WHERE id IN (SELECT show_id FROM ratings WHERE rating >= 9);

3. What is the difference between COUNT(*) and COUNT(column_name)?

   COUNT(*) counts all rows in the result, including rows with NULLs; COUNT(column_name) counts only rows where that column's value is not NULL.

4. What is the purpose of a JOIN in SQL?

   To combine rows from two or more tables into a single result set by matching related values (typically a foreign key in one table with a primary key in another).

5. What rows does an INNER JOIN return?

   Only rows where the join condition matches in both tables; rows in either table without a matching partner are excluded from the result.

6. What is a LEFT JOIN, and how does it differ from an INNER JOIN?

   A LEFT JOIN returns all rows from the left (first) table plus matching rows from the right table; where no match exists, the right table's columns are filled with NULL. An INNER JOIN would drop those unmatched left-table rows entirely.

7. What do RIGHT JOIN and FULL OUTER JOIN return?

   RIGHT JOIN returns all rows from the right table with NULLs for unmatched left-table columns; FULL OUTER JOIN returns all rows from both tables, filling in NULLs wherever there is no match on either side.

8. Write an example JOIN query linking a shows table to a ratings table on the show's id.

   SELECT title, rating FROM shows JOIN ratings ON shows.id = ratings.show_id; The ON clause specifies which columns must match.

9. How is a many-to-many relationship (e.g., students and courses) represented in a relational database?

   With a third junction (join/associative) table containing foreign keys to both tables — e.g., an enrollments table with student_id and course_id columns — since neither original table can hold multiple references directly.

10. How is a one-to-many relationship implemented in a relational schema?

    By placing a foreign key column on the "many" side that references the primary key of the "one" side — e.g., each row in an albums table stores the artist_id of its single artist, while one artist can appear in many album rows.

11. What is a database index, and what data structure typically implements it?

    An auxiliary data structure that lets the database find rows matching a column value quickly without scanning the whole table. It is typically implemented as a B-tree, a balanced wide tree that keeps lookups fast.

12. What SQL statement creates an index on the title column of a movies table?

    CREATE INDEX title_index ON movies (title); Subsequent queries filtering on title can then use the index instead of a full table scan.

13. In terms of time complexity, how does searching with an index compare to a full table scan?

    An index (B-tree) lookup runs in roughly $O(\log n)$ time, whereas a full table scan over $n$ rows takes $O(n)$ time — a dramatic speedup on large tables.

14. What are the main costs (trade-offs) of adding indexes to a table?

    Indexes consume extra storage space, and they slow down INSERT, UPDATE, and DELETE operations because the index structure must be updated whenever the data changes. So they should be created only on columns frequently used in searches/joins.

15. What is a transaction in a database?

    A group of one or more SQL statements executed as a single, indivisible unit of work: either all of its statements take effect, or none do.

16. What do the ACID properties of transactions stand for, and what does each mean?

    Atomicity (all-or-nothing execution), Consistency (the database moves from one valid state to another), Isolation (concurrent transactions do not interfere with each other), and Durability (once committed, changes survive crashes).

17. What do the SQL statements BEGIN TRANSACTION, COMMIT, and ROLLBACK do?

    BEGIN TRANSACTION starts a transaction; COMMIT permanently applies all its changes; ROLLBACK undoes all changes made since the transaction began, restoring the prior state (used when an error or invalid condition occurs).

18. What does atomicity mean for a transaction, illustrated with a bank transfer?

    The transaction cannot be interrupted partway: transferring money must both debit one account and credit the other, or do neither. It is impossible to observe or end up in a state where only one of the two updates happened.

19. What is a race condition in the context of databases?

    A bug that occurs when two or more concurrent operations read and write shared data in an interleaved order, so the final result depends on timing and updates can be lost or incorrect.

20. Give the classic race-condition example of two users liking the same post at the same time.

    Both processes SELECT the current like count (say 2), each computes 2 + 1 = 3, and each UPDATEs the count to 3. One increment is lost: the count should be 4 but is stored as 3, because the second write was based on a stale read.

21. How do databases prevent race conditions?

    By wrapping the read-modify-write sequence in a transaction and/or using locks: the database locks the affected rows so other operations must wait until the transaction commits, making the sequence execute as if it were uninterrupted. (Performing the change in one atomic statement, e.g., UPDATE ... SET likes = likes + 1, also avoids the stale read.)

22. What is a SQL injection attack?

    An attack in which a user supplies input containing SQL syntax that gets concatenated into a query string, causing the database to execute the attacker's SQL rather than treating the input as plain data — potentially bypassing logins, reading, or deleting data.

23. How could an attacker use a comment sequence like -- (or ' OR '1'='1) to bypass a login query built by string concatenation?

    If the app builds SELECT * FROM users WHERE username = '{input}' AND password = '{input}', entering something like malan@harvard.edu'-- closes the string with the quote and comments out the rest of the query (including the password check), so the query returns the user without verifying the password. Similarly, ' OR '1'='1 makes the condition always true.

24. What is a parameterized (prepared) query, and why does it prevent SQL injection?

    A query written with placeholders (e.g., ? or %s) for user input — such as db.execute("SELECT * FROM users WHERE username = ?", username) — where the database library binds the input separately, escaping it and treating it strictly as data, never as executable SQL. Attacker-supplied quotes or comments therefore cannot alter the query's structure.

What this deck covers

SQL and Databases flashcards FAQ