Chapter 23 Quiz: Database Basics

Instructions: Choose the best answer for each question. The answer key is at the end of this document.

Questions

1. Which of the following is the strongest reason to use a relational database instead of a CSV file?

A) CSV files cannot be read by Python B) Databases support concurrent access and enforce data integrity that files cannot C) Databases are always faster than files for small datasets D) CSV files do not support column headers

2. In a relational database, a foreign key:

A) Is always an auto-incrementing integer B) Is a column that references the primary key of another table C) Must be unique within its own table D) Cannot contain NULL values

3. Which Python code correctly uses a parameterized query to look up a product by SKU?

A)

cursor.execute(f"SELECT * FROM products WHERE sku = {sku}")

B)

cursor.execute("SELECT * FROM products WHERE sku = ?", sku)

C)

cursor.execute("SELECT * FROM products WHERE sku = ?", (sku,))

D)

cursor.execute("SELECT * FROM products WHERE sku = '%s'" % sku)

4. What does PRAGMA foreign_keys = ON do in SQLite?

A) Creates a new foreign key column B) Enables foreign key constraint enforcement, which SQLite does not apply by default C) Converts text columns to integer foreign keys automatically D) Removes all foreign key definitions from the schema

5. You run this query:

SELECT category, COUNT(*), AVG(unit_price)
FROM products
GROUP BY category
HAVING COUNT(*) >= 3;

What does the HAVING clause do?

A) Filters individual product rows before grouping B) Filters the grouped results to show only categories with 3 or more products C) Sorts the results by count descending D) Limits the output to 3 rows

6. What is the difference between fetchone() and fetchall()?

A) fetchone() returns a dict; fetchall() returns a list of tuples B) fetchone() retrieves one row; fetchall() retrieves all remaining rows from the result set C) fetchone() only works on SELECT queries; fetchall() works on all query types D) There is no difference; they are aliases for the same function

7. Which SQL keyword is used to filter groups after a GROUP BY clause?

A) WHERE B) FILTER C) HAVING D) LIMIT

8. You want to find all customers who have never placed an order. Which JOIN type is most appropriate?

A) INNER JOIN B) RIGHT JOIN C) LEFT JOIN D) CROSS JOIN

9. In SQLAlchemy ORM, what does session.commit() do?

A) Closes the database connection B) Writes all pending changes (adds, updates, deletes) from the session to the database C) Creates a new database session D) Rolls back all changes made since the last commit

10. What is the primary advantage of using pd.read_sql() instead of cursor.fetchall()?

A) pd.read_sql() runs queries faster than cursor B) pd.read_sql() automatically returns results as a DataFrame with column names, ready for analysis C) pd.read_sql() works only with PostgreSQL, not SQLite D) pd.read_sql() prevents SQL injection by default

11. Which of the following SQLAlchemy connection strings connects to a PostgreSQL database?

A) sqlite:///mydb.db B) postgresql://user:pass@localhost:5432/mydb C) postgres+sqlite://localhost/mydb D) sql://postgresql:5432/mydb

12. You define a SQLAlchemy model with:

class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True, autoincrement=True)
    sku = Column(String(50), nullable=False, unique=True)

What does unique=True enforce?

A) The sku column will be indexed for faster lookups only B) No two rows in the products table can have the same sku value C) The sku column can only contain values from a predefined list D) The sku column cannot be NULL

13. What is SQL injection, and how do parameterized queries prevent it?

A) SQL injection is a performance problem; parameterized queries cache the query plan B) SQL injection is when malicious user input alters the SQL statement; parameterized queries treat all input as data, never as SQL code C) SQL injection is when two queries run simultaneously; parameterized queries serialize them D) SQL injection is a type of JOIN; parameterized queries avoid it by using subqueries instead

14. In the context of database transactions, what does ROLLBACK do?

A) Saves all pending changes to the database B) Undoes all changes made since the last COMMIT, returning the database to its previous state C) Deletes all rows in the affected table D) Closes the database connection permanently

15. Consider this Python code:

with sqlite3.connect("mydb.db") as conn:
    conn.execute("UPDATE products SET quantity = quantity - 10 WHERE id = 1")
    conn.execute("INSERT INTO order_lines (product_id, quantity) VALUES (1, 10)")

If the INSERT raises an exception, what happens to the UPDATE?

A) The UPDATE is committed; only the INSERT is rolled back B) Both the UPDATE and the INSERT are rolled back C) Both the UPDATE and the INSERT are committed D) The UPDATE is rolled back; the INSERT is committed

16. Which SQL aggregate function would you use to find the highest value in a column?

A) TOP() B) HIGHEST() C) MAX() D) CEILING()

17. In a SQLAlchemy ORM query, how do you retrieve a single Product by its primary key (id = 5)?

A) session.query(Product).filter(id == 5).all() B) session.get(Product, 5) C) session.find(Product, primary_key=5) D) Product.get(session, 5)

18. What is the purpose of Base.metadata.create_all(engine) in SQLAlchemy?

A) Drops all existing tables and creates them fresh B) Creates all tables defined in model classes that inherit from Base, if they do not already exist C) Exports all data to a metadata file D) Generates SQL documentation from the model definitions

19. You run a DELETE statement and want to confirm how many rows were removed. Which property do you check?

A) cursor.result_count B) cursor.affected_rows C) cursor.rowcount D) cursor.deleted

20. Which of the following correctly describes the relationship between an INNER JOIN and a LEFT JOIN?

A) INNER JOIN returns all rows from the left table; LEFT JOIN returns only matching rows B) INNER JOIN returns only rows with matches in both tables; LEFT JOIN returns all rows from the left table plus NULLs for non-matching rows on the right C) INNER JOIN and LEFT JOIN are identical; the name is a matter of convention D) INNER JOIN is used for multiple tables; LEFT JOIN can only combine two tables

Answer Key

Scoring Guide

• 18–20 correct: Excellent — you are ready for the SQLAlchemy ORM and cloud chapters.
• 14–17 correct: Good — review the sections on JOINs, transactions, and parameterized queries.
• 10–13 correct: Fair — re-read sections 23.3 through 23.5 and try the Tier 2 exercises.
• Below 10: Return to the chapter and work through all Tier 1 exercises before retaking the quiz.