PostgreSQL

Chapter 7 - DQL (Data Query Language)

'>' and '<' Operator (greater than, less than)

In PostgreSQL, the DQL (Data Query Language) primarily involves querying data from a database using the SELECT statement. When it comes to querying data, comparison operators like > (greater than) and < (less than) are essential for filtering rows based on specific conditions. These operators allow you to retrieve data from a table where certain column values meet the criteria defined by the operator.

Here’s how you can use the > and < operators in a banking context:

Basic Usage of>and<in Queries
To filter records in PostgreSQL, you can use the > (greater than) and < (less than) operators in the WHERE clause. These operators help you compare numeric, date, or even string values.

Example Table Structure

Let’s assume we have the following table called transactions to represent bank transactions:

CREATE TABLE transactions (
  transaction_id SERIAL PRIMARY KEY,
  customer_id INT,
  account_id INT,
  amount DECIMAL(10, 2),
  transaction_date DATE
);

Query to Find Transactions Greater Than a Specific Amount
Use the > operator to find all transactions where the amount is greater than 1000:
```
SELECT transaction_id, customer_id, amount
FROM transactions
WHERE amount > 1000;
```
- This query will return all transactions where the amount is greater than 1000.
- You can modify the amount to any other value depending on your requirement.
Query to Find Transactions Less Than a Specific Amount
Similarly, the < operator helps find all transactions where the amount is less than a certain value, for instance, 500:
```
SELECT transaction_id, customer_id, amount
FROM transactions
WHERE amount < 500;
```
Using>and<with Dates
The comparison operators can also be applied to dates. For example, if you want to find transactions that occurred after January 1st, 2024, you can use the > operator:
```
SELECT transaction_id, customer_id, transaction_date
FROM transactions
WHERE transaction_date > '2024-01-01';
```
- Here, we are filtering transactions that occurred after the specified date.
- You can use the < operator to find transactions before a particular date.
Combining>and<in a Query
You can also combine both operators to set a range. For example, to find transactions between 500 and 1000:
```
SELECT transaction_id, customer_id, amount
FROM transactions
WHERE amount > 500 AND amount < 1000;
```
- This query fetches all transactions where the amount is greater than 500 but less than 1000.
- You can combine multiple conditions using logical operators like AND or OR.
Practical Use Cases in Banking
- Finding high-value transactions: Identify transactions that exceed a certain threshold (e.g., transactions over $1000).
- Filtering transactions by date: List all transactions within a certain period, for example, between two dates.
- Customer analysis: Fetch all transactions where the amount is below a certain limit to identify low-value customers.

By using the > and < operators in your queries, you can easily filter the data to retrieve only what is necessary, especially in the context of banking where monitoring transaction amounts and dates is critical.

Tansy SQL Course - '>' and '<' Operator (greater than, less than) - Video Thumbnail

TEST CODE

In PostgreSQL, to fetch salaries exceeding 150,000, excluding the amount of 150,000 itself, you can use a subquery to achieve the same result.

SELECT *
FROM (
SELECT *
FROM org_employee
WHERE salary > 150000
) filtered_employees
ORDER BY salary;

In PostgreSQL, to retrieve salaries equal to or greater than 150,000, including the amount of 150,000 itself, you can use a subquery to achieve the same result.

SELECT *
FROM (
SELECT *
FROM org_employee
WHERE salary >= 150000
) AS filtered_employees
ORDER BY salary;

In PostgreSQL, you can use the `TRUNC` function to achieve the same result by truncating the date to the day level and comparing it with the desired date.

SELECT *
FROM act_order
WHERE DATE(order_date) >= '2023-12-15'::DATE
ORDER BY order_date;

Example 1:

Let's explore the procedure of extracting information from a designated table using the '>' SQL operator with numeric data. This query retrieves all rows from the employee table where the salary column is greater than 150k.

Example 1 - Raw data from employee table

Example 1 -Query

SELECT *
FROM org_employee
WHERE salary > 150000
ORDER BY salary;

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query. Note that salary 150,000 is not selected.

Example 1 - Query Output

Example 2:

Let's explore the procedure for extracting information from a designated table using '>=' operator on integer data. This query retrieves all rows from the employee table where the salary column is greater than 150k, including border line salary of 150k.

Example 2 - Raw data from employee table

Example 2 - Query

SELECT *
FROM org_employee
WHERE salary >= 150000
ORDER BY salary;

Example 2 - Query data mapping

Example 2 - Query Output

Example 3:

Let's explore the procedure for extracting information from a designated table using the '>=' SQL operator on date time data. This query retrieves all rows from the orders table where the order date is greater than or equal to '2023-12-15'

Example 3 - Raw data from order table

Example 3 - Query

SELECT *
FROM act_order
WHERE order_date >= '2023-12-15'
ORDER BY order_date;

Example 3 - Query data mapping

Example 3 - Query Output

Previous Next

Comments(0 comments)

Comments Not Found