Difference between revisions of "PostgreSQL-Database/C2/Conditional-Operators/English"

Title Slide

Learning Objectives

• LIKE, BETWEEN, NOT BETWEEN, IN, NOT IN and DISTINCT operators

System Requirements

• Ubuntu 24.04 LTS
• PostgreSQL version 18.1, and
• PgAdmin 4 version 9.11.

Pre-requisites

To follow this tutorial,

• Learners should have PostgreSQL and pgAdmin 4
• For the prerequisite PostgreSQL tutorials, please visit

https://EduPyramids.org

• Learners should have PostgreSQL and pgAdmin 4
• For the prerequisite PostgreSQL tutorials, please visit this website.

Code Files

The following code file is required to practice this tutorial:

• co-commands.txt

This file is provided in the Code Files link on this tutorial page.

Please download and extract the file.

This file is provided in the Code Files link of this tutorial page.

Please download and extract the file.

Type:

SELECT * FROM students;

It contains studentid, studentname, city, gender, date of birth, and cgpa.

LIKE operator

The LIKE operator is used to search for a specified pattern in a column.

It is used along with the WHERE clause.

It is used along with the WHERE clause.

Syntax of LIKE operator

SELECT column1, column2, ...

FROM table_name

WHERE column LIKE pattern;

Highlight pattern keyword.

The pattern may contain percent sign or underscore.

Percent sign represents zero or more characters.

Underscore represents exactly one character.

SELECT * FROM students

WHERE studentname LIKE 'R%';

Highlight:*

Highlight: WHERE

Highlight:‘R%’

Click on the execute query button.

Type the following query.

The WHERE clause filters rows and displays the student names starting with R.

The percent sign allows any number of characters after R.

Let us execute the query.

The output displays students whose names start with R.

SELECT studentname, city

FROM students

WHERE city LIKE '%a';

Type the following query.

The pattern within single quotes percentage a means any number of characters followed by the letter a.

The output shows student from the city ending with a.

BETWEEN Operator

It works with numbers, dates, and text values.

The range values are inclusive.

Syntax of BETWEEN Operator

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

SELECT studentname, cgpa FROM students WHERE cgpa BETWEEN 8 AND 9;

Type the following query.

The BETWEEN clause filters rows where CGPA is between 8 and 9.

It includes values greater than or equal to 8 and less than or equal to 9.

The output shows students whose CGPA is within the range of 8 and 9.

SELECT studentname, dob FROM students WHERE dob BETWEEN '1996-01-01' AND '1996-12-31';

Highlight dob BETWEEN '1996-01-01' AND '1996-12-31'

Type the following query.

This query retrieves the studentname and date of birth.

The BETWEEN clause filters students born between the specified dates.

The output displays students born in the year 1996.

NOT BETWEEN Operator

Syntax of NOT BETWEEN Operator

SELECT column_name(s)

FROM table_name

WHERE column_name NOT BETWEEN

value1 AND value2;

SELECT studentname, cgpa

FROM students

WHERE cgpa NOT BETWEEN 8 AND 9;

Type this query.

The output displays students whose CGPA falls outside this range.

IN Operator

The IN operator is used to specify multiple values in a WHERE clause.

It checks whether a column matches any value in a given list.

It checks whether a column matches any value in a given list.

Syntax of IN Operator

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,

value2, ...);

SELECT * FROM students

WHERE city IN ('Mumbai', 'Pune', 'Delhi');

Type the following query.

The output displays students from Mumbai, Pune, and Delhi.

NOT IN Operator

Syntax of NOT IN Operator

SELECT column_name(s)

FROM table_name

WHERE column_name NOT IN (value1,

value2, ...);

SELECT * FROM students

WHERE city NOT IN ('Mumbai','Pune');

Type the following query.

The output shows students from other cities except Mumbai and Pune.

DISTINCT Operator

It removes the duplicate rows or column values from the query result.

Syntax of DISTINCT Operator

SELECT DISTINCT column_names FROM table_name WHERE [condition];

SELECT DISTINCT city FROM students;

This select statement will retrieve the unique city names from the students table.

Let us execute the query.

Output will eliminate duplicate values from the specified column city.

It only shows distinct city names.

Summary

In this tutorial, we have learnt about

• LIKE, BETWEEN, NOT BETWEEN, IN, NOT IN and DISTINCT operators

Assignment

As an Assignment,

• Retrieve student records whose names start with letter A using the LIKE operator.
• Display the studentname and city where city names contain the letter “n”.
• List students who belong to Chennai, Kolkata, or Bengaluru using the IN operator.
• Find students whose CGPA is between 7 and 8.5.
• Display students records who are born after 1997 using the NOT BETWEEN operator.
• Retrieve the distinct gender values from the students table.
• Display students whose city is neither Chennai nor Jaipur.

This Spoken Tutorial is brought to you by EduPyramids Educational Private Limited SINE IIT Bombay.

Thank you