Getting Started With PostgreSQL: Basic Querries (Part 2)

Introduction

In this blog post, we'll continue our investigation of various PostgreSQL queries, starting with the UPDATE query and moving on to the DELETE, WHERE, ORDER BY, and LIMIT and OFFSET clauses at the end. Let's get started with some real-world examples to help you understand.

UPDATE Query

A table's existing records can be easily changed using to the UPDATE query.
The age of the user "john_doe" is now 35.

UPDATE users
SET age = 35
WHERE username = 'john_doe';

DELETE Query

You can efficiently delete records from a table using the DELETE query if specific requirements are satisfied.

Let's delete the user with the username "john_doe" from the "users" table

DELETE FROM users
WHERE username = 'john_doe';

WHERE Clause

In SELECT, UPDATE, and DELETE queries, the WHERE clause applies a filter to the data depending on predefined criteria.

Let's retrieve users who are above the age of 25

SELECT * FROM users
WHERE age > 25;

ORDER BY Clause

You can use the ORDER BY clause to arrange the result set in either ascending or descending order according to one or more columns.

Let's retrieve users from the "users" table sorted by age in descending order

SELECT * FROM users
ORDER BY age DESC;

LIMIT and OFFSET Clauses

While the OFFSET clause skips a predetermined number of rows, the LIMIT clause limits the amount of rows returned in the result set.

Let's get the first three users from the "users" table.

SELECT * FROM users
LIMIT 3;

Use the OFFSET clause to return the following three users while skipping the first three rows.

SELECT * FROM users
LIMIT 3 OFFSET 3;

Conclusion

Now that you've learnt about the fundamental PostgreSQL queries UPDATE to LIMIT and OFFSET. You may manage your PostgreSQL database effectively by manipulating data, filtering outcomes, and using these queries. Your ability to handle challenging data tasks will increase as you explore deeper into the world of databases and find even more powerful queries and features.Good Luck!