SQL &
PostgreSQL –
Part I
Session 21
Agenda
• What is Data?
• What is Database?
• What is DBMS?
• Evolution of Databases.
• Relational Databases.
• What is SQL?
• SQL commands.
• What is PostgreSQL?
• PostgreSQL Installation.
• PostgreSQL Queries.
Introduction to Databases and SQL
What is data?
• Data can be anything and everything.
• Any information or fact can be considered as data.
• Your name, age, data of birth or any other information such as your house address,
bank balance, vehicle you drive or even the food you eat can be considered as data.
• Details related to a School, technology, statistics and even mathematics can be
considered as data and be stored in a database.
• Data can be in any form such as an image, file, voice recording, video or even a plain
text etc.
• For a school, data can be information related to its teachers, students or the subjects
they teach.
• To summarize, data can be anything and everything and it can be in any format.
What is a Database?
• Database can be considered as a container filled with data or information which is
electronically stored in a computer system.
• Data in any form can be stored into the database.
• Purpose of storing data in a database is so that it can be easily accessed, modified,
protected and analyzed.
What is a Database?
What is a Database?
• You may already be using database everyday without your knowledge because most
of the websites or mobile apps that you use everyday such as Google, Instagram,
WhatsApp, Facebook etc already use database to store their data.
What is a Database?
• This is true to any application that uses a database. The images, videos, post or any
other information that you see on Facebook is actually stored in a database. And
whenever you search for a specific post or friend, Facebook actually searches for this
data in its database.
• When it comes to bigger applications or companies or government, they need huge
database to store huge data which may be installed on huge servers.
What is DBMS?
• Lot of people refer to database as DBMS but that’s not
entirely correct because database is just a container which
stores data whereas Database Management System or DBMS
is a software which is used to manage your database.
• You need DBMS to interact with database, to store, modify, retrieve and protect data.
DBMS is also required to create, modify and delete databases.
• Users like you and me can use DBMS to enter commands in specific language to
interact with Database.
• Example of a DBMS is MySQL, PostgreSQL, MongoDB, Neo4j, Cassandra etc.
Evolution of Database
• Evolution of database started in 1960’s when the first type of database were made
which was the Flat File Database. Here the data was stored in simple files such as CSV
file or fixed length files etc.
• Later on came the Hierarchical Database and then the Network database which
stored data though parent child relation ship. But both Hierarchical and Network
database were not capable of storing complex data relationships hence were soon
replaced by Relational database.
Evolution of Database
• Fast forward to 2020,
In today’s world, there are mainly 2
popular database types.
- Relational Database
- Non-Relational Database (or NoSQL
Database)
• As per the usage, over 74% of
database used today are relational
database but due to the immense raise
in data usage over the past decade,
mainly due to social media platforms,
non relational database have become
very popular.
Relational Database
• In a relational database, data is stored through
collection of tables. These tables are related to
one another.
• Each table consist of columns and rows. Each
column has a name and a data type. Data type
can be said as a data rule which is associated to
every column. Only those data that satisfy these
data rule can be inserted in the specific column.
• A row can be treated as a record which is
formed by single or multiple columns.
Relational Database
Relational Database
• As an example, if we consider an office database, it may have information related to
Employees, Managers and Departments. These details are stored in different tables. But
these tables will be related to each other through certain columns.
• Here Employee and Manager table is related through the Manager_ID column which is
present in both these tables. In a relational database, the foreign key constraint is used to
form relations between different tables.
• Similarly Manager and Department table are related through dept id column.
• As you can see, employee and department table are not directly related to each other. But
still it is possible to fetch data from employee table based on specific conditions from
department table.
Relational Database
• This is how relational database works, information is scattered across multiple tables which
are related to one another. Hence using table relations, it is possible to retrieve data from
different tables.
• In a relational database, using DBMS, you can enter commands in specific language to
store, retrieve and modify data.
• This specific language is SQL (Structured Query Language)
SQL
• SQL is a programming language which follows
a standard format for querying data across
different relations database.
• Most of the financial institutions such as a
Bank or Insurance companies use relational
database.
• Examples of relational database are Oracle,
MySQL, Microsoft SQL Server, PostgreSQL
etc.
SQL Commands
PostgreSQL
Introduction
• PostgreSQL is a powerful, open-source object-relational database system with a
strong reputation for reliability, feature robustness, and performance. It is widely used
in the industry for handling complex queries and large datasets, making it a popular
choice for data analysis tasks.
Why PostgreSQL?
• PostgreSQL is favored for data analysis due to several key features:
- It supports advanced SQL features like window functions, common table
expressions (CTEs), and full joins.
- It offers excellent performance for complex queries and large datasets.
- It is extensible, supporting custom functions, data types, and procedural languages.
- It integrates well with data analysis tools and programming languages such as
Python, R, and Tableau.
- It is highly secure and compliant with standards.
Installation
https://www.youtube.com/watch?v=GpqJzWCcQXY&ab_channel=ProgrammingKnowledge
Data Types
• https://www.geeksforgeeks.org/postgresql/postgresql-data-types/
Querying
-- Create a database
CREATE DATABASE my_database;
Querying
-- Create a table
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
department VARCHAR(50)
);
Querying
-- Insert a single row
INSERT INTO employees (name, age, department)
VALUES ('Ali', 30, 'HR');
-- Insert multiple rows
INSERT INTO employees (name, age, department)
VALUES
('Sara', 28, 'Finance'),
('Omar', 35, 'IT');
Querying
--Data Retrieval (SELECT)
-- Select all columns
SELECT * FROM employees;
-- Select specific columns
SELECT name, age FROM employees;
-- Use WHERE clause
SELECT * FROM employees WHERE age > 30;
Querying
-- Use LIKE (pattern matching)
SELECT * FROM employees WHERE name LIKE 'A%';
-- Use ORDER BY
SELECT * FROM employees ORDER BY age DESC;
-- Use LIMIT
SELECT * FROM employees LIMIT 5;
Querying
-- Update a value
UPDATE employees SET age = 29 WHERE name = 'Sara';
-- Delete a row
DELETE FROM employees WHERE name = 'Ali';
Querying
-- Count all employees
SELECT COUNT(*) FROM employees;
-- Average age
SELECT AVG(age) FROM employees;
-- Group by department
SELECT department, COUNT(*) FROM employees GROUP BY department;
A Complete Example
CREATE DATABASE customer_db;
A Complete Example
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
country VARCHAR(50),
product VARCHAR(100),
total NUMERIC(10, 2)
);
A Complete Example
INSERT INTO customers (name, age, country, product, total) VALUES
('Ali Hassan', 28, 'Egypt', 'Smartphone', 12000.50),
('Sara Mahmoud', 34, 'Jordan', 'Laptop', 17500.00),
('Lina Nassar', 22, 'UAE', 'Headphones', 850.00),
('Omar Adel', 30, 'Egypt', 'Tablet', 5500.99),
('Dana Salim', 41, 'Lebanon', 'Monitor', 3200.00),
('Youssef Samir', 27, 'Morocco', 'Keyboard', 400.00),
('Mona Said', 36, 'Tunisia', 'Smartwatch', 2300.75),
('Khaled Zidan', 31, 'Saudi Arabia', 'Camera', 8400.00),
('Fatma Ali', 29, 'Algeria', 'Printer', 1250.60),
('Rami Tarek', 25, 'Bahrain', 'Router', 799.99);
Basic Analytical Questions
• Get customers with age > 30
SELECT * FROM customers WHERE age > 30;
• Find the total number of customers from Egypt
SELECT COUNT(*) FROM customers WHERE country = 'Egypt';
• Get customers who spent more than 5000
SELECT * FROM customers WHERE total > 5000;
Basic Analytical Questions
• Find the average age of all customers
SELECT AVG(age) AS average_age FROM customers;
Show the most expensive purchase
SELECT * FROM customers ORDER BY total DESC LIMIT 1;
Total spending per country
SELECT country, SUM(total) AS total_spent
FROM customers
GROUP BY country;
Basic Analytical Questions
• Average spending per product
SELECT product, AVG(total) AS average_price
FROM customers
GROUP BY product;
• Count of customers per country
SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country;
Basic Analytical Questions
• Minimum and maximum age of customers
SELECT MIN(age) AS youngest, MAX(age) AS oldest FROM customers;
• Get all customers whose name starts with 'A'
SELECT * FROM customers WHERE name LIKE 'A%';
Thank you.
