Mastering SQL Queries in PostgreSQL: A Hands-On Tutorial

If you're delving into relational database management, PostgreSQL stands out as the most powerful and versatile option available today. Hence, integrating PostgreSQL into our development journey holds significant importance.

As we embark on our database learning journey, we often begin by mastering CRUD operations. In this blog, we'll delve into fundamental SQL queries in PostgreSQL, covering practical examples of SELECT, INSERT, UPDATE, and DELETE statements.

Understanding SELECT Statements

The SELECT statement is a ubiquitous tool in our arsenal, akin to using the find method in mongoose.

It allows us to fetch data from the database, and its syntax is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Here,

• SELECT: Specifies the columns from which we want to retrieve data. Use '*' to select all columns.
• FROM: Specifies the table from which to fetch data.
• WHERE: An optional clause used to filter rows based on specific conditions.

Example:

Suppose we have a table named employees, with columns id, name, age, and department. If we want to fetch data for employees under 30 years of age, working in any department, our query would be:

SELECT department
FROM employees
WHERE age < 30;

Understanding INSERT Statements

The INSERT statement is used to add new rows to a table. In mongoose, you may have used the create method. Its syntax is as follows:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Here,

• INSERT INTO: Specifies the table where data will be inserted.
• VALUES: Specifies the values to be inserted into specific columns in the correct order.

Example:

To insert a new employee into the employees table, our query would be:

INSERT INTO employees (name, age, department)
VALUES ('John Doe', 25, 'Marketing');

Understanding UPDATE Statements

The UPDATE statement allows us to modify existing data in a table. Its syntax is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here,

• UPDATE: Specifies the table to update.
• SET: Specifies the columns to be updated with the specified values.
• WHERE: An optional clause used to specify conditions for updating rows.

Example:

Let's update the department of an employee named 'Alice' to 'Human Resources':

UPDATE employees
SET department = 'Human Resources'
WHERE name = 'Alice';

Understanding DELETE Statements

The DELETE statement removes one or more rows from a table. Its syntax is as follows:

DELETE FROM table_name
WHERE condition;

Here,

• DELETE FROM: Specifies the table from which to remove data.
• WHERE: An optional clause used to specify conditions for deleting rows.

Example:

Let's remove employees over 60 years old from the employees table:

DELETE FROM employees
WHERE age > 60;

In this blog, we've covered the essential SQL queries in PostgreSQL, empowering you to harness the full potential of this robust database management system.

Resources:

• PostgreSQL Tutorial

By mastering these SQL queries, you'll be well-equipped to handle a wide array of database operations effectively. Happy querying!