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

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

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

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

Need a hint?

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