Difference between revisions of "PostgreSQL Database"

Latest revision as of 21:24, 21 May 2026

For the free version of the RDBMS PostgreSQL series please click the link below.

https://spoken-tutorial.org/tutorial-search/?search_foss=RDBMS+PostgreSQL&search_language=English

PostgreSQL is one of the most popular open-source Relational Database Management Systems used today. It is used to store, manage, and retrieve data efficiently in applications ranging from small projects to enterprise systems. PostgreSQL supports advanced SQL features and provides tools such as psql and pgAdmin4 for database management. It can be installed on Linux, Windows, macOS and other platforms. For this tutorial series, we have used PostgreSQL version 18 with pgAdmin4 version 9.11 on Ubuntu 24.04 LTS. In these tutorials, we will learn to create databases, manage tables, write SQL queries, and use AI-assisted features for query generation and troubleshooting.

The Domain reviewers for the PostgreSQL Database are Keerthi Seetha from Open source team and Hari P Kiran. The Spoken Tutorial Effort for PostgreSQL Database series has been contributed by Anjali Satdive, Ketki Bhamble, Madhuri Ganapathi and Ambika Vanchinathan. Editing of the videos is done by Arvind Pillai.

Learners: Anyone who wishes to learn the basics of SQL queries and Database.

Basic Level

1. Installation of PostgreSQL in Windows

• Download PostgreSQL on Windows 11 OS
• Start the process of installation setup.
• Verify the installation.
• Open the pgAdmin4 interface.

2. Installation of PostgreSQL in Linux

• About PostgreSQL.
• Update the system.
• Add the repository for the 18.1 version of PostgreSQL.
• Install PostgreSQL.
• Verify the installation by checking the version.
• Set the user password.
• Run commands to check the interactive interface.
• Add the pgAdmin repository key.
• Install pgAdmin4.
• Check the installation.

3. Create Database using pgAdmin4

• Open and close the pgAdmin 4 interface.
• Connect to the server.
• About database and its objects.
• How to create a database.

4. Creating a Database using a Terminal

• Log in to the Linux terminal.
• Switch to postgres user.
• Access psql prompt.
• Create a database using SQL.

5. Creating a Table and its Attributes

• Connect to the postgreSQL local host server.
• Define table.
• Define column.
• Create a table.

6. Inserting and Retrieving Data

• Insert data into a table.
• Retrieve data from a table.
• Executing query.
• Retrieve data using a SELECT query.

7. Data Redundancy and Primary Key

• About data redundancy.
• About primary key and its rules.
• Retrieve data from a table.
• Adding data repeatedly.
• Executing query to show the details.
• Using Primary Key.
• Drop or Delete TABLE.

8. Basic SELECT Statement

• About SELECT statement.
• Retrieving all the data from the table.
• Execute the query.

9. Filtering and Sorting Data

• About WHERE clause and ORDER BY clause.
• Syntax of WHERE clause and ORDER BY clause.
• Filtering the records using the WHERE clause.
• Sorting the records in ascending and descending order.

10. Relational Operators in PostgreSQL

• About Relational operators.
• Use of WHERE Clause to filter the records.
• Syntax of Relational operators using WHERE Clause.
• Retrieving data using Relational operators.

11. Logical Operators in PostgreSQL

• Logical operators.
• AND, OR, and NOT.
• Syntax of AND, OR, and NOT operators.

12. NULL Values and UPDATE Statement

• What is a NULL Value?
• Syntax of Null value.
• UPDATE Statement.
• Syntax of UPDATE Statement.

13. Aggregate Functions in PostgreSQL

• About aggregate functions in PostgreSQL.
• Identify common aggregate functions like COUNT, SUM, AVG, MIN, and MAX.
• Use aggregate functions to summarize data from a table.
• Handle NULL values in aggregate calculations.
• Use the Round keyword to limit the digits after the decimal point.
• Interpret results returned by aggregate queries.

14. GROUP BY and HAVING Clause

• About GROUP BY clause and HAVING clause.
• Syntax of the GROUP BY clause.
• Apply aggregate functions on grouped data.
• Use the COUNT and ROUND functions.
• Filter the grouped results using the HAVING Clause.
• Analyzing records citywise.

15. Conditional Operators

• About the Conditional Operators.
• Syntax of the conditional operators.
• Apply conditions using the WHERE clause.
• Filter data using pattern matching with LIKE operator.
• Use range conditions with BETWEEN and NOT BETWEEN operators
• Work with lists using IN and NOT IN operators.
• Use the DISTINCT operator to retrieve unique values.
• Interpret results based on applied conditions.

16. Alias Names

• About an Alias name.
• Create an alias name using:
  • a space.
  • AS keyword.
  • an underscore.
  • double quotes.
• Display the student table to illustrate that alias names are temporary.

17. JOIN and INNER JOIN Clause

• About the JOIN Clause.
• Syntax of the JOIN clause.
• Creating a table named results.
• Inserting a few sample records in the results table.
• About the INNER JOIN clause.
• Syntax of the INNER JOIN clause.
• Join the tables using the INNER JOIN clause.

18. OUTER JOINS and SELF JOIN

• About OUTER JOINS, LEFT JOIN, RIGHT JOIN,and FULL JOIN.
• Retrieve the records using LEFT JOIN, RIGHT JOIN and FULL JOINs.
• About SELF JOIN.
• Syntax of the SELF JOIN.
• Retrieve the records using SELF JOIN.

19. Alter Table Statement

• About the Alter Table statement.
• Syntax of Alter Table statement.
• Add two new columns Maths and English to the table.
• Insert marks in the added columns.
• Modify a column name using the Alter Table Statement.
• Run queries to view the changes in the table.

20. Foreign Key Constraint

• Create a database and tables.
• Insert records into tables.
• Observe inconsistent data.
• Delete the inconsistent data from the table.
• Understand Foreign Key concept.
• Add Foreign Key constraint.
• Test valid and invalid records.
• Verify updated table.

21. Effective Use of AI in PostgreSQL

• About how Claude AI works
• Generate SQL queries using Claude AI
• Create and populate tables
• Retrieve and filter records
• Verify and correct queries using Claude AI
• Execute queries in pgAdmin4 and observe the output

22. Troubleshooting and Monitoring with AI

• Introduction to troubleshooting and monitoring.
• View records from a table.
• Count records using COUNT(*).
• Use Claude AI to explain SQL queries.
• Troubleshoot incorrect SQL queries.
• Fix datatype errors using AI.
• Fix ORDER BY syntax errors using AI.
• Verify corrected query output.