Wednesday, November 28, 2012

Self join: Employee - Manager


ID   Name        ManagerID

1 Balaji NULL
2 Ashish 1
3 Divya 1
4 Nidhish 2


Write a query to display ManagerName (ManagerID is foreign key to ID)/



SELECT     e.ID, e.Name, mgr.Name AS ManagerName
FROM         Employee AS e LEFT OUTER JOIN
                      Employee AS mgr ON e.ManagerID = mgr.ID

Result:


1 Balaji NULL
2 Ashish Balaji
3 Divya Balaji
4 Nidhish Ashish


SQL Sample question





1.       Find out Name of customer who has maximum number of product?
2.       Find out Name of customer who does not have product.

/*find customer who has maximum product*/
select top 1 c.Name , COUNT(c.ID) as CustomerCount from Customer c left outer join CustomerProduct cp
      on c.ID = cp.CustomerID
            group by c.ID ,c.Name
            order by CustomerCount desc



/*find all customers who does not have any product*/
select c.Name  from Customer c left outer join CustomerProduct cp
      on c.ID = cp.CustomerID
     
            where cp.CustomerID is null

Thursday, October 25, 2012

Strategy Pattern


A manufacturing company produces different products, and they sell it with help of distributors. Manufacturing company and distributor makes agreement like 10% rebate for 1000 products. Rebate claim by distributor can be send to company in the form of files (txt, excel1, excel2, excel3). And claim management system processes these files. 


txt         - process directly
excel1 - 4 columns template
excel2 - 8 columns template
excel3 - 9 columns template

Which GoF pattern is ideal in this scenario?

Solution: Strategy pattern

  • Strategy is a behavioral pattern
  • algorithms can be selected at runtime
  • defines a family of algorithms, encapsulates each one, make them interchangable

Reference: 
http://www.dofactory.com/Patterns/PatternStrategy.aspx
http://en.wikipedia.org/wiki/Strategy_pattern




Friday, October 5, 2012

Publisher - Subscriber (optimized)


  • Publisher-Subscriber is a messaging pattern. 
  • Publisher is the sender of the message. (PUSH)
  • Publisher does not have to know about the subscribers.
  • Subscribers has the control to subscribe/unsubscribe message

Reference: http://en.wikipedia.org/wiki/Publish%E2%80%93subscribe_pattern 

Scenario: There is a publisher and many subscribers (like Printer, Fax, Web server etc). Whenever Publisher gets file, it should notify the subscribers. And subscriber should have the authority to subscribe/not subscribe. How will you design this approach?



class Program
    {
        static void Main(string[] args)
        {
            //create publisher
            Publisher p = new Publisher();

            //Create subscribers
            Print pr = new Print(p);
            Fax f = new Fax(p);
            Broadcast b = new Broadcast(p);

            //subscribe
            pr.Subscribe();
            b.Subscribe();

            //message
            Console.WriteLine("Message: ");
            string s = Console.ReadLine();

            //push message to subscribers
            p.Publish(s);

            Console.Read();
        }
    }

    class Publisher
    {
        public delegate void NotifyDelegate(string message);
        public event NotifyDelegate NotifyMessageToSubscribers;

        public void Publish(string message)
        {
            NotifyMessageToSubscribers(message);
        }
    }

    class Print
    {
        Publisher _publisher;

        public Print(Publisher p)
        {
            _publisher = p;
        }

        public void Subscribe()
        {
            _publisher.NotifyMessageToSubscribers += doPrint;
        }

        public void doPrint(string message)
        {
            Console.WriteLine("Printing: " + message);
        }
    }

    class Fax
    {
        Publisher _publisher;

        public Fax(Publisher p)
        {
            _publisher = p;
        }

        public void Subscribe()
        {
            _publisher.NotifyMessageToSubscribers += doFax;
        }

        public void doFax(string message)
        {
            Console.WriteLine("Faxing: " + message);
        }
    }

    class Broadcast
    {
        Publisher _publisher;

        public Broadcast(Publisher p)
        {
            _publisher = p;
        }

        public void Subscribe()
        {
            _publisher.NotifyMessageToSubscribers += doBroadcast;
        }

        public void doBroadcast(string message)
        {
            Console.WriteLine("Broadcasting: " + message);
        }
    }


Thursday, October 4, 2012

is a and has a relationship




House is a building (inheritance)

House has bathroom (composition)

Publisher - Subscriber

Scenario: There is a publisher and many subscribers (like Printer, Fax, Web server etc). Whenever Publisher gets file, it should notify the subscribers. And subscriber should have the authority to subscribe/not subscribe. How will you design this approach?

Solution:




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

namespace PublisherSubscriber
{
    class Program
    {
        static void Main(string[] args)
        {
            Publisher p = new Publisher();

            Console.WriteLine("Message: ");
            string s = Console.ReadLine();
            p.Publish(s);

            Console.Read();           
        }
    }

    class Publisher
    {
        public delegate void NotifyDelegate(string message);
        public event NotifyDelegate NotifyMessageToSubscribers;

        Print p ;
        Fax f ;
        Broadcast b ;

        public Publisher()
        {
             p = new Print(this);
             f = new Fax(this);
             b = new Broadcast(this);
        }

       
        public void Publish(string message)
        {
            NotifyMessageToSubscribers(message);
        }

    }

    class Print
    {
        Publisher _publisher;

        public Print(Publisher p)
        {
            _publisher = p;

            //Subscribe to publisher event
            Subscribe();
        }

        public void Subscribe()
        {
            _publisher.NotifyMessageToSubscribers += doPrint;
        }
       
        public void doPrint(string message)
        {
            Console.WriteLine("Printing: " + message);
        }
    }

    class Fax
    {
        Publisher _publisher;

        public Fax(Publisher p)
        {
            _publisher = p;

            //Subscribe to publisher event
            Subscribe();
        }

        public void Subscribe()
        {
            _publisher.NotifyMessageToSubscribers += doFax;
        }

        public void doFax(string message)
        {
            Console.WriteLine("Faxing: " + message);
        }
    }

    class Broadcast
    {
        Publisher _publisher;

        public Broadcast(Publisher p)
        {
            _publisher = p;

            //Subscribe to publisher event
            Subscribe();
           
        }

        public void Subscribe()
        {
            //Publisher.NotifyMessageToSubscribers += doBroadcast;
        }

        public void doBroadcast(string message)
        {
            Console.WriteLine("Broadcasting: " + message);
        }
    }
}