PostgreSQL-Database/C2/Conditional-Operators/English

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