How to Implement Case Statement Using Vultr Managed Database for PostgreSQL

[mahesh]

Question:
How to Implement Case Statement Using Vultr Managed Database for PostgreSQL

Introduction
PostgreSQL is a high performance database server that allows you to store common data types and use different SQL tools to sort available data. These tools include the SQL CASE statement that evaluates a list of expressions and returns a value based on the possible results. For instance, in a school database, you can use the PostgreSQL CASE statement to grade student marks based on their scores.

Code Select Expand

For example:

scores    | grade         |
----------+---------------+
  75 -100 | Distinction   |
  70 - 74 | Credit        |
  40 - 70 | Pass          |
  0  - 39 | Fail          |
Compared to the PostgreSQL IF statement, the CASE statement is more readable. You can use the CASE statement to construct cleaner and highly maintainable database code. This means other database users can understand the SQL code better and construct effective queries to get meaningful insights from the database.

This guide explains how you can implement the PostgreSQL CASE statement on a Vultr Managed Database for PostgreSQL to grade student marks.

Prerequisites
Before you begin:

• Deploy a Vultr Managed Database for PostgreSQL
• Deploy a Vultr Linux server to use the management system
• Access the server terminal using SSH
• Create a non-root sudo user and switch to the account
• Depending on your Linux distribution, install the PostgreSQL psql client tool. For example, on Ubuntu, run the following command:

CONSOLE

Code Select Expand

$ sudo apt install -y postgresql-client
The PostgreSQL CASE Statement
The PostgreSQL CASE statement works together with a SELECT statement to query table data. However, the CASE statement must contain the following components:

• CASE: Start a PostgreSQL CASE statement.
• WHEN: Follows an expression that PostgreSQL evaluates to return a value.
• THEN: Defines a value that PostgreSQL returns when the given expression meets a condition.
• END: Terminates the CASE expression.

The PostgreSQL CASE statement uses the following query syntax:

Code Select Expand

[b]SQL[/b]
Copy
SELECT
    sample_column_1,
    sample_column_2,
    sample_column_n,
    CASE
        WHEN sample_condition_1 THEN 'sample_value_1'
        WHEN sample_condition_2 THEN 'sample_value_2'
        WHEN sample_condition_n THEN 'sample_value_n'
    END AS sample_case_column
FROM SAMPLE_TABLEIn addition, the standard SQL operators help in the formation of most SQL CASE expressions, these include:

• =: Equal operator.
• <: Less than operator.
• <=: Less than or equal operator.
• >: Greater than operator.
• >=: Greater than or equal operator.

Create a Sample PostgreSQL Database
1.Log in to your Vultr Managed Database for PostgreSQL. Replace host.vultrdb.com, 16751 and vultradmin with your actual database details

Code Select Expand

CONSOLE
Copy
$ psql -h host.vultrdb.com -p 16751 -U vultradmin defaultdb
When prompted, enter your correct Vultr Managed Database for PostgreSQL password to access the cluster.

2.Create a sample school database

Code Select Expand

SQL
Copy
defaultdb=> CREATE DATABASE school;Output:

Code Select Expand

CREATE DATABASE
3.Switch to the new school database.

Code Select Expand

SQL
Copy
defaultdb=> \c school;
Output:

You are now connected to database "school" as user "vultradmin".
4.Create a new students table with student_id,first_name and last_name columns.

Code Select Expand

SQL
Copy
school=> CREATE TABLE students (
                student_id SERIAL PRIMARY KEY,
                first_name VARCHAR(50),
                last_name VARCHAR(50)
            );The above table query creates a new student_id column with a PRIMARY KEY that uniquely identifies each student in the table while the first_name and last_name columns store the students' names.

5.Insert sample data to the students table.

Code Select Expand

SQL
Copy
school=> INSERT INTO students (first_name, last_name) VALUES ('JOHN', 'DOE');
            INSERT INTO students (first_name, last_name) VALUES ('JANE', 'SMITH');
            INSERT INTO students (first_name, last_name) VALUES ('PETER', 'HENRY');
            INSERT INTO students (first_name, last_name) VALUES ('MARY', 'ANN');
            INSERT INTO students (first_name, last_name) VALUES ('JESSICA', 'WILLIAMS');
            INSERT INTO students (first_name, last_name) VALUES ('BOB', 'JAMES');
            INSERT INTO students (first_name, last_name) VALUES ('ESTHER', 'CHLOE');
6.View the students table data to verify the new values.

Code Select Expand

SQL
Copy
school=> SELECT
                student_id,
                first_name,
                last_name
            FROM students;
Output:

Code Select Expand

    student_id | first_name | last_name
------------+------------+-----------
            1 | JOHN       | DOE
            2 | JANE       | SMITH
            3 | PETER      | HENRY
            4 | MARY       | ANN
            5 | JESSICA    | WILLIAMS
            6 | BOB        | JAMES
            7 | ESTHER     | CHLOE
(7 rows)
7.Create a new marks table with score_id, student_id, and score columns.

Code Select Expand

SQL
Copy
school=> CREATE TABLE marks (
                score_id SERIAL PRIMARY KEY,   
                student_id INT,                       
                score INT
            );The above table query creates a score_id column with a PRIMARY KEY value. The student_id points to the students table you created earlier. The score column stores the actual points a student gets in the exam. In real-life examples, the score can relate to goals in a gaming application or steps in a fitness application.

8.Insert sample data to the marks table.

Code Select Expand

SQL
Copy
school=> INSERT INTO marks (student_id, score) VALUES (1, 85);
            INSERT INTO marks (student_id, score) VALUES (2, 39);
            INSERT INTO marks (student_id, score) VALUES (3, 70);
            INSERT INTO marks (student_id, score) VALUES (4, 55);
            INSERT INTO marks (student_id, score) VALUES (5, 0);
            INSERT INTO marks (student_id, score) VALUES (6, 41);
            INSERT INTO marks (student_id, score) VALUES (7, 32);
In the above queries, the student_id values are referenced from the students table while the score column accepts any values between 0 and 100.

9.View the marks table data to verify the values

Code Select Expand

SQL
Copy
school=> SELECT
                score_id,
                student_id,
                score
            FROM marks;
Output:

   

Code Select Expand

score_id | student_id | score
----------+------------+-------
        1 |          1 |    85
        2 |          2 |    39
        3 |          3 |    70
        4 |          4 |    55
        5 |          5 |     0
        6 |          6 |    41
        7 |          7 |    32
(7 rows)Implement the PostgreSQL CASE Statement in a Query
Grade scores from the students table and join the marks table using the CASE statement to generate the necessary query results.

Code Select Expand

SQL
Copy
school=> SELECT
                students.student_id,
                students.first_name,
                students.last_name,
                marks.score,
                (
                    CASE
                        WHEN marks.score >= 75 THEN 'Distinction'
                        WHEN marks.score >= 70 THEN 'Credit'
                        WHEN marks.score >= 40 THEN 'Pass'
                        WHEN marks.score <= 39 THEN 'Fail'
                    END
                ) as grade
            FROM marks
            LEFT JOIN students
            ON marks.student_id = students.student_id;
In the above query, you joined the marks and students tables to generate the following values:

Code Select Expand

student_id | first_name | last_name | score |    grade
------------+------------+-----------+-------+-------------
        1 | JOHN       | DOE       |    85 | Distinction
        2 | JANE       | SMITH     |    39 | Fail
        3 | PETER      | HENRY     |    70 | Credit
        4 | MARY       | ANN       |    55 | Pass
        5 | JESSICA    | WILLIAMS  |     0 | Fail
        6 | BOB        | JAMES     |    41 | Pass
        7 | ESTHER     | CHLOE     |    32 | Fail
(7 rows)
As displayed in the query output, the PostgreSQL CASE statement evaluates data from the students and marks tables to display the necessary data.

Conclusion
You have set up a sample PostgreSQL database and implemented the CASE SQL statement to generate user data from multiple tables. Apart from the CASE statement, PostgreSQL supports other data evaluation procedures such as the IF statement and lookup tables. For more infromation, visit the PostgreSQL conditional statements documentation.