Sign In
Get Started

Understanding Data Types and Basic Operators in PostgreSQL, SQL, SQLite

by | SQL

Table of Contents

Introduction to Data Types in SQL

Setup

PostgreSQL

CREATE DATABASE example_db;
c example_db

SQLite

sqlite3 example_db.db

Data Types and Basic Operators

Creating a Table with Different Data Types

PostgreSQL

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    birthday DATE,
    balance NUMERIC(10, 2),
    is_member BOOLEAN
);

SQLite

CREATE TABLE example (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER,
    birthday TEXT,
    balance REAL,
    is_member INTEGER CHECK(is_member IN (0, 1))
);

Inserting Data into the Table

PostgreSQL & SQLite

INSERT INTO example (name, age, birthday, balance, is_member)
VALUES 
('John Doe', 30, '1990-01-01', 1000.00, TRUE),
('Jane Smith', 25, '1995-05-15', 250.50, FALSE);

Querying Data

Basic Select

SELECT * FROM example;

Using Operators

Comparison Operators
SELECT * FROM example WHERE age > 25;
SELECT * FROM example WHERE balance <= 1000.00;
Logical Operators
SELECT * FROM example WHERE age > 25 AND is_member = TRUE;
SELECT * FROM example WHERE age < 30 OR balance > 200.00;
Arithmetic Operators
SELECT id, name, age + 5 AS new_age FROM example;
SELECT id, name, balance * 1.05 AS updated_balance FROM example;

Updating Data

PostgreSQL & SQLite

UPDATE example SET balance = balance + 50.00 WHERE is_member = TRUE;

Deleting Data

PostgreSQL & SQLite

DELETE FROM example WHERE age < 28;

This concludes the practical implementation of working with different data types and basic operators in PostgreSQL and SQLite.

-- PostgreSQL, SQL, and SQLite example of working with Integer Data Types

-- Creating a sample table with integer columns
CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT,
    years_of_service INT
);

-- Inserting data into the table
INSERT INTO employees (id, age, years_of_service) VALUES (1, 25, 2);
INSERT INTO employees (id, age, years_of_service) VALUES (2, 30, 5);
INSERT INTO employees (id, age, years_of_service) VALUES (3, 45, 10);

-- Querying data from the table
SELECT * FROM employees;

-- Using integer arithmetic operations
SELECT id, age, years_of_service, (age + years_of_service) AS total_time FROM employees;

-- Filtering based on integer values
SELECT * FROM employees WHERE age > 30;

-- Updating integer values
UPDATE employees SET age = age + 1 WHERE id = 2;

-- Deleting entries based on integer conditions
DELETE FROM employees WHERE years_of_service < 3;

-- Dropping the table after use
DROP TABLE employees;

Character Data Types in PostgreSQL, SQL, and SQLite

PostgreSQL

-- Creating a table with character data types
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50), -- Variable length with a limit of 50 characters
    last_name CHAR(50),     -- Fixed length of 50 characters
    job_description TEXT    -- Unlimited length
);

-- Inserting data into the table
INSERT INTO employees (first_name, last_name, job_description)
VALUES
('John', 'Doe', 'Senior Developer'),
('Jane', 'Smith', 'Project Manager');

-- Querying data
SELECT * FROM employees;

-- Updating data
UPDATE employees SET job_description = 'Lead Developer' WHERE employee_id = 1;

-- Deleting data
DELETE FROM employees WHERE employee_id = 2;

SQL (Standard SQL)

-- Creating a table with character data types
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50), -- Variable length with a limit of 50 characters
    last_name CHAR(50),     -- Fixed length of 50 characters
    job_description TEXT    -- Unlimited length
);

-- Inserting data into the table
INSERT INTO employees (employee_id, first_name, last_name, job_description)
VALUES
(1, 'John', 'Doe', 'Senior Developer'),
(2, 'Jane', 'Smith', 'Project Manager');

-- Querying data
SELECT * FROM employees;

-- Updating data
UPDATE employees SET job_description = 'Lead Developer' WHERE employee_id = 1;

-- Deleting data
DELETE FROM employees WHERE employee_id = 2;

SQLite

-- Creating a table with character data types
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,        -- TEXT provides unlimited length
    last_name TEXT,         -- TEXT provides unlimited length
    job_description TEXT    -- TEXT provides unlimited length
);

-- Inserting data into the table
INSERT INTO employees (first_name, last_name, job_description)
VALUES
('John', 'Doe', 'Senior Developer'),
('Jane', 'Smith', 'Project Manager');

-- Querying data
SELECT * FROM employees;

-- Updating data
UPDATE employees SET job_description = 'Lead Developer' WHERE employee_id = 1;

-- Deleting data
DELETE FROM employees WHERE employee_id = 2;

Conclusion

These samples can be directly implemented to manage character data types. Adapt as necessary for the specific database environment.

Sales Now On Advertisement

Date and Time Data Types

PostgreSQL

Create a table with DATE, TIME, TIMESTAMP, and INTERVAL data types and manipulate the data:

CREATE TABLE event_schedule (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP,
    event_duration INTERVAL
);

-- Insert Data
INSERT INTO event_schedule (event_name, event_date, event_time, event_timestamp, event_duration)
VALUES 
('Conference', '2023-12-05', '09:00:00', '2023-12-05 09:00:00', INTERVAL '1 day');

-- Select Data
SELECT * FROM event_schedule;

-- Update Data
UPDATE event_schedule
SET event_date = '2023-12-06'
WHERE event_id = 1;

-- Delete Data
DELETE FROM event_schedule
WHERE event_id = 1;

SQL (Generic SQL Syntax Compatible with Various DBMS)

Create a table and manipulate data with commonly supported functions:

CREATE TABLE event_schedule (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255),
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP
);

-- Insert Data
INSERT INTO event_schedule (event_name, event_date, event_time, event_timestamp)
VALUES 
('Workshop', '2023-11-11', '14:30:00', '2023-11-11 14:30:00');

-- Select Data
SELECT * FROM event_schedule;

-- Update Data
UPDATE event_schedule
SET event_date = '2023-11-12'
WHERE event_id = 1;

-- Delete Data
DELETE FROM event_schedule
WHERE event_id = 1;

SQLite

Create a table and manipulate date and time data using DATE, TIME, and DATETIME:

CREATE TABLE event_schedule (
    event_id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_name TEXT,
    event_date TEXT,
    event_time TEXT,
    event_datetime TEXT
);

-- Insert Data
INSERT INTO event_schedule (event_name, event_date, event_time, event_datetime)
VALUES 
('Meeting', '2023-10-20', '10:00:00', '2023-10-20 10:00:00');

-- Select Data
SELECT * FROM event_schedule;

-- Update Data
UPDATE event_schedule
SET event_date = '2023-10-21'
WHERE event_id = 1;

-- Delete Data
DELETE FROM event_schedule
WHERE event_id = 1;

These practical examples can be executed directly on your respective SQL environments for PostgreSQL, generic SQL, and SQLite.

Boolean Data Types in SQL (PostgreSQL, SQL, SQLite)

This section provides practical implementations of Boolean data types across PostgreSQL, SQL, and SQLite.

PostgreSQL

Creating a Table with BOOLEAN Data Type

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true
);

Inserting Data

INSERT INTO users (username, is_active) VALUES ('Alice', true);
INSERT INTO users (username, is_active) VALUES ('Bob', false);

Querying Data

SELECT * FROM users WHERE is_active = true;

Updating Data

UPDATE users SET is_active = false WHERE username = 'Alice';

SQL (Standard SQL for relational databases)

Note: Standard SQL does not have a BOOLEAN type explicitly. Instead, 0 and 1 (or other methods) are often used as substitutes.

Creating a Table with BOOLEAN-Like Data Type (Using TINYINT)

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    is_active TINYINT DEFAULT 1
);

Inserting Data

INSERT INTO users (user_id, username, is_active) VALUES (1, 'Alice', 1);
INSERT INTO users (user_id, username, is_active) VALUES (2, 'Bob', 0);

Querying Data

SELECT * FROM users WHERE is_active = 1;

Updating Data

UPDATE users SET is_active = 0 WHERE username = 'Alice';

SQLite

Creating a Table with BOOLEAN Data Type

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    is_active INTEGER NOT NULL DEFAULT 1
);

Inserting Data

INSERT INTO users (username, is_active) VALUES ('Alice', 1);
INSERT INTO users (username, is_active) VALUES ('Bob', 0);

Querying Data

SELECT * FROM users WHERE is_active = 1;

Updating Data

UPDATE users SET is_active = 0 WHERE username = 'Alice';

These examples demonstrate how to create tables, manage data insertion, query, and updates for Boolean-like data types in PostgreSQL, SQL, and SQLite.

Power BI Tools Advertisement

Basic Arithmetic Operators in PostgreSQL, SQL, and SQLite

PostgreSQL

-- Adding two numbers
SELECT 10 + 5 AS sum;

-- Subtracting two numbers
SELECT 10 - 5 AS difference;

-- Multiplying two numbers
SELECT 10 * 5 AS product;

-- Dividing two numbers
SELECT 10 / 5 AS quotient;

-- Modulus operation
SELECT 10 % 3 AS remainder;

SQL (Standard)

-- Adding two numbers
SELECT 10 + 5 AS sum;

-- Subtracting two numbers
SELECT 10 - 5 AS difference;

-- Multiplying two numbers
SELECT 10 * 5 AS product;

-- Dividing two numbers
SELECT 10 / 5 AS quotient;

-- Modulus operation
SELECT 10 % 3 AS remainder;

SQLite

-- Adding two numbers
SELECT 10 + 5 AS sum;

-- Subtracting two numbers
SELECT 10 - 5 AS difference;

-- Multiplying two numbers
SELECT 10 * 5 AS product;

-- Dividing two numbers
SELECT 10 / 5 AS quotient;

-- Modulus operation
SELECT 10 % 3 AS remainder;

Example in Practise using Tables

For a table named transactions with columns item_price and quantity:

PostgreSQL

-- Calculating Total Cost
SELECT item_price * quantity AS total_cost
FROM transactions;

-- Adding Tax
SELECT item_price * quantity * 1.08 AS total_with_tax
FROM transactions;

-- Calculate Profit
SELECT (item_price * quantity) - (cost_price * quantity) AS profit
FROM transactions;

SQL (Standard)

-- Calculating Total Cost
SELECT item_price * quantity AS total_cost
FROM transactions;

-- Adding Tax
SELECT item_price * quantity * 1.08 AS total_with_tax
FROM transactions;

-- Calculate Profit
SELECT (item_price * quantity) - (cost_price * quantity) AS profit
FROM transactions;

SQLite

-- Calculating Total Cost
SELECT item_price * quantity AS total_cost
FROM transactions;

-- Adding Tax
SELECT item_price * quantity * 1.08 AS total_with_tax
FROM transactions;

-- Calculate Profit
SELECT (item_price * quantity) - (cost_price * quantity) AS profit
FROM transactions;

These snippets can be executed directly to see the basic arithmetic operations in action within different SQL-based databases.

Comparison Operators in PostgreSQL, SQL, and SQLite

-- Dataset
CREATE TABLE sample_data (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    salary NUMERIC,
    is_active BOOLEAN,
    join_date DATE
);

INSERT INTO sample_data (id, name, age, salary, is_active, join_date) VALUES
(1, 'Alice', 30, 60000, true, '2022-01-15'),
(2, 'Bob', 25, 50000, false, '2021-11-21'),
(3, 'Charlie', 35, 75000, true, '2020-05-30');

-- Comparison using equality ( = )
SELECT * FROM sample_data WHERE age = 30;

-- Comparison using inequality ( <> or != )
SELECT * FROM sample_data WHERE salary <> 50000;

-- Comparison using greater than ( > )
SELECT * FROM sample_data WHERE age > 25;

-- Comparison using less than ( < )
SELECT * FROM sample_data WHERE age < 35;

-- Comparison using greater than or equal to ( >= )
SELECT * FROM sample_data WHERE age >= 30;

-- Comparison using less than or equal to ( <= )
SELECT * FROM sample_data WHERE join_date <= '2021-12-31';

-- Comparison using Boolean values
SELECT * FROM sample_data WHERE is_active = true;

-- Comparison using pattern match (LIKE)
SELECT * FROM sample_data WHERE name LIKE 'A%';

-- Comparison using null check (IS NULL, IS NOT NULL)
SELECT * FROM sample_data WHERE name IS NOT NULL;

-- Comparison using AND, OR operators
SELECT * FROM sample_data WHERE age > 25 AND is_active = true;
SELECT * FROM sample_data WHERE age < 30 OR salary > 70000;

This implementation demonstrates how to work with various comparison operators in SQL for PostgreSQL, SQL, and SQLite databases.

Logical Operators in SQL (PostgreSQL, SQLite)

Practical Implementations:

1. Using AND Operator

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' AND salary > 50000;

2. Using OR Operator

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

3. Using NOT Operator

SELECT first_name, last_name
FROM employees
WHERE NOT department = 'HR';

4. Combining AND, OR, and NOT Operators

SELECT first_name, last_name
FROM employees
WHERE (department = 'Sales' OR department = 'Marketing') 
  AND salary > 50000 
  AND NOT status = 'Inactive';

Advanced Usage:

5. Using Logical Operators with IS NULL

SELECT first_name, last_name
FROM employees
WHERE department IS NOT NULL AND salary IS NULL;

6. Combining Logical Operators with LIKE

SELECT first_name, last_name
FROM employees
WHERE (first_name LIKE 'J%' OR last_name LIKE 'D%') 
  AND NOT city = 'New York';

Joins with Logical Operators

7. Using Logical Operators in JOIN Conditions

SELECT e.first_name, e.last_name, m.first_name AS manager_first_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.department = 'Sales' AND m.department = 'Sales';

8. Filtering with Logical Operators in JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department = d.department_id
WHERE d.location = 'New York' AND e.salary > 60000;

Ensure your SQL statements match the structure and columns of your actual database schema. Adjust table and column names accordingly. These examples provide foundational usage of logical operators that you can apply as needed.

Related Posts