DEV Community

Ehtisam Haq
Ehtisam Haq

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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, ...);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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:

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

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay