SQL Query Questions

1. Write a query for below scenario
Customer
Address

customer having first name santhosh and do not have any address.

CREATE TABLE [dbo].[Customer](

[Id] [int] NOT NULL,

[FName] [varchar](50) NULL,

[MName] [varchar](50) NULL,

 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[Address](

[Id] [int] NOT NULL,

[CustId] [int] NOT NULL,

[Add1] [varchar](50) NOT NULL,

[Add2] [varchar](50) NOT NULL,

 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

Ans :
select * from Customer
left join Address on Customer.Id = Address.CustId
where Address.Add1 is NULL and Customer.FName = 'Santhosh'

2. Write a  query for employee and dept problem where employee salary between 5000 to 10000 and name start with San. ?

select * from Employee
left join Department on Customer.DeptId = Department.Id
Where Salary between 5000 and 10000 and FName like 'San%'

3. Write a query for deleting duplicate customer and keeping 1 customer record .

DELETE n1 FROM Customer n1, Customer n2
Where n1.id > n2.id AND n1.FName = n2.FName

4.  Find the employee with second most salary

In MySQL this works

select *from employee 
group by salary 
order by  salary desc limit 1,1;

SELECT name, salary  
FROM employee  
WHERE salary = (  
    SELECT MAX(salary)  
    FROM employee  
    WHERE salary < (SELECT MAX(salary) FROM employee)  
);

Find the nth highest salary

SELECT salary 
FROM employee 
ORDER BY salary desc 
limit n-1,1

In SQL Server

;WITH T AS
(
SELECT *,
       DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk
FROM Employees
)
SELECT Name
FROM T
WHERE Rnk=2;

If Multiple employees are having 2nd highest salary

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);

Test this

CREATE NONCLUSTERED INDEX ix
  ON Employees(Salary)

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);

WITH T
     AS (SELECT *,
                DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rnk
         FROM   Employees)
SELECT Name
FROM   T
WHERE  Rnk = 2;

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT DISTINCT TOP (1) Salary
                 FROM   Employees
                 WHERE  Salary NOT IN (SELECT DISTINCT TOP (1) Salary
                                       FROM   Employees
                                       ORDER  BY Salary DESC)
                 ORDER  BY Salary DESC)

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT TOP 1 Salary
                 FROM   (SELECT TOP 2 Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) sel
                 ORDER  BY Salary ASC)