MySQL vs PostgreSQL vs SQL Server overview - Hands-On Comparison

Comparing MySQL, PostgreSQL, and SQL Server

📖 Scenario: You are working as a junior database assistant in a company that wants to choose the best database system for their new project. They want to understand the basic differences between MySQL, PostgreSQL, and SQL Server.

🎯 Goal: Build a simple database table and write queries to explore basic features of MySQL, PostgreSQL, and SQL Server. This will help you see how each system handles data and queries.

📋 What You'll Learn

Create a table named employees with columns id, name, and department.

Insert three specific employee records into the employees table.

Write a query to select all employees from the employees table.

Write a query to count how many employees are in each department.

💡 Why This Matters

🌍 Real World

Companies use different database systems to store and manage employee data. Knowing how to create tables and query data is essential.

💼 Career

Database administrators and developers must understand how to work with tables and queries in various SQL systems to support business needs.

1

Create the employees table

Write a SQL statement to create a table called employees with three columns: id as an integer primary key, name as a text or varchar, and department as a text or varchar.

SQL

-- Write the CREATE TABLE statement for employees
-- Your code here

Hint

Use CREATE TABLE employees and define id as INT PRIMARY KEY. Use VARCHAR(100) for name and department.

2

Insert employee records

Write three SQL INSERT INTO employees statements to add these employees exactly: (1, 'Alice', 'HR'), (2, 'Bob', 'IT'), and (3, 'Charlie', 'Finance').

SQL

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);
-- Insert the three employees below
-- Your code here

Hint

Use three separate INSERT INTO employees (id, name, department) VALUES (...) statements with the exact values given.

3

Select all employees

Write a SQL query to select all columns and rows from the employees table using SELECT * FROM employees.

SQL

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'HR');
INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'IT');
INSERT INTO employees (id, name, department) VALUES (3, 'Charlie', 'Finance');

-- Write a query to select all employees
-- Your code here

Hint

Use SELECT * FROM employees; to get all employee data.

4

Count employees per department

Write a SQL query to count the number of employees in each department using SELECT department, COUNT(*) FROM employees GROUP BY department.

SQL

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'HR');
INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'IT');
INSERT INTO employees (id, name, department) VALUES (3, 'Charlie', 'Finance');

SELECT * FROM employees;

-- Write a query to count employees in each department
-- Your code here

Hint

Use GROUP BY department with COUNT(*) to count employees per department.

Practice

1. Which database system is known for easy setup and is commonly used in web projects?

easy

A. Oracle

B. PostgreSQL

C. SQL Server

D. MySQL

Solution

1. Step 1: Understand the common uses of MySQL

   MySQL is widely recognized for its easy installation and is popular in web development.

2. Step 2: Compare with other databases

   PostgreSQL is more feature-rich for complex queries, and SQL Server is suited for Windows business environments.

3. Final Answer:

   MySQL -> Option D

4. Quick Check:

   Easy setup and web use = MySQL [OK]

Hint: Think 'easy web projects' and pick MySQL [OK]

Common Mistakes:

• Confusing PostgreSQL with MySQL
• Choosing SQL Server for web projects
• Choosing Oracle, which is enterprise-focused

2. Which of the following is the correct way to start a SQL Server service on Windows using command line?

easy

A. service start sqlserver

B. net start MSSQLSERVER

C. systemctl start sqlserver

D. start-service sqlserver

Solution

1. Step 1: Identify Windows command for services

   On Windows, the command to start services is 'net start' followed by the service name.

2. Step 2: Recognize SQL Server service name

   The default SQL Server service is named MSSQLSERVER, so the command is 'net start MSSQLSERVER'.

3. Final Answer:

   net start MSSQLSERVER -> Option B

4. Quick Check:

   Windows service start = net start MSSQLSERVER [OK]

Hint: Windows services start with 'net start' [OK]

Common Mistakes:

• Using Linux commands like systemctl
• Using incorrect service names
• Confusing PowerShell commands

3. Given the following SQL query run on PostgreSQL:

SELECT LENGTH('Hello World');

What will be the output?

medium

A. 10

B. 12

C. 11

D. Error

Solution

1. Step 1: Understand LENGTH function in PostgreSQL

   The LENGTH function returns the number of characters in the string.

2. Step 2: Count characters in 'Hello World'

   There are 11 characters including the space between Hello and World.

3. Final Answer:

   11 -> Option C

4. Quick Check:

   Count characters including space = 11 [OK]

Hint: Count all characters including spaces [OK]

Common Mistakes:

• Ignoring the space character
• Confusing LENGTH with LEN
• Expecting error due to function name

4. You try to run this query on MySQL:

SELECT TOP 5 * FROM users;

But it gives an error. What is the problem?

medium

A. MySQL does not support SELECT TOP syntax

B. Missing WHERE clause

C. Table 'users' does not exist

D. Incorrect use of semicolon

Solution

1. Step 1: Recognize SELECT TOP syntax usage

   SELECT TOP is used in SQL Server to limit rows, but MySQL uses LIMIT.

2. Step 2: Identify correct MySQL syntax

   In MySQL, the query should be: SELECT * FROM users LIMIT 5;

3. Final Answer:

   MySQL does not support SELECT TOP syntax -> Option A

4. Quick Check:

   MySQL uses LIMIT, not TOP [OK]

Hint: MySQL uses LIMIT, not TOP [OK]

Common Mistakes:

• Assuming TOP works in MySQL
• Thinking WHERE is mandatory
• Blaming semicolon usage

5. You want to use a database system that supports advanced JSON querying and complex data types for a data analytics project. Which database is the best choice?

hard

A. PostgreSQL

B. MySQL

C. SQL Server

D. SQLite

Solution

1. Step 1: Identify database strengths for complex data

   PostgreSQL is known for powerful features including advanced JSON support and complex data types.

2. Step 2: Compare with other options

   MySQL has JSON support but less advanced; SQL Server supports JSON but is more Windows-focused; SQLite is lightweight and limited.

3. Final Answer:

   PostgreSQL -> Option A

4. Quick Check:

   Advanced JSON and complex data = PostgreSQL [OK]

Hint: Pick PostgreSQL for complex data and JSON [OK]

Common Mistakes:

• Choosing MySQL for advanced JSON
• Selecting SQL Server for cross-platform JSON
• Picking SQLite for heavy analytics