Tuesday 21 February 2012

Puzzle Question for interview

Awesome Answers In IAS Examination

Q. How can you drop a raw egg... onto a concrete floor without cracking it?
A. Concrete floors are very hard to crack! (UPSC Topper)
... 
Q. If it took eight men ten hours to build a wall, how long would it take four men to build it?
A. No time at all it is already built. (UPSC 23rd Rank Opted for IFS)

Q. If you had three apples and four oranges in one hand and four apples and three oranges in the other hand, what would you have?
A. Very large hands. (Good one) (UPSC 11 Rank Opted for IPS)

Q. How can you lift an elephant with one hand?
A. you will never find an elephant with one hand. (UPSC Rank 14 Opted for IES)

Q. How can a man go eight days without sleep?
A. No Probs, He sleeps at night. (UPSC IAS Rank 98)

Q. If you throw a red stone into the blue sea what it will become?
A. It will Wet or Sink as simple as that. (UPSC IAS Rank 2)

Q. What looks like half apple ?
A: The other half. (UPSC - IAS Topper )

Q. What can you never eat for breakfast?
A: Dinner.

Q. Bay of Bengal is in which state?
A: Liquid (UPSC 33 Rank)

Interviewer said "I shall either ask you ten easy questions or one really difficult question. Think well before you make up your mind!" The boy thought for a while and said, "my choice is one really difficult question." "Well, good luck to you, you have made your own choice! Now tell me this.
"What comes first, Day or Night?"
The boy was jolted into reality as his admission depends on the correctness of his answer, but he thought for a while and said, "It's the DAY sir!"
"How" the interviewer asked.
"Sorry sir, you promised me that you will not ask me a SECOND difficult question!"
He was selected for IIM!

Technical Skill is the mastery of complexity, while Creativity is the master of presence of mind.
This is a famous paper written for an Oxford philosophy exam, normally requiring an eight page essay answer and expected to be backed up with source material, quotes and analytical reasoning. This guy wrote the below answer and topped the exam!

OXFORD EXAMINATION BOARD 1987, ESSAY QUESTION
Question: What is courage? (50 Marks)
Answer (After 7 blank pages, at the end of the last page…): This is courage

Join In sql server with example

Introduction

This is just a simple article visually explaining SQL JOINs.

Background

I'm a pretty visual person. Things seem to make more sense as a picture. I looked all over the Internet for a good graphical representation of SQL JOINs, but I couldn't find any to my liking. Some had good diagrams but lacked completeness (they didn't have all the possible JOINs), and some were just plain terrible. So, I decided to create my own and write an article about it.

Using the code

I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins. The seven Joins I will discuss are shown below:
  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. OUTER JOIN
  5. LEFT JOIN EXCLUDING INNER JOIN
  6. RIGHT JOIN EXCLUDING INNER JOIN
  7. OUTER JOIN EXCLUDING INNER JOIN
For the sake of this article, I'll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN, RIGHT EXCLUDING JOIN, andOUTER EXCLUDING JOIN, respectively. Some may argue that 5, 6, and 7 are not really joining the two tables, but for simplicity, I will still refer to these as Joins because you use a SQL Join in each of these queries (but exclude some records with a WHERE clause).

Inner JOIN

INNER_JOIN.png
This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:
SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

Left JOIN

LEFT_JOIN.png
This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

Right JOIN

RIGHT_JOIN.png
This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

Outer JOIN

FULL_OUTER_JOIN.png
This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:
SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

Left Excluding JOIN

LEFT_EXCLUDING_JOIN.png
This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:
SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Right Excluding JOIN

RIGHT_EXCLUDING_JOIN.png
This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

Outer Excluding JOIN

OUTER_EXCLUDING_JOIN.png
This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. This Join is written as follows:
SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

Examples

Suppose we have two tables, Table_A and Table_B. The data in these tables are shown below:
TABLE_A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

TABLE_B
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH
The results of the seven Joins are shown below:
-- INNER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7

(5 row(s) affected)
-- LEFT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
  10 LUCENT     NULL       NULL

(8 row(s) affected)
-- RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(8 row(s) affected)
-- OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(11 row(s) affected)
-- LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
  10 LUCENT     NULL       NULL
(3 row(s) affected)
-- RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(3 row(s) affected)
-- OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(6 row(s) affected)
Note on the OUTER JOIN that the inner joined records are returned first, followed by the right joined records, and then finally the left joined records (at least, that's how my Microsoft SQL Server did it; this, of course, is without using any ORDER BY statement).
You can visit the Wikipedia article for more info here (however, the entry is not graphical).
I've also created a cheat sheet that you can print out if needed. If you right click on the image below and select "Save Target As...", you will download the full size image.

Thursday 9 February 2012

Ajax AutoCompleteExtender sample without using webservice (or) Asp.Net AJAX AutoComplete Extender From Database without using webservice


Ajax AutoCompleteExtender sample without using webservice (or) Asp.Net AJAX AutoComplete Extender From Database without using webservice


Introduction:

Here I will explain how to use Ajax AutoCompleteExtender without using webservice to display the words begin with prefix typed into the textbox using asp.net

Description:

In previous article I explained clearly how to implement AjaxAutoCompleteExtender with webservice. We can attach Ajax autocomplete exteneder to any textbox to implement this and after assign autocomplete extender to textbox and type more content than the specified minimum word length, a popup will show words or phrases starting with that value.  So the user can choose exact word from the popup panel. Here we are implementing autoCompleteextender to fetch data from the database without using Webservice.


First design table in your database like this 

Column Name
Data Type
Allow Nulls
ID
Int(set identity property=true)
No
CountryName
Varchar(50)
Yes
After completion of design table in database add AjaxControlToolkit reference to your application after that add

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
Our aspx page code like this


<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Ajax AutoCompleteExtender without Webservice</title>
</head>
<body>
<form id="form1" runat="server">
<ajax:ToolkitScriptManager ID="ScriptManager1" runat="server"/>
<div>
<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
<ajax:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="txtCountry"
MinimumPrefixLength="1" EnableCaching="true" CompletionSetCount="1" CompletionInterval="1000" ServiceMethod="GetCountries" >
</ajax:AutoCompleteExtender>
</div>
</form>
</body>
</html>

Here if you observe above code for autocompleteextender I declared different properties now I will explain each property clearly

TargetControlID - The TextBox control where the user types content to be automatically completed.

EnableCaching- Caching is turned on, so typing the same prefix multiple times results in only one call to the web service.

MinimumPrefixLength- Minimum number of characters that must be entered before getting suggestions from the web service.

CompletionInterval - Time in milliseconds when the timer will kick in to get suggestions using the web service.

CompletionSetCount - Number of suggestions to be retrieved from the web service.

Don’t get confuse I explained all the properties details only it’s very simple to implement auto completion textbox after completion of your aspx page design add following namcespaces in your code behind page

using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
After completion of adding namespaces write the following code in codebehind


[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> GetCountries(string prefixText)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select * from Country where CountryName like @Name+'%'", con);
cmd.Parameters.AddWithValue("@Name", prefixText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List<string> CountryNames = new List<string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CountryNames.Add(dt.Rows[i][1].ToString());
}
return CountryNames;
}
Note: Use same parameter name string prefixText  in List<string> GetCountries(string prefixText) method.

Demo



AUTOCOMPLETE EXTENDER VALUE IN TEXTBOX BASED ON DROPDOWNLIST VALUE


AUTOCOMPLETE EXTENDER VALUE IN TEXTBOX BASED ON DROPDOWNLIST VALUE








.aspx

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
        <style>
        .autocomplete_completionListElement
        {
            visibility: hidden;
            margin: 0px !important;
            background-color: inherit;
            color: windowtext;
            border: buttonshadow;
            border-width: 1px;
            border-style: solid;
            cursor: 'default';
            overflow: auto;
            height: 200px;
            text-align: left;
            list-style-type: none;
            font-family: courier new;
            font-size: 8pt;
        }
        /* AutoComplete highlighted item */.autocomplete_highlightedListItem
        {
            background-color: #e3ec6e;
            color: black;
            padding: 1px;
        }
        /* AutoComplete item */.autocomplete_listItem
        {
            background-color: window;
            color: windowtext;
            padding: 1px;
        }
        body, div, p, h1, h2, h3, h4, ul, li, table
        {
            margin: 0;
            padding: 0;
            border: none;
 
        }
    </style>
</head>
<script type="text/javascript">


</script>
<body>
    <form id="form1" runat="server">
        <cc1:autocompleteextender id="AutoCompleteExtender1" runat="server" minimumprefixlength="1"
            servicemethod="GetCountryInfo" servicepath="WebService.asmx"  TargetControlID="TextBox1">
           
            </cc1:autocompleteextender>
        <asp:TextBox ID="TextBox1" runat="server" Width="381px"></asp:TextBox>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <br />
     
      <cc1:AutoCompleteExtender ID="AutoCompleteExtender2" runat="server"
      CompletionListItemCssClass="autocomplete_listItem"  MinimumPrefixLength="1"
      ServiceMethod="GetCountryOrStatesInfo" UseContextKey="true" ContextKey="salem"
      ServicePath="WebService.asmx" TargetControlID="TextBox2" EnableCaching="true" Enabled="true" ></cc1:AutoCompleteExtender>
        <br />
        <br />
        <br />
        <asp:TextBox ID="TextBox2" runat="server" Width="431px"></asp:TextBox>
     <asp:DropDownList ID="cmbList" runat="server" AutoPostBack="True" OnSelectedIndexChanged="cmbList_SelectedIndexChanged" DataSourceID="SqlDataSource1" DataTextField="city_name" DataValueField="city_name"> </asp:DropDownList><asp:SqlDataSource
         ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:flywindzConnectionString %>"
         SelectCommand="SELECT DISTINCT [city_name] FROM [city_master]"></asp:SqlDataSource>
    
    
    
    
     
    
    
    
    </form>
</body>
</html>

.cs file

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {


            AutoCompleteExtender2.ContextKey = "Salem";


        }

    }
   
    protected void cmbList_SelectedIndexChanged(object sender, EventArgs e)
    {
      
        AutoCompleteExtender2.ContextKey = cmbList.SelectedValue;
    }
using System;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data;


/// <summary>
/// Summary description for WebService
/// </summary>
[System.Web.Script.Services.ScriptService]
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService
{

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

    [WebMethod]
    public string[] GetCountryInfo(string prefixText)
    {
        int count = 10;
        string sql = "select * from client_master where client_name like @prefixText";
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
        DataTable dt = new DataTable();

        da.Fill(dt);
        string[] items = new string[dt.Rows.Count];
        int i = 0;
        foreach (DataRow dr in dt.Rows)
        {
            items.SetValue(dr["client_name"].ToString(), i);
            i++;

        }
        return items;
    }



[WebMethod] public string[] GetCountryOrStatesInfo(string prefixText, string contextKey)
{
  int count = 10;
  string sql=null;

      sql = "select Client_name +','+ client_city as [client_name]  from client_master where client_name like @prefixText and client_city='" + contextKey + "'";
 
  SqlDataAdapter da = new SqlDataAdapter(sql,conn);
  da.SelectCommand.Parameters.Add("@prefixText",SqlDbType.VarChar, 50).Value = prefixText + "%";
  DataTable dt = new DataTable();
  da.Fill(dt);

  string[] items = new string[dt.Rows.Count];
  int i = 0;
  foreach (DataRow dr in dt.Rows)
  {
    items.SetValue(dr[0].ToString(), i);
    i++;
  
  }

    return items;
}

 
   
}