Learn querying, MongoDB with comparing SQL query

DBMS MONGODB
 Select * from Data db.Data.Find() (Show all records with all columns.)
Select year,importer,exporter,quantity from Data db.Data.find({},{year:true,importer:true,exporter:true,quantity:true,_id:false})

(In Mongodb field name must be case sensitive – you can no write Year in place of year.)

Select year,importer,exporter,quantity from Data where quantity > 50 db.Data.find({quantity:{$gt:50}},{year:true,importer:true,exporter:true,quantity:true,_id:false})

(where – quantity greater than > 50)

Select year,importer,exporter,quantity from table where quantity >= 50 db.Data.find({quantity:{$gte:50}},{year:true,importer:true,exporter:true,quantity:true,_id:false})

(Where – quantity >= 50.)

Select year,importer,exporter,quantity from Data where quantity = 200 db.Data.find({quantity:200},{year:true,importer:true,exporter:true,quantity:true,_id:false})

(where – quantity = 200)

Select year,importer,exporter,quantity from table where quantity > 50 db.Data.find({quantity:{$lt:50}},{year:true,importer:true,exporter:true,quantity:true,_id:false})

(where – quantity greater than < 50)

Select year,importer,exporter,quantity from Data where quantity <= 50 db.Data.find({quantity:{$lte:50}},{year:true,importer:true,exporter:true,quantity:true,_id:false})

(Where – quantity <= 50.)

Select year,importer,exporter,quantity from Data where quantity is null db.Data.find({quantity:null},{year:true,importer:true,exporter:true,quantity:true,_id:false})

(Null check and null compare.)

Select year,importer,exporter,quantity from Data where quantity > 100 order by importer db.Data.find({quantity:{$gt:100}},{year:true,importer:true,exporter:true,quantity:true,_id:false})

.sort({importer:1})

(Order by ascending  –  sort({column:1}))

Select year,importer,exporter,quantity from Data where quantity > 100 order by importer desc db.Data.find({quantity:{$gt:100}},{year:true,importer:true,exporter:true,quantity:true,_id:false})

.sort({importer:-1})

(Order by descending – sort({column:-1}))

db.Data.aggregate({ $group : { _id:”$importer” , Total : { $sum : 1} , Totalquantity : { $sum : “$quantity” }}}) (Group by query)

How to Do Replication in MongoDB ?

Replication: A replica set in MongoDB is a group of mongod processes that maintain the same data set. Replica sets provide redundancy and high availability, and are the basis for all production deployments.

We will do replication in single server, for that we need to create three different folders for Mongodb dbpath setting and start three mongod processes on three different ports in three different command prompts.

below List of commands that need to run.

mongod –port 27017 –dbpath D:\MongoDatabase\data –replSet rep

mongod –port 27018 –dbpath D:\MongoDatabase\data1 –replSet rep

mongod –port 27019 –dbpath D:\MongoDatabase\data2 –replSet rep

Then connect to Mongo with below command.

mongo –port 27017

Use below command to initiate the replication.

rs.initiate()

Then we need to add instance in replication, for that use below commands one by one.

rs.add(“<hostname>:Port”)

rs.add(“Ahmcpu1392:27018”)

rs.add(“Ahmcpu1392:27019”)

then you can see difference in mongo prompt.

rep:PRIMARY>

That means you have connected to primary node of replication, that’s the confirmation of replication has been done successfully, then use below command

rs.status()

In order to enable secondary’s to read the data on the primary we need to run the following command after connecting as client to each replica set secondary.

mongo –port 27018

> rs.slaveOk()

mongo –port 27019

> rs.slaveOk()

MongoDB Useful link to know more about replication in Mongodb.

http://docs.mongodb.org/v2.2/reference/replica-commands/

http://docs.mongodb.org/manual/core/replica-set-members/

Ranking Functions in Microsoft SQLSERVER

Today, We will see example of different ranking functions supported in Sqlserver.

Use below script to create table.

CREATE TABLE dbo.CustomerData
(OrderId INT IDENTITY(1,1) NOT NULL,CustomerId INT,TotalAmt decimal (10,2))

Populate data in table.

INSERT dbo.CustomerData (CustomerId, TotalAmt)
SELECT 1, 100
UNION ALL
SELECT 1, 2000
UNION ALL
SELECT 1, 560
UNION ALL
SELECT 1, 1000
UNION ALL
SELECT 1, 1000
UNION ALL
SELECT 2, 1000
UNION ALL
SELECT 2, 2500
UNION ALL
SELECT 2, 500
UNION ALL
SELECT 2, 1500
UNION ALL
SELECT 3, 1500

Below are list of Ranking function.

1. ROW_NUMBER – It will generate Row Number base on order by and partition by  provided in over clause.

SELECT *,ROW_NUMBER() OVER (ORDER BY TotalAmt DESC) AS ROWNUMBER
FROM dbo.CustomerData
ORDER BY TotalAmt DESC

Row_Number

 

 

 

 

 

 

 

SELECT *,ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS ROWNUMBERPartitionby
FROM dbo.CustomerData
ORDER BY TotalAmt DESC

Row_numberPartitionby

 

 

 

 

 

 

 

 

2. RANK – It will Generate rank base on order by and partition by provided in over clause.

Partition by – What ever column written in partition by SQL will logically create partition on that and after on that partition Rank function will apply. Rank will give same rank if value is same for column provided in over clause in order by.

SELECT *,RANK() OVER (ORDER BY TotalAmt DESC) AS RANKF,
RANK() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS RANKFPartitionby
FROM dbo.CustomerData
ORDER BY TotalAmt DESC

rank

 

 

 

 

 

 

 

3. DENSE_RANK – It will Generate rank base on order by and partition by provided in over clause.

Partition by – What ever column written in partition by SQL will logically create partition on that and after on that partition DENSE_Rank function will apply. DENSE_Rank will give same rank if value is same for column provided in over clause in order by, and next number in sequence will generate base on last number use in sequence, you can see in below example.

SELECT *,DENSE_RANK() OVER (ORDER BY TotalAmt DESC) AS DENSERANK,
DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS DENSERANKPartitionby
FROM dbo.CustomerData
ORDER BY TotalAmt DESC

Dense-rank

 

 

 

 

 

 

 

Below is query by which you compare all the ranking function.

SELECT *,

ROW_NUMBER() OVER (ORDER BY TotalAmt DESC) AS ROWNUMBER,

ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS ROWNUMBERPartitionby,

RANK() OVER (ORDER BY TotalAmt DESC) AS RANKF,

RANK() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS RANKFPartitionby,

DENSE_RANK() OVER (ORDER BY TotalAmt DESC) AS DENSERANK,

DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY TotalAmt DESC) AS DENSERANKPartitionby

FROM dbo.CustomerData

ORDER BY TotalAmt DESC

For partition by comparison see customerid and base on that see all rank function output.

Untitled

Schedule Job Related Information from Sqlserver

Today I am sharing for, how we get list of jobs schedule in Microsoft Sqlserver?
Use below query to get all information about jobs schedule in Microsoft Sqlserver.

use msdb
select distinct a.name JOBNAME, isnull(DESCRIPTION,’No description available’) DESCRIPTION,c.Name Category,
isnull (b.database_name,’None’) DATABASE_NAME, isnull(f.name, ‘None’) Job_Owner,
case when d.name is null then ‘No Schedule’ else d.name end Schedule,
isnull (case d.freq_type
when ‘1 ‘ then ‘Once’
when ‘4’ then ‘Daily’
when ‘8’ then ‘Weekly’
when ’16’ then ‘Monthly’
when ’32’ then ‘Monthly relative’
when ’64’ then ‘When SQL Server Agent starts’ end, ‘None’) as Frequency,
isnull (case d.freq_interval
when ‘1’ then ‘None’
when ‘2’ then ‘Monday’
when ‘4’ then ‘Tuesday’
when ‘8’ then ‘Wednesday’
when ’16’ then ‘Thursday’
when ’32’ then ‘Friday’
when ’64’ then ‘Saturday’
end,’None’) as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date
from sysjobs a
Inner join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
where a.enabled = 1
order by a.name

Hope this post will useful for you.

How to Remove duplicate rows from result ?

Today I explain how to remove duplicate value from result.
Below I am creating one table and I am putting only one column, but this scenario will work for more than one columns also.
— Create table
create table t1(id int)
— Insert data
insert into t1
values(1)
insert into t1
values(1)
insert into t1
values(2)
insert into t1
values(2)
insert into t1
values(3)
insert into t1
values(3)
insert into t1
values(4)
insert into t1
values(4)
— Use Cte for getting data
;with cte
as
(select ROW_NUMBER() OVER(Partition by id order by id) as idno,id from t1)
select * from cte where idno = 1

I hope this post will be useful for you.

Simplest way to get data from Coma separated String

There are many ways you will get value from coma or any other separator string, but today I will show you simplest approach to get value from string by using XML.


DECLARE @String varchar(max),
@Splitchar char(1),
@Xmlvalue xml

set @String = ‘10,20,30,40’
set @Splitchar = ‘,’

SELECT @Xmlvalue = CONVERT(xml,’ <root> <Node> ‘ + REPLACE(@String,@Splitchar,'</Node> <Node>’) + ‘ </Node> </root>’)
SELECT [Value] = T.c.value(‘.’,’varchar(20)’)
FROM @Xmlvalue.nodes(‘/root/Node’) T(c)

Example

 

 

 

 

 

 

 

 

 

I personally believe that , that’s simplest approach to get value from string which is coma or any other separated.

If you know any clear and good approach please give feed back so I can share it.