Popular SQL Databases: MySQL, PostgreSQL, MS SQL, and SQLite Compared

Why Database Choice Matters for an Analyst

You join a new company and the data team says "we use Redshift." Your previous job used MySQL. A client sends you a .db file. These situations come up constantly. Knowing how the major SQL databases differ saves you from confusion when syntax behaves differently or a feature you rely on does not exist.

This lesson does not go deep into database administration. It gives you just enough to orient yourself when you switch environments.

The Four Databases You Will Encounter Most

MySQL

MySQL is one of the oldest and most widely deployed open-source databases. It powers a large portion of the web, including many e-commerce platforms built on WordPress, Magento, and similar stacks. If you work at a startup or a mid-size company, there is a good chance their backend talks to MySQL.

Syntax example you might run on a MySQL-backed store:

SELECT c.first_name, c.last_name, COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

This query works identically in MySQL, PostgreSQL, SQLite, and MS SQL Server. Core SQL like this is portable.

PostgreSQL

PostgreSQL (often called Postgres) is the database of choice for teams that need advanced analytical features. It has strong support for JSON, full-text search, window functions, and custom data types. Many modern data warehouses like Amazon Redshift are based on PostgreSQL's syntax.

SELECT c.first_name, o.total_amount,
       RANK() OVER (ORDER BY o.total_amount DESC) AS spend_rank
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

RANK() and other window functions are fully supported in PostgreSQL. SQLite supports them too. MySQL added window function support only in version 8.0.

MS SQL Server

MS SQL Server (also called MSSQL or T-SQL) is Microsoft's enterprise database. You will find it in large corporations, especially those running Microsoft stacks with tools like SSRS, SSIS, or Power BI connected directly to the database.

One notable syntax difference: MS SQL uses TOP instead of LIMIT.

-- MS SQL Server syntax
SELECT TOP 5 customer_id, total_amount
FROM orders
ORDER BY total_amount DESC;

-- SQLite / MySQL / PostgreSQL syntax
SELECT customer_id, total_amount
FROM orders
ORDER BY total_amount DESC
LIMIT 5;

SQLite

SQLite is a file-based database. There is no server to set up. The entire database lives in a single .db file. It is embedded in every Android and iOS device, in browsers, and in tools like DB Browser for SQLite.

SQLRound uses SQLite for all practice exercises. You do not need to install anything. Write a query, run it, see the result immediately.

SELECT product_name, price
FROM products p
WHERE p.is_active = 1
ORDER BY p.price DESC
LIMIT 10;

SQLite enforces fewer constraints than other databases. For example, you can insert text into a column defined as INTEGER. This flexibility is fine for learning and prototyping, but keep it in mind when you move to stricter databases.

Quick Feature Comparison

| Feature | MySQL | PostgreSQL | MS SQL Server | SQLite | |---|---|---|---|---| | Open source | Yes | Yes | No | Yes | | Window functions | v8.0+ | Yes | Yes | v3.25+ | | JSON support | Yes | Advanced | Yes | Basic | | Server required | Yes | Yes | Yes | No | | Best for | Web apps | Analytics, complex queries | Enterprise, Microsoft stack | Learning, embedded, mobile |

Common Mistakes

-- wrong
SELECT TOP 10 order_id, total_amount FROM orders ORDER BY total_amount DESC;

TOP is T-SQL syntax specific to MS SQL Server. SQLite and MySQL do not recognize it.

-- correct
SELECT order_id, total_amount FROM orders ORDER BY total_amount DESC LIMIT 10;

---

-- wrong
SELECT order_date, DATEDIFF(shipped_date, order_date) AS days_to_ship FROM orders o;

DATEDIFF() is a MySQL and MS SQL function. SQLite does not support it.

-- correct
SELECT order_date, JULIANDAY(shipped_date) - JULIANDAY(order_date) AS days_to_ship FROM orders o;

Use JULIANDAY() for date arithmetic in SQLite.

---

-- wrong
SELECT customer_id, first_name FROM customers LIMIT 10 OFFSET 0;

This is not wrong syntax, but many beginners assume OFFSET starts at 1. It starts at 0, so OFFSET 0 returns the first page, not the second.

-- correct
SELECT customer_id, first_name FROM customers LIMIT 10 OFFSET 10;

Use OFFSET 10 to skip the first 10 rows and get the second page.

Quick Recap

• MySQL, PostgreSQL, MS SQL Server, and SQLite all use SQL, but each has small syntax differences you will run into as an analyst.
• Core SQL (SELECT, JOIN, GROUP BY, WHERE) is nearly identical across all four. Edge cases like date functions and pagination differ.
• SQLRound uses SQLite, which requires no installation and supports all the standard SQL features you need to learn.

SELECT p.product_name, p.price FROM products p WHERE p.is_active = 1 ORDER BY p.price DESC LIMIT 5;

SELECT o.order_id, o.customer_id, o.total_amount FROM orders o ORDER BY o.order_date ASC LIMIT 10 OFFSET 10;

SELECT c.customer_id, c.first_name, JULIANDAY(MAX(o.order_date)) - JULIANDAY(MIN(o.order_date)) AS days_as_customer FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.first_name HAVING COUNT(o.order_id) > 1;