About Me

My Photo
Mumbai, Maharastra, India
He has more than 4.2 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in the ASP.Net, C#, VB,SQL Server, AJAX. You can reach him at viki_keshari@yahoo.co.in or viki.keshari@gmail.com http://www.facebook.com/viki.keshari

Sunday, April 19, 2015

Find A, B, C with a condition where A+B+C=30 Chose A, B, C only from given list of number (1,3,5, 7,9,11)


Thanks Sushree for whatapping me this question, but unfortunately the answer is NO we cannot chose  any number from the given list to form A+B+C=30 :D

As a Techie as soon as I got this problem from my cute friend, the only reflection popped up in my mind...Oh!! Put pen to paper write a small block of code and hit upon the answer.

I am so stupid, I didn’t even thought that the given numbers are all ODD in nature, and when you add three odd number you will never going to get even result and here we are expecting to get 30 i.e. even output. :D

But anyways to my best level of stupidity I wrote a small piece of code, which ultimately proves that it is not possible J

DECLARE @no1 INT
       ,@no2 INT
       ,@no3 INT
       ,@no4 INT

CREATE TABLE #temp (
       id INT identity(1, 1)
       ,GivenNo INT
       );

INSERT INTO #temp
SELECT 1 UNION
SELECT 3 UNION
SELECT 5 UNION
SELECT 7 UNION
SELECT 9 UNION
SELECT 11 UNION
SELECT 13 UNION
SELECT 15

WHILE (1 = 1)
BEGIN
       SELECT @no1 = GivenNo
       FROM #temp
       WHERE id = FLOOR(10 * RAND());

       SELECT @no2 = GivenNo
       FROM #temp
       WHERE id = FLOOR(10 * RAND());

       SELECT @no3 = GivenNo
       FROM #temp
       WHERE id = FLOOR(10 * RAND());

       IF isnull(@no1, 0) + isnull(@no2, 0) + isnull(@no3, 0) = 30
       BEGIN
              PRINT cast(@no1 AS VARCHAR(10));
              PRINT cast(@no2 AS VARCHAR(10));
              PRINT cast(@no3 AS VARCHAR(10));

              BREAK
       END
END

DROP TABLE #temp

Expecting a tea with you  :)


Post Reference: Vikram Aristocratic Elfin Share

Thursday, March 19, 2015

How can I join two table (with a query) that, one table has no data and the other has some data!!???


Third question goes in the series of 10 typical questions; this is a very interesting question which I got from the comment section of one of the fantastic article from Pinal Dave

The question was like this “How can I join two table (with a query) that, one table has no data and the other has some data!!???  this is because sometimes my data base may have a table with or without data.”

The solution is very simple; yes it is full outer join.

Lets stimulate the problem and implement our solution. Here I am creating two table one with few records in it and another with no records.

create table Department
(did int identity(1,1) primary key,
dname varchar(15))
Command(s) completed successfully.

create table Employee
(eid int identity(1,1) primary key,
ename varchar(15),
did int references Department(did))
Command(s) completed successfully.

Here we have two table created with common field did. Now its time to insert few records in Department table and keep the Employee table empty.

insert into Department
select 'IT' union
select 'OPS' union
select 'HR' union
select 'PMO'

select * from Department
did         dname
----------- ---------------
1           HR
2           IT
3           OPS
4           PMO

(4 row(s) affected)

select * from Employee

eid         ename           did
----------- --------------- -----------

(0 row(s) affected)

Now lets implement our solution to join two table where Department has data but there are no data in Employee Table. Our solution is to use FULL OUTER JOIN

select d.dname, e.ename from Department d
full outer join Employee e
on d.did = e.did
dname           ename
--------------- ---------------
HR              NULL
IT              NULL
OPS             NULL
PMO             NULL

(4 row(s) affected)

The result shows that there are no related employee data for any department exist in Department table.

Programmers have burning passion towards programming; don’t dare them to their technology :)


Post Reference: Vikram Aristocratic Elfin Share

Wednesday, March 18, 2015

How can I join two table (with a query) that, one table has no data and the other has some data!!???



Third question goes in the series of 10 typical questions; this is a very interesting question which I got from the comment section of one of the fantastic article from Pinal Dave

The question was like this “How can I join two table (with a query) that, one table has no data and the other has some data!!???  this is because sometimes my data base may have a table with or without data.”

The solution is very simple; yes it is full outer join.

Lets stimulate the problem and implement our solution. Here I am creating two table one with few records in it and another with no records.

create table Department
(did int identity(1,1) primary key,
dname varchar(15))
Command(s) completed successfully.

create table Employee
(eid int identity(1,1) primary key,
ename varchar(15),
did int references Department(did))
Command(s) completed successfully.

Here we have two table created with common field did. Now its time to insert few records in Department table and keep the Employee table empty.

insert into Department
select 'IT' union
select 'OPS' union
select 'HR' union
select 'PMO'

select * from Department
did         dname
----------- ---------------
1           HR
2           IT
3           OPS
4           PMO

(4 row(s) affected)

select * from Employee

eid         ename           did
----------- --------------- -----------

(0 row(s) affected)

Now lets implement our solution to join two table where Department has data but there are no data in Employee Table. Our solution is to use FULL OUTER JOIN

select d.dname, e.ename from Department d
full outer join Employee e
on d.did = e.did
dname           ename
--------------- ---------------
HR              NULL
IT              NULL
OPS             NULL
PMO             NULL

(4 row(s) affected)

The result shows that there are no related employee data for any department exist in Department table.

Whatever you are, it is just because of programming. Love your program like anything. I love SQL Server :)


Post Reference: Vikram Aristocratic Elfin Share