Querying historical data in SCADA systems - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all records from the historical data table.

SCADA systems

SELECT * FROM [1];

Drag options to blanks, or click blank then click option'

Acurrent_data

Bhistorical_data

Clive_data

Dsensor_data

Attempts:

3 left

💡 Hint

Common Mistakes

Using the live or current data table instead of historical data.

Misspelling the table name.

The table historical_data stores past records. Using SELECT * FROM historical_data; retrieves all historical records.

2fill in blank

medium

Complete the code to filter historical data for sensor ID 101.

SCADA systems

SELECT * FROM historical_data WHERE sensor_id = [1];

Drag options to blanks, or click blank then click option'

A101

B'101'

Csensor_101

Did_101

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the sensor ID in quotes causing type mismatch.

Using incorrect sensor ID formats.

The sensor ID is a number, so it should be used without quotes in the query.

3fill in blank

hard

Fix the error in the query to get data between two dates.

SCADA systems

SELECT * FROM historical_data WHERE timestamp [1] '2024-01-01' AND timestamp [2] '2024-01-31';

Drag options to blanks, or click blank then click option'

A>=

B<=

C==

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using equality instead of range operators.

Mixing up greater than and less than signs.

To get data from January 1st to January 31st, use >= '2024-01-01' for the start date and <= '2024-01-31' for the end date.

4fill in blank

hard

Fill both blanks to query average temperature for sensor 202 in February 2024.

SCADA systems

SELECT AVG([1]) FROM historical_data WHERE sensor_id = 202 AND timestamp [2] '2024-02-01' AND timestamp <= '2024-02-28';

Drag options to blanks, or click blank then click option'

Atemperature

Bhumidity

C>=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong data column like humidity.

Using incorrect date comparison operators.

The query calculates the average temperature for sensor 202 starting from February 1st (using >= for the date).

5fill in blank

hard

Fill all three blanks to get max pressure for sensor 303 between March 1 and March 15, 2024.

SCADA systems

SELECT MAX([1]) FROM historical_data WHERE sensor_id = [2] AND timestamp >= '[3]' AND timestamp <= '2024-03-15';

Drag options to blanks, or click blank then click option'

Apressure

B303

C2024-03-01

Dtemperature

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names like temperature instead of pressure.

Using sensor ID as a string instead of number.

Incorrect date format or wrong start date.

This query finds the maximum pressure for sensor 303 from March 1st (2024-03-01) to March 15th, 2024.

Practice

1. What is the main purpose of querying historical data in SCADA systems?

easy

A. To control real-time device operations

B. To review past system behavior and analyze trends

C. To update firmware on sensors

D. To configure network settings

Solution

1. Step 1: Understand the role of historical data

   Historical data stores past readings and events from the system.

2. Step 2: Identify the purpose of querying it

   Querying helps analyze past behavior and detect trends or issues.

3. Final Answer:

   To review past system behavior and analyze trends -> Option B

4. Quick Check:

   Historical data = review past behavior [OK]

Hint: Historical data is for past info, not real-time control [OK]

Common Mistakes:

• Confusing historical data with real-time control
• Thinking it updates devices
• Assuming it changes network settings

2. Which SQL query correctly selects temperature readings from sensor 'S1' recorded after '2024-01-01'?

easy

A. SELECT sensor_id, timestamp FROM readings WHERE type = 'temperature' OR sensor_id = 'S1' AND timestamp > '2024-01-01';

B. SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp < '2024-01-01' AND type = 'temperature';

C. SELECT * FROM readings WHERE sensor_id == 'S1' AND timestamp > '2024-01-01' AND type = 'temperature';

D. SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp > '2024-01-01' AND type = 'temperature';

Solution

1. Step 1: Check correct SQL syntax for conditions

   Use single equals (=) for comparison and AND to combine conditions.

2. Step 2: Verify logical conditions match requirements

   SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp > '2024-01-01' AND type = 'temperature'; correctly filters sensor_id = 'S1', timestamp > '2024-01-01', and type = 'temperature'.

3. Final Answer:

   SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp > '2024-01-01' AND type = 'temperature'; -> Option D

4. Quick Check:

   Correct syntax and filters = SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp > '2024-01-01' AND type = 'temperature'; [OK]

Hint: Use = for comparison and AND to combine filters [OK]

Common Mistakes:

• Using '==' instead of '=' in SQL
• Mixing AND and OR without parentheses
• Using wrong comparison operators
• Filtering with wrong timestamp direction

3. Given this query:
SELECT timestamp, value FROM readings WHERE sensor_id = 'S2' AND timestamp BETWEEN '2024-03-01' AND '2024-03-05' ORDER BY timestamp DESC LIMIT 3;
What will be the output?

medium

A. The 3 latest readings from sensor S2 between March 1 and 5, sorted descending

B. An error because LIMIT cannot be used with ORDER BY

C. All readings from sensor S2 between March 1 and 5, unsorted

D. The 3 earliest readings from sensor S2 between March 1 and 5, sorted ascending

Solution

1. Step 1: Understand the WHERE and BETWEEN clause

   Filters readings from sensor 'S2' between '2024-03-01' and '2024-03-05'.

2. Step 2: Analyze ORDER BY and LIMIT

   ORDER BY timestamp DESC sorts newest first; LIMIT 3 returns top 3 newest readings.

3. Final Answer:

   The 3 latest readings from sensor S2 between March 1 and 5, sorted descending -> Option A

4. Quick Check:

   ORDER BY DESC + LIMIT 3 = latest 3 readings [OK]

Hint: ORDER BY DESC + LIMIT gets newest records first [OK]

Common Mistakes:

• Confusing ascending vs descending order
• Thinking LIMIT returns earliest records
• Assuming LIMIT causes syntax error with ORDER BY

4. You wrote this query to get pressure data:
SELECT * FROM readings WHERE sensor_id = 'P1' AND timestamp > '2024-02-01' AND type = 'pressure'
But it returns no results, even though data exists. What is the likely problem?

medium

A. The query is missing a GROUP BY clause

B. The sensor_id should be numeric, not string

C. The timestamp format is incorrect and does not match stored data

D. The type column does not exist in the readings table

Solution

1. Step 1: Check timestamp format correctness

   Timestamp format must match stored data format exactly to filter correctly.

2. Step 2: Verify other query parts

   Sensor_id as string is valid; GROUP BY not needed; type column likely exists.

3. Final Answer:

   The timestamp format is incorrect and does not match stored data -> Option C

4. Quick Check:

   Timestamp format mismatch = no results [OK]

Hint: Match timestamp format exactly to stored data [OK]

Common Mistakes:

• Assuming sensor_id must be numeric
• Adding unnecessary GROUP BY
• Ignoring timestamp format differences

5. You want to find the average temperature for each of sensors 'T1' and 'T2' during January 2024, but only for readings above 20°C. Which SQL query achieves this?

hard

A. SELECT sensor_id, AVG(value) FROM readings WHERE (sensor_id = 'T1' OR sensor_id = 'T2') AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id;

B. SELECT AVG(value) FROM readings WHERE sensor_id IN ('T1', 'T2') AND timestamp >= '2024-01-01' AND timestamp <= '2024-01-31' AND value > 20 AND type = 'temperature';

C. SELECT sensor_id, AVG(value) FROM readings WHERE sensor_id = 'T1' AND sensor_id = 'T2' AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id;

D. SELECT sensor_id, AVG(value) FROM readings WHERE sensor_id = 'T1' OR sensor_id = 'T2' AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature';

Solution

1. Step 1: Filter sensors correctly

   Use (sensor_id = 'T1' OR sensor_id = 'T2') or sensor_id IN ('T1', 'T2') to include both sensors.

2. Step 2: Apply date and value filters with grouping

   Filter timestamp between January 1 and 31, value > 20, and type = 'temperature'. Group by sensor_id to get averages per sensor.

3. Step 3: Check query correctness

   SELECT sensor_id, AVG(value) FROM readings WHERE (sensor_id = 'T1' OR sensor_id = 'T2') AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id; uses correct syntax with parentheses and GROUP BY; SELECT AVG(value) FROM readings WHERE sensor_id IN ('T1', 'T2') AND timestamp >= '2024-01-01' AND timestamp <= '2024-01-31' AND value > 20 AND type = 'temperature'; misses GROUP BY; SELECT sensor_id, AVG(value) FROM readings WHERE sensor_id = 'T1' AND sensor_id = 'T2' AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id; has impossible condition; SELECT sensor_id, AVG(value) FROM readings WHERE sensor_id = 'T1' OR sensor_id = 'T2' AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature'; lacks parentheses causing wrong logic.

4. Final Answer:

   SELECT sensor_id, AVG(value) FROM readings WHERE (sensor_id = 'T1' OR sensor_id = 'T2') AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id; -> Option A

5. Quick Check:

   Correct filters + grouping = SELECT sensor_id, AVG(value) FROM readings WHERE (sensor_id = 'T1' OR sensor_id = 'T2') AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id; [OK]

Hint: Use parentheses for OR and GROUP BY for averages per sensor [OK]

Common Mistakes:

• Missing GROUP BY when aggregating by sensor
• Using AND instead of OR between sensor_ids
• Incorrect timestamp filtering logic