Introduction to SQL Using PostreSQL

PostgreSQL for Absolute Beginners: Your First Step into Data Management Imagine managing millions of Instagram posts or handling customer orders worldwide without confusion. Behind the scenes, a powerful tool called SQL (Structured Query Language) makes it all possible. In this beginner’s guide, we’ll walk you through PostgreSQL, one of the world’s most popular database systems, and help you write your first SQL commands. Table of contents . What is SQL? . How to set up PostreSQL . Basic database concepts (schemas, tables, rows) . Writing your first SQL queries . A mini hands-on project . Pro tips and next steps Introduction to SQL? SQL (structured query language) is a programming language for storing and processing information in relational database management systems (stores information in tabular form, with rows and columns). It is used extensively for storing, manipulating and retrieving data in systems such as MySQL, PostgreSQL etc. You can use SQL statements to store, update, remove, search, and retrieve information from the database - making it essential for tasks like data analysis, software development, and database administration. You can also use SQL to maintain and optimize database performance. Whether you're building applications, generating reports, or analysing big data, SQL provides the tools needed to interact with complex datasets quickly and efficiently. Thanks to its versatility and widespread adoption, SQL remains one of the most important skills in today’s data-driven world. In this article, we will focus on PostgreSQL, a powerful and widely-used open-source relational database management system (RDBMS). You will learn what makes PostgreSQL a preferred choice for developers, data analysts, and businesses managing complex data. In our next article, we will provide a step-by-step guide on how to download, install, and set up PostgreSQL on your computer, so you can start building and managing your own databases. How to Set Up PostgreSQL: Step-by-Step Guide for Beginners Setting up PostgreSQL correctly is essential for creating secure and high-performing databases. In this guide, we’ll cover everything you need to know to download, install, and configure PostgreSQL on your machine, whether you are using Windows, macOS, or Linux. Step 1: Download PostgreSQL . Go to the official PostgreSQL website: https://www.postgresql.org/download/ . Choose your operating system (Windows, macOS, Linux). . Download the latest stable version of PostgreSQL. Step 2: Install PostgreSQL For Windows: . Open the downloaded installer. . Follow the setup wizard. . Choose an installation directory. . Set a password for the PostgreSQL superuser (postgres). . Select the default port (5432) unless you need to change it. . Complete the installation. For macOS: Install using the PostgreSQL installer package or use Homebrew For Linux: Install using your package manager Step 3: Set Up PostgreSQL . Verify the installation. . Open pgAdmin (the PostgreSQL management tool) or connect via command line: psql -U postgres Basic database concepts Before you dive into writing SQL queries, it’s important to understand the core building blocks of databases. These basic concepts will help you feel more confident as you start creating and managing your own data structures. Term Meaning Database A collection of organized data stored electronically Schema A logical container that holds tables, views, and other database objects. Think of it like a folder that keeps everything organized Table A structured set of data organized into rows and columns, similar to a spreadsheet Row A single record inside a table, containing related data for one item Column A specific attribute or field that holds one type of data, such as a name, email, or date of enrollment Visual Example of a Database Structure Database: school │ └── Schema: public │ ├── Table: students │ ├── id (column) │ ├── name (column) │ ├── enrollment_date (column) In this example: Database: school is the main database. Schema: public groups related tables. Table: students stores information about students. Columns: id, name, and enrollment_date are the fields capturing each student's data. Your first SQL queries Now that you understand the basic building blocks of databases, it’s time to write your first SQL queries! SQL (Structured Query Language) is the tool you’ll use to interact with your database — to create tables, insert data, and retrieve information. Here are a few simple SQL commands to get you started: 1. Create a Database First, you need a database to store your tables. CREATE DATABASE hr_system; Explanation: CREATE DATABASE creates a new database named hr_system. This is where all your data and tables will live. ✅ Tip: After creating it, make sure to connect to the hr_system database before p

Apr 26, 2025 - 15:08

PostgreSQL for Absolute Beginners: Your First Step into Data Management

Imagine managing millions of Instagram posts or handling customer orders worldwide without confusion.
Behind the scenes, a powerful tool called SQL (Structured Query Language) makes it all possible. In this beginner’s guide, we’ll walk you through PostgreSQL, one of the world’s most popular database systems, and help you write your first SQL commands.

. What is SQL?
. How to set up PostreSQL
. Basic database concepts (schemas, tables, rows)
. Writing your first SQL queries
. A mini hands-on project
. Pro tips and next steps

Introduction to SQL?

SQL (structured query language) is a programming language for storing and processing information in relational database management systems (stores information in tabular form, with rows and columns).

It is used extensively for storing, manipulating and retrieving data in systems such as MySQL, PostgreSQL etc.

You can use SQL statements to store, update, remove, search, and retrieve information from the database - making it essential for tasks like data analysis, software development, and database administration.

You can also use SQL to maintain and optimize database performance.
Whether you're building applications, generating reports, or analysing big data, SQL provides the tools needed to interact with complex datasets quickly and efficiently. Thanks to its versatility and widespread adoption, SQL remains one of the most important skills in today’s data-driven world.

In this article, we will focus on PostgreSQL, a powerful and widely-used open-source relational database management system (RDBMS). You will learn what makes PostgreSQL a preferred choice for developers, data analysts, and businesses managing complex data. In our next article, we will provide a step-by-step guide on how to download, install, and set up PostgreSQL on your computer, so you can start building and managing your own databases.

How to Set Up PostgreSQL: Step-by-Step Guide for Beginners

Setting up PostgreSQL correctly is essential for creating secure and high-performing databases. In this guide, we’ll cover everything you need to know to download, install, and configure PostgreSQL on your machine, whether you are using Windows, macOS, or Linux.

Step 1: Download PostgreSQL
. Go to the official PostgreSQL website: https://www.postgresql.org/download/
. Choose your operating system (Windows, macOS, Linux).
. Download the latest stable version of PostgreSQL.

Step 2: Install PostgreSQL

For Windows:
. Open the downloaded installer.
. Follow the setup wizard.
. Choose an installation directory.
. Set a password for the PostgreSQL superuser (postgres).
. Select the default port (5432) unless you need to change it.
. Complete the installation.

For macOS:
Install using the PostgreSQL installer package or use Homebrew

For Linux:
Install using your package manager

Step 3: Set Up PostgreSQL
. Verify the installation.
. Open pgAdmin (the PostgreSQL management tool) or connect via command line:

psql -U postgres

Basic database concepts

Before you dive into writing SQL queries, it’s important to understand the core building blocks of databases. These basic concepts will help you feel more confident as you start creating and managing your own data structures.

Term	Meaning
Database	A collection of organized data stored electronically
Schema	A logical container that holds tables, views, and other database objects. Think of it like a folder that keeps everything organized
Table	A structured set of data organized into rows and columns, similar to a spreadsheet
Row	A single record inside a table, containing related data for one item
Column	A specific attribute or field that holds one type of data, such as a name, email, or date of enrollment

Visual Example of a Database Structure

Database: school
│
└── Schema: public
    │
    ├── Table: students
    │    ├── id (column)
    │    ├── name (column)
    │    ├── enrollment_date (column)

In this example:

Database: school is the main database.

Schema: public groups related tables.

Table: students stores information about students.

Columns: id, name, and enrollment_date are the fields capturing each student's data.

Your first SQL queries

Now that you understand the basic building blocks of databases, it’s time to write your first SQL queries! SQL (Structured Query Language) is the tool you’ll use to interact with your database — to create tables, insert data, and retrieve information.

Here are a few simple SQL commands to get you started:

1. Create a Database

First, you need a database to store your tables.

CREATE DATABASE hr_system;

Explanation:

CREATE DATABASE creates a new database named hr_system.

This is where all your data and tables will live.

✅ Tip: After creating it, make sure to connect to the hr_system database before proceeding.

\c hr_system

2. Create a Schema

A schema helps organize your tables inside the database.

CREATE SCHEMA hr_schema;

Explanation:

CREATE SCHEMA creates a container (folder-like structure) for your tables.

It’s especially useful when you have many tables or want to separate different areas of your system.

✅ Tip: You can also skip this if you want to use the default public schema.

3. Create a Table

To create a new table for storing student information:

CREATE TABLE hr_schema.HR_DATASET (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department VARCHAR(100),
    age CHAR(4)
    gender VARCHAR(10)
    enrollment_date DATE
);

Explanation:

CREATE TABLE hr_schema.HR_DATASET means you’re creating the table HR_DATASET inside hr_schema

employee_id is a unique, auto-incremented number for each employee.

first_name and last_name store the employee’s name.

department indicates which department the employee belongs to.

age is stored as a fixed-length character field.

gender and enrollment_date capture additional details about the employee.

4. Insert Data into the Table

INSERT INTO HR_DATASET (first_name, last_name, department, age, gender, enrollment_date)
VALUES ('John', 'Doe', 'Finance', '29', 'Male', '2024-09-01');

Explanation:

INSERT INTO tells SQL where to add the new record.

We provide values for first_name, last_name, department, age, gender, and enrollment_date.

5. Retrieve Data with SELECT

To view all the records in the table:

SELECT * FROM HR_DATASET;

Explanation:

SELECT * fetches all columns and all rows from the HR_DATASET table.

6. Update Data

If you need to update an employee's department:

UPDATE HR_DATASET
SET department = 'Human Resources'
WHERE employee_id = 1;

Explanation:

UPDATE modifies existing records.

SET changes the value of a specific column.

WHERE ensures only the correct record is updated.

7. Delete Data

To remove an employee from the table:

DELETE FROM HR_DATASET
WHERE employee_id = 1;

Explanation:

DELETE FROM removes specific data.

Using WHERE is important to avoid deleting all records accidentally.

Hands-on Project: Customer Order Management System

In this project, you will create a simple Customer Order Management System. This system will help manage customer details, the products they purchase, and the orders they place. The goal is to create a database that can handle customer and order data effectively.

Project Objective:
You will:

Create a database and schema.
Create tables for customers, orders, and products.
Insert records.
Query the data.
Update and delete records.

Step 1: Create the Database

Create a database called customer_orders_system:

CREATE DATABASE customer_orders_system;

Objective: This database will hold all the data for customer orders, including customer details, products, and orders.

Step 2: Create the Schema

Create a schema called sales_schema:

CREATE SCHEMA sales_schema;

Objective: Organize your data into a dedicated schema for the sales system.

Step 3: Create the Customers Table

Now, create a table to store customer information:

CREATE TABLE sales_schema.customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT
);

Objective: This table will store customer details, such as name, email, phone number, and address.

Step 4: Create the Products Table

Next, create a table to store product information:

CREATE TABLE sales_schema.products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    stock_quantity INT
);

Objective: This table holds the product details — name, price, and available stock.

Step 5: Create the Orders Table

Create a table to store order details:

CREATE TABLE sales_schema.orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES sales_schema.customers(customer_id),
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

Objective: This table holds the orders placed by customers. It includes references to the customer table and stores the total amount of the order.

Step 6: Create the Order Items Table

Since an order can contain multiple products, we need a table to link products to orders:

CREATE TABLE sales_schema.order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES sales_schema.orders(order_id),
    product_id INT REFERENCES sales_schema.products(product_id),
    quantity INT,
    subtotal DECIMAL(10, 2)
);

Objective: This table stores the relationship between orders and the products within each order, including the quantity and subtotal for each product in the order.

Step 7: Insert Customer Records

Now, insert some customer records into the customers table:

INSERT INTO sales_schema.customers (first_name, last_name, email, phone, address)
VALUES
('Alice', 'Johnson', 'alice.johnson@example.com', '123-456-7890', '123 Elm Street'),
('Bob', 'Smith', 'bob.smith@example.com', '234-567-8901', '456 Oak Avenue');

Objective: Add some customers to the system.

Step 8: Insert Product Records

Now, insert some products into the products table:

INSERT INTO sales_schema.products (product_name, price, stock_quantity)
VALUES
('Laptop', 1000.00, 50),
('Smartphone', 500.00, 200),
('Tablet', 300.00, 150);

Objective: Add products that customers can purchase.

Step 9: Insert Orders

Let’s now create some orders for the customers:

INSERT INTO sales_schema.orders (customer_id, order_date, total_amount)
VALUES
(1, '2025-04-01', 1500.00),
(2, '2025-04-02', 800.00);

Objective: Create orders for Alice and Bob.

Step 10: Insert Order Items

Add products to each order by inserting records into the order_items table. Let’s assume Alice bought a Laptop and a Tablet, while Bob bought a Smartphone:

INSERT INTO sales_schema.order_items (order_id, product_id, quantity, subtotal)
VALUES
(1, 1, 1, 1000.00),  -- Alice bought 1 Laptop
(1, 3, 1, 300.00),  -- Alice bought 1 Tablet
(2, 2, 1, 500.00);  -- Bob bought 1 Smartphone

Objective: Link the products to the orders, including the quantity and subtotal for each item.

Step 11: Retrieve Data

Let’s query the system to retrieve records for all tables:

SELECT * FROM sales_schema.customers;
SELECT * FROM sales_schema.products;
SELECT * FROM sales_schema.orders;
SELECT * FROM sales_schema.order_items;

Step 12: Update Product Stock

Let’s say Bob purchased the last smartphone. Update the stock quantity for the smartphone:

UPDATE sales_schema.products
SET stock_quantity = stock_quantity - 1
WHERE product_name = 'Smartphone';

Objective: Learn how to update product inventory after an order is placed.

Step 14: Delete an Order

If you need to remove an order, say Alice’s order with ID 1:

DELETE FROM sales_schema.orders
WHERE order_id = 1;

Objective: Practice deleting records from the database.

Step 15: Review and Reflection

Once you’ve completed this mini project:

Review the relationships between customers, orders, products, and order items.

Experiment by adding more customers, products, and orders.

Reflect on how your system is tracking orders and inventory.

Pro tips: Understanding SQL Data Types and Constraints

When working with SQL, it’s essential to understand the various data types and constraints to properly define your tables and columns. Here’s a quick summary of some commonly used SQL data types and constraints:

Term	Type	Purpose	Example	Range/Details
Integer	`INT`	Stores whole numbers	`age INT`	Typically -2,147,483,648 to 2,147,483,647
Large Integer	`BIGINT`	Stores large whole numbers	`employee_id BIGINT`	Up to 9,223,372,036,854,775,807
Character	`VARCHAR(n)`	Stores variable-length strings	`first_name VARCHAR(100)`	Max length varies (commonly up to 255 characters)
Fixed Character	`CHAR(n)`	Stores fixed-length strings (padded with spaces if shorter)	`gender CHAR(10)`	Max length typically 255 characters
Large Text	`TEXT`	Stores large strings of text (no predefined length)	`address TEXT`	Varies by database (e.g., 65,535 characters in MySQL)
Decimal	`DECIMAL(p, s)`	Stores fixed-point numbers, precision (`p`) and scale (`s`)	`price DECIMAL(10, 2)`	Max precision typically 65 digits
Floating Point	`FLOAT`	Stores floating-point numbers with approximate precision	`rating FLOAT`	Range typically -1.0E+308 to 1.0E+308
Date	`DATE`	Stores date values (year, month, day)	`enrollment_date DATE`	Format: `YYYY-MM-DD`
Time	`TIME`	Stores time values (hours, minutes, seconds)	`order_time TIME`	Format: `HH:MM:SS`
Datetime	`DATETIME`	Stores both date and time values	`order_timestamp DATETIME`	Format: `YYYY-MM-DD HH:MM:SS`
Timestamp	`TIMESTAMP`	Stores date and time with automatic updates when records are modified	`created_at TIMESTAMP`	Auto-updates on insert/update
Boolean	`BOOLEAN`	Stores true/false values	`is_active BOOLEAN`	Typically `TRUE` or `FALSE`
Primary Key	`PRIMARY KEY`	Uniquely identifies records in a table	`employee_id SERIAL PRIMARY KEY`	Must be unique, cannot be NULL
Foreign Key	`FOREIGN KEY`	Creates a relationship between tables by referencing another table's primary key	`customer_id INT REFERENCES customers(customer_id)`	Ensures referential integrity
Not Null	`NOT NULL`	Ensures that a column cannot contain a `NULL` value	`first_name VARCHAR(100) NOT NULL`	Column must always have a value
Auto-Increment	`SERIAL`	Automatically generates unique numbers for new records (commonly used for primary keys)	`employee_id SERIAL PRIMARY KEY`	Similar to `AUTO_INCREMENT` in MySQL
Unique	`UNIQUE`	Ensures all values in a column are distinct	`email VARCHAR(100) UNIQUE`	No duplicate values allowed

Where To Go From Here

Now that you've learned the basics of SQL, it’s time to take your skills to the next level. Here’s what you should focus on next:

Learn SQL JOINs — Master how to combine data from multiple tables using SQL JOINs like INNER JOIN, LEFT JOIN, and RIGHT JOIN. This is essential for working with relational databases.
Understand SQL Indexes — Learning how SQL indexes work will help you optimize your database performance and make your queries run faster.
Work on SQL Project Ideas — The best way to improve your SQL skills is through practice. Start by building small projects like:

- A blog database to manage posts, authors, and comments

- An inventory management system to track products, stock levels, and 
  suppliers

Lastly, Consistent SQL practice and real-world projects will help you master database management and prepare you for advanced topics like stored procedures and database optimization.