Fun Fact: The total number of steps in the Eiffel Tower are 1665.
Now, let's start with the first WHERE clause. If you have a query where you need to return results from your Vehicles table where your vehicle makes start with the letter V. You can achieve this by using a LIKE WHERE clause. So what is a LIKE OPERATOR? The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator:
- %
- _
Below is an example where we return all vehicle makes starting with the letter V.
SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval FROM dbo.Vehicles WHERE Make LIKE 'V%'
When we execute the above query, we will see example data as follows:
| Make | Series | LicenseNumber | LicenseExpiryDate | Odometer | ServiceInterval |
| Volkswagen | Pasat | EE55EEGP | 2017/12/31 | 40000 | 10000 |
| Volvo | XC90 | II99IIGP | 2018/04/30 | 80000 | 15000 |
As you can see, the query only returned the records where the vehicle makes starting with V. The reason for the results is because we only used the wildcard (%) after the character. If you want results where you need all vehicles that contains the character V, doesn't matter where in the vehicle make name, you can use the wildcard on both sides of the character. Example below:
SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval FROM dbo.Vehicles WHERE Make LIKE '%V%'
If we run the above query, we will get a different results set.
| Make | Series | LicenseNumber | LicenseExpiryDate | Odometer | ServiceInterval |
| Volkswagen | Pasat | EE55EEGP | 2017/12/31 | 40000 | 10000 |
| Volvo | XC90 | II99IIGP | 2018/04/30 | 80000 | 15000 |
| Chevrolet | Volt | JJ10JJGP | 2018/05/31 | 90000 | 150000 |
As you can see, we have 1 more record in our record set. That is because Chevrolet has the character V in the middle of the string. If you want results where the vehicle make ends with a specific character, you can place the wildcard before your search character.
Our next discussion on the WHERE clause is handling Date and Time fields. Lets start with a basic example. Lets say you want to return all the vehicles from your SQL database where the vehicle license expires at the end of the month. Below is an example SELECT:
SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval FROM dbo.Vehicles WHERE LicenseExpiryDate = '2017-09-30'
If we run the above query, we will get the following results set.
| Make | Series | LicenseNumber | LicenseExpiryDate | Odometer | ServiceInterval |
| Nissan | Leaf | BB22BBGP | 2017/09/30 | 10000 | 10000 |
As you can see, we have 1 vehicle where the license will expire at the end of the month. The above query will return only the records where the license expire on a specific day. Lets say you want to return all vehicles that will expire in the September month for year 2017, below is an example query:
SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval FROM dbo.Vehicles WHERE YEAR(LicenseExpiryDate) = 2017 AND MONTH(LicenseExpiryDate) = 9
The above query is not using best practices. The reason is because we apply manipulation to the filtered column, the predicate is not considered a search argument and SQL won't be able to rely on index order. Best practice for the above query is as follows and it will return the same record set:
SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval FROM dbo.Vehicles WHERE LicenseExpiryDate >= '20170901' AND LicenseExpiryDate < '20171001'
By using the above method, the predicate is considered a search argument and there's the potential for SQL to rely on index ordering. You can use the BETWEEN for the above query but because my column datatype is DATE, it is best to use GREATER THAN OR EQUALS TO and LESS THAN because using BETWEEN, the column holds both date and time elements. That means, BETWEEN is used when your column datatype is DATETIME. Below is an example:
SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval FROM dbo.Vehicles WHERE LicenseExpiryDate BETWEEN '20170901' AND '20170930 23:59:59'
If you don't add the TIME part on the WHERE clause, your query will exclude vehicles that expires on 30 Sep 2017.
Another Fun Fact: Recycling one glass jar saves enough energy to watch TV for 3 hours.
: