Day 4 - Let’s Play with Dates and Strings

Learn how to filter data in SQL queries based on dates and strings. Use operators like <, <=, >, >= for date ranges. Select single dates using "=", exclude specific dates with "<>". Filter strings with "=", "in", "like", "ilike" for exact or partial matches. Employ wildcard '%' for pattern matching.

Now, let’s play with some dates and strings.

For filtering the date column, we require it to be within single quotes, e.g., ‘ 2020-12-31’. Here, we can use operators like <, <=, >, >=, etc.

SELECT first_name, last_name,phone,course_date
FROM students
WHERE course_date > '2020-12-31'

We will select rows equaling a singFle date using the "=" sign.

SELECT first_name, last_name,phone,course_date
FROM students
WHERE course_date = '2024-09-16'

Now we want to exclude a specific date, we cannot use the “!” sign instead of “<>”:

SELECT first_name, last_name,phone,course_date
FROM students
WHERE course_date <> '2024-09-16'

Selecting a range is completed by using “between”, “and” keywords.

SELECT first_name, last_name,phone,course_date
FROM students
WHERE course_date between '2023-01-01' and '2024-12-31'

For filtering strings, we use four main keywords.

• “=”: When you know exactly what you are looking for (only one condition)
• “in”: When you know exactly what you but you can search with multiple filtering conditions
• “like”: When you part of the string
• “ilike”: When you want to ignore case sensitivity

Searching for a known last_name

SELECT first_name, last_name,phone,course_date
FROM students
WHERE last_name = 'Suresh'

Keep in mind that it is case-sensitive.

SELECT first_name, last_name,phone,course_date
FROM students
WHERE last_name = 'suresh'

Living in a state “TN” and “DL” next states with second letter “L” and first letter "T”

SELECT first_name,last_name, address,phone, state,course_name
FROM students
WHERE state in ('TN', 'DL')

SELECT first_name,last_name, address,phone, state,course_name
FROM students
WHERE state like '_L'

SELECT first_name,last_name,address,phone,state,course_name
FROM students
WHERE state like 'T_'

Finding first_name with 4 letters which start with “E” and end with “A”

SELECT first_name,last_name,address,phone,state,course_name
FROM students
WHERE first_name like 'E__a'

With course_name starting with "Data” and a few more filtering.

SELECT first_name,last_name,address,phone,state,course_name
FROM students
WHERE course_name like 'Data%'

We further narrow the course containing MSc using the “%” symbol

SELECT first_name,last_name,address,phone,state,course_name
FROM students
WHERE course_name like 'Data%MSc%'

The address has the word “Oak” and Ignores capital.

SELECT first_name,last_name,address,phone,state,course_name
FROM students
WHERE address like '90%O__%Street'

We can ignore case sensitivity using the “like” keyword.

SELECT first_name,last_name,address,phone,state,course_name
FROM students
WHERE address ilike ('%oak%street%')

That’s it for today, in the next blog we will see how to perform complex searches and a quick guide for the commands seen so far! Happy learning.