Day 4 - Let’s Play with Dates and Strings

Rohanathan Suresh

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'

Sqlection

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'

Select rows equaling

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'

File

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'

Using between and keywords

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'

No results found

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')

State in and dl

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

Tables

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

Select 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'

Select name like E

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%'

Select course name

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%'

Select course name

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'

Select address like

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%')

Select address

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.

Comments

Join the conversation! Your thoughts help the community grow.

Day 4 - Let’s Play with Dates and Strings

Rohanathan Suresh

2y1.8k025Blog

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'

Sqlection

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'

Select rows equaling

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'

File

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'

Using between and keywords

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'

No results found

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')

State in and dl

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

Tables

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

Select 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'

Select name like E

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%'

Select course name

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%'

Select course name

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'

Select address like

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%')

Select address

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.

Comments

Join the conversation! Your thoughts help the community grow.