How to solve cursor-based SQL task on SQLite?

Question

I've recently had a job interview with SQL task, which requires using cursors, but code must have been written for SQLite that doesn't have cursors. So, how could I solve it? Task is following:

Exists a table which has two columns: "b" - the beginning and "e" - the end. 
Each row of this table represents 1-dimentional line - column "b"
contains coordinate of the beginning, column "e" - coordinate of
the end. e.g.:
 b | e
-------
 1 | 5
 2 | 4
 3 | 7
-------
Column "e" always greater than "b" in each row. It is required to calculate 
length of covered surface on coordinate axis. For this example answer 
is 6 = ((5 - 1) + (7 - 5)).

This is my solution with cursors:

DECLARE @temp_table TABLE(b int, e int);
DECLARE [cursor] CURSOR FOR SELECT b, e FROM [table]
DECLARE @b INT
DECLARE @e INT

OPEN [cursor]
FETCH NEXT FROM [cursor] INTO @b, @e
WHILE @@FETCH_STATUS = 0
BEGIN
    IF (EXISTS(SELECT * FROM @temp_table))
        FETCH NEXT FROM [cursor] INTO @b, @e
    IF (EXISTS(SELECT * FROM @temp_table WHERE b <= @b AND e >= @e)) 
        CONTINUE;
    IF (EXISTS(SELECT * FROM @temp_table WHERE e < @b OR b > @e)
        OR NOT EXISTS(SELECT * FROM @temp_table)) 
    BEGIN
        INSERT INTO @temp_table VALUES(@b, @e)
        CONTINUE
    END
    IF (EXISTS(SELECT * FROM @temp_table WHERE b <= @b AND e <= @e)) 
    BEGIN
        UPDATE @temp_table SET e = @e WHERE b <= @b AND e <= @e 
        CONTINUE;
    END
    IF (EXISTS(SELECT * FROM @temp_table WHERE b >= @b AND e >= @e)) 
    BEGIN
        UPDATE @temp_table SET b = @b WHERE b >= @b AND e >= @e     
        CONTINUE;
    END
END

CLOSE [cursor]
DEALLOCATE [cursor]

SELECT SUM(e - b) FROM @temp_table

How can I write same code for SQLite? I asked my friends and nobody knows. I tried to solve this task many times, but I didn't have success. Thought about this unresolved problem makes me upset. Help me, please! Any help is appreciated.

Answer 1

SQLite is an embedded database, i.e., it is designed to be used inside some application. You would handle the cursor(s) in your code.

Anyway, you don't really need an iterative algorithm.

You need to ignore all start/end coordinates that are covered by another line. Assuming that no two lines start/end at the same coordinate, this can be done with a simple EXISTS:

SELECT (SELECT SUM(e)
        FROM MyTable
        WHERE NOT EXISTS (SELECT 1
                          FROM MyTable AS T2
                          WHERE T2.e >= MyTable.e
                            AND T2.b <= MyTable.e
                            AND T2.rowid <> MyTable.rowid)
       ) -
       (SELECT SUM(b)
        FROM MyTable
        WHERE NOT EXISTS (SELECT 1
                          FROM MyTable AS T2
                          WHERE T2.e >= MyTable.b
                            AND T2.b <= MyTable.b
                            AND T2.rowid <> MyTable.rowid)
       )