Skip to content

Instantly share code, notes, and snippets.

@agawronski
Created January 14, 2018 21:18
Show Gist options
  • Save agawronski/2e92ba6ecc6c51eea96d07f3332699f3 to your computer and use it in GitHub Desktop.
Save agawronski/2e92ba6ecc6c51eea96d07f3332699f3 to your computer and use it in GitHub Desktop.
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