PostgreSQL Tutorial for Beginners: Learn SQL with Practical Examples

Introduction

PostgreSQL is a powerful, open-source relational database management system used to store, organize, and retrieve data. It is widely used in web applications, business software, reporting systems, and data analytics projects.

In this tutorial, you will learn the fundamentals of PostgreSQL through practical examples. You will understand how to create tables, insert records, retrieve data, filter results, and summarize information using SQL queries.

This guide is designed for beginners who want to develop practical database skills for software development, data analytics, and other technology-related careers.

What You Will Learn

By the end of this tutorial, you will understand:

• What PostgreSQL is and how it works.
• How to create a database table.
• How to insert and retrieve records.
• How to filter and sort data.
• How to summarize data using aggregate functions.
• How to practice SQL using a business-related example.

What Is PostgreSQL?

PostgreSQL is an open-source relational database management system. It stores information in tables consisting of rows and columns.

For example, an online store might use PostgreSQL to maintain customer information, product details, orders, and payments.

A customer table might contain the following information:

Customer_ID	Customer_Name	City
101	Rahul	Hyderabad
102	Priya	Bengaluru
103	Arjun	Chennai

Each row represents a customer, while each column represents a specific attribute.

PostgreSQL supports SQL (Structured Query Language), which allows users to create tables, retrieve information, update records, and perform calculations on stored data.

Why Learn PostgreSQL?

PostgreSQL is useful for several reasons:

1. Data analysis: SQL queries help analysts extract and summarize business information.
2. Application development: Developers use databases to store application data.
3. Data management: Organizations can organize and maintain structured information.
4. Practical learning: PostgreSQL provides an environment for practicing SQL concepts such as joins, subqueries, and window functions.

PostgreSQL is one of several database systems that support SQL. Although many fundamental SQL concepts are shared across database systems, some commands and functions differ. 

Creating Your First Table in PostgreSQL

A database table stores information in rows and columns. Before inserting data, we need to create a table and define its columns.

For this example, we will create an employees table containing employee IDs, names, departments, and salaries.

SQL Query

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

Understanding the Query

• CREATE TABLE creates a new table.

• employee_id INT stores an integer employee ID.

• PRIMARY KEY uniquely identifies each employee.

• employee_name VARCHAR(100) stores the employee's name.

• department VARCHAR(50) stores the department name.

• salary NUMERIC(10, 2) stores salary values with up to two decimal places.

The semicolon (;) marks the end of the SQL statement.

Inserting Sample Data

After creating the table, we can insert sample employee records using the INSERT INTO statement.

INSERT INTO employees
    (employee_id, employee_name, department, salary)
VALUES
    (1, 'Rahul', 'Analytics', 35000),
    (2, 'Priya', 'Finance', 42000),
    (3, 'Arjun', 'IT', 50000),
    (4, 'Sneha', 'Analytics', 45000),
    (5, 'Kiran', 'IT', 55000);

This query inserts five sample employee records into the table. These names and salary figures are fictional examples for learning purposes.

Understanding the INSERT Query

• INSERT INTO employees specifies the table where the records will be added.

• The column list identifies where each value should be stored.

• VALUES introduces the records being inserted.

• Each set of parentheses represents one employee record.

Retrieving Data Using SELECT

The SELECT statement retrieves data from a table.

To display all columns and records, use this query:

SELECT *
FROM employees;

The asterisk (*) represents all columns.

To display only employee names and salaries, use:

SELECT employee_name, salary
FROM employees;

Selecting only the required columns helps you focus on the information needed for a particular task.