MySQL vs PostgreSQL vs SQLite - Quick Revision & Key Differences

Recall & Review

beginner

What is MySQL primarily known for?

MySQL is known as a popular open-source relational database management system (RDBMS) widely used for web applications and supports large-scale databases.

intermediate

How does PostgreSQL differ from MySQL in terms of features?

PostgreSQL offers advanced features like full ACID compliance, support for complex queries, custom data types, and extensibility, making it suitable for complex applications.

beginner

What is SQLite best suited for?

SQLite is a lightweight, serverless database engine ideal for embedded systems, mobile apps, and small to medium applications where simplicity and minimal setup are important.

intermediate

Which database among MySQL, PostgreSQL, and SQLite requires a separate server process?

Both MySQL and PostgreSQL require a separate server process to run, while SQLite is serverless and runs within the application process.

intermediate

Can SQLite handle multiple users accessing the database at the same time efficiently?

SQLite supports multiple readers but allows only one writer at a time, so it is not ideal for high-concurrency multi-user environments.

Which database is known for being serverless and embedded?

A MySQL

B PostgreSQL

C SQLite

D Oracle

SQLite is serverless and embedded, unlike MySQL and PostgreSQL which require a server.

Which database is best suited for complex queries and custom data types?

A PostgreSQL

B MySQL

C SQLite

D MongoDB

PostgreSQL supports advanced features like complex queries and custom data types.

Which database requires a separate server process to run?

A MySQL

B Both MySQL and PostgreSQL

C SQLite

D None of the above

Both MySQL and PostgreSQL require a separate server process; SQLite does not.

Which database is most suitable for mobile apps and small projects?

A PostgreSQL

B MySQL

C SQL Server

D SQLite

SQLite is lightweight and ideal for mobile apps and small projects.

Which database supports multiple concurrent writers efficiently?

A Both B and C

B MySQL

C PostgreSQL

D SQLite

MySQL and PostgreSQL support multiple concurrent writers better than SQLite.

Explain the main differences between MySQL, PostgreSQL, and SQLite.

Think about usage scenarios, features, and server requirements.

When would you choose SQLite over MySQL or PostgreSQL?

Consider simplicity and environment constraints.

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