SQL components to specify conditions
SQL Statement | Syntax | Example Statement | Example Result or Description |
AND / OR |
SELECT column_name(s) FROM table_name WHERE condition AND|OR condition |
select WhenOccurred, FailUserName, FromIPAddress from event where EventType = 'Cloud.Core.LoginFail' and whenoccurred >= DateFunc('now','-30') |
Use AND to combine conditions - results display if the database record meets both conditions. Use OR to show results that meet either the first or second condition. |
BETWEEN (advanced)
|
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 |
select OSVersion,Count(*) as Count from device where InternalDeviceType = 'I' and OSVersion between '6' and '7' group by osversion order by count desc |
Use BETWEEN to select results that are within a specified range. |
IN / NOT IN |
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) |
select UserName, DisplayName, LastLogin from User where username not in (select NormalizedUser from Event where EventType = 'Cloud.Core.Login' and WhenOccurred >= DateFunc('now', '-30')) |
Use IN to select results where a column name is one of a specified list of values (or not). |
LIKE |
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
Select * from Users where username like ‘j%’ returns all users whose names begin with J |
Use LIKE to select results that match a specified pattern. Use s to indicate the pattern. Use % for zero or more characters, and use _ (underscore) for a single character. |
CASE (WHEN... THEN, END) |
CASE X WHEN W1 THEN T1 WHEN W2 THEN T2 ELSE T3 END To evaluate the base expression multiple times: CASE WHEN X=W1 THEN T1 WHEN X=W2 THEN T2 ELSE T3 END |
SELECT CASE(InternalDeviceType) WHEN 'I' THEN 'iOS' WHEN 'M' THEN 'Mac' WHEN 'A' THEN 'Android' WHEN 'W' THEN 'Windows' END as Platform, Count(*) as Count from device GROUP BY InternalDeviceType ORDER BY Count desc |
Use CASE when you want to do an if/then/else statement. You can specify to have the base expression evaluated once or multiple times.
|
WHERE |
SELECT column_name(s) FROM table_name WHERE column_name operator value |
select ApplicationName as Name, count(*) as Count from Event where WhenOccurred >= DateFunc('now', '-30') and EventType='Cloud.Saas.Application.AppLaunch' group by name order by count desc |
Use WHERE to specify the condition, such as a column name value. |