Limit:
LIMIT Functions helps us receiving the limited results. For example: if there are 1000 rows but if you want only top 50 rows than we can use the LIMIT function.
If in the above query if we want only 2 results than:
SELECT * FROM Table 1
ORDER BY C3 DESC, C2 ASC
LIMIT 2;
Result:
| Table 1 | ||
| C1 | C2 | C3 |
| B | 2 | Pencil |
| C | 4 | Pencil |
*Limit can also be used to get a general understanding of a table by using the commant:
SELECT * FROM Table
LIMIT 1;
Try this yourself on SQL dvdrental database:
Challenge 1:
We want to reward our first 10 paying customers. What are the customer ids of the first 10 customers who created a payment.
Challenge 2:
A customer wants to quickly rent a video to watch over their short lunch break. What are the titles of the shortest (in length of runtime) movies?
BETWEEN:
BETWEEN operator can be used to match a value according to a range of values. For example “BETWEEN 2 AND 100”.
The above statement can also be written by using where statement:
WHERE N>2 AND N<100
BETWEEN can also be used for time period. For example: “BETWEEN 2022-01-01 AND 2022-05-30”
IN:
IN operator helps us in creating a condition to check some specific value. For example: “ ‘Saurabh’ IN Table”
Example:
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | Pen |
| B | 2 | Pencil |
| C | 2 | Pencil |
| D | 5 | Pen |
| E | 6 | Pen |
If we want rows which have values only having value of 2 and 3
SELECT * FROM Table 1
WHERE C2 In (2,3)
Result:
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | Pen |
| B | 2 | Pencil |
| C | 2 | Pencil |
Now if we want all the values other than 2 and 3 than:
SELECT * FROM Table 1
WHERE C2 NOT IN (2,3)
LIKE and ILIKE
If we want to get a pattern in the string of a column then Like and Ilike functions are used.
For example: if you want to get an email ending with @gmail.com or all names beginning with A etc.
LIKE – It is case sensitive
ILIKE – It is not case sensitive
Like and Ilike is used with Wildcards i.e.,
- “%” – matches any sequence of characters. For example: if we want all names starting with A then it will be “A%”
- “_” Matches any single character. For example: if we want all fast and furious movies than we will use “Fast and Furious _” . Multiple underscores can be used in one single query.
Example:
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | A1@gmail.com |
| B | 2 | B1@Gmail.com |
| C | 2 | C2@outlook.com |
| D | 5 | D2@gmail.com |
| E | 6 | E3@outlook.com |
If we want rows which have @ gmail.com
SELECT * FROM Table 1
WHERE C3 LIKE ‘%@gmail.com’
Result:
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | A1@gmail.com |
| D | 5 | D2@gmail.com |
We did not get the row B as LIKE function is case sensitive.
Now, if we use ILIKE:
SELECT * FROM Table 1
WHERE C3 ILIKE ‘%@gmail.com’
| Table 1 | ||
| C1 | C2 | C3 |
| A | 3 | A1@gmail.com |
| B | 2 | B1@Gmail.com |
| D | 5 | D2@gmail.com |
Now if we want in C3 only which have the numberic value of 2 than:
SELECT * FROM Table 1
WHERE C3 LIKE’ ‘_2%’
| Table 1 | ||
| C1 | C2 | C3 |
| C | 2 | C2@outlook.com |
| D | 5 | D2@gmail.com |
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:
Previous:
Why do we need Structured Query Language (SQL)? SQL PART 4
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
What’s up colleagues, how iss all, andd what yyou
woujld likee to saay concerning this paragraph,
iin my viw itts actually awesomee iin fzvor oof me.
I beloved as much as you’ll receive performed right here. The sketch is tasteful, your authored material stylish. nevertheless, you command get bought an shakiness over that you would like be turning in the following. unwell no doubt come more beforehand again as exactly the similar nearly very steadily inside of case you defend this increase.
you are in reality a good webmaster The website loading velocity is amazing It sort of feels that youre doing any distinctive trick Also The contents are masterwork you have done a fantastic job in this topic
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
Your writing is like a breath of fresh air in the often stale world of online content. Your unique perspective and engaging style set you apart from the crowd. Thank you for sharing your talents with us.