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

Need a 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

Need a 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

Need a 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

Need a hint?

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