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

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "'''Title of the script: Conditional Operators''' '''Authors: '''Ketki Bhamble EduPyramids '''Keywords: '''PostgreSQL, pgAdmin 4, SQL, WHERE Clause, LIKE, percent sign, un...")
 
 
Line 1: Line 1:
 
'''Title of the script: Conditional Operators'''  
 
'''Title of the script: Conditional Operators'''  
−
 
  
 
'''Authors: '''Ketki Bhamble EduPyramids
 
'''Authors: '''Ketki Bhamble EduPyramids
−
 
  
 
'''Keywords: '''PostgreSQL, pgAdmin 4, SQL, WHERE Clause, LIKE, percent sign, underscore, BETWEEN, NOT BETWEEN, IN, NOT IN, DISTINCT, EduPyramids, video tutorial.
 
'''Keywords: '''PostgreSQL, pgAdmin 4, SQL, WHERE Clause, LIKE, percent sign, underscore, BETWEEN, NOT BETWEEN, IN, NOT IN, DISTINCT, EduPyramids, video tutorial.
−
 
  
  

Latest revision as of 12:14, 24 April 2026

Title of the script: Conditional Operators

Authors: Ketki Bhamble EduPyramids

Keywords: PostgreSQL, pgAdmin 4, SQL, WHERE Clause, LIKE, percent sign, underscore, BETWEEN, NOT BETWEEN, IN, NOT IN, DISTINCT, EduPyramids, video tutorial.


Visual Cue Narration
Slide 1


Title Slide

Welcome to this Spoken Tutorial on Conditional Operators in PostgreSQL.
Slide 2


Learning Objectives

In this tutorial, we will learn about,
  • LIKE, BETWEEN, NOT BETWEEN, IN, NOT IN and DISTINCT operators


Slide 3


System Requirements


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


To record this tutorial, I am using the following setup.
Slide 4


Pre-requisites


https://EduPyramids.org


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


Slide 5


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.

The following code file is required to practice this tutorial.


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


Please download and extract the file.

Let us get started.
Show students-table We are using the students table.


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

Slide 6


LIKE operator

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

It is used along with the WHERE clause.


Slide 7


Syntax of LIKE operator


SELECT column1, column2, ...

FROM table_name

WHERE columnN LIKE pattern;


Highlight pattern keyword.

This is the syntax of the LIKE operator.

The pattern may contain percent sign or underscore.


Percent sign represents zero or more characters.

Underscore represents exactly one character.

Type the command:


SELECT * FROM students

WHERE studentname LIKE 'R%';


Highlight:*


Highlight: WHERE


Highlight:‘R%’


Click on the execute query button.


Let us retrieve the records of students whose names start with the letter R.


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.

Type the query:


SELECT studentname, city

FROM students

WHERE city LIKE '%a';

Now let’s find students whose city name ends with letter a using the WHERE clause.


Type the following query.

Highlight LIKE '%a' This query retrieves studentname and city.

The pattern '%a' means any number of characters followed by the letter a.

Click the Execute query icon. Let us execute the query.


The output shows student from the city ending with a.

Slide 8


BETWEEN Operator

The BETWEEN operator is used to filter values within a specified range.


It works with numbers, dates, and text values. The range values are inclusive.

Slide 9


Syntax of BETWEEN Operator


SELECT column name(s)

FROM table name

WHERE column name BETWEEN value1 AND value2;

This is the syntax of BETWEEN operator.
Type the command:

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

Let us now find students whose CGPA is between 8 and 9.


Type the following query.


Highlight BETWEEN 8 AND 9 This query retrieves the studentname and c g p a.


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.

Click the Execute query icon. Let us execute the query.


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

Type the command:

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'

Now, let us find students born in the year 1996.


Type the following query.



This query retrieves the studentname and date of birth.


The BETWEEN clause filters students born between the specified dates.

Click the Execute query icon. Let us execute the query.

The output displays students born in the year 1996.

Slide 10


NOT BETWEEN Operator

The NOT BETWEEN operator is used to filter values that fall outside a specified range.
Slide 11


Syntax of NOT BETWEEN Operator


SELECT column name(s)

FROM table name

WHERE column name NOT BETWEEN

value1 AND value2;

This is the syntax of the NOT BETWEEN operator.
Type:


SELECT studentname, cgpa

FROM students

WHERE cgpa NOT BETWEEN 8 AND 9;

Let us retrieve students whose CGPA is not between 8 and 9.Type this query.
Highlight NOT BETWEEN 8 AND 9 The NOT BETWEEN clause returns rows where cgpa is less than 8 or greater than 9.
Click on the execute query icon Let us execute the query.


The output displays students whose CGPA falls outside this range.

Slide 13

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.

Slide 14

Syntax of IN Operator


SELECT column name(s)

FROM table name

WHERE column name IN (value1,

value2, ...);

This is the syntax of the IN operator.
Type:


SELECT * FROM students

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


Let us retrieve students who belong to Mumbai, Pune, or Delhi.


Type the following query.

Highlight city IN ('Mumbai','Pune','Delhi') In this query, the IN operator matches the city with the specified values.
Click on the execute query icon Click on the Execute query icon to execute the query.


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

Slide 15


NOT IN Operator

The NOT IN operator excludes specified values from the result set.
Slide 16


Syntax of NOT IN Operator


SELECT column name(s)

FROM table name

WHERE column name NOT IN (value1,

value2, ...);

This is the syntax of the NOT IN operator.
Type:

SELECT * FROM students

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

Now let us retrieve students who do not belong to Mumbai or Pune.


Type the following query.

Highlight NOT IN ('Mumbai','Pune') The NOT IN operator filters out the specified cities and returns the remaining records.
Click on the execute query icon Let us execute the query.


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

Slide 17


DISTINCT Operator


DISTINCT keyword is used in the SELECT statement to list distinct and unique values.

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

Slide 18


Syntax of DISTINCT Operator

SELECT DISTINCT column-names FROM table-name WHERE [condition]

The syntax is as follows:


Type the command:

SELECT DISTINCT city FROM students;


Type the query as shown here.

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.

With this, we come to the end of this tutorial.
Slide 19


Summary

In this tutorial, we have learnt about

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


Let us summarise.
Slide 20 +21+22


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.


As an Assignment, please do the following.
Slide 23

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

Thank you

Thank You.

Contributors and Content Editors

Madhurig

Retrieved from "https://script.spoken-tutorial.org/index.php?title=PostgreSQL-Database/C2/Conditional-Operators/English&oldid=57680"