Day 3 - Let’s Browse and Filter the Database Columns!

Day 3 of this series involves browsing and filtering database columns, enabling you to explore and refine data effectively. Discover how to navigate through the database, filter columns based on specific criteria, and optimize your search for desired information efficiently.

Hello everyone! Welcome to the learning series of PostgreSQL.

Before reading this article, please read the previous article of this series.

• https://www.c-sharpcorner.com/blogs/setup-postgresql-with-sqlectron
• https://www.c-sharpcorner.com/blogs/day-2-lets-import-and-browse-a-sample-database

Today, we will understand the datatypes loaded in our database and perform some filtering and browsing actions on the previously loaded database. Let’s go!

The datatypes present inside the sample db we've loaded into our Postgres are.

String or Character (VARCHAR)

VARCHAR can store strings of different lengths up to a maximum limit of 100 characters.

first_name
last_name
address
state
zip_code
phone
email
course_name

Date

This is represented by DATE datatype, which will store a year, month, and day.

course_date

Number

• INT represents whole numbers, positive, negative, or zero. Examples: 10, -5, 0. (4bytes of data will be consumed).
• DOUBLE PRECISION represents numbers with decimal points that have a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. (8 bytes of data will be consumed).

school_id       INT,
course_fees     DOUBLE PRECISION,
scholarship_amt DOUBLE PRECISION,

Now, let’s consider 3 scenarios, we want to retrieve the First and last Names, and Phones of students whose.

1. The fee is more than 30000
2. The fee is between 25000 – 30000
3. The fee is exactly 22298.76 or 22345.67

For all the above requirements, we will use the first 2 as common statements.

• SELECT first_name, last_name, phone, course_fees: This statement will SELECT the required columns inside the table mentioned below (we could also use * to add all columns or add more columns by specifying the name eg: course_fees).
• FROM students: This statement tells us what table to look for using the FROM keyword.
• WHERE course_fees > 30000: We use the> symbol to display the course_fee column greater than 30000.
• WHERE course_fees between 25000 and 30000: We use the “between”, “and” keywords to display values within a certain range.
• WHERE course_fees in (22298.76, 22345.67): We use the “in” keyword to display the exact value of the rows in the column.

SELECT 
    first_name, 
    last_name, 
    phone, 
    course_fees
FROM 
    students
WHERE 
    course_fees > 30000

The above statement gives us 9 rows as a result.

SELECT 
    first_name, 
    last_name,
    phone,
    course_fees
FROM 
    students
WHERE 
    course_fees BETWEEN 25000 AND 30000;

The above statement gives us 100 rows as a result.

SELECT 
    first_name, 
    last_name,
    phone,
    course_fees
FROM 
    students
WHERE 
    course_fees IN (22298.76, 22345.67)

The above statement gives us 2 rows as a result.

That's it for today. We will catch up in the next series of this blog with more queries! Happy learning!