INSERT INTO "baseball"("teamName", "city", "division", "ranking", "managerName.first", "managerName.last", "colors", "worldChampionships", "stats")
  VALUES('Cubs', 'Chicago', 'NL Central', 5.0, 'Dale', 'Sveum', [ 'blue', 'white' ], 2.0, [ { "year" : 2010.0, "wins" : 75.0, "losses" : 87.0, "winPercentage" : 0.463 }, { "year" : 2011.0, "wins" : 71.0, "losses" : 91.0, "winPercentage" : 0.438 }, { "year" : 2012.0, "wins" : 61.0, "losses" : 101.0, "winPercentage" : 0.377 } ])
GO
INSERT INTO "baseball"("teamName", "city", "division", "ranking", "managerName.first", "managerName.last", "colors", "worldChampionships", "stats")
  VALUES('Clubs', 'Nweyork', 'NL Central', 3.0, 'Stayne', 'Sveum', [ 'black', 'white' ], 2.0, [ { "year" : 2011.0, "wins" : 85.0, "losses" : 7.0, "winPercentage" : 0.863 }, { "year" : 2011.0, "wins" : 71.0, "losses" : 91.0, "winPercentage" : 0.438 }, { "year" : 2012.0, "wins" : 61.0, "losses" : 101.0, "winPercentage" : 0.377 } ])
GO
INSERT INTO "baseball"("teamName", "city", "division", "ranking", "managerName.first", "managerName.last", "colors", "worldChampionships", "stats")
  VALUES('Miyepan', 'Delhi', 'North', 6.0, 'AV', 'delv', [ 'green', 'yellow' ], 3.0, [ { "year" : 2012.0, "wins" : 85.0, "losses" : 2.0, "winPercentage" : 0.903 }, { "year" : 2012.0, "wins" : 71.0, "losses" : 9.0, "winPercentage" : 0.848 }, { "year" : 2012.0, "wins" : 61.0, "losses" : 101.0, "winPercentage" : 0.377 } ])
GO


SELECT FLATTEN * FROM "baseball"
GO


SELECT FLATTEN "_id", "teamName", "city", "division", "ranking", "managerName", "colors", "worldChampionships", "stats" 
	FROM "baseball"
GO


SELECT * FROM baseball
WHERE colors = 'white'


SELECT * FROM baseball
WHERE stats.year CONTAINS_ALL ( 2010, 2011 )

SELECT * FROM baseball
WHERE stats.wins > 80 AND
 stats.winPercentage > 0.58


......................................Date and BSONTimeStamp ........................................


create database adsunittest collection date_coll
go

use adsunittest
go

insert into date_coll(dates,timer,currmonth,curryear) values(Date('2012-12-12'),BSONTimeStamp(),1,2012)
go

insert into date_coll(dates,timer,currmonth,curryear) values(Date('2012-10-12'),BSONTimeStamp(),8,2014)
go

insert into date_coll(dates,timer,currmonth,curryear) values(Date('2012-01-01'),BSONTimeStamp(),3,2015)
go

insert into date_coll(dates,timer,currmonth,curryear) values(Date('2013-06-02'),BSONTimeStamp(),6,2013)
go

select currmonth from date_coll where currmonth=month(dates)  
go

select curryear from date_coll where curryear=year(dates)  
go


select curryear from date_coll where currmonth=6  
go

select currmonth from date_coll where currmonth=month(dates)+2  
go

select currmonth from date_coll where month(dates)+2=currmonth  
go

select currmonth from date_coll where month(dates)=currmonth%2  

select curryear from date_coll where currmonth=curryear%2  
go


DROP COLLECTION date_coll
go


drop database adsunittest
go


...........................................Limit and offset...................................................



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



select city, loc, pop, state, _id from zips limit 5
go



select city, loc, pop, state, _id from zips  limit 3 offset 10 
go



select city, loc, pop, state, _id from zips limit 1 offset 3
go



select city, loc, pop, state, _id from zips offset 19
go



select * from zips offset 50000
go



select * from zips limit 0 offset 0
go



select * from zips limit 5 offset -1
go


drop database adsunittest
go


..........................................Index................................................................



create database adsunittest collection baseball
go


use adsunittest
go


INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Zimmer" },
"colors": [ "blue", "gray" ],
"stats":
[
{
"year": 1904,
"wins": 100,
"mostRbis": 1000,
"grade": "A",
"battingAvg": 0.300
},
{
"year": 1987,
"wins":80,
"mostRbis":200,
"grade":"A",
"battingAvg":0.267
}
]
} )
go

INSERT INTO baseball
VALUES (
{
"teamName": "Cubs",
"city": "Luis",
"valuation": 100,
"managerName": { "first": "Dale", "last": "Sveum" },
"colors": [ "blue", "green","third" ],
"stats":
[
{
"year": 2008,
"wins": 60

},
{
"year": 1997,
"wins":100
}
]
} )
go

INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Belamy" },
"colors": [ "black", "grey" ],
"stats":
[
{
"year": 1008,
"wins": 0

},
{
"year": 1965,
"wins":2
}
]
} )
go

insert into baseball(valuation,city,mangerName,stats,colors) values (null,null,null,null,null) 
go


create index "colorarr_idx1" on baseball(colors[0] ASC)
go

create index "colorarr_idx2" on baseball("colors.0" DESC)
go

SHOW INDEXES IN adsunittest FOR COLLECTION baseball
go

select count("stats.0.year") as cnt from baseball 
go

select count(stats[0].year) as cnt from baseball 
go

select stats from baseball order by stats[0].year
go


DROP COLLECTION baseball
go


drop database adsunittest
go

...............................................create index and drop index...........................




create database adsunittest collection sch
go


use adsunittest
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(1, 'ഘതണഗഖ', [ { "type" : 'exam', "score" : 60.06045071030959 }, { "type" : 'quiz', "score" : 52.79790691903873 }, { "type" : 'homework', "score" : 71.76133439165544 }, { "type" : 'homework', "score" : 34.85718117893772 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(2, 'ﬠﬢתן', [ { "type" : 'exam', "score" : 67.03077096065002 }, { "type" : 'quiz', "score" : 6.301851677835235 }, { "type" : 'homework', "score" : 20.18160621941858 }, { "type" : 'homework', "score" : 66.28344683278382 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(3, 'テトソケコ', [ { "type" : 'exam', "score" : 71.64343899778332 }, { "type" : 'quiz', "score" : 24.80221293650313 }, { "type" : 'homework', "score" : 1.694720653897219 }, { "type" : 'homework', "score" : 42.26147058804812 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(4, 'ﬠﬢתן', [ { "type" : 'exam', "score" : 78.68385091304332 }, { "type" : 'quiz', "score" : 90.2963101368042 }, { "type" : 'homework', "score" : 34.41620148042529 }, { "type" : 'homework', "score" : 19.21886443577987 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(6, 'بصظرةت', [ { "type" : 'exam', "score" : 37.32285459166097 }, { "type" : 'quiz', "score" : 28.32634976913737 }, { "type" : 'homework', "score" : 16.58341639738951 }, { "type" : 'homework', "score" : 81.57115318686338 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(7, 'بصظرةت', [ { "type" : 'exam', "score" : 90.37826509157176 }, { "type" : 'quiz', "score" : 42.48780666956811 }, { "type" : 'homework', "score" : 67.18328596625217 }, { "type" : 'homework', "score" : 96.52986171633331 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(8, 'テトソケコ', [ { "type" : 'exam', "score" : 22.13583712862635 }, { "type" : 'quiz', "score" : 14.63969941335069 }, { "type" : 'homework', "score" : 75.94123677556644 }, { "type" : 'homework', "score" : 73.2975330340769 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(9, 'খআঘখণ', [ { "type" : 'exam', "score" : 97.00509953654694 }, { "type" : 'quiz', "score" : 97.80449632538915 }, { "type" : 'homework', "score" : 12.47568017314781 }, { "type" : 'homework', "score" : 25.27368532432955 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(10, 'খআঘখণ', [ { "type" : 'exam', "score" : 45.61876862259409 }, { "type" : 'quiz', "score" : 98.35723209418343 }, { "type" : 'homework', "score" : 19.31113429145131 }, { "type" : 'homework', "score" : 55.90835657173456 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(11, 'ഘതണഗഖ', [ { "type" : 'exam', "score" : 78.42617835651868 }, { "type" : 'quiz', "score" : 82.58372817930675 }, { "type" : 'homework', "score" : 87.49924733328717 }, { "type" : 'homework', "score" : 15.81264595052612 } ])
go

INSERT INTO sch(_id, name, scores)
  VALUES(12, 'ஞசஙகற', [ { "type" : 'exam', "score" : 54.29841278520669 }, { "type" : 'quiz', "score" : 85.61270164694737 }, { "type" : 'homework', "score" : 14.78936520432093 }, { "type" : 'homework', "score" : 80.40732356118075 } ])
go


 
INSERT INTO sch(_id, name, scores)
  VALUES(13, 'ஞசஙகற', [ { "type" : 'exam', "score" : 90.47179954427436 }, { "type" : 'quiz', "score" : 90.3001402468489 }, { "type" : 'homework', "score" : 95.17753772405909 }, { "type" : 'homework', "score" : 78.18795058912879 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(14, 'యఢతథ', [ { "type" : 'exam', "score" : 25.15924151998215 }, { "type" : 'quiz', "score" : 68.64484047692098 }, { "type" : 'homework', "score" : 13.66179556675781 }, { "type" : 'homework', "score" : 24.68462152686763 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(15, 'యఢతథ', [ { "type" : 'exam', "score" : 69.1565022533158 }, { "type" : 'quiz', "score" : 3.311794422000724 }, { "type" : 'homework', "score" : 45.03178973642521 }, { "type" : 'homework', "score" : 42.19409476640781 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(16, 'Dodie Staller', [ { "type" : 'exam', "score" : 7.772386442858281 }, { "type" : 'quiz', "score" : 31.84300235104542 }, { "type" : 'homework', "score" : 80.52136407989194 }, { "type" : 'homework', "score" : 70.3632405320854 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(17, 'Fletcher Mcconnell', [ { "type" : 'exam', "score" : 39.41011069729274 }, { "type" : 'quiz', "score" : 81.13270307809924 }, { "type" : 'homework', "score" : 31.15090466987088 }, { "type" : 'homework', "score" : 97.70116640402922 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(18, 'Verdell Sowinski', [ { "type" : 'exam', "score" : 62.12870233109035 }, { "type" : 'quiz', "score" : 84.74586220889356 }, { "type" : 'homework', "score" : 81.58947824932574 }, { "type" : 'homework', "score" : 69.09840625499065 } ])
go

 
INSERT INTO sch(_id, name, scores)
  VALUES(19, 'Gisela Levin', [ { "type" : 'exam', "score" : 44.51211101958831 }, { "type" : 'quiz', "score" : 0.6578497966368002 }, { "type" : 'homework', "score" : 93.36341655949683 }, { "type" : 'homework', "score" : 49.43132782777443 } ])
go


 


select count (name) from sch
go



select count (distinct name) from sch
go



create index idx_name on sch (name)
go


drop index idx_name on sch
go



create sparse index sidx_name on sch (name desc)
go




drop index sidx_name on sch
go




create index idx_name_bck on sch ("name") background
go


drop index idx_name_bck on sch
go

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




create database adsunittest collection eqcoll
go

use adsunittest
go



insert into eqcoll(a,b) values([1,2],1)
go


insert into eqcoll(a,b) values([3,4],2)
go


insert into eqcoll(a,b) values([[1],[2]],3)
go

select a,b from eqcoll where a=[1]
go
 
select a,b from eqcoll where a[0]=[1]
go


select a,b from eqcoll where b=[1]
go

select a,b from eqcoll where a=[[1]]
go

select a,b from eqcoll where b=[[1]]
go

select a,b from eqcoll where a contains_all(1,2)
go


select a,b from eqcoll where a[0] contains_all(1)
go

DROP COLLECTION eqcoll
go


drop database adsunittest
go



create index idx_name_bck_dd on sch ("name") background drop_dups
go



drop index idx_name_bck_dd on sch
go



create index idx_name_id on sch (_id asc, name desc) background
go



drop index idx_name_id on sch
go


create unique index uidx_name on sch (name)  background drop_dups
go


select count (name) from sch
go



drop collection sch
go



drop database adsunittest
go

....................................................where clause in array field.......................



create database adsunittest collection baseball
go

use adsunittest
go


INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Zimmer" },
"colors": [ "blue", "gray" ],
"stats":
[
{
"year": 1904,
"wins": 100,
"mostRbis": 1000,
"grade": "A",
"battingAvg": 0.300
},
{
"year": 1987,
"wins":80,
"mostRbis":200,
"grade":"A",
"battingAvg":0.267
}
]
} )
go

INSERT INTO baseball
VALUES (
{
"teamName": "Cubs",
"city": "Luis",
"valuation": 100,
"managerName": { "first": "Dale", "last": "Sveum" },
"colors": [ "blue", "green","third" ],
"stats":
[
{
"year": 2008,
"wins": 60

},
{
"year": 1997,
"wins":100
}
]
} )
go

INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Belamy" },
"colors": [ "black", "grey" ],
"stats":
[
{
"year": 1008,
"wins": 0

},
{
"year": 1965,
"wins":2
}
]
} )
go

insert into baseball(valuation,city,mangerName,stats) values (null,null,null,null) 
go

insert into baseball(valuation) values (0) 
go

select city from baseball where "colors.0"='blue'
go


select city from baseball where colors[0]='blue'
go



select city from baseball where "stats.0.year"=1904
go

select city from baseball where stats[0].year=1904
go


update baseball set colors[0]='blue'
go

select colors from baseball 
go

DROP COLLECTION baseball
go



drop database adsunittest
go

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



create database adsunittest collection baseball
go


use adsunittest
go


INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Zimmer" },
"colors": [ "blue", "gray" ],
"stats":
[
{
"year": 1904,
"wins": 100,
"mostRbis": 1000,
"grade": "A",
"battingAvg": 0.300
},
{
"year": 1987,
"wins":80,
"mostRbis":200,
"grade":"A",
"battingAvg":0.267
}
]
} )
go

INSERT INTO baseball
VALUES (
{
"teamName": "Cubs",
"city": "Luis",
"valuation": 100,
"managerName": { "first": "Dale", "last": "Sveum" },
"colors": [ "blue", "green","third" ],
"stats":
[
{
"year": 2008,
"wins": 60

},
{
"year": 1997,
"wins":100
}
]
} )
go

INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Belamy" },
"colors": [ "black", "grey" ],
"stats":
[
{
"year": 1008,
"wins": 0

},
{
"year": 1965,
"wins":2
}
]
} )
go

insert into baseball(valuation,city,mangerName,stats,colors) values (null,null,null,null,null) 
go

select month(date('2013-10-11')) from baseball 
where  stats[0].year =1904  group by valuation
go



select min(stats.year) as mn,month(date('2013-10-11')) from baseball 
where  array_size(stats) =2  group by valuation 
go


select "valuation" +1  from baseball
go


select valuation +1  from baseball
go

select valuation from baseball where "valuation"+1=11 
go

select (valuation*valuation) as mul from baseball group by valuation  
go

select (valuation/valuation)  as mul from baseball group by valuation  
go

select (valuation+valuation)  as mul from baseball group by valuation  
go

select (valuation-valuation)  as mul from baseball group by valuation  
go

select (valuation%valuation)  as mul from baseball group by valuation  
go



update baseball set valuation=-1
go

select valuation from baseball
go



DROP COLLECTION baseball
go



drop database adsunittest
go

..........................Insert array record..............................


create database adsunittest collection arrcoll
go


use adsunittest
go

INSERT INTO arrcoll(a) values ([])
go

update arrcoll set a=[1,2,3,4,5,6,7,8,9]
go

select a from arrcoll  
go

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


create database adsunittest collection tde
go


use adsunittest
go


INSERT INTO tde(_id, bindata, codedata, contribs, dob, flag, md5Data, mnky, mxky, stats, uuidData, views, weight)
  VALUES(OBJECTID('523e93882318904ad2312360'), OBJECTID('523e93882318904ad2312360'), 7.898090808988789E8, OBJECTID('523e93882318904ad2312360'), NULL, false, false, MINKEY(), MAXKEY(), 89089980912, NULL, '1340090880.7897', 7.898090808988789E8)
GO

INSERT INTO tde(_id, codedata, contribs, dob, flag, md5Data, mnky, mxky, stats, uuidData, views, weight, name.first, name.last)
  VALUES(OBJECTID('523e92862318904ad231235c'), NULL, NULL, NULL, true, MINKEY(), MINKEY(), MAXKEY(), MAXKEY(), UUID('d28a74da-87a6-1752-2476-c71108323bd5'), '1340090880.23', 7898.898, 'James', 'Pradhan')
GO

INSERT INTO tde(_id, codedata, contribs, dob, flag, md5Data, mnky, mxky, stats, uuidData, views)
  VALUES(OBJECTID('524baf3623181debf1716e19'), NULL, NULL, 'MaxKey', 'MaxKey', NULL, MINKEY(), 'MaxKey', 'function f() { return 12; }', NULL, '€')
GO

INSERT INTO tde(_id, bindata, codedata, contribs, dob, flag, md5Data, stats, uuidData, views)
  VALUES(OBJECTID('524bb22123181debf1716e1a'), ISODATE('1943-09-20T04:15:00.000Z'), NULL, ISODATE('1943-09-20T04:15:00.000Z'), ISODATE('1943-09-20T04:15:00.000Z'), ISODATE('1943-09-20T04:15:00.000Z'), ISODATE('1943-09-20T04:15:00.000Z'), 89089, NULL, '€')
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, stats, uuidData, views, weight)
  VALUES(OBJECTID('524c0f872318d95ad173ed21'), 'Asian Art News (HKG)', '6/Year', 'US$', 'US$', 'US$', 'Der Spiegel (Deutsch)', '52/Year', '€', 380.0)
GO


INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, stats, uuidData, views, weight)
  VALUES(OBJECTID('524c0fc72318d95ad173ed26'), OBJECTID('524c0fc72318d95ad173ed26'), OBJECTID('524c0fc72318d95ad173ed26'), OBJECTID('524c0fc72318d95ad173ed26'), OBJECTID('524c0fc72318d95ad173ed26'), OBJECTID('524c0fc72318d95ad173ed26'), 'Paris Match class (Français)', '51/Year', '€ $$$', 175.0)
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, mnky, mxky, stats, uuidData, views, weight)
  VALUES(OBJECTID('524c0f872318d95ad173ed22'), 'Cover: Modern Carpets & Textiles', '4/Year', 'UK£', 60.0, MINKEY(), MAXKEY(), 'Le Monde Diplomatique (Français)', '12/Year', '€', 60.0)
GO

INSERT INTO tde(_id, mnky, mxky)
  VALUES(OBJECTID('524e6be823182d76d507d8ef'), 'MinKey', 'MaxKey')
GO

INSERT INTO tde(_id, mnky, mxky)
  VALUES(OBJECTID('524e6d3723182d76d507d8f0'), 'MinKey', 'MaxKey')
GO

INSERT INTO tde(_id, bindata)
  VALUES(OBJECTID('524e9f5c23182d76d507d8f9'), 'jkljkl')
GO

INSERT INTO tde(_id, bindata, codedata, contribs, dob, flag, mnky, mxky, stats, uuidData, views, weight)
  VALUES(OBJECTID('524c0f872318d95ad173ed20'), 'Arts of Asia (HKG)jkljalksdf', CODE('function f() { return 12; return crap } '), '6/Year', 'US$', 160.0, MINKEY(), MAXKEY(), 'Courrier International (Français)', '48/Year', '€', 210.0)
GO

INSERT INTO tde(_id, bindata, codedata, contribs, dob, flag, mnky, mxky, stats, uuidData, views, weight)
  VALUES(OBJECTID('524c0f872318d95ad173ed23'), 'Hali - Carpet & Textile', CODE('function f() { return 12; return crap } '), '4/Year', 'UK£', 150.0, MINKEY(), MAXKEY(), 'Le Monde Selection (Français)', '51/Year', '€ $$', 140.0)
GO

INSERT INTO tde(_id, bindata, codedata, contribs)
  VALUES(OBJECTID('524ea0bd23182d76d507d8fa'), 'hjhkjlhkjh', CODE('function f() { return 12; return crap } '), '80890')
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, mnky, mxky, stats, uuidData, views, weight)
  VALUES(OBJECTID('524c0f872318d95ad173ed24'), 'Orientations (HKG)', '8/Year', 'US$', 150.0, '150.00', MINKEY(), MAXKEY(), 'Le Nouvel Observateur (Français)', '50/Year', '€', 175.0)
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, mnky, mxky, name.first)
  VALUES(OBJECTID('524e9f3123182d76d507d8f3'), 'Dare (India)', '12/Year', 'NPR', '1,000.00', '', MINKEY(), MAXKEY(), '')
GO

INSERT INTO tde(_id, contribs)
  VALUES(OBJECTID('525391332318abf48ef414f4'), [ 23, 77, 21 ])
GO

INSERT INTO tde(_id, codedata, contribs, stats, name.last)
  VALUES(OBJECTID('524e9b6023182d76d507d8f1'), 'function f() { return 12; return crap } ', '[34, 56, 0, 23]', '%^&*()-=+|"~?><', '!@#')
GO

INSERT INTO tde(_id, codedata, flag, mnky, name.first, view)
  VALUES(OBJECTID('5253ecdb231826ea474f5c78'), CODE('function f() { return 12; return crap } '), true, MINKEY(), 'Ravi', 8989.178)
GO

INSERT INTO tde(_id, bindata, codedata, contribs, dob, flag, md5Data, mnky, mxky, stats, uuidData, views, weight, name.first, name.last)
  VALUES(OBJECTID('523e7f992318904ad2312359'), HEXDATA('616B'), CODE('function f() { return 12; return crap } '), [ 'Java', 'Kawa', 'Chhawa' ], ISODATE('1943-09-20T04:15:00.000Z'), true, MD5('79054025255FB1A26E4BC422AEF54EA7'), MINKEY(), MAXKEY(), ' Cat ', '13400.67687', '13400.67687', CODE('function f() { return 12; }'), 'Rat ', ' Cat ')
GO

INSERT INTO tde(_id, bindata, codedata, contribs, dob, flag, md5Data, mnky, mxky, stats, uuidData, views, weight, name.first, name.last)
  VALUES(OBJECTID('52401c1623187624650c6cad'), HEXDATA('616B'), CODE('function gh(){return 12}'), [ 34, 23, 12 ], ISODATE('1943-09-20T04:15:00.000Z'), true, MD5('79054025255FB1A26E4BC422AEF54EA4'), MINKEY(), MAXKEY(), ' Cat ', UUID('d28a74da-87a6-1752-2476-c71108323bd5'), '13400.67687', 7898.898, 'Kazi', ' Cat ')
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, mxky, stats, uuidData, views, weight)
  VALUES(OBJECTID('524c0f872318d95ad173ed25'), 'World Sculpture News (HKG)', '4/Year', 'US$', 25.0, 'MaxKey', 'Le Point (Français)', '50/Year', '€', 175.0)
GO

INSERT INTO tde(_id, bindata, codedata, contribs, dob, flag, md5Data, stats, uuidData, views, weight, name.first, name.last)
  VALUES(OBJECTID('523e932b2318904ad231235e'), HEXDATA('616B'), 'function f() { return 12; return crap } ', [ 'Java', 'Kawa', 'Chhawa' ], ISODATE('1943-09-20T04:15:00.000Z'), false, MD5('79054025255FB1A26E4BC422AEF54EB3'), 89089980912, UUID('f81d4fae-7dec-11d0-a765-00a0c91e6bf7'), '1340090880.9098', 7.898090808988789E8, MD5('79054025255FB1A26E4BC422AEF54EB3'), 'Pradhan')
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, name.first)
  VALUES(OBJECTID('524e9f3123182d76d507d8f2'), 'Bloomberg Businessweek', '50/Year', 'NPR', '11,000.00', '', '')
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, name.first)
  VALUES(OBJECTID('524e9f3123182d76d507d8f4'), 'Detail On Retail', '4/Year', 'US$', '130.00', '', '')
GO


INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, name.first)
  VALUES(OBJECTID('524e9f3123182d76d507d8f5'), 'Entrepreneur (India)', '12/Year', 'NPR', '2,000.00', '', '')
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, name.first)
  VALUES(OBJECTID('524e9f3123182d76d507d8f6'), 'Forbes (India)', '26/Year', 'NPR', '4,400.00', '', '')
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, name.first)
  VALUES(OBJECTID('524e9f3123182d76d507d8f7'), 'Foreign Affairs (US)', '6/Year', 'NPR', '6,600.00', '', '')
GO

INSERT INTO tde(_id, bindata, contribs, dob, flag, md5Data, name.first)
  VALUES(OBJECTID('524e9f3123182d76d507d8f8'), 'Fortune', '18/Year', 'NPR', '4,800.00', '9,400.00', '13,800.00')
GO

INSERT INTO tde(_id, codedata, contribs, flag, md5Data, mnky, mxky, uuidData, name.first)
  VALUES(OBJECTID('5253ee73231826ea474f5c79'), CODE('function gh(){return 12}'), [ 34, 23, 12 ], true, MD5('79054025255FB1A26E4BC422AEF54EA4'), MINKEY(), MAXKEY(), UUID('d28a74da-87a6-1752-2476-c71108323bd5'), 'Kazi')
GO


select stats from tde
intersect
select name.last from tde
go


select count(*) from tde where stats between (89078 + 2) and (89089980918)
go 

select count(*) from tde where stats between (89080) and (89089980918)
go

select count(*) from tde where stats + weight > 1
go

.................................................Date Scenarios....................................................



create database adsunittest collection orddate
go

use adsunittest
go

insert into orddate(dates,funds) values(Date('2012-12-12 14:18:12.977'),10)
go

insert into orddate(dates,funds) values(Date('2012-12-13 14:18:12.977'),11)
go

insert into orddate(dates,funds) values(Date('2012-10-14 15:18:12.977'),1)
go

insert into orddate(dates,funds) values(Date('2012-10-2 15:18:12.977'),5)
go


select dates from orddate where month(dates) between 1 and 12 group by dates 
go



select dates from orddate where month(dates) = 1 or month(dates)=12 group by dates 
go


select month(dates) from orddate where month(dates) =12 and funds>1 group by dates   
go

select dates from orddate group by dates having  month(dates) between 1 and 12
go


DROP COLLECTION orddate
go

drop database adsunittest
go

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


create database adsunittest collection baseball
go

use adsunittest
go


INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Zimmer" },
"colors": [ "blue", "gray" ],
"stats":
[
{
"year": 1904,
"wins": 100,
"mostRbis": 1000,
"grade": "A",
"battingAvg": 0.300
},
{
"year": 1987,
"wins":80,
"mostRbis":200,
"grade":"A",
"battingAvg":0.267
}
]
} )
go

INSERT INTO baseball
VALUES (
{
"teamName": "Cubs",
"city": "Luis",
"valuation": 100,
"managerName": { "first": "Dale", "last": "Sveum" },
"colors": [ "blue", "green","third" ],
"stats":
[
{
"year": 2008,
"wins": 60

},
{
"year": 1997,
"wins":100
}
]
} )
go

INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Belamy" },
"colors": [ "black", "grey" ],
"stats":
[
{
"year": 1008,
"wins": 0

},
{
"year": 1965,
"wins":2
}
]
} )
go

insert into baseball(valuation,city,mangerName,stats,colors) values (null,null,null,null,null) 
go

select count(valuation), SUM(valuation/valuation) from baseball   
go


select valuation,count(valuation), SUM(valuation) from baseball   
group by valuation
go

select valuation,sum(valuation) from baseball group by valuation having sum(valuation)>0
go

create index "stat_idx" on baseball(stats[0].wins ASC,stats[0].year DESC)
go

select flatten_array key from "system.indexes" where ns='adsunittest.baseball'
go

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



create database adsunittest collection jscopy
go

use adsunittest
go

insert into jscopy(a,b) values (1,HEXDATA('01'))
go

select b from jscopy where b=HEXDATA('01') group by b  
go

select b from jscopy group by b having b=HEXDATA('01') 
go