Unlocking Python's Secret SQL Database: SQLite Essentials Quiz

Explore the surprisingly powerful features of Python's built-in SQLite with these beginner-friendly questions covering its strengths, common uses, and important limitations.

1. Discovering Python's Built-In Database

   Which SQL database engine is included with Python for immediate use without additional setup?

   1. SQLite
   2. Oracle
   3. PostgreSQL
   4. MySQL

   Explanation: SQLite comes bundled with Python through the sqlite3 module, enabling immediate use with no installation. PostgreSQL and MySQL are popular but not included by default. Oracle is a separate commercial product and never bundled with Python.

2. Serverless Simplicity

   What is a key advantage of using SQLite in Python applications?

   1. Only works in web browsers
   2. Requires Docker setup
   3. No server installation required
   4. Needs internet access

   Explanation: SQLite operates serverlessly, so there's no need to install or run an external database server. Docker setup and internet access are unnecessary, and it runs locally, not just in web browsers.

3. Creating a New Database File

   How do you create a new SQLite database file in Python using sqlite3?

   1. create_db('filename.db')
   2. new Database('filename.db')
   3. sqlite3.connect('filename.db')
   4. open('filename.db', 'w')

   Explanation: Calling sqlite3.connect with a filename either creates the file or opens it if it exists. The other options are not valid methods for creating or opening a SQLite database via Python.

4. Storing Data for Prototypes

   What type of projects commonly use SQLite because of its zero-setup and lightweight design?

   1. Massive distributed analytics
   2. GPU-accelerated computation
   3. CLI tools and local development
   4. Enterprise database clusters

   Explanation: SQLite is ideal for CLI tools, simple apps, or local prototypes due to its ease of use. It is not designed for large-scale analytics, enterprise clusters, or intensive computational workloads.

5. In-Memory Databases

   Which special filename lets you create a temporary SQLite database in RAM only?

   1. RAM.sql
   2. /dev/shm/sqldb
   3. tempfile.db
   4. :memory:

   Explanation: Using ':memory:' as the database name with sqlite3.connect creates a SQLite database in RAM, perfect for testing. The other options do not serve this function in SQLite.

6. Transaction Support

   Which database feature does SQLite support to guarantee data consistency during changes?

   1. GraphQL queries
   2. Database sharding
   3. Horizontal scaling
   4. ACID transactions

   Explanation: SQLite is ACID-compliant, supporting reliable transactions. GraphQL and sharding are unrelated, while horizontal scaling is not a feature of SQLite.

7. Foreign Key Constraints

   What must you do to use foreign key support in SQLite?

   1. Enable it with a PRAGMA command
   2. Only use it on Linux
   3. Upgrade to a paid version
   4. Use a third-party plugin

   Explanation: Foreign key constraints in SQLite require enabling with 'PRAGMA foreign_keys = ON;'. There is no paid version, OS limitation, or required plugin.

8. Concurrency and Limitations

   Why is SQLite less suitable for heavily used multi-user web applications?

   1. It deletes data randomly
   2. It lacks SQL support
   3. It locks the database on writes
   4. It can't store any data

   Explanation: SQLite locks the entire database during write operations, making it less ideal for high-concurrency scenarios. It fully supports SQL and reliable data storage, and does not delete data unpredictably.

9. Authentication Handling

   Which statement about SQLite's user authentication is true?

   1. It automatically hashes all passwords
   2. It requires an admin password at setup
   3. It does not include authentication; manage this in your app
   4. It has built-in user roles

   Explanation: SQLite has no built-in user authentication. Responsibility for access control falls to the application. It does not support user roles, password hashing, or mandatory admin accounts.

10. Real-World Uses of SQLite

    Which scenario best illustrates a practical use of SQLite in Python?

    1. Hosting a high-traffic e-commerce site
    2. Storing scraped data before moving it to the cloud
    3. Live streaming stock prices to thousands of users
    4. Running parallel analytics jobs on a cluster

    Explanation: SQLite is well-suited for intermediate local storage, such as keeping scraped data before cloud upload. It is not intended for high-traffic, massively parallel, or streaming use cases.