SQL Puzzle1

Very Recently i faced one Problem, struggled a lot to Find Solution, but didn't find that solution yet. So trying to share that Problem with you all.
Consider there are two tables. Table 1 and Table 2.

Table 1 Contain Following Details

Question_Id	Question
1	Question1
2	Question2
3	Question3
4	Question4

Table 2 Contain Following Details

Question_Id	Option_Id	Options
1	11	Q1Option1
1	12	Q1Option2
1	13	Q1Option3
1	14	Q1Option4
2	21	Q2Option1
2	22	Q2Option2
2	23	Q2Option3
2	24	Q2Option4
3	31	Q3Option1
3	32	Q3Option2
3	33	Q3Option3
3	34	Q3Option4
3	35	Q3Option5
3	36	Q3Option6
4	41	Q4Option1
4	42	Q4Option2
4	43	Q4Option3
4	44	Q4Option4
4	45	Q4Option5

Now we have to write a Query that should return a Table in a Below Stated format.

ID	QuestionOption
1	Question1
11	Q1Option1
12	Q1Option2
13	Q1Option3
14	Q1Option4
2	Question2
21	Q2Option1
22	Q2Option2
23	Q2Option3
24	Q2Option4
3	Question3
31	Q3Option1
32	Q3Option2
33	Q3Option3
34	Q3Option4
35	Q3Option5
36	Q3Option6
4	Question4
41	Q4Option1
42	Q4Option2
43	Q4Option3
44	Q4Option4
45	Q4Option5

Condition:
Never use UNION and LOOP


Thank you

SQL SELECT Puzzle

This is one of the good puzzle i heard from my Interview.

When i went for an Interview, interviewer asked me one good Puzzle in SQL , Quite Simple but really very Good Puzzle. 

Question:

    Consider a Local Variable,

DECLARE @Name Varchar(50)=NULL

SELECT FirstName+ ' '+LastName FROM Employee WHERE FirstName=@Name

Question is very simple,
When value is passed through that variable then it should execute complete SELECT statement with WHERE Clause, if value is not passed then SELECT  statement should execute without WHERE Clause.
Eg:
 Consider there are 5 Records in that Employee Table

ID	FirstName	LastName	EmailId	ContactNo
1	Anand	Kumar	anand1@gmail.com	9898989898
2	Anderson		anderson@gmail.com	9898989898
3	Arnold	schwarzenegger	arnold@yahoo.com	9898989898
4	David	Blaine	david@gmail.com	9898989898
5	Ranbir	Kapoor	Ranbir@gmail.com	9898989898

If we pass @Name='Anand' then it should return First Record with FirstName and LastName.
If we are not passing any value then obviously @Name is Null as it is declared as NULL, so it should return all records with FirstName and LastName.

Condition
         You should never use two set of Select Query (Some people will say we can use IF Statement and can write two set of  Queries, one with WHERE Clause and one without WHERE Clause-never do this).

Thank you

Executing one ASP.NET Page from Another

Very recently i faced one problem where i want to execute one ASP.NET Page from Another Page just by calling inside some Function. Let we take this Scenario.

I have changed my code to dummy code as i cant write my real code here due to Copyright Violation. First i will discuss about that Problem.

Consider there are two ASP.NET Pages, Page1.aspx and Page2.aspx

Page1.aspx :

protected void Page_Load(object sender,EventArgs e)

{

Function1();

Function2();

Function3();

}

protected void Function1()

{

Response.Write("Function1");

}

protected void Function2()

{

Response.Write("Function2");

//Call Page2.aspx

}

protected void Function3()

{

Response.Write("Function3");

}

Page2.aspx:

protected void Page_Load(object sender,EventArgs e)

{

Response.Write("Page2");

}

Now we need result like this,

Output:

Function1

Function2

Page2

Function3

How will you do this?

Beware of Implicit Datatype Conversion in SQL Server

Do you know SQL Server does Implicit Conversion in Queries? If you know that then you are safe. If you don't then please don't miss reading this Complete Article.

Now we'll create a Test Table and Insert Some Values, to understand this concept more clearly.

CREATE TABLE [dbo].[Test3]( [N1] [int] NOT NULL CONSTRAINT [PK_Test3] PRIMARY KEY,
[V1] [nvarchar](10) NULL, )
INSERT INTO Test3 VALUES(1,'1000')
INSERT INTO Test3 VALUES (2,'1001')
INSERT INTO Test3 VALUES (3,'1003')
INSERT INTO Test3 VALUES (4,'1004')
INSERT INTO Test3 VALUES (5,'200')

If we try to do

SELECT * FROM Test3 WHERE V1>1002

Will it Work? It will work. This is where Implicit Conversion is Happening. Whenever SQL Server finds Arithmetic Operator it will convert string to Number if it can do that. This Facility is not Provided in DB2. It will throw exception. But thats a good advantage in DB2. So that people will never get in trap.

Output for Above Query will be

N1	V1
3	1003
4	1004
5	200