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.