Complex Queries in SQL- Part1

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
2. To select ALTERNATE records from a table. (ODD NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
3. Find the 3rd MAX salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
5. Select FIRST n records from a table.select * from emp where rownum <= &n;
6. Select LAST n records from a tableselect * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
7. List dept no., Dept name for all the departments in which there are no employees in the department.select * from dept where deptno not in (select deptno from emp); 
   alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
   altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
8. How to get 3 Max salaries ?select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
9. How to get 3 Min salaries ?select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
10. How to get nth max salaries ?
    select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
11. Select DISTINCT RECORDS from emp table.select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
12. How to delete duplicate rows in a table?delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
13. Count of number of employees in  department  wise.select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
14.  Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?

select ename,sal/12 as monthlysal from emp;

15. Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;

16. Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;

17. Select  all record  from emp where job not in SALESMAN  or CLERK.

select * from emp where job not in ('SALESMAN','CLERK');

18. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

19. Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';

20. Select all records where ename may be any no of  character but it should end with ‘R’.

select * from emp where ename like'%R';

21. Count  MGR and their salary in emp table.

select count(MGR),count(sal) from emp;

22. In emp table add comm+sal as total sal  .

select ename,(sal+nvl(comm,0)) as totalsal from emp;

23. Select  any salary <3000 from emp table. 

select * from emp  where sal> any(select sal from emp where sal<3000);

24. Select  all salary <3000 from emp table. 

select * from emp  where sal> all(select sal from emp where sal<3000);

25. Select all the employee  group by deptno and sal in descending order.

select ename,deptno,sal from emp order by deptno,sal desc;

26. How can I create an empty table emp1 with same structure as emp?

Create table emp1 as select * from emp where 1=2;

27. How to retrive record where sal between 1000 to 2000?
    Select * from emp where sal>=1000 And  sal<2000
28. Select all records where dept no of both emp and dept table matches.
    select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
29. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
    (Select * from emp) Union (Select * from emp1)
30. How to fetch only common records from two tables emp and emp1?
    (Select * from emp) Intersect (Select * from emp1)
31.  How can I retrive all records of emp1 those should not present in emp2?
    (Select * from emp) Minus (Select * from emp1)
32. Count the totalsa  deptno wise where more than 2 employees exist.
    SELECT  deptno, sum(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2

Message Contract in Wcf

Consume Class:




using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;


namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("Enter a Key");
                string _AuthorId = Console.ReadLine();
                ServiceReference1.ServiceClient sc = new ServiceReference1.ServiceClient();
                ServiceReference1.Author a = sc.GetAuthorInfo(_AuthorId);
                if (a != null)
                {
                    Console.WriteLine(a.ArticleId);
                    Console.WriteLine(a.FirstName);
                    Console.WriteLine(a.LastName);

                }
                else
                {
                    Console.WriteLine("Key is not valid");
                }
                    Console.ReadLine();
            }
            catch (FaultException<string> e1)
            {
                Console.WriteLine(e1.Message);
                Console.WriteLine(e1.Reason);
                Console.WriteLine(e1.Detail);
            }
        }
    }
}



Service.Cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service" in code, svc and config file together.
public class Service : IService
{
    private const string _AuthorId = "Gulshan";
    SqlConnection con;

    SqlDataAdapter dap;
    DataSet ds;
    public bool CreateConnection(AuthorRequest Req)
    {
        con = new SqlConnection();
        con.ConnectionString = "Data Source=xxxx;Initial Catalog=Master;User Id=xxxxx;Password=xxxxxx;";

        dap = new SqlDataAdapter("SELECT EMP_ID FROM Employee_Test1 where EMP_ID='"+Req._AuthorId+"'", con);
        ds = new DataSet();
        dap.Fill(ds);
        if (ds.Tables[0].Rows.Count > 0)
        {
            string x = "Data Exists";
            return true;

        }
        else
        {
            return false;
        }
       


    }


    public AuthorResponse GetAuthorInfo(AuthorRequest Req)
    {
        bool b=CreateConnection(Req);
        if (b == true)
        {

            //if (Req._AuthorId != _AuthorId)
            //{
            //    string _Error = "Invalid Author Id";
            //    throw new FaultException<string>(_Error);
            //}
            AuthorResponse au = new AuthorResponse();
            au.Obj = new Author();
            au.Obj.FirstName = "Gulshan Kumar";
            au.Obj.LastName = "Arora";
            au.Obj.ArticleId = "11001";
            return au;

        }
        else
        {
            AuthorResponse au = new AuthorResponse();
            au.Obj = null;
            return au;

        }
     
    }
}


Interface:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService" in both code and config file together.
[ServiceContract]
public interface IService
{
    [OperationContract]
    [FaultContract(typeof(string))]
    AuthorResponse GetAuthorInfo(AuthorRequest Req);


}
[MessageContract]
public class AuthorRequest
{
    [MessageHeader(Name = "AuthorId")]
    public string _AuthorId;


}
[MessageContract]
public class AuthorResponse
{
    [MessageBodyMember]
   public Author Obj;

}

[DataContract]
public class Author
{
    [DataMember]
    public string FirstName;

    [DataMember]
    public string LastName;

    [DataMember]
    public string ArticleId;
}

Server.Transfer v/s Response.Redirect

Server.Transfer v/s Response.Redirect
Both “Server” and “Response” are objects of ASP.NET. Server.Transfer and Response.Redirect both are used to transfer a user from one page to another page. Both are used for the same purpose but still there are some differences are there between both that are as follows:

      Syntactically both are different, if you want to transfer the user to a page named newpage.aspx then syntax for both methods will be,

Response.Redirect(“newpage.aspx”) and Server.Transfer(“newpage.aspx”)

      Response.Redirect involves a roundtrip to the server whereas Server.Transfer conserves server resources by avoiding the roundtrip. It just changes the focus of the webserver to a different page and transfers the page processing to a different page.

Roundtrip means in case of Response.Redirect it first sends the request for the new page to the browser then browser sends the request for the new page to the webserver then after your page changes But in case of Server.Transfer it directly communicate with the server to change the page hence it saves a roundtrip in the whole process.

How to make generic provider

How to make generic provider




using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Configuration;

public partial class Default2 : System.Web.UI.Page
{



    //This Function is to Create Connection
    public static DbConnection CreateConnection(string provider, string connection)
    {
        DbConnection con = null;
        if (con == null)
        {
            try
            {
                DbProviderFactory dp = DbProviderFactories.GetFactory(provider);
                con = dp.CreateConnection();
                con.ConnectionString = connection;
            }
            catch (Exception e1)
            {
                Console.WriteLine(e1.Message);
            }
        }
        return con;
    }



    //This Function gets all DataProvider name and information
    public static DataTable GetAllProvider()
    {
        DataTable dt = DbProviderFactories.GetFactoryClasses();
        return dt;
    }

    public static string GetConnectionString(string userProviderName)
    {
        string retreive = null;

        try
        {

       

        ConnectionStringSettingsCollection c = ConfigurationManager.ConnectionStrings;
        if (c != null)
        {
            foreach (ConnectionStringSettings cs in c)
            {
                if (cs.ProviderName == userProviderName)
                {
                   retreive=  cs.ConnectionString;
                   // retreive = cs.ProviderName;
                    break;
                }
            }
        }
        return retreive;

        }
        catch (Exception)
        {
            throw;
           
           
        }
       
    }
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataTable dt = GetAllProvider();

        for (int i = 0; i < dt.Rows.Count; i++)
            DropDownList1.Items.Add(dt.Rows[i][2].ToString());
        GridView1.DataSource = GetAllProvider();
        GridView1.DataBind();
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string _connctionstring = DropDownList1.SelectedItem.Text;
        string s = null;
        if (DropDownList1.SelectedIndex == 0)
        {
             s=GetConnectionString(_connctionstring);
             Response.Write(s);

        }
        else if (DropDownList1.SelectedIndex == 1)
        {
            s = GetConnectionString(_connctionstring);
            Response.Write(s);
        }
        else if (DropDownList1.SelectedIndex == 2)
        {
            s = GetConnectionString(_connctionstring);
            Response.Write(s);
        }
        else if (DropDownList1.SelectedIndex == 3)
        {
            s = GetConnectionString(_connctionstring);
            Response.Write(s);
        }
    }
}




change in web.config


<connectionStrings>
<clear/>  "This Tag is very important else it will not work"
<add name="Sql" connectionString="data source=72.233.78.98;uid=sa; pwd=xxxxxxxx;database=PG_SystemDB" providerName="System.Data.SqlClient"/>
<add name="Odbc" connectionString="data source=72.233.78.98;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.Odbc"/>
<add name="Oledb" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.Oledb"/>
<add name="OracleClient" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.OracleClient"/>
</connectionStrings>

Differences Between C++ Templates and C# Generics (C# Programming Guide)

Differences Between C++ Templates and C# Generics (C# Programming Guide)


C# Generics and C++ templates are both language features that provide support for parameterized types. However, there are many differences between the two. At the syntax level, C# generics are a simpler approach to parameterized types without the complexity of C++ templates. In addition, C# does not attempt to provide all of the functionality that C++ templates provide. At the implementation level, the primary difference is that C# generic type substitutions are performed at runtime and generic type information is thereby preserved for instantiated objects. For more information, see Generics in the Runtime (C# Programming Guide).
The following are the key differences between C# Generics and C++ templates:
C# generics do not provide the same amount of flexibility as C++ templates. For example, it is not possible to call arithmetic operators in a C# generic class, although it is possible to call user defined operators.
C# does not allow non-type template parameters, such as template C<int i> {}.
C# does not support explicit specialization; that is, a custom implementation of a template for a specific type.
C# does not support partial specialization: a custom implementation for a subset of the type arguments.
C# does not allow the type parameter to be used as the base class for the generic type.

How to get Ram Details in C#

How to get Ram Details in C#

using System;
using System.Runtime.InteropServices;

[StructLayout(LayoutKind.Sequential)]
class Blittable
{
    int x;
}

class Program
{
    public static unsafe void Main()
    {
        int i;
        object o = new Blittable();
        int* ptr = &i;
        IntPtr addr = (IntPtr)ptr;

        Console.WriteLine(addr.ToString("x"));

        GCHandle h = GCHandle.Alloc(o, GCHandleType.Pinned);
        addr = h.AddrOfPinnedObject();
        Console.WriteLine(addr.ToString("x"));

        h.Free();
    }
}

Create Xml file in C#

Compiled Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;

namespace ConsoleApplication7
{
    class Class2
    {

        public static void Main()
        {
            XmlDocument doc = new XmlDocument();
            XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
            doc.AppendChild(docNode);

            XmlNode productsNode = doc.CreateElement("products");
            doc.AppendChild(productsNode);

            XmlNode productNode = doc.CreateElement("product");
            XmlAttribute productAttribute = doc.CreateAttribute("id");
            productAttribute.Value = "01";
            productNode.Attributes.Append(productAttribute);
            productsNode.AppendChild(productNode);

            XmlNode nameNode = doc.CreateElement("Name");
            nameNode.AppendChild(doc.CreateTextNode("Java"));
            productNode.AppendChild(nameNode);
            XmlNode priceNode = doc.CreateElement("Price");
            priceNode.AppendChild(doc.CreateTextNode("Free"));
            productNode.AppendChild(priceNode);

            // Create and add another product node.
            productNode = doc.CreateElement("product");
            productAttribute = doc.CreateAttribute("id");
            productAttribute.Value = "02";
            productNode.Attributes.Append(productAttribute);
            productsNode.AppendChild(productNode);
            nameNode = doc.CreateElement("Name");
            nameNode.AppendChild(doc.CreateTextNode("C#"));
            productNode.AppendChild(nameNode);
            priceNode = doc.CreateElement("Price");
            priceNode.AppendChild(doc.CreateTextNode("Free"));
            productNode.AppendChild(priceNode);

            doc.Save(Console.Out);
            Console.ReadLine();
         
        }
    }
}

Using Typeof Operator in C#

Using typeof

// Demonstrate typeof.
using System;
using System.IO;
class UseTypeof {
static void Main() {
Type t = typeof(StreamReader);
Console.WriteLine(t.FullName);
if(t.IsClass) Console.WriteLine("Is a class.");
if(t.IsAbstract) Console.WriteLine("Is abstract.");
else Console.WriteLine("Is concrete.");
}
}
This program outputs the following:
System.IO.StreamReader
Is a class.
Is concrete.

Using "as" Operator in C#

Using as
Sometimes you will want to try a conversion at runtime, but not throw an exception if the
conversion fails (which is the case when a cast is used). To do this, use the as operator,
which has this general form:
expr as type


Here, expr is the expression being converted to type. If the conversion succeeds, then a
reference to type is returned. Otherwise, a null reference is returned. The as operator can
be used to perform only reference, boxing, unboxing, or identity conversions.
The as operator offers a streamlined alternative to is in some cases. For example,
consider the following program that uses is to prevent an invalid cast from occurring:
// Use is to avoid an invalid cast.
using System;
class A {}
class B : A {}
class CheckCast {
static void Main() {
A a = new A();
B b = new B();
// Check to see if a can be cast to B.
if(a is B) // if so, do the cast
b = (B) a;
else // if not, skip the cast
b = null;
if(b==null)
Console.WriteLine("The cast in b = (B) a is NOT allowed.");
else
Console.WriteLine("The cast in b = (B) a is allowed");
}
}

How to Compare Type in C#

How to CompareType

using System;

class A { }

class B : A { }

class UseIs

{

static void Main()

{

A a = new A();

B b = new B();

if (a is A) Console.WriteLine("a is an A");

if (b is A)

Console.WriteLine("b is an A because it is derived from A");

if (a is B)

Console.WriteLine("This won’t display -- a not derived from B");

if (b is B) Console.WriteLine("b is a B");

if (a is object) Console.WriteLine("a is an object");

}

}

The output is shown here:

a is an A

b is an A because it is derived from A

b is a B

a is an object

How Garbage Collector works

using System;

namespace GCCollectIntExample
{
    class MyGCCollectClass
    {
        private const long maxGarbage = 1000;

        static void Main()
        {
            MyGCCollectClass myGCCol = new MyGCCollectClass();

            // Determine the maximum number of generations the system 
     // garbage collector currently supports.
            Console.WriteLine("The highest generation is {0}", GC.MaxGeneration);

            myGCCol.MakeSomeGarbage();

            // Determine which generation myGCCol object is stored in.
            Console.WriteLine("Generation: {0}", GC.GetGeneration(myGCCol));

            // Determine the best available approximation of the number  
     // of bytes currently allocated in managed memory.
            Console.WriteLine("Total Memory: {0}", GC.GetTotalMemory(false));

            // Perform a collection of generation 0 only.
            GC.Collect(0);