SQLite Data Types and Type Affinity Essentials Quiz Quiz

Explore key concepts in SQLite data types and type affinity with practical questions designed to enhance your understanding of column storage, type conversions, and common behaviors. Ideal for beginners seeking clarity on SQLite’s flexible data typing system and its practical implications.

1. Storage Class Selection

   When you define a column as INTEGER in an SQLite CREATE TABLE statement, which storage class does SQLite actually use for the data in that column?

   1. NUMBER
   2. INTEGER
   3. INT64
   4. INTNUM

   Explanation: SQLite uses one of five storage classes, and columns declared as INTEGER use the INTEGER storage class. The other options, INTNUM, INT64, and NUMBER, are not valid SQLite storage classes and are commonly confused due to similarity to other database systems. Using the correct storage class is important for predictable behavior.

2. Type Affinity Application

   What is the type affinity of a column defined as VARCHAR(50) in SQLite?

   1. BLOB affinity
   2. NUMERIC affinity
   3. TEXT affinity
   4. INTEGER affinity

   Explanation: SQLite assigns TEXT affinity to columns declared with VARCHAR and similar types like CHAR. The BLOB affinity is for no specified type, NUMERIC affinity applies to columns with certain keywords (like NUMERIC), and INTEGER affinity is for declarations containing INT. TEXT affinity helps store any kind of text efficiently in SQLite.

3. Implicit Data Conversion

   If you insert the string '123' into an INTEGER-affinity column, how does SQLite store the value?

   1. As a BLOB
   2. As the integer 123
   3. As the string '123'
   4. As NULL

   Explanation: SQLite tries to convert values inserted into a column based on its type affinity. Since '123' is numeric in content, it will be converted to the integer 123 in an INTEGER-affinity column. If conversion were not possible, it would store the text; however, in this case, string '123' converts cleanly. BLOB and NULL are not chosen unless the value cannot convert or is explicitly null.

4. Affinities and BLOB Storage

   What happens if you declare a column in SQLite without any type or with the exact type name 'BLOB'?

   1. It gets REAL affinity
   2. It gets BLOB affinity
   3. It gets NUMERIC affinity
   4. It gets TEXT affinity

   Explanation: In SQLite, columns with no type specified, or those declared exactly as BLOB, are given BLOB affinity. NUMERIC and TEXT affinities are reserved for certain other type declarations, and REAL affinity is for specific real number declarations. BLOB affinity means the value is stored with minimal conversion.

5. REAL Type Representation

   If you insert the floating-point value 5.73 into a column with REAL affinity, how is it typically stored in SQLite?

   1. As a string
   2. As a BLOB
   3. As a 4-byte integer
   4. As an 8-byte floating point number

   Explanation: SQLite uses 8-byte floating point numbers (known as 'double precision') for REAL storage class. A 4-byte integer would be for INTEGER storage. Strings are not the default for numeric storage, and BLOB is only used if the affinity or type is BLOB. The REAL affinity preserves the precision of floating-point values.

6. Strictness of Type Declarations

   How strictly does SQLite enforce data types for inserted values compared to some other databases?

   1. It always stores values as strings
   2. It stores numbers only in text format
   3. It is relaxed and allows storing any type, converting if possible
   4. It rejects any mismatched data types

   Explanation: SQLite uses dynamic typing with type affinity, meaning it attempts to convert data types as needed and only rejects if conversion is impossible. Unlike stricter systems, it does not reject mismatched types outright. Data is not always stored as strings or in text, and numbers are stored using their respective formats if compatible.

7. Type Affinity Rules

   Which keyword in a SQLite column type declaration will NOT assign INTEGER affinity to the column?

   1. INT
   2. TINYINT
   3. INTEGER
   4. FLOAT

   Explanation: Keywords like INT, INTEGER, and TINYINT assign INTEGER affinity. FLOAT, however, triggers REAL affinity. This is a common point of confusion because FLOAT sounds numeric, but it is not treated as INTEGER affinity in SQLite.

8. Handling NULL Values

   What happens if you insert NULL into any column in SQLite, regardless of its declared type?

   1. NULL is stored as BLOB zero
   2. NULL is converted to an empty string
   3. The NULL value is stored as NULL
   4. NULL is stored as integer zero

   Explanation: SQLite stores NULL values in any column as actual NULLs, regardless of the column's affinity or storage class. NULL does not convert to an empty string or zero, whether as integer or blob. This behavior ensures the consistent representation of missing data.

9. Affinity for BOOLEAN Types

   If you define a column as BOOLEAN in SQLite, which type affinity does it get?

   1. INTEGER affinity
   2. NO affinity
   3. BOOLEAN affinity
   4. NUMERIC affinity

   Explanation: SQLite does not have a separate BOOLEAN affinity. Columns declared as BOOLEAN are interpreted with NUMERIC affinity, allowing for values like 0 and 1 or true and false. BOOLEAN affinity and NO affinity are not valid, and INTEGER affinity is only assigned with certain integer keywords.

10. Storing Date and Time Values

    How does SQLite typically store date and time values if you define a column as DATETIME?

    1. Always as an 8-byte REAL
    2. As TEXT, REAL, or INTEGER, depending on the inserted format
    3. Always as TEXT only
    4. As a BLOB only

    Explanation: SQLite can store dates and times as TEXT (ISO8601), REAL (Julian day number), or INTEGER (Unix timestamp), and does not enforce a single storage class for DATETIME. Restricting DATETIME to only TEXT or BLOB would not allow this flexibility. The storage adapts to the inserted data format.