First, let us recap the keywords and commands seen so far, along with the above conditions.
SELECT |
This statement returns all columns from the table mentioned in the FROM statement |
FROM |
Use this to choose the Table from which we require the columns |
WHERE |
Here, we mention the filtering condition |
Operators/Keywords |
Definition |
Example |
= |
Check if the two values are the same. |
SELECT * FROM students WHERE course_date = '2024-09-16' |
> |
Check if the value on the left is greater than the value on the right. |
SELECT * FROM students WHERE course_date > '2020-12-31' |
< |
Checks if the value on the left is less than the value on the right. |
SELECT * FROM students WHERE course_fees < 30000 |
>= |
Check if the value on the left is greater than or equal to the value on the right. |
SELECT * FROM students WHERE course_fees >= 20000 |
<= |
Check if the value on the left is less than or equal to the value on the right. |
SELECT * FROM students WHERE course_fees <= 10000 |
<> |
Check if two values are not the same |
SELECT * FROM students WHERE course_date <> '2024-09-16' |
between and |
Used to specify a range for numeric or date/time columns. |
SELECT * FROM students WHERE course_date between '2023-01-01' and '2024-12-31' |
in |
Used to specify a list of known values to compare a column against. |
SELECT * FROM students WHERE state in ('TN', 'DL') |
AND |
Used to specify that all conditions connected by AND must be true for a record to be included in the results |
SELECT * FROM students WHERE state = ('DL') AND course_fees >= 25000 |
OR |
Used to specify that at least one condition connected by OR must be true for a record to be included in the results. |
SELECT * FROM students WHERE course_name like ('comp%') OR course_name like ('data%') |
NOT |
Inverts the logical state of a condition. |
SELECT * FROM students WHERE course_name NOT like ('comp%') |
like |
Used for pattern matching in text columns: "%" will Match zero, one, or more characters. "_" will Matches a single character." |
SELECT * FROM students WHERE state like '_L' |
like |
Similar to LIKE but performs case-insensitive comparisons. |
SELECT * FROM students WHERE course_name like 'data%' |
Assume that we want to retrieve information based on the filtering conditions of two columns. That’s what we call a complex query.
SELECT
first_name,
last_name,
address,
phone,
state,
course_name,
course_fees
FROM
students
WHERE
state = 'DL'
AND course_fees >= 25000
SELECT
first_name,
last_name,
address,
phone,
state,
course_name,
course_fees
FROM
students
WHERE
state IN ('DL', 'KA')
AND course_fees >= 25000;
SELECT *
FROM students
WHERE course_date >= '2024-01-01'
AND course_fees >= 20000
AND scholarship_amt >= 1000
SELECT *
FROM students
WHERE course_name ILIKE ('comp%')
AND course_fees >= 25000
AND zip_code LIKE ('3_____');
SELECT
first_name,
last_name,
address,
state,
zip_code,
course_name,
course_fees
FROM
students
WHERE
(course_name ilike 'comp%' OR course_name ilike 'data%')
AND course_fees >= 22000;
Here, we will have results from either of the conditions mentioned using the 'OR' keyword.
SELECT
first_name,
last_name,
address,
state,
zip_code,
course_name,
course_fees,
course_date
FROM
students
WHERE
course_date > ('2022-01-01')
OR course_name ILIKE ('data%')
OR last_name ILIKE ('S%')
SELECT
first_name,
last_name,
address,
state,
zip_code,
course_name,
course_fees,
course_date
FROM
students
WHERE
state NOT IN ('DL', 'MH', 'KA', 'PB')
AND course_name NOT LIKE ('% %');
Now let’s say we do not want to view students whose address has the word ‘Lane’ as well as ‘View’:
SELECT
first_name,
last_name,
address,
state,
zip_code,
course_name,
course_fees,
course_date
FROM
students
WHERE
state NOT IN ('DL', 'MH', 'KA', 'PB')
AND course_name NOT LIKE ('% %')
AND (address NOT ILIKE ('%lane%') AND address NOT ILIKE ('%view%'))
This might seem complex at first, but the brackets help us to define the order of precedence.
Similarly, we want all the students whose state is DL, and course_fee is exactly 26890.34 OR 22456.78.
SELECT
first_name,
last_name,
address,
state,
zip_code,
course_name,
course_fees,
course_date
FROM
students
WHERE
state IN ('DL')
AND (course_fees = 26890.34 OR course_fees = 22456.78);
Note. Precedence and Parentheses.
NOT has a higher precedence than AND and OR. So, NOT (condition1 AND condition2) is evaluated before the AND.
Use parentheses to explicitly define the order of evaluation, especially when combining multiple operators.
That's it for today! We will catch up on the next blog with more math, and string concatenation.