Instantly share code, notes, and snippets.

Embed
What would you like to do?
Filter SQL queries, examples. Is, is not, equal, not equal, in, not in, greater than, less than, and, or.
-- Are there any null values in the EndDate field
-- of the humanresources.employeedepartmenthistory table?
-- If so how many records are there?
select count(*)
from humanresources.employeedepartmenthistory
where EndDate is null;
-- How many records are there where EndDate is not null?
select count(*)
from humanresources.employeedepartmenthistory
where EndDate is not null;
-- How many records are there where departmentid is equal to 7, or 11, or 12?
-- Note both of the following queries achieve the same result
select count(*)
from humanresources.employeedepartmenthistory
where departmentid = 7
or departmentid = 11
or departmentid = 12;
select count(*)
from humanresources.employeedepartmenthistory
where departmentid in (7, 11, 12);
-- How many records are there where departmentid is not equal to 7, or 11, or 12?
-- Note both of the following queries achieve the same result
select count(*)
from humanresources.employeedepartmenthistory
where departmentid != 7
and departmentid != 11
and departmentid != 12;
select count(*)
from humanresources.employeedepartmenthistory
where departmentid not in (7, 11, 12);
-- What are the max and min "rate" in the humanresources.employeepayhistory table?
select
max(rate) as max_rate,
min(rate) as min_rate
from humanresources.employeepayhistory;
-- How many records are there in humanresources.employeepayhistory
-- where the rate is above 50?
select count(*)
from humanresources.employeepayhistory
where rate > 50;
-- How many records are there in humanresources.employeepayhistory
-- where the rate is less than or equal to 25?
select count(*)
from humanresources.employeepayhistory
where rate <= 25;
-- How many records are there in humanresources.employeepayhistory
-- where the rate is equal to 25?
select count(*)
from humanresources.employeepayhistory
where rate = 25;
-- How many records are there in humanresources.employeepayhistory
-- where the rate is equal to 25 or the rate is equal to 12.45?
select count(*)
from humanresources.employeepayhistory
where rate = 25
or rate = 12.45;
-- How many records are there in humanresources.employeepayhistory
-- where the rate is greater than 5, less than 20, and the payfrequency is equal to 1?
select count(*)
from humanresources.employeepayhistory
where rate > 5
and rate < 20
and payfrequency = 1;
-- How many records are there in humanresources.employeepayhistory
-- where the payfrequency is equal to 1 and the rate is greater than 5 and less than 20
-- or the payfrequency is equal to 2 and the rate is greater than 50 and less than 100
select count(*)
from humanresources.employeepayhistory
where (rate > 5 and rate < 20 and payfrequency = 1)
or (rate > 50 and rate < 100 and payfrequency = 2);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment