Filter SQL queries, examples. Is, is not, equal, not equal, in, not in, greater than, less than, and, or.

basic_sql_explore_10.sql

-- 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);