Where

use ReportBuilderTraining

---
--- Strings
---

select *
	from Student
	where LastName='Jones'

select *
	from Student
	where 
		Ethnicity not in ('White', 'International', 'Unknown')

select *
	from Student
	where LastName = 'Jones'
	and FirstName like 'E%'

select *
	from Student
	where LastName = 'Jones'
	and FirstName like '[ES]%'

select *
	from Student
	where LastName = 'Jones'
	and FirstName like 'I__'

select *
	from Address
	where ZIP not like '[0-9][0-9][0-9][0-9][0-9]'

select *
	from Address
	where Street1 like '%!_%' escape '!'

select *
	from Student
	where LastName like '%''%'


---
--- Numeric
---

select *
	from Student
	where HS_GPA = 4.0

select *
	from Student
	where HS_GPA <= 2.0



select *
	from Student
	where
		case
			when datepart(DY, Birthdate) > datepart(DY, getdate())
				then datediff(YYYY, Birthdate, getdate()) - 1
			else datediff(YYYY, Birthdate, getdate())
		end between 18 and 21


---
--- precidence
---

select *
	from Student
	where 
		Ethnicity not in ('White', 'International', 'Unknown') or
		FirstGeneration = 1 or 
		LowIncome = 1
		and HS_GPA=4.0
		-- 2,423 students

select *
	from Student
	where 
		(
			Ethnicity not in ('White', 'International', 'Unknown') or
			FirstGeneration = 1 or 
			LowIncome = 1
		)
		and HS_GPA=4.0
		-- 65 students