SQL Basics Part II
The WHERE clause is an optional parameter that can be used to filter information from the database. Following is the basic SQL query structure with a WHERE clause.
SELECT <column name> FROM <table name>
WHERE <column name operator value>
Odds are, if you’re new to SQL, you’re saying.. “Great Nick… but what the heck is a <column name operator value>?” Fair enough question that is important to understand for using the WHERE clause.
The WHERE clause can be used with the following operators or conditions.
Operator Description
= Equal
<> Not Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
With this information lets run a query against the vcomputer table that we used in Part I.
Name Domain User OS Name OS Version IP Address
Computer1 Altiris Jim Windows XP 5.1 192.168.0.1
Computer2 Altiris Andrew Windows 2000 5.0 192.168.15.4
SELECT * FROM vcomputer
WHERE Name = ‘Computer1’
This would return the following results from our table.
Name Domain User OS Name OS Version IP Address
Computer1 Altiris Jim Windows XP 5.1 192.168.0.1
You will notice that I used single quote marks around the value. You will want to do this for the value you are searching for. I have read that this is only needed for text values and can be left out for numerical values. I haven’t been successful doing that but wanted to mention it.
You use the LIKE operator if you want to get results from a field whose value contains specific data. When using the LIKE operator, you will most likely want to use the percentage (%) symbol within the value. The percentage (%) symbol tells the database to return the defined value plus anything that would be present in the same location as the percentage symbol. Lets look at an example.
SELECT * FROM vcomputer
WHERE Name LIKE ‘Computer%’
This query is telling the database to return all results from the vcomputer table where the Name column contains any value of Computer + any value after it. Ie… Computer1, Computer2, Computer3
This would return:
Name Domain User OS Name OS Version IP Address
Computer1 Altiris Jim Windows XP 5.1 192.168.0.1
Computer2 Altiris Andrew Windows 2000 5.0 192.168.15.4
SELECT * FROM vcomputer
WHERE Name LIKE ‘%2’
This query is telling the database to return all results from the vcomputer table where the Name column contains any value of 2 + any value before it. Ie… Computer2, Computer22, Computer32, Computer2Test
This would return:
Name Domain User OS Name OS Version IP Address
Computer2 Altiris Andrew Windows 2000 5.0 192.168.15.4
One bit of information that will be good to keep in mind. When dealing with column names that have spaces in them you will need to enclose them with brackets [] as shown in the following example.
SELECT * FROM vcomputer
WHERE [OS Name] LIKE ‘Windows%’
This concludes part II of the SQL Basics. I have attached a copy of this in Word document format for those that may want it.
Linear Mode
