Where:
Where statement helps us to use the comparison operators like equal to, greater than, less than etc and logical operators like AND, OR, NOT.
Example of Comparison Operator:
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | Pen |
| B | 2 | Pencil |
| C | 4 | Pencil |
| D | 5 | Pen |
| E | 6 | Pen |
If we want rows which have values more than 2 in C2
SELECT * FROM Table 1
WHERE C2>2
Result:
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | Pen |
| C | 4 | Pencil |
| D | 5 | Pen |
| E | 6 | Pen |
Example of operational and comparison operator:
Now if we want rows which have values more than 2 in C2 and have Pen in C3
SELECT * FROM Table 1
WHERE C2>2 AND C3 = ‘Pen’
Result:
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | Pen |
| D | 5 | Pen |
| E | 6 | Pen |
Try this yourself on SQL dvdrental database:
Challenge 1:
A customer forgot their wallet at our store! We need to track down their email to inform them. What is the email for the customer with the name Nancy Thomas?
Challenge 2:
A customer wants to know what the movie “Outlaw Hanky” is about. Could you give them the description for the movie “Outlaw Hanky”?
Order by:
Order by Function helps us in getting results in our desired output of ascending and descending.
Order by is generally at the end of a query. It is one of the last things SQL does in a query.
ASC – Ascending
DESC – Desending
Blank – Ascending by default
Example:
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | Pen |
| B | 2 | Pencil |
| C | 4 | Pencil |
| D | 5 | Pen |
| E | 6 | Pen |
If we want rows which have values more than 2 in C2
SELECT * FROM Table 1
ORDER BY C2 ASC
Result:
| Table 1 | ||
| C1 | C2 | C3 |
| B | 2 | Pencil |
| A | 3 | Pen |
| C | 4 | Pencil |
| D | 5 | Pen |
| E | 6 | Pen |
Now, if we want to arrange first table by all Pencil and than by C2 ascending order:
SELECT * FROM Table 1
ORDER BY C3 DESC, C2 ASC
Result:
| Table 1 | ||
| C1 | C2 | C3 |
| B | 2 | Pencil |
| C | 4 | Pencil |
| A | 3 | Pen |
| D | 5 | Pen |
| E | 6 | Pen |
Credits: The concepts and materials are taken from the Udemy course:The complete SQL Bootcamp 2022: Go from Zero to Hero
Check out the other parts in the SQL series:
Next:
Why do we need Structured Query Language (SQL)? SQL PART 5
Previous:
Why do we need Structured Query Language (SQL)? SQL PART 3
Why do we need Structured Query Language (SQL)? SQL PART 2
Why do we need Structured Query Language (SQL)? SQL PART 1
Vitazen Keto This is my first time pay a quick visit at here and i am really happy to read everthing at one place
Thanks I have recently been looking for info about this subject for a while and yours is the greatest I have discovered so far However what in regards to the bottom line Are you certain in regards to the supply
Oh my goodness! a tremendous article dude. Thank you However I am experiencing issue with ur rss . Don抰 know why Unable to subscribe to it. Is there anyone getting equivalent rss downside? Anybody who knows kindly respond. Thnkx