-- John Aromando -- SQL Text Criteria --

select state, city, custname
from Customers
where state = 'NC'
order by state, city, custname

select state, city, custname
from Customers
where state = 'NC' or state = 'NY' or state = 'CA' or state = 'VT'
order by state, city, custname

select state, city, custname
from Customers
where state in ('NY', 'NC', 'CA', 'VT') --array
order by state, city, custname

select state, city, custname
from Customers
where not(state = 'NC' or state = 'NY' or state = 'CA' or state = 'VT')
order by state, city, custname

select state, city, custname
from Customers
where state not in ('NY', 'NC', 'CA', 'VT') --better than previous
order by state, city, custname

select state, city, custname
from Customers
where state = 'NY' and city = 'ryebrook' or state = 'NC' and city = 'Wilson' or state = 'CA' and city = 'San Francisco'
order by state, city, custname

select state, city, custname
from Customers
where state = 'NY' and city = 'ryebrook' or state = 'NY' and city = 'Santa Ana' or state = 'NC' and city = 'Wilson' or state = 'CA' and city = 'San Francisco' or state = 'CA' and city = 'Greensboro' --not full proof (cities can appear for wrong state)
order by state, city, custname

--doesn't work in MS SQL Server, but will work in some other applications
select state, city, custname
from Customers
where (state='NY' and 
		city='ryebrook' or city ='Santa Ana')
	or (state='NC' and 
		city ='Wilson')
	or (state='ca' and 
		city='San Francisco' or city ='Greensboro') 
order by state, city, custname

--Correct result in SQL Server, but not some other applications
select state, city, custname
from Customers
where state='NY' and 
		(city='ryebrook' or city ='Santa Ana')
	or state='NC' and 
		city ='Wilson'
	or state='ca' and 
		(city='San Francisco' or city ='Greensboro')
order by state, city, custname

select state, city, custname
from Customers
where state='NY' and city='ryebrook' 
	or state='NY' and city ='Santa Ana'
	or state='NC' and city ='Wilson'
	or state='ca' and city='San Francisco' 
	or  state ='CA' and city ='Greensboro'
order by state, city, custname

--Simpler and sequence not important, not as precise as step 12 or 13 above, gives the same results as step 10 or 11
select state, city, custname
from customers
where state in ('ny','NC','ca') and
	city in('San Francisco','Wilson',
	'ryebrook','Santa Ana','Greensboro')
order by state, city, custname

--Wild Cards 

select bktitle, partnum from titles
where bktitle like 'a%'

select bktitle, partnum from titles
where bktitle like '%g'

select bktitle, partnum from titles
where rtrim(bktitle) like '%g'

select bktitle, partnum from titles
where bktitle like '_[hr]%'
order by bktitle

select * from customers
where custnum like '___7%'