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.

Practice

1. Which database is best known for being lightweight and easy to set up without a server?

easy

A. SQLite

B. MySQL

C. PostgreSQL

D. Oracle

Solution

1. Step 1: Understand SQLite's design

   SQLite is designed as a serverless, self-contained database engine, making it lightweight and easy to use.

2. Step 2: Compare with other databases

   MySQL and PostgreSQL require server setup, unlike SQLite.

3. Final Answer:

   SQLite -> Option A

4. Quick Check:

   Lightweight and serverless = SQLite [OK]

Hint: Lightweight and no server means SQLite [OK]

Common Mistakes:

• Confusing MySQL as lightweight
• Thinking PostgreSQL is serverless
• Choosing Oracle which is not open-source

2. Which of the following is the correct way to start a MySQL server from the command line?

easy

A. service mysql start

B. mysql --start

C. start mysql

D. mysql start-server

Solution

1. Step 1: Recall MySQL server start commands

   On many systems, MySQL server is started using system service commands like 'service mysql start'.

2. Step 2: Check other options

   'mysql --start', 'start mysql', and 'mysql start-server' are not valid MySQL server start commands.

3. Final Answer:

   service mysql start -> Option A

4. Quick Check:

   Use system service command to start MySQL [OK]

Hint: Use 'service mysql start' to launch MySQL server [OK]

Common Mistakes:

• Using 'mysql --start' which is invalid
• Trying 'start mysql' which is not a command
• Confusing client commands with server commands

3. Given the following SQL snippet run on PostgreSQL, what will be the output?

SELECT version();

medium

A. An error because version() is not supported

B. Empty result set

C. The MySQL version string

D. The PostgreSQL version string

Solution

1. Step 1: Understand the version() function in PostgreSQL

   PostgreSQL supports the version() function which returns the current version string of the server.

2. Step 2: Evaluate the query output

   The query returns a single row with the PostgreSQL version details.

3. Final Answer:

   The PostgreSQL version string -> Option D

4. Quick Check:

   version() returns PostgreSQL version info [OK]

Hint: version() shows PostgreSQL version info [OK]

Common Mistakes:

• Assuming version() is MySQL-only
• Expecting an error from version()
• Thinking it returns empty result

4. You try to run this SQLite command but get an error:

CREATE DATABASE testdb;

What is the problem?

medium

A. Database name 'testdb' is reserved

B. Syntax error in the command

C. SQLite does not support CREATE DATABASE command

D. You need to specify a file path

Solution

1. Step 1: Understand SQLite database creation

   SQLite does not use CREATE DATABASE; it creates databases by opening a file.

2. Step 2: Analyze the error cause

   Using CREATE DATABASE causes an error because SQLite expects a file to open, not a database creation command.

3. Final Answer:

   SQLite does not support CREATE DATABASE command -> Option C

4. Quick Check:

   SQLite creates DB by file, no CREATE DATABASE [OK]

Hint: SQLite creates DB by file, no CREATE DATABASE command [OK]

Common Mistakes:

• Assuming CREATE DATABASE works in SQLite
• Thinking syntax is wrong
• Believing database names are reserved words

5. You want to choose a database for a web app that needs complex queries, full ACID compliance, and extensibility. Which is the best choice?

hard

A. SQLite

B. PostgreSQL

C. MySQL

D. MariaDB

Solution

1. Step 1: Identify requirements

   The app needs complex queries, full ACID compliance, and extensibility.

2. Step 2: Match database features

   PostgreSQL is known for advanced features, strong ACID compliance, and extensibility compared to SQLite and MySQL.

3. Final Answer:

   PostgreSQL -> Option B

4. Quick Check:

   Advanced features and ACID = PostgreSQL [OK]

Hint: Advanced features and ACID compliance point to PostgreSQL [OK]

Common Mistakes:

• Choosing SQLite for complex queries
• Picking MySQL over PostgreSQL for extensibility
• Confusing MariaDB as more advanced than PostgreSQL