MySQL vs PostgreSQL vs SQLite - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Differences in Transaction Support

Which database among MySQL, PostgreSQL, and SQLite fully supports ACID-compliant transactions with rollback and commit features?

A Only MySQL supports full ACID-compliant transactions.

B PostgreSQL and SQLite support full ACID-compliant transactions, but MySQL does not.

C PostgreSQL fully supports ACID-compliant transactions; MySQL supports them depending on the storage engine; SQLite supports them but with some limitations.

D None of these databases support ACID-compliant transactions.

💡 Hint

Think about how each database handles transactions and storage engines.

PostgreSQL is known for full ACID compliance. MySQL's transaction support depends on the storage engine used (e.g., InnoDB supports transactions, MyISAM does not). SQLite supports transactions but has some limitations due to its file-based nature.

❓ query_result

intermediate

Query Result Differences in LIMIT Syntax

Given the following SQL query, which database will produce a syntax error when executed as is?

SELECT * FROM users LIMIT 5 OFFSET 10;

A PostgreSQL will produce a syntax error.

B MySQL will produce a syntax error.

C SQLite will produce a syntax error.

D None of the databases will produce a syntax error.

💡 Hint

Check how LIMIT and OFFSET are supported in each database.

All three databases support the syntax LIMIT 5 OFFSET 10 to paginate results, so none will produce a syntax error.

📝 Syntax

advanced

JSON Data Type Support

Which of the following CREATE TABLE statements will cause a syntax error in MySQL but not in PostgreSQL or SQLite?

MySQL

CREATE TABLE data_store (id INT PRIMARY KEY, info JSON);

A MySQL versions before 5.7 do not support JSON data type natively, causing a syntax error.

B PostgreSQL supports JSON data type natively, so no syntax error occurs.

C SQLite supports JSON data type natively, so no syntax error occurs.

D MySQL supports JSON data type natively, so no syntax error occurs.

💡 Hint

Consider the version and native JSON support of each database.

MySQL added native JSON data type support starting from version 5.7. Older versions will throw a syntax error. PostgreSQL and SQLite handle JSON differently but support JSON data types or JSON functions without syntax errors.

❓ optimization

advanced

Indexing Performance Differences

Which statement best describes the indexing capabilities and performance differences among MySQL, PostgreSQL, and SQLite?

A MySQL and PostgreSQL both support advanced indexing like B-tree and GiST, while SQLite only supports basic indexing.

B PostgreSQL supports advanced indexing methods like GiST and GIN, MySQL supports only B-tree indexes, and SQLite supports only basic indexes.

C SQLite supports more advanced indexing than MySQL and PostgreSQL.

D All three databases support the same indexing methods with no performance differences.

💡 Hint

Think about the types of indexes each database supports and their use cases.

PostgreSQL supports advanced index types like GiST and GIN for full-text search and other complex queries. MySQL primarily supports B-tree indexes. SQLite supports only basic B-tree indexes, limiting optimization options.

🔧 Debug

expert

Diagnosing Concurrency Issues

A developer reports that concurrent writes sometimes cause database locks and slow performance. Which database is most likely to handle high concurrency with minimal locking issues out of MySQL, PostgreSQL, and SQLite?

A PostgreSQL, because it uses Multi-Version Concurrency Control (MVCC) to minimize locking.

B MySQL with MyISAM engine, because it supports row-level locking.

C SQLite, because it uses file-level locking which is efficient for concurrency.

D MySQL with MyISAM engine, because it supports MVCC like PostgreSQL.

💡 Hint

Consider how each database manages concurrent transactions and locking.

PostgreSQL uses MVCC to allow multiple transactions to occur without locking conflicts. SQLite uses file-level locking which limits concurrency. MySQL's MyISAM engine uses table-level locking and does not support MVCC; InnoDB supports MVCC.