SQL is a widely used database language in the industries to analyze data. It allows you to keep relational databases safe and secure. This tutorial will teach you the PostgreSQL database in detail and enable you to understand how to write SQL queries using the SQL Shell and pgAdmin.
You will cover the following topics in this PostgreSQL tutorial:
- What is PostgreSQL?
- History of PostgreSQL
- PostgreSQL Features
- Connect to Postgresql using SQL Shell
- Connect to PostgreSQL using pgAdmin
- PostgreSQL Commands
What Is PostgreSQL?
PostgreSQL is an open-source database. It is an object-relational database management system that stores data in rows, with columns as different data attributes. It allows you to store, process, and retrieve data safely. It was developed by a worldwide team of volunteers. According to the DB-Engines Ranking, PostgreSQL is currently ranked 4th in popularity amongst hundreds of databases worldwide.
Now that you saw what PostgreSQL is, it’s time to understand the next topic in this PostgreSQL tutorial.
History of PostgreSQL
- From 1977 onwards, PostgreSQL’s journey came into being with the Ingres project. which was developed at the University of California, Berkeley.
- In 1986, professor Michael Stonebraker led the POSTGRES project.
- In 1987, they released the first demo version.
- In 1994, they added an SQL Interpreter to Postgres.
On January 29, the developers released 1997, the first PostgreSQL and it was known as version 6.0. Since then, developers continue to create and maintain PostgreSQL, courtesy of the PostgreSQL Global Development Group. It is a diverse group consisting of companies and thousands of individual contributors.
Now, look at the critical features of the database in this PostgreSQL tutorial.
PostgreSQL Features
- PostgreSQL is an advanced open-source database that is free to download.
- PostgreSQL is compatible with Linux, Windows and macOS operating systems.
- PostgreSQL is highly secure, robust, and reliable. PostgreSQL supports multiple programming interfaces such as Java, C, C++, and Python.
- PostgreSQL supports various data types such as integer, string, and boolean. It also supports structured data types such as date/time, array, range. It can also work with documents like JSON and XML.
- Postgresql supports Multiversion Concurrency Control (MVCC).
It’s now time to get started with the hands-on demo part in this PostgreSQL tutorial.
Connect to PostgreSQL Using SQL Shell (psql).
Once you install PostgreSQL on your system, you will access the SQL Shell and pgAdmin.
Open the SQL Shell (psql) and key in the username and password.
- Let’s check the version of PostgreSQL:
select version();
- Create a test database:
create database test;
- Display all the databases:
\l
- Now, connect to the test database:
\c test;
Next, you will look at how to write queries using pgAdmin in this PostgreSQL tutorial.
Connect to PostgreSQL Using pgAdmin
Right-click on the test database, choose the Query Tool.
The query editor opens, type - select version();
Hit the execute button.
You will perform some simple operations in this PostgreSQL tutorial.
Mathematical Operations Using PostgreSQL
-
Multiplication
select 5*3 as multiplication;
-
Addition
select 5+3+4 as addition;
-
Sequence Operation
select 5*(3+4) as maths_operation
In the above three examples, you saw the “as” keyword for giving an alias name to any operation. The alias name becomes the new column name.
Now, create a table called “movies” in this PostgreSQL tutorial. The movies table will have four columns - movie id, movie name, movie genre, IMDB ratings.
Go to PgAdmin and explore the table and the columns
In the browsers window, click on the databases dropdown. Under Databases, there is the test database.
Got to test > Refresh schemas > Tables > movies > Columns
You will see all the columns present in the movies database.
Now, create a Students table on the pgAdmin.
- Right-click on the Tables > select Create Table
- The Create - Table window appears. Provide the name of the table
- Assign the column names - Student_rollno (integer), Student_name (character varying), Student_gender (character).
- Click on save, and it will create your table.
To delete the students’ table:
drop table students;
Now, insert records to the movies table.
Describe the movies table using:
\d movies;
Now, you will understand the update and delete commands in this PostgreSQL tutorial.
Update Records in a Table:
update movies
set movie_genre = 'Drama, Crime'
where movie_id = 103;
Delete Records From a Table
delete from movies
where movie_id = 108;
Now, you will learn how to filter data using the Where clause in this PostgreSQL tutorial.
Where Clause in PostgreSQL
- SQL query to learn the movie details with imdb_ratings greater than 8.7:
- SQL query to select all the movies between 8.5 and 9.0:
- SQL query to display the movies whose genre is Action:
- Select all the movies whose rating is either 8.7 or 9.0:
Now, create another table called Employees using PostgreSQL pgAdmin.
Import the Employees’ Data to PostgreSQL
- Right-click on the employees table > Select Import/Export > Choose Import
- Give the file location /emp data.csv
- Format - csv
- Select Header as yes, delimiter as comma, and click on ok.
Now, you have the employees’ table ready, let’s explore a few queries in this PostgreSQL tutorial.
- Display unique address from the employees’ table:
select distinct address from employees;
- You can find the missing values in a column using the IS NULL operator.
select * from employees where email is null;
- You can use the alter command to change a table.
The below query will help you rename the address column to country.
alter table employees rename column address to country;
- Find the employees who are from France and their salary is less than 80000.
select * from employees where country = 'France'
and salary < 80000;
- PostgreSQL LIMIT is an optional clause of the SELECT statement that restricts the number of rows returned by the query.
Below PostgreSQL query returns the top five employees based on salary.
select * from employees order by salary desc limit 5;
- The OFFSET clause in PostgreSQL will skip the number of rows mentioned before returning rows.
select * from employees
order by salary desc limit 5 offset 3;
- PostgreSQL supports the FETCH clause to display the number of rows that a query returns.
The below query returns the top three employees based on their salary.
select * from employees order by salary desc
fetch first 3 row only;
- You can use the offset clause along with the fetch clause in PostgreSQL.
select * from employees order by salary desc
offset 3 rows fetch first 5 row only;
Now, look at an important operator, i.e., LIKE, and how to use it in this PostgreSQL tutorial.
LIKE Clause in PostgreSQL
The LIKE clause in PostgreSQL is used to perform pattern matching.
- SQL query to find the employee names and email ids whose name starts with A.
select emp_name, email from employees
where emp_name like 'A%';
- SQL query to find the employee names and email ids whose name ends with d.
select emp_name, email from employees
where emp_name like '%d';
- SQL query to find the employee names and email ids for employees that contain @google in their email id.
select emp_name, email from employees
where email like '%@google%';
- SQL query to find the employees that have u as the second letter in their names
select emp_name, email from employees
where emp_name like '_u%';
Next up, you will see the usage of aggregate functions in this PostgreSQL tutorial.
Aggregate Functions in PostgreSQL
- Count the number of distinct departments in the table:
select count(distinct department) as total_depts
from employees;
- Find the maximum salary in the table:
select max(salary) as max_salary from employees;
- Find the minimum salary in the table:
select min(salary) as min_salary from employees;
- Find the total salary in the table:
select sum(salary) as total_salary from employees;
Now you will learn how to group data using the Group By clause in this PostgreSQL tutorial.
Group By in PostgreSQL
The GROUP BY statement groups rows that have the same values into summary rows. For example, you can find the average salary of employees in each country or city, or department. You use group by along with the SELECT statement to arrange identical data into groups.
- Find the average salary of the employees in each country.
select country, avg(salary) as average_salary from employees group by country;
- Find the maximum salary based on gender.
select gender , max(salary) as maximum_salary from employees group by gender;
- Find the total number of employees in each department.
select count(emp_id) as total_employees, country from employees group by department order by count(emp_id);
Let’s learn how to filter data using the having clause in this PostgreSQL tutorial.
Having in PostgreSQL
The HAVING clause works like the WHERE clause. The difference is that the where clause cannot be used with aggregate functions. The HAVING clause is used with the GROUP BY clause to return those rows that meet a condition.
- Find the countries where the average salary is greater than 80000.
select country, avg(salary) as average_salary from employees group by country having avg(salary) > 80000;
- Find the countries that have less than 30 employees.
select count(emp_id), country from employees
group by country having count(emp_id) < 30;
Case Statement Using PostgreSQL
The PostgreSQL CASE expression is similar to the IF/ELSE statement in other programming languages. It allows you to add if-else logic to a set of statements to make a powerful query.
In the below SQL case statement, you need to categorize the salaries of employees as low salary, medium salary, and high salary based on a certain range.
Select Department, Country, salary,
Case
when salary>45000 and salary<55000 then 'Low Salary'
when salary>55000 and salary<80000 then 'Medium Salary'
when salary>80000 then 'High Salary'
End as salary_range
From employees
Order by salary desc;
Mathematical Functions in PostgreSQL
abs() - Returns the absolute value of a number.
select abs(-102);
ceil() - Returns the next smallest integer value that is greater than or equal to a number.
select ceil(6.45);
floor() - Returns a value rounded down to the next whole number.
select floor (6.45);
greatest() - Returns the largest number in a list of numbers.
select greatest (2,54,67,4,90, 56, 76);
least() - Returns the smallest number in a list of numbers.
select least (20.5,54,67,40,90, 56, 76);
mod() - Returns the remainder of a division.
select mod (54,10);
sqrt() - Returns the square root of a number.
select sqrt (49);
String Functions in PostgreSQL
char_length() - Returns the length of a character string.
select char_length('India is a democracy');
concat() - Combines multiple string values into a single string.
select concat('PostgreSQL ', 'is ', 'interesting.');
left() - Returns a specified number of characters from the left of a string.
select left('India is a democracy', 5);
right() - Returns a specified number of characters from the right of a string.
select right ('India is a democracy', 12);
upper() - Converts a string to uppercase.
select upper ('India is a democracy');
lower() - Converts a string to lowercase.
select lower ('India is a democracy');
reverse() - Prints a string in reverse order.
select reverse('India is a democracy');
Create a Function in PostgreSQL
PostgreSQL allows you to write user-defined functions of your choice to solve specific problems.
Here is an example to create a function that counts the number of email ids in the employees’ table.
CREATE OR REPLACE FUNCTION count_emails ()
RETURNS integer AS $total_emails$
declare
total_emails integer;
BEGIN
SELECT count(email) into total_emails FROM employees;
RETURN total_emails;
END;
$total_emails$ LANGUAGE plpgsql;
Use the function name in the select statement to display the total count.
select count_emails();
Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!
Conclusion
This article on PostgreSQL tutorial helped you understand the history of PostgreSQL and its crucial features. You learned how to connect PostgreSQL with SQL Shell and pgAdmin. Later, you looked at writing PostgreSQL queries to store, manipulate and analyze data.
Do you have any questions regarding this tutorial? If you do, then please put them in the comments section. Our team will solve your queries at the earliest.
To get certified in SQL, click on the following link: SQL Certification Training Simplilearn.
