
create database adsunittest collection zips
go


use adsunittest
go


INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('35004', 'ACMAR', [ -86.51557, 33.584132 ], 6055, 'AL')
go

 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('35005', 'ADAMSVILLE', [ -86.959727, 33.588437 ], 10616, 'AL')
 go
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('35006', 'ADGER', [ -87.167455, 33.434277 ], 3205, 'AL')
 go
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('90001', 'LOS ANGELES', [ -118.247896, 33.973093 ], 51841, 'CA')
go
 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('90002', 'LOS ANGELES', [ -118.246213, 33.94969 ], 40629, 'CA')
 go
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('90003', 'LOS ANGELES', [ -118.272739, 33.965335 ], 53938, 'CA')
go
 
INSERT INTO zips(_id, city, loc, pop, state)
 VALUES('75002', 'ALLEN', [ -96.645433, 33.093383 ], 24151, 'TX')
go
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('75006', 'CARROLLTON', [ -96.882464, 32.965736 ], 37699, 'TX')
go 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('75007', 'CARROLLTON', [ -96.881988, 33.003294 ], 54796, 'TX')
go 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('58002', 'ABSARAKA', [ -97.388769, 47.014359 ], 124, 'ND')

go 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('58003', 'ALICE', [ -97.531819, 46.768892 ], 255, 'ND')

go 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('58004', 'AMENIA', [ -97.204808, 47.019395 ], 321, 'ND')

go 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('60002', 'ANTIOCH', [ -88.117802, 42.464811 ], 18058, 'IL')
go 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('60004', 'ARLINGTON HEIGHT', [ -87.979099, 42.111619 ], 52947, 'IL')
go 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('60005', 'ARLINGTON HEIGHT', [ -87.985461, 42.066599 ], 26742, 'IL')
go
 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('32008', 'BRANFORD', [ -82.899288, 29.939472 ], 2439, 'FL')
go
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('32009', 'BRYCEVILLE', [ -81.972397, 30.419274 ], 1875, 'FL')
 go
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('32011', 'CALLAHAN', [ -81.814465, 30.551958 ], 9111, 'FL')
 go
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('84001', 'ALTAMONT', [ -110.446356, 40.370225 ], 146, 'UT')
 go
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('84002', 'ALTONAH', [ -110.438499, 40.441894 ], 10, 'UT')
go
 
INSERT INTO zips(_id, city, loc, pop, state)
  VALUES('84003', 'AMERICAN FORK', [ -111.794107, 40.392784 ], 21864, 'UT')
go

.......................................................................................

// intersect clause 

insert into tempunion
select distinct city from zips where state = 'IL' and pop > 10000
intersect
select distinct city from zips where state in ('IL', 'CA') and pop >= 10000
go

select count(distinct city) from zips where city in (
select distinct city from zips where state = 'CA'
intersect 
select distinct city from zips where state = 'IL') and state = 'IL'
go

...................................................................................
// union clause

insert into tempunion
select distinct city from zips where state = 'AL'
union
select distinct city from zips where state = 'IL'
union
select distinct city from zips where state = 'TX'
go


select * from zips where state = 'TX' order by city DESC LIMIT 3
go

...................................................................................
// except clause

select distinct city from zips where state = 'AL'
except 
select distinct city from zips where state = 'AL'
except
select distinct city from zips where state = 'TX'
go






