SQLite database for sensor data in Raspberry Pi - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this SQLite query on sensor data?

Given a table sensors with columns id, temperature, and timestamp, what is the output of this query?

SELECT AVG(temperature) FROM sensors WHERE timestamp > '2024-01-01 00:00:00';

Assume the table has these rows:

• (1, 20.5, '2023-12-31 23:59:59')
• (2, 22.0, '2024-01-01 01:00:00')
• (3, 21.5, '2024-01-02 12:00:00')

Raspberry Pi

SELECT AVG(temperature) FROM sensors WHERE timestamp > '2024-01-01 00:00:00';

A 21.33

B 21.75

C 20.5

D NULL

💡 Hint

Only rows with timestamp after 2024-01-01 00:00:00 are included.

The rows with timestamps '2024-01-01 01:00:00' and '2024-01-02 12:00:00' have temperatures 22.0 and 21.5. Their average is (22.0 + 21.5) / 2 = 21.75.

🧠 Conceptual

intermediate

Which SQLite data type is best for storing sensor timestamps?

You want to store the exact time when sensor data was recorded. Which SQLite data type should you use?

A INTEGER storing Unix timestamps (seconds since 1970-01-01)

B BLOB storing binary time data

C REAL storing Julian day numbers

D TEXT storing ISO 8601 strings like '2024-06-01 12:30:00'

💡 Hint

Consider readability and SQLite's built-in date functions.

SQLite recommends storing timestamps as TEXT in ISO 8601 format because it is human-readable and works well with SQLite date/time functions.

🔧 Debug

advanced

Why does this SQLite INSERT fail for sensor data?

Consider this table creation and insert statements:

CREATE TABLE sensor_data (id INTEGER PRIMARY KEY, temperature REAL NOT NULL, timestamp TEXT NOT NULL UNIQUE);

INSERT INTO sensor_data (temperature, timestamp) VALUES (23.5, '2024-06-01 10:00:00');
INSERT INTO sensor_data (temperature, timestamp) VALUES (24.0, '2024-06-01 10:00:00');

Why does the second INSERT fail?

A Because the timestamp column has a UNIQUE constraint and the second insert uses a duplicate timestamp

B Because the timestamp format is invalid

C Because id is not provided and must be manually inserted

D Because temperature cannot be NULL and the second insert has NULL temperature

💡 Hint

Look at the constraints on the timestamp column.

The timestamp column has a UNIQUE constraint, so inserting a duplicate timestamp value causes a constraint violation error.

📝 Syntax

advanced

Which option correctly creates a SQLite table for sensor data with an auto-incrementing ID?

Choose the correct SQL statement to create a table sensor_readings with columns id (auto-incrementing primary key), humidity (REAL), and timestamp (TEXT).

A CREATE TABLE sensor_readings (id INTEGER PRIMARY KEY, humidity REAL, timestamp TEXT AUTOINCREMENT);

B CREATE TABLE sensor_readings (id INT AUTO_INCREMENT PRIMARY KEY, humidity REAL, timestamp TEXT);

C CREATE TABLE sensor_readings (id INTEGER PRIMARY KEY AUTOINCREMENT, humidity REAL, timestamp TEXT);

D CREATE TABLE sensor_readings (id INTEGER PRIMARY KEY, humidity REAL, timestamp TEXT DEFAULT CURRENT_TIMESTAMP AUTOINCREMENT);

💡 Hint

SQLite uses AUTOINCREMENT keyword differently than other SQL databases.

Option C uses the correct SQLite syntax: INTEGER PRIMARY KEY AUTOINCREMENT for auto-incrementing IDs. Option C uses MySQL syntax which is invalid in SQLite. Options C and D misuse AUTOINCREMENT on wrong columns.

🚀 Application

expert

How many rows will this SQLite query return for sensor data?

Given a table sensor_logs with columns id, temperature, and timestamp, what is the number of rows returned by this query?

SELECT DISTINCT temperature FROM sensor_logs WHERE temperature > 20 AND temperature < 25;

Assume the table has these rows:

• (1, 21.0, '2024-06-01 08:00:00')
• (2, 22.5, '2024-06-01 09:00:00')
• (3, 21.0, '2024-06-01 10:00:00')
• (4, 19.5, '2024-06-01 11:00:00')
• (5, 24.9, '2024-06-01 12:00:00')
• (6, 25.0, '2024-06-01 13:00:00')

A 3

B 4

C 5

D 6

💡 Hint

Remember DISTINCT removes duplicates and the temperature must be strictly greater than 20 and less than 25.

The temperatures that satisfy the condition are 21.0, 22.5, 21.0 (duplicate), and 24.9. Distinct values are 21.0, 22.5, and 24.9, so 3 rows are returned.

Practice

1. What is the main purpose of using SQLite on a Raspberry Pi for sensor data?

easy

A. To send sensor data directly to the cloud

B. To control the sensor hardware directly

C. To store sensor readings locally without needing a separate database server

D. To display sensor data on a web page automatically

Solution

1. Step 1: Understand SQLite's role on Raspberry Pi

   SQLite is a lightweight database that runs locally without needing a separate server.

2. Step 2: Connect SQLite to sensor data storage

   It stores sensor readings on the Raspberry Pi itself, making data management easy and offline.

3. Final Answer:

   To store sensor readings locally without needing a separate database server -> Option C

4. Quick Check:

   SQLite stores data locally = B [OK]

Hint: SQLite stores data locally on Pi, no extra server needed [OK]

Common Mistakes:

• Confusing SQLite with cloud services
• Thinking SQLite controls hardware
• Assuming SQLite automatically displays data

2. Which of the following is the correct Python code snippet to create a table named sensors with columns id (integer primary key), name (text), and value (real)?

easy

A. cursor.execute('CREATE TABLE sensors (id INTEGER PRIMARY KEY, name TEXT, value REAL)')

B. cursor.execute('CREATE sensors TABLE (id INT, name STRING, value FLOAT)')

C. cursor.execute('MAKE TABLE sensors (id PRIMARY KEY, name TEXT, value REAL)')

D. cursor.execute('CREATE TABLE sensors id INTEGER, name TEXT, value REAL')

Solution

1. Step 1: Check SQL syntax for table creation

   The correct SQL command is CREATE TABLE followed by table name and column definitions in parentheses.

2. Step 2: Match Python sqlite3 syntax

   Python uses cursor.execute() with a valid SQL string. cursor.execute('CREATE TABLE sensors (id INTEGER PRIMARY KEY, name TEXT, value REAL)') matches correct SQL and Python syntax.

3. Final Answer:

   cursor.execute('CREATE TABLE sensors (id INTEGER PRIMARY KEY, name TEXT, value REAL)') -> Option A

4. Quick Check:

   Correct SQL CREATE TABLE syntax = D [OK]

Hint: Use 'CREATE TABLE table_name (columns)' inside cursor.execute() [OK]

Common Mistakes:

• Using wrong SQL keywords like MAKE TABLE
• Incorrect column types like STRING instead of TEXT
• Missing parentheses around columns

3. Given this Python code snippet using sqlite3 on Raspberry Pi:

import sqlite3
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS sensors (name TEXT, value REAL)')
cursor.execute("INSERT INTO sensors VALUES ('temp', 23.5)")
cursor.execute('SELECT * FROM sensors')
rows = cursor.fetchall()
print(rows)

What will be the output?

medium

A. [('temp', 23.5)]

B. []

C. SyntaxError

D. None

Solution

1. Step 1: Understand table creation and insertion

   The table 'sensors' is created if not exists, then a row ('temp', 23.5) is inserted.

2. Step 2: Fetch all rows and print

   The SELECT query fetches all rows, which includes the inserted row, so print outputs [('temp', 23.5)].

3. Final Answer:

   [('temp', 23.5)] -> Option A

4. Quick Check:

   Inserted row fetched = [('temp', 23.5)] [OK]

Hint: Inserted data appears in fetchall() output as list of tuples [OK]

Common Mistakes:

• Forgetting to insert data before SELECT
• Expecting empty list if table exists
• Confusing fetchall() output format

4. This Python code tries to insert sensor data but causes an error:

cursor.execute('CREATE TABLE sensors (name TEXT, value REAL)')
cursor.execute('INSERT INTO sensors (name, value) VALUES (?, ?)', ('humidity'))
conn.commit()

What is the error and how to fix it?

medium

A. Error: SQL syntax error; fix by removing parentheses in VALUES

B. Error: Missing second value in tuple; fix by adding a second value like ('humidity', 45.0)

C. Error: Table sensors does not exist; fix by creating table first

D. Error: commit() missing; fix by adding conn.commit()

Solution

1. Step 1: Identify parameter mismatch in INSERT

   The VALUES clause expects two values, but only one ('humidity') is given as a tuple missing a second element.

2. Step 2: Fix by providing both values

   Provide a tuple with two elements, e.g., ('humidity', 45.0), to match the two columns.

3. Final Answer:

   Error: Missing second value in tuple; fix by adding a second value like ('humidity', 45.0) -> Option B

4. Quick Check:

   Parameter count matches columns = C [OK]

Hint: Tuple for parameters must match number of placeholders [OK]

Common Mistakes:

• Passing single string instead of tuple with two values
• Ignoring commit() after insert
• Wrong SQL syntax in INSERT statement

5. You want to store sensor readings with a timestamp in SQLite on Raspberry Pi. Which table schema and insertion code correctly store sensor name, value, and current time using Python's sqlite3?

hard

A. CREATE TABLE readings (name TEXT, value REAL, time TEXT); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, 'CURRENT_TIMESTAMP')")

B. CREATE TABLE readings (name TEXT, value REAL, time INTEGER); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, CURRENT_TIMESTAMP)")

C. CREATE TABLE readings (name TEXT, value REAL, time INTEGER); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, datetime('now'))")

D. CREATE TABLE readings (name TEXT, value REAL, time TEXT); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, datetime('now', 'localtime'))")

Solution

1. Step 1: Choose correct table schema for timestamp

   Use TEXT type for time column to store datetime strings, which SQLite supports well.

2. Step 2: Insert current local time correctly

   Use SQLite function datetime('now', 'localtime') to get current local timestamp as text.

3. Step 3: Match insertion code with schema

   CREATE TABLE readings (name TEXT, value REAL, time TEXT); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, datetime('now', 'localtime'))") uses TEXT time column and inserts with datetime('now', 'localtime'), which is correct.

4. Final Answer:

   CREATE TABLE readings (name TEXT, value REAL, time TEXT); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, datetime('now', 'localtime'))") -> Option D

5. Quick Check:

   Local timestamp stored as TEXT = A [OK]

Hint: Use datetime('now', 'localtime') for current local time in SQLite [OK]

Common Mistakes:

• Using CURRENT_TIMESTAMP without quotes in Python
• Storing time as INTEGER without conversion
• Ignoring localtime adjustment for timestamps