Getting Started with PostgreSQL Part.2 — Basic Queries & Clauses

Following our last article, we will continue to explore various PosgreSQL queries, starting with the UPDATE query and moving onto the DELETE, WHERE, ORDER BY, LIMIT, and OFFSET clauses and JOINS 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 the UPDATE query. Ex.) The age of the user “john_doe” is now 35.

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

DELETE Query
You can efficienctly 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 folllowing three users while skipping the first three rows.

SELECT*FROM users
LIMIT 3 OFFSET 3;

Database JOINS
When you need to merge data from different tables based on a relevant column, database joins are important. There are various join kinds as follows;

• FULL JOIN — Returns all the rows when there is a match in either the left or right table. If there is no match, it returns NULL values for the missing slide.
• INNER JOIN — Returns only the rows that have matching values in both tables.
• LEFT JOIN — Returns all the rows from the left table and the matching rows from the right table. If there is no match in the right table, it returns NULL values.
• RIGHT JOIN — Returns all the rows from the right table and the matching rows from the left table. If there is no match in the left table, it returns NULL values.
• CROSS JOIN — The cartesian product of the two tables, i.e., all possible combinations of rows from both tables.

Let’s bring an example in which we want to extract all ORDERS with RELEVANT USER DATA from two tables, “users” and “orders.” To retrieve this list, ORDERS must be joined with all matching USER data, and hence:

SELECT orders.order_id, orders.order_date,users.username
FROM orders
INNER JOIN users on orders.user_id=users.user_id:

Using the INNER JOIN, we can retrieve all matching user data to order data.

Today’s Takeaways
As we explored PostgreSQL queries in the past two series, you are now equipped with essential tools to manipulate and retrieve data effectively. From the fundamental UPDATE and DELETE queries to the powerful WHERE, ORDER BY, LIMIT, and OFFSET clauses, we’ve covered a range of techniques to tailor your queries to specific requirements. Moreover, delving into the intricacies of database JOINS, including FULL, INNER, LEFT, RIGHT, and CROSS JOINs, has provided you with the ability to seamlessly merge data from different tables. Armed with these insights, you are well-prepared to navigate and harness the full potential of PostgreSQL for your data management needs. As you continue your journey in database management, these skills will undoubtedly prove invaluable in optimizing the efficiency and accuracy of your queries.

Want to take your PostgreSQL to the next level? — AGEDB offers Enterprise-grade PostgreSQL database along with its graph extension tool, Apache AGE. To learn more, Visit our website today or Contact us for more information!

Otherwise, stay tuned to learn further on PostgreSQL!