Find nth highest and lowest salary of an Employee

This question has been asked many times in interview to candidate that find or fetch nth highest and lowest salary of an Employee like 2nd, 3rd, 4th, 5th… highest and lowest salary of an Employee from Employee table. This question some times also asked as get nth highest and lowest salary of an Employee without using TOP and sub query. Let’s see solutions for all this questions in this article.

Suppose we have following table of Employee with Name and Salary of Employee.

Query to get nth(3rd) highest salary of an Employee

Suppose we want to get 3rd highest salary of an Employee so query should be like.

select top 1 Salary as '3rd highest salary' from (select distinct top 3 Salary from Employee order by Salary desc)   a order by Salary asc

Let me illustrate above query as here we want to fetch nth(3rd) highest salary of an Employee so first inside sub query we find all top n(3) distinct records using order by descending which result in to top n(3) records with highest salary and than we fetch top 1 record among all these records with order by ascending which full fill our requirement to find nth(3rd) highest salary of an Employee.

Query to get nth(3rd) lowest salary of an Employee

select top 1 Salary as '3rd lowest salary' from (select distinct top 3 Salary from Employee order by Salary asc)   a order by Salary desc

See here we want to find nth(3rd) lowest salary of an Employee so our logical of fetching data will be reversed so first inside sub query we find all top n(3) distinct records using order by ascending which result in to top n(3) records with lowest salary and than we fetch top 1 record among all these records with order by descending which full fill our requirement to find nth(3rd) lowest salary of an Employee.

Query to get nth(3rd) highest salary of an Employee without using TOP and Sub Query

This question also can twisted to find or fetch nth(3rd) highest salary of an Employee without using TOP and Sub Query so solutions can be using With CTE(Common Table Expressions) as below query.

WITH Salaries AS( SELECT distinct Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS 'RowNum'     FROM Employee)SELECT Salary FROM Salaries WHERE RowNum = 3

in above query we find salary in descending order using With CTE(Common Table Expression) than we used RowNum = n(3) which results into nth(3rd) highest salary of an Employee without using TOP and Sub Query.

Query to get nth(3rd) lowest salary of an Employee without using TOP and Sub Query

To find or fetch nth(3rd) lowest salary of an Employee without using TOP and Sub Query so solutions can be using With CTE(Common Table Expressions) as below query.

WITH Salaries AS(SELECT distinct Salary, ROW_NUMBER() OVER(ORDER BY Salary asc) AS 'RowNum'     FROM Employee)SELECT Salary FROM Salaries WHERE RowNum = 3

in above query we find salary in ascending order using With CTE(Common Table Expression) than we used RowNum = n(3) which results into nth(3rd) lowest salary of an Employee without using TOP and Sub Query.

What is cookie? ADVANTAGES AND DISADVANTAGES OF COOKIES

What is cookie?
A cookie is a small piece of text file stored on user's computer in the form of name-value pair. Cookies are used by websites to keep track of visitors e.g. to keep user information like username etc. If any web application using cookies, Server send cookies and client browser will store it. The browser then returns the cookie to the server at the next time the page is requested. The most common example of using a cookie is to store User information, User preferences, Password Remember Option etc.It is also one of the common and mostly asked interview questions.

Some facts about Cookie

Here are a few facts to know about cookies:

Cookies are domain specific i.e. a domain cannot read or write to a cookie created by another domain. This is done by the browser for security purpose.
   
Cookies are browser specific. Each browser stores the cookies in a different location. The cookies are browser specific and so a cookie created in one browser(e.g in Google Chrome) will not be accessed by another browser(Internet Explorer/Firefox).
 
Most of the browsers store cookies in text files in clear text. So it’s not secure at all and no sensitive information should be stored in cookies.
 
Most of the browsers have restrictions on the length of the text stored in cookies. It is 4096(4kb) in general but could vary from browser to browser.

Some browsers limit the number of cookies stored by each domain(20 cookies). If the limit is exceeded, the new cookies will replace the old cookies.

Cookies can be disabled by the user using the browser properties. So unless you have control over the cookie settings of the users (for e.g. intranet application), cookies should not be used.

Cookie names are case-sensitive. E.g. UserName is different than username.
Advantages of using cookies
Here are some of the advantages of using cookies to store session state.

Cookies are simple to use and implement.

Occupies less memory, do not require any server resources and are stored on the user's computer so no extra burden on server.

We can configure cookies to expire when the browser session ends (session cookies) or they can exist for a specified length of time on the client’s computer (persistent cookies).

Cookies persist a much longer period of time than Session state.

Disadvantages of using cookies

Here are some of the disadvantages:

As mentioned previously, cookies are not secure as they are stored in clear text they may pose a possible security risk as anyone can open and tamper with cookies. You can manually encrypt and decrypt cookies, but it requires extra coding and can affect application performance because of the time that is required for encryption and decryption

Several limitations exist on the size of the cookie text(4kb in general), number of cookies(20 per site in general), etc.

User has the option of disabling cookies on his computer from browser’s setting .

Cookies will not work if the security level is set to high in the browser.
 
Users can delete a cookies.

Users browser can refuse cookies,so your code has to anticipate that possibility.
 
Complex type of data not allowed (e.g. dataset etc). It allows only plain text (i.e. cookie allows only string content)

How to find whether a string contains any of the special characters?

 Regex RgxUrl = new Regex("[^a-z0-9]");
                    blnContainsSpecialCharacters = RgxUrl.IsMatch(stringToCheck);

========================================================

public static bool IsSpecialCharacters(this string stringToTest)
       {
           //const string charSet = "[^a-z0-9]";
             const string charSet = "[^a-zA-Z0-9]";

           //Regex RgxUrl = new Regex("[^a-z0-9]");
           //blnContainsSpecialCharacters = RgxUrl.IsMatch(stringToCheck);

           return Regex.Match(stringToTest, @"^[" + charSet + @"]+$").Success;
       }

MVC4 site publish to Somee

Run As Admin mode in Visual stu..

Publish method: FTP

Server: ftp://[YOUR USERNAME].somee.com

Site path: /www.[YOUR USERNAME].somee.com/ 

Passive mode: Checked 

User name: [YOUR USERNAME] 

Password: [YOUR PASSWORD] 

how to paste in cmd prompt using keyboard.. without mouse

 ALT+ Space  E  P : ALT+Space for System Menu E for Edit sub-menu P for Paste item ... njoy..:)

Downloading Files in ASP.NET MVC

namespace My.Models

{

    public class DataClasses

    {

        public List<FileNames> GetFiles()

        {

            List<FileNames> lstFiles = new List<FileNames>();

            DirectoryInfo dirInfo = new DirectoryInfo(HostingEnvironment.MapPath("~/Files"));

            int i = 0;

            foreach (var item in dirInfo.GetFiles())

            {

                lstFiles.Add(new FileNames()

                {

                    FileId = i + 1,

                    FileName = item.Name,

                    FilePath = dirInfo.FullName + @"\" + item.Name

                });

                i = i + 1;

            }

            return lstFiles;

        }

    }

    public class FileNames

    {

        public int FileId { get; set; }

        public string FileName { get; set; }

        public string FilePath { get; set; }

    }

}

namespace My.Controllers

{

    public class ReportsController : Controller

    {

        DataClasses objData;

        public ReportsController()

        {

            objData = new DataClasses();

        }

        //

        // GET: /Reports/

        public ActionResult Index()

        {

            var files = objData.GetFiles();

            return View(files);

        }

        public FileResult Download(string id)

        {

            int fid = Convert.ToInt32(id);

            var files = objData.GetFiles();

            string filename = (from f in files

                               where f.FileId == fid

                               select f.FilePath).First();

            string contentType = "application/pdf";

            //Parameters to file are

            //1. The File Path on the File Server

            //2. The content type MIME type

            //3. The parameter for the file save by the browser

            return File(filename, contentType, "Report.pdf");

        }

    }

}

model IEnumerable<my.models.filenames>

    @{

        ViewBag.Title = "Index";

    }

    <h2>Index</h2>

    <p>

        @Html.ActionLink("Create New", "Create")

    </p>

    <table>

        <tr>

            <th>

                FileId

            </th>

            <th>

                FileName

            </th>

        </tr>

        @foreach (var item in Model)

        {

            <tr>

                <td>

                    @Html.DisplayFor(modelItem => item.FileId)

                </td>

                <td>

                    @Html.DisplayFor(modelItem => item.FileName)

                </td>

                <td>

                    @Html.ActionLink("Download", "Download", new { id = item.FileId })

                </td>

            </tr>

        }

    </table>

The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at

https://www.google.com/settings/security/lesssecureapps

Not Working https://ci4.googleusercontent.com/proxy/1G6grh1raakXp8PvPCJANFOKCJ4nFF3nuJoQB4sGWQ94Dm0Mqs2rQ8WgrOlEAqRPe54_zJ3s48ydA8foxj3S7ye7o3rlGCBYE1LCc0EGCFFRXRO2fiZxfgkjoOk=s0-d-e1-ft#https://IMAGEURL

This is something related to CACHING concept. suppose, you have recently deployed your php code on your server but you forgot to upload images. you tested once with your email logic. your system generated an HTML email. When this email will hit the gmail server GoogleImageProxy will try to fetch and store the images from your site to its own proxy server. while fetching the images, GoogleImageProxy found some 404 statuses against your missing images and 403 against some protected images. GoogleImagesProxy has stored these statuses into its own proxy server.

Now tried to open your email, you noticed some 404 statuses against your images. This is something understandable. You immediately realized that you forgot to upload some images, so you uploaded them to your server. and also you have fixed some permissions against protected images.

You are all done now. Now you try to run your php-email script once again. As a result you receive another email in your Gmail or Hotmail inbox. you had fixed all the issues with your images. Now the images must be displayed in your email content. but you are still unable to see the images.

Ah, possibly you forgot to clear your browser's cache. Clear your browser's cache and load the gmail or hotmail page once again. But the result will be still the same. Try to apply dozens of fixes/patches and try to run your php-email script a thousands time. But the result will be still the same. No improvement.

THE REAL PROBLEM

What the hell is going on? Let me explain it to you. Go to your access log and try to find requests from GoogleImageProxy. You'll be surprised to see that there will be only 2 or 3 three requests from GoogleImageProxy depending on the number of different images used in your email. GoogleImageProxy never tried to fetch images Even after you have fixed the issues with your images by uploading missing images and setting permissions for protected images. Why? Clearing your browser's cache has no impact. GoogleImageProxy will never fetch the fresh images even for your newer email because the images are now cached into GoogleImageProxy along with their last status code and not cached in your own browser's.

GoogleImageProxy has set its own expiry date for the images. I think one month. so now the fresh copy of images will be fetch after expiry date. I mean after one month. You can not force GoogleImageProxy to fetch the images. But its important for you to display images in your email. What can be the solution?

THE SOLUTION

Following is the only way to force GoogleImageProxy to fetch your images

• Rename your images to something else with png, jpg or gif extensions only.
• Don't use any kind of query string in your image url like ?t=34343
• your image must include png, jpg or gif as an extension.
• your image url must be mapped onto your image directly.
• If you need to use some proxy url for your protected images then your response must include the proper header like Content-Type: image/jpeg
• File extension and content-type header must match
• Status-code must be 200 instead of 403, 500 etc

IMPORTANT NOTE

Try to repeat the whole process for every run of php-email script. because every time GoogleImageProxy will cache your images and you'll have to repeat the same process for every new try.