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.