Timestamp-based protocols in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the timestamp column from the transactions table.

DBMS Theory

SELECT [1] FROM transactions;

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

Astatus

Btransaction_id

Cuser_id

Dtimestamp

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting a column that does not store time information.

Confusing transaction ID with timestamp.

The timestamp column stores the time when each transaction occurred, which is essential for timestamp-based protocols.

2fill in blank

medium

Complete the code to retrieve transactions ordered by their timestamp in ascending order.

DBMS Theory

SELECT * FROM transactions ORDER BY [1] ASC;

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

Atimestamp

Bstatus

Cuser_id

Dtransaction_id

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by transaction ID instead of timestamp.

Using descending order when ascending is required.

Ordering by timestamp in ascending order lists transactions from oldest to newest, which is important in timestamp-based concurrency control.

3fill in blank

hard

Fix the error in the query to select transactions with timestamp greater than a given value.

DBMS Theory

SELECT * FROM transactions WHERE timestamp [1] '2024-01-01 00:00:00';

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

A<=

B>

C=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using = which selects only exact matches.

Using <= or < which select older transactions.

The operator > selects transactions with timestamps after the specified date, which is correct for filtering newer transactions.

4fill in blank

hard

Fill both blanks to create a query that selects transaction IDs and timestamps for transactions after a certain time.

DBMS Theory

SELECT [1], [2] FROM transactions WHERE timestamp > '2024-01-01 00:00:00';

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

Atransaction_id

Btimestamp

Cuser_id

Dstatus

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns unrelated to transaction identity or time.

Mixing up column names.

Selecting transaction_id and timestamp shows the unique ID and time of each transaction after the specified date.

5fill in blank

hard

Fill all three blanks to create a query that counts transactions per user after a certain timestamp.

DBMS Theory

SELECT [1], COUNT([2]) FROM transactions WHERE timestamp > '2024-01-01 00:00:00' GROUP BY [3];

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

Auser_id

B*

Dtransaction_id

Attempts:

3 left

💡 Hint

Common Mistakes

Counting a specific column instead of all transactions.

Grouping by the wrong column.

This query counts all transactions (*) grouped by each user_id for transactions after the given timestamp.