MySQL vs PostgreSQL vs SQLite - Hands-On Comparison

Compare MySQL, PostgreSQL, and SQLite Databases

📖 Scenario: You are working as a junior database assistant for a small company. Your manager wants you to create a simple table to compare three popular databases: MySQL, PostgreSQL, and SQLite. This will help the team understand their features side by side.

🎯 Goal: Create a table called database_comparison with columns for db_name, type, license, and use_case. Insert exact data for MySQL, PostgreSQL, and SQLite. Then write a query to select all rows where the type is 'Relational'.

📋 What You'll Learn

Create a table named database_comparison with columns: db_name (VARCHAR), type (VARCHAR), license (VARCHAR), and use_case (VARCHAR).

Insert three rows with exact values for MySQL, PostgreSQL, and SQLite as specified.

Write a SELECT query to get all columns for databases where type is 'Relational'.

💡 Why This Matters

🌍 Real World

Comparing database features helps teams choose the right database for their projects.

💼 Career

Database administrators and developers often create tables, insert data, and query databases to manage and analyze information.

1

Create the database_comparison table

Write a SQL statement to create a table called database_comparison with columns: db_name VARCHAR(20), type VARCHAR(20), license VARCHAR(30), and use_case VARCHAR(50).

MySQL

-- Your code here to create the table

Hint

Use CREATE TABLE with the exact column names and types.

2

Insert data for MySQL, PostgreSQL, and SQLite

Write three INSERT INTO database_comparison statements to add these exact rows:
1. ('MySQL', 'Relational', 'GPL', 'Web applications')
2. ('PostgreSQL', 'Relational', 'PostgreSQL License', 'Advanced analytics')
3. ('SQLite', 'Relational', 'Public Domain', 'Embedded devices')

MySQL

CREATE TABLE database_comparison (
  db_name VARCHAR(20),
  type VARCHAR(20),
  license VARCHAR(30),
  use_case VARCHAR(50)
);
-- Your code here to insert the three rows

Hint

Use three separate INSERT INTO statements with the exact values.

3

Write a SELECT query for relational databases

Write a SQL query to select all columns from database_comparison where the type column is exactly 'Relational'. Use SELECT * FROM database_comparison WHERE type = 'Relational'.

MySQL

CREATE TABLE database_comparison (
  db_name VARCHAR(20),
  type VARCHAR(20),
  license VARCHAR(30),
  use_case VARCHAR(50)
);

INSERT INTO database_comparison VALUES ('MySQL', 'Relational', 'GPL', 'Web applications');
INSERT INTO database_comparison VALUES ('PostgreSQL', 'Relational', 'PostgreSQL License', 'Advanced analytics');
INSERT INTO database_comparison VALUES ('SQLite', 'Relational', 'Public Domain', 'Embedded devices');

-- Your code here to select all relational databases

Hint

Use SELECT * FROM database_comparison WHERE type = 'Relational' exactly.

4

Add a primary key column and update the table

Add a new column called id as an integer primary key that auto-increments. Modify the table creation statement to include id INT PRIMARY KEY AUTO_INCREMENT as the first column. Update the insert statements to include id values 1, 2, and 3 respectively.

MySQL

-- Your code here to add the id column and update inserts

Hint

Modify the CREATE TABLE to add id INT PRIMARY KEY AUTO_INCREMENT as the first column. Add id values in the INSERT statements.

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