Some tricky SQL queries Part 2 (Interview Questions)

1.Update Employee salary Department wise in single query

update tblEMP
set salary=
case when dept='HR' then
salary + (salary*10)/100 
when dept='IT' then
salary + (salary*20)/100 
when dept='Admin' then
salary + (salary*5)/100 
when dept='SAP' then
salary + (salary*2)/100 
end

2.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;

3.Get 3 Min salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

4. 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;

5. Delete duplicate rows in a table

delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

Drop all the tables, stored procedures, triggers, constriants and all dependencies from SQL Server 2005

Drop all the tables, stored procedures, triggers, constriants and all dependencies from SQL Server 2005

Code

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Sorting asp.net Gridview using Jquery

Tablesorter is a Jquery pulgin easy to use. Now you can sort your data without postbacking the page.It gives the client side sorting functionality.
Click to check more on Tablesorter

Code

Default.aspx

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="jqueryTableSort._Default" %>


    
    
    
    


    
      Sort gridview using Jquery plugin Table Sorter
    
  
    
       
            
                
                
            
        
    
    

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace jqueryTableSort
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) 
            {
                BindGrid();
                
            }

        }
        #region BIND GRID
        private void BindGrid() 
        {
            CountryDataData objData = new CountryDataData();
            GridView1.DataSource = objData.GetCountryDataList();
            GridView1.DataBind();
            GridView1.UseAccessibleHeader = true;
            GridView1.HeaderRow.TableSection = TableRowSection.TableHeader; 

        }
        #endregion
    }
}

CountryData.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace jqueryTableSort
{
    public class CountryDataData
    {
        public int Id { get; set; }
        public string CountryName { get; set; }
        public List GetCountryDataList()
        {
            return new List
            {
                new CountryDataData{Id=1,CountryName="India"},
                new CountryDataData{Id=2,CountryName="Australia"},
                new CountryDataData{Id=3,CountryName="UK"},
                new CountryDataData{Id=4,CountryName="UAE"},
                new CountryDataData{Id=5,CountryName="Bangladesh"},
                new CountryDataData{Id=6,CountryName="Austria"},
                new CountryDataData{Id=7,CountryName="Japan"},
                new CountryDataData{Id=8,CountryName="China"},
                new CountryDataData{Id=9,CountryName="Dubai"},
                new CountryDataData{Id=10,CountryName="South Africa"},
                new CountryDataData{Id=11,CountryName="Mexcico"},
                new CountryDataData{Id=12,CountryName="Merryland"},
                new CountryDataData{Id=13,CountryName="USA"},
                new CountryDataData{Id=14,CountryName="Peru"},
                new CountryDataData{Id=15,CountryName="Nepal"},
                new CountryDataData{Id=16,CountryName="Pakistan"},
                new CountryDataData{Id=17,CountryName="Srilanka"},
                new CountryDataData{Id=18,CountryName="Vietnam"},
                new CountryDataData{Id=19,CountryName="Westindies"},
                new CountryDataData{Id=20,CountryName="England"},
                new CountryDataData{Id=21,CountryName="Afganistan"},
                new CountryDataData{Id=22,CountryName="Russia"},
                new CountryDataData{Id=23,CountryName="Newzeland"},
                new CountryDataData{Id=24,CountryName="Timore"},
                new CountryDataData{Id=25,CountryName="Canada"}

            };

        }

    }
}

Download Code

Creating ZIP and UNZIP files in ASP.NET using DotNetZip library.

DotNetZip is easy to use free class library for ziping and extracing the zip files.
Check this DotNetZip for more information.

Example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ionic.Zlib;
using Ionic.Zip;
using System.IO;




namespace ZipFilesAspDotnet
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        #region Download ZIP File
        private void DownloadFile()
        {
            if (FileUpload1.HasFile)
            {
                //file upload folder App_Data
                string _fileName = FileuploadUtility.UploadFile(FileUpload1, Server.MapPath("~/App_Data/"), Session.SessionID);
                string[] _zip_fileName = _fileName.ToString().Split('.');
                Response.Clear();
                Response.ContentType = "application/zip";
                Response.AddHeader("content-disposition", "filename=" + "download_" + _zip_fileName.ToString() + ".zip");

                using (ZipFile zip = new ZipFile())
                {
                    zip.AddEntry(_fileName.ToString(), File.ReadAllBytes(Server.MapPath("~/App_Data/" + _fileName.ToString())));
                    zip.Save(Response.OutputStream);
                }
            }

        }
        #endregion

        #region EXTRACT THE ZIP FILE
        private void ExtractZipFile() 
        {
            string _fileName = FileuploadUtility.UploadFile(FileUpload1, Server.MapPath("~/App_Data/"), Session.SessionID);

            using (ZipFile zip1 = ZipFile.Read(Server.MapPath("~/App_Data/" + _fileName.ToString())))
            {
                
                foreach (ZipEntry e in zip1)
                {
                    //destination folder zipfiles
                    e.Extract(Server.MapPath("~/ZipFiles/"), ExtractExistingFileAction.OverwriteSilently);
                }
            }

        }
        #endregion

        protected void Button1_Click(object sender, EventArgs e)
        {
            DownloadFile();

        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            ExtractZipFile();
        }
    }
}

Download Sample

Change DB owner in SQL Server Database

Change db owner in sql server 2005 database.

Example

DECLARE @old sysname, @sql varchar(1000)

SELECT

 @old = 'oldOwner_CHANGE_THIS'

 , @sql = '

 IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

 WHERE

     QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

     AND TABLE_SCHEMA = ''' + @old + '''

 )

 ALTER SCHEMA dbo TRANSFER ?'

EXECUTE sp_MSforeachtable @sql

Dotnet Codes

Dotnet Codes

Difference between ref and out parameters in c#

The difference between ref and out parameters is at the language level. In case of ref you must have to assign it as in "Example by ref" string val is assigned.
In case of out you don't need to assign an out parameter before passing it to a method. But the out parameter in that method must assign the parameter before returning.

Example

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
        Response.Write("Example by ref");
        string val = "hello";
        ExampleByRef(ref  val);
        Response.Write(val);
        Response.Write("Example by out
");
        string vals;
        ExampleByOut(out  vals);

    }
    public static void ExampleByRef(ref string val)
    {
        if (val == "hello")
        {

            System.Web.HttpContext.Current.Response.Write("Example by ref case");
        }
        else
        { System.Web.HttpContext.Current.Response.Write("Example by ref else response"); }
        val = "hi";
    }
    public static void ExampleByOut(out string vals)
    {
        vals = "Example by Out case
";
        System.Web.HttpContext.Current.Response.Write(vals);
    }
     
 }

Output
Example by ref
Example by ref case
hi
Example by out
Example by Out case