SQL course · Module 1: Databases and setup · Lesson 2 of 40
Quick answer: PostgreSQL, MySQL, SQLite and SQL Server are all relational databases that speak SQL, and the core of SELECT, WHERE, JOIN and GROUP BY is almost the same in all four. They differ in licence, in how they run (SQLite is a single file inside your program, the others are servers) and in a few pieces of syntax, such as LIMIT versus TOP. A good first choice is PostgreSQL: it is free for any use, it follows the SQL standard closely, and what you learn moves easily to the other three. This course teaches PostgreSQL.
In this lesson you will learn:
- what PostgreSQL, MySQL, SQLite and SQL Server have in common
- how their licences and ways of running differ
- the dialect differences a beginner meets first, with real outputs
- why this course uses PostgreSQL
Before you start: Lesson 1, What is a relational database?
Table of Contents
What the four databases have in common
Everything from Lesson 1 applies to all four. They store data in tables with rows and columns, they use primary and foreign keys, and you query them with SQL. A query like this one runs without changes in three of the four:
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
That LIMIT works in PostgreSQL, MySQL and SQLite, but not in SQL Server. Small differences like this are dialect differences: SQL is a standard, but each product adds its own extras. The PostgreSQL project says it “tries to conform with the SQL standard” and that, as of version 18, it conforms to at least 170 of the 177 mandatory features of SQL:2023 Core. It also says that no relational database fully meets this standard (About PostgreSQL).
The good news: the dialect differences are small next to what is shared. If you learn one database well, most of your SQL works in the others.
The four databases at a glance
Here is what the official sites say about each one.
| PostgreSQL | MySQL | SQLite | SQL Server | |
|---|---|---|---|---|
| Made by | open source community | Oracle | SQLite project | Microsoft |
| Licence | PostgreSQL License (similar to BSD or MIT) | GPL, or a paid commercial licence | public domain | commercial; free Express and Developer editions |
| How it runs | server | server | library inside your program, one file | server |
| Official description of use | “safely store and scale the most complicated data workloads” | “highly suited for accessing databases on the Internet” | devices, app file formats, low to medium traffic websites, data analysis | Express: “learning and building desktop and small server data-driven applications” |
Licences: who can use them for free
- PostgreSQL uses the PostgreSQL License, which the project calls “a liberal Open Source license, similar to the BSD or MIT licenses”. You may use, copy, change and share it “for any purpose, without fee” (PostgreSQL licence).
- MySQL is open source under the GPL. The MySQL manual says that if you “need to embed MySQL code into a commercial application, you can buy a commercially licensed version” from Oracle (MySQL 9.7 manual: What is MySQL?).
- SQLite is in the public domain. Anyone is free to “copy, modify, publish, use, compile, sell, or distribute” it “for any purpose” (SQLite copyright).
- SQL Server is a commercial product. Microsoft offers a free Express edition, “ideal for development and production for desktop, web, and small server applications”, and a free Developer edition “for non-production development and testing” (SQL Server downloads). Express has limits, for example a maximum database size of 50 GB in SQL Server 2025 (SQL Server 2025 editions).
For learning, all four cost nothing.
How they run: a server or a single file
PostgreSQL, MySQL and SQL Server are client/server systems. A server program runs in the background, and your tools (psql, an app, a script) connect to it.
SQLite is different. Its page Appropriate uses for SQLite says: “SQLite does not compete with client/server databases. SQLite competes with fopen().” The whole database “is contained in a single disk file” that the library reads and writes directly (About SQLite). The trade-off: it allows many readers but “only one writer at any instant in time” (same “Appropriate uses” page).
SQL Server is no longer Windows-only. Microsoft says it “runs on Linux, starting with SQL Server 2017” and also in containers (SQL Server on Linux).
Dialect differences a beginner meets first
We ran the examples below on PostgreSQL 16.15 with the shop database from Lesson 1, and on SQLite 3.53.1 through Python 3.14’s built-in sqlite3 module, loaded with the same customers and products. MySQL and SQL Server were not installed here, so their syntax in the table at the end comes from their official manuals.
Limiting rows: LIMIT, FETCH FIRST and TOP
PostgreSQL accepts LIMIT:
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
name | price
-----------------------------+--------
Ergonomic Chair | 259.00
Noise-Cancelling Headphones | 149.00
Mechanical Keyboard | 89.00
(3 rows)
It also accepts the SQL standard form, FETCH FIRST. The PostgreSQL docs say LIMIT is “PostgreSQL-specific syntax, also used by MySQL”, while FETCH FIRST comes from the SQL:2008 standard (PostgreSQL docs: SELECT).
SELECT name, price FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY;
name | price
-----------------------------+--------
Ergonomic Chair | 259.00
Noise-Cancelling Headphones | 149.00
Mechanical Keyboard | 89.00
(3 rows)
SQL Server uses TOP instead, written as SELECT TOP (10) ..., and OFFSET ... FETCH for paging (SQL Server docs: TOP). PostgreSQL does not understand TOP:
SELECT TOP 3 name, price FROM products ORDER BY price DESC;
ERROR: syntax error at or near "3"
LINE 1: SELECT TOP 3 name, price FROM products ORDER BY price DESC;
^
In SQLite, LIMIT worked, but both FETCH FIRST and TOP failed:
sqlite> SELECT name, price FROM products ORDER BY price DESC LIMIT 3
('Ergonomic Chair', 259.0)
('Noise-Cancelling Headphones', 149.0)
('Mechanical Keyboard', 89.0)
sqlite> SELECT name, price FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY
OperationalError: near "FETCH": syntax error
sqlite> SELECT TOP 3 name, price FROM products ORDER BY price DESC
OperationalError: near "3": syntax error
Python prints each row as a tuple, so the output looks different from psql.
Case-insensitive search: LIKE and ILIKE
Customer 8 typed his name in capitals: NOAH WILSON. In PostgreSQL, LIKE is case-sensitive, so a lower-case search finds nothing:
SELECT name FROM customers WHERE name LIKE '%noah%';
name
------
(0 rows)
PostgreSQL has ILIKE for a case-insensitive match. The docs say it “is not in the SQL standard but is a PostgreSQL extension” (PostgreSQL docs: Pattern Matching).
SELECT name FROM customers WHERE name ILIKE '%noah%';
name
-------------
NOAH WILSON
(1 row)
SQLite behaves the other way round. Its LIKE ignores case for ASCII letters (SQLite docs: LIKE), and ILIKE does not exist:
sqlite> SELECT name FROM customers WHERE name LIKE '%noah%'
('NOAH WILSON',)
sqlite> SELECT name FROM customers WHERE name ILIKE '%noah%'
OperationalError: near "ILIKE": syntax error
MySQL’s LIKE is also case-insensitive by default, because of its default collation utf8mb4_0900_ai_ci (MySQL 9.7 manual: Case Sensitivity in String Searches). The same LIKE query can return different rows on different databases.
Quotes: text versus names
In PostgreSQL, text goes in single quotes. Double quotes mean the name of a column or table (PostgreSQL docs: Lexical Structure). Using double quotes for text gives a confusing error:
SELECT name FROM customers WHERE city = "Lahore";
ERROR: column "Lahore" does not exist
LINE 1: SELECT name FROM customers WHERE city = "Lahore";
^
PostgreSQL looked for a column called Lahore. With single quotes it works:
SELECT name FROM customers WHERE city = 'Lahore';
name
--------------
Ayesha Malik
Omar Haddad
(2 rows)
To quote a name, MySQL uses backticks by default (MySQL 9.7 manual: Identifiers). SQL Server uses square brackets, or double quotes when QUOTED_IDENTIFIER is on (SQL Server docs: Database identifiers). SQLite accepts double quotes, and also brackets and backticks for compatibility (SQLite keywords).
Data types: strict or flexible
PostgreSQL checks types strictly. Text in a number column is rejected:
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (22, 1, 'three', 24.99);
ERROR: invalid input syntax for type integer: "three"
LINE 2: VALUES (22, 1, 'three', 24.99);
^
SQLite accepted the same row and stored the word as text:
sqlite> INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (22, 1, 'three', 24.99)
sqlite> SELECT quantity, typeof(quantity) FROM order_items
('three', 'text')
The SQLite docs explain that most databases use “static, rigid typing”, while SQLite is flexible unless you create a STRICT table (SQLite datatypes).
Quick syntax table
| Task | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| First 3 rows | LIMIT 3 or FETCH FIRST 3 ROWS ONLY | LIMIT 3 | LIMIT 3 | TOP (3) or OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY |
| Ignore case in LIKE | ILIKE | LIKE (default collation) | LIKE (ASCII letters) | depends on the collation |
| Quote a name | "name" | backticks around the name | "name" | [name] or "name" |
| Auto-numbered id | GENERATED ... AS IDENTITY | AUTO_INCREMENT | INTEGER PRIMARY KEY | IDENTITY(1,1) |
For SQL Server, the docs say “LIKE comparisons are affected by collation” (SQL Server docs: LIKE). The id syntax comes from each official manual: PostgreSQL identity columns, MySQL AUTO_INCREMENT, SQLite autoincrement and SQL Server IDENTITY. You will meet these again in Lesson 16.
Why this course uses PostgreSQL
- Free for any use. Its licence lets you use it for learning, for work and in products, with no fees.
- Close to the standard. It supports standard syntax such as
FETCH FIRST, so habits you build here carry over. - Strict. It tells you when you mix up types or quotes, which is exactly when a beginner needs to know.
- A real server. You learn the same way of working that MySQL and SQL Server use.
- Easy to install. The project offers packages and installers for Linux, macOS and Windows (PostgreSQL downloads). Lesson 3 walks you through it.
When a lesson topic differs in MySQL, SQLite or SQL Server, you will see a short note.
Where this fits
Whichever database you start with, the SQL you learn carries over to the roles described in our data analyst roadmap, data engineer roadmap and data science roadmap. Later in this course you will practise advanced queries with the SQL window functions practice problems.
Try it yourself
Exercise 1: Rewrite a SQL Server query
A colleague sends you this SQL Server query: SELECT TOP 2 name, price FROM products ORDER BY price;. Rewrite it for PostgreSQL and find the two cheapest products.
Exercise 2: A name typed in lower case
Customer 4 typed his name in lower case. Find every customer whose name contains “Ahmed”, first with LIKE '%Ahmed%', then with ILIKE. What changes?
Exercise 3: Fix the quotes
This query fails in PostgreSQL: SELECT name FROM customers WHERE country = "Pakistan";. Fix it.
Solutions
Solution 1
SELECT name, price FROM products ORDER BY price LIMIT 2;
name | price
------------------+-------
Gel Pens 10-Pack | 6.75
Notebook Pack | 12.50
(2 rows)
TOP 2 becomes LIMIT 2 at the end of the query.
Solution 2
SELECT name FROM customers WHERE name LIKE '%Ahmed%';
SELECT name FROM customers WHERE name ILIKE '%ahmed%';
name
------
(0 rows)
name
-------------
bilal ahmed
(1 row)
LIKE is case-sensitive in PostgreSQL, so only ILIKE finds bilal ahmed.
Solution 3
SELECT name FROM customers WHERE country = 'Pakistan' ORDER BY id;
name
---------------
Ayesha Malik
bilal ahmed
Hira Siddiqui
Omar Haddad
(4 rows)
Text needs single quotes. With double quotes PostgreSQL reported ERROR: column "Pakistan" does not exist.
Common mistakes
- Using
TOPin PostgreSQL. It fails withERROR: syntax error at or near "3". UseLIMITorFETCH FIRST. - Double quotes around text. PostgreSQL reads
"Lahore"as a column name:ERROR: column "Lahore" does not exist. Use single quotes for text. - Expecting
LIKEto ignore case everywhere. It does in SQLite (ASCII) and in MySQL’s default collation, but not in PostgreSQL. UseILIKEin PostgreSQL. - Using
ILIKEoutside PostgreSQL. SQLite answersnear "ILIKE": syntax error. It is a PostgreSQL extension. - Trusting SQLite to check types. SQLite stored the word
threein an INTEGER column. PostgreSQL rejects it withinvalid input syntax for type integer.
Frequently asked questions
Is PostgreSQL harder to learn than MySQL?
For the topics a beginner learns first (SELECT, WHERE, JOIN, GROUP BY), the SQL is nearly the same in both. PostgreSQL is stricter about types and quotes, so it shows more errors at the start, but those errors teach good habits.
Can I learn SQL with SQLite instead?
Yes. SQLite needs no server, and the core SQL is the same. Just remember the differences shown above: flexible types, case-insensitive LIKE for ASCII letters, and no ILIKE. This course uses PostgreSQL, so some queries here will not run in SQLite without small changes.
Should I learn SQL Server if jobs ask for it?
Look at the job ads you are interested in. If they ask for SQL Server, you will mainly need to learn its dialect (TOP, square brackets, IDENTITY) on top of the SQL you learn here. The free Developer edition lets you practise on it.
Summary
- PostgreSQL, MySQL, SQLite and SQL Server all use tables, keys and SQL. The core SQL is shared.
- Licences: PostgreSQL License (free, any use), MySQL GPL or commercial, SQLite public domain, SQL Server commercial with free Express and Developer editions.
- SQLite is a library that stores the whole database in one file; the other three are servers.
- First dialect differences:
LIMITvsTOP,ILIKEonly in PostgreSQL, quote characters for names, and strict vs flexible types. - This course teaches PostgreSQL and adds short notes where other databases differ.
Next lesson
Next: Lesson 3, Install PostgreSQL and Load a Sample Database for SQL Practice.
Sources
- About PostgreSQL
- PostgreSQL licence
- PostgreSQL downloads
- PostgreSQL documentation: SELECT (LIMIT and FETCH)
- PostgreSQL documentation: Pattern Matching (LIKE, ILIKE)
- PostgreSQL documentation: Lexical Structure
- PostgreSQL documentation: Identity Columns
- MySQL 9.7 Reference Manual: What is MySQL?
- MySQL 9.7 Reference Manual: Case Sensitivity in String Searches
- MySQL 9.7 Reference Manual: Schema Object Names
- MySQL 9.7 Reference Manual: Using AUTO_INCREMENT
- SQLite: About SQLite
- SQLite: Copyright
- SQLite: Appropriate Uses For SQLite
- SQLite: Datatypes In SQLite
- SQLite: SQL Language Expressions (LIKE)
- SQLite: SQL Keywords (quoting)
- SQLite: Autoincrement
- Microsoft: SQL Server downloads
- Microsoft Learn: Editions and features of SQL Server 2025
- Microsoft Learn: SQL Server on Linux overview
- Microsoft Learn: TOP (Transact-SQL)
- Microsoft Learn: ORDER BY clause (OFFSET and FETCH)
- Microsoft Learn: LIKE (Transact-SQL)
- Microsoft Learn: Database identifiers
- Microsoft Learn: IDENTITY property





