More on CRUD
We will be getting more comfortable with CRUD by doing even more queries.
LETS START !!!
🔹 Equality, Greater Than and Less Than Queries
Q. Display names of all whose age is below 30.
S. Lets break it down, We need to display all the names, so only the name column is required next we need to use a filter to only select people whose age are below 30. To check whether a value is less or more than something we can use < (Less Than for doing less than check) and > (Greater Than for doing greater than check).
SELECT name FROM customers WHERE age < 30;
We select name column only from customers table and filter them based on the age column by checking the age is less than 30 or not.
Q. Display names of all whose age is above 30.
S. Same logic as before but this time we just check using > (Greater Than).
SELECT name FROM customers WHERE age > 30;
Q. Display names of all whose age is exactly equals to 30.
S. To check for equality for some value we can use = (equality).
SELECT name FROM customers WHERE age = 30;
🔹 Logical Clauses in Queries and Ranged Based Queries
Logical clauses help us to determine which value to take between 2 given values. Its exactly like logical operations (&&, ||) in programming.
Logical Operations are often used for range based querying. We can filter rows based on ranges, for example getting all the people between a age x and age y.
Its all better understood by seeing it in action.
Q. Display names of all whose age is between 25 and 35.
S. Here we are required to display the name of everyone from age 25 to 35.
SELECT name FROM customers WHERE age >= 25 AND age <= 35
Here as we can see, we need to get all whose age is between 25 and 35. Its a range ! 25 to 35 !
So we got 2 conditions:
- age greater than or equals to 25.
- age less than or equals to 35.
That enough to conclude we derive 2 filters : age >= 25 and age <= 35.
We got both the filters we just need to combine them and to do that we use the AND Clause ! The AND Clause ensures that any row MUST MATCH BOTH THE FILTERS. If either of them fails to match then that row is not counted.
For example if anyone is below 25, the left filter (age >= 25) fails, if someone is above 35, the right filter (age <= 35) fails.
AND ensures that only those rows are returned which match both the filters !!
just for technicality, AND works as -> TRUE AND TRUE = TRUE, TRUE AND FALSE = FALSE (if one value is FALSE the result is FALSE).
Now lets take a look at the OR Clause.
The OR clause lets returns the row only if one of the conditions(filters and conditions same thing, will use them interchangeably) match.
Q. Display all the records for those whose ages are below 25 or either their name is "Tommy".
S. First step we break down. We are not asked to just return the name but the whole records so we will be selecting all (*) !
Next we find the 2 conditions:
age < 25.name = 'Tommy'.
If either matches we return the row. Very simple we use OR !
SELECT * FROM customers WHERE age < 25 OR name = 'Tommy';
Unlike AND which needs both the conditions to be true, OR just needs one condition to be true. So for any row where any one of the 2 conditions are true, the row is valid. It only fails if neither of them are true.
OR ensures that only those rows are returned which match at least one filter.
and just for technicality, OR works as -> TRUE OR FALSE = TRUE, FALSE OR FALSE = FALSE (if at least one value is TRUE the result is TRUE).
🍊 A little more on ranges.
With above example we already saw how ranges work but lets just see a little more.
Using the BETWEEN Clause.
Rather than using < or > or <= or >= we can use the BETWEEN clause, as its way more cleaner than those. Lets see by an example.
SELECT name FROM customers WHERE age BETWEEN 25 AND 35;
See how clean that is ? Now both can be used based on preference and conditions. I personally prefer using <, >, etc.
The general syntax for BETWEEN Clause is:
SELECT <column> FROM <table_name> WHERE <filter_column> BETWEEN <lower_bound> AND <upper_bound>;
And yes both the lower bound and upper bound are inclusive of the range. Means both the values for the bounds are included in the range.
That was logical Operation and range based queries.
Now these are the basic principles for querying and gets even more complex based on situation. With the foundation of CRUD clear lets move on to learning the structure.
Follow SQL_Structure to continue from here.
Browse Full SQL Page.