SQl-Logical


https://rextester.com/ZHFH51082

Write an SQL query to fetch all the Employees who are also managers from EmployeeDetails table.

Let us first create the same table for an employee.

One of the columns in the same table contains the ID of the manger, who is also an employee for the same company. Now all the employees and their managers are present in the same table. Let us see how Self Join works in the real world scenario now.

What is a Self Join? Explain with Example - Interview Question of the Week #064 selfjoini1

— Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

What is a Self Join? Explain with Example - Interview Question of the Week #064 selfjoini2

In the result set, we can see that all the employees who have a manager are visible. Though the above solution has one limitation. The limitation is that we are not able to find out the top manager of the company in our result set. Inner join does not display any result which does not have a manager id in our scenario.

Next let us convert Inner Join to Outer Join and then see the result set.

SELECT E1.EMPLOLYEENAME,ISNULL(E2.MANAGERNAME,’TOP MANAGER’) AS MANAGERNAME
FROM EMPLOYEE E1
LEFT JOIN EMPLOYEE E2
ON E1.MANAGERID = E2.EMPLOPYEEID
What is a Self Join? Explain with Example - Interview Question of the Week #064 selfjoini3

Different ways to SQL delete duplicate rows from a SQL Table

We use the SQL MAX function to calculate the max id of each data row.

identify duplicate data

To remove this data, replace the first Select with the SQL delete statement as per the following query.

DELETE FROM [SampleDB].[dbo].[Employee]   
 WHERE ID NOT IN       
SELECT MAX(ID) AS MaxRecordID        
FROM [SampleDB].[dbo].[Employee]       
 GROUP BY [FirstName], [LastName], [Country]
 );

SQL delete duplicate Rows using Common Table Expressions (CTE)

We use a SQL ROW_NUMBER function, and it adds a unique sequential row number for the row.

In the following CTE, it partitions the data using the PARTITION BY clause for the [Firstname], [Lastname] and [Country] column and generates a row number for each row.

WITH CTE ([firstname],[lastname], [country],duplicatecount) AS (
SELECT [firstname], [lastname], [country], ROW_NUMBER() OVER(PARTITION BY [firstname], [lastname], [country]  ORDER BY id) AS DuplicateCount 
FROM [SampleDB].[dbo].[employee])
SELECT *FROM CTE;

In the output, if any row has the value of [DuplicateCount] column greater than 1, it shows that it is a duplicate row.

Remove Duplicate Rows using Common Table Expressions (CTE)

We can remove the duplicate rows using the following CTE.

WITH CTE([FirstName],[LastName],[Country],DuplicateCount)AS (SELECT [FirstName],[LastName],[Country],ROW_NUMBER() OVER(PARTITION BY [FirstName],[LastName],[Country] ORDER BY ID) AS DuplicateCount  
FROM [SampleDB].[dbo].[Employee]) 
DELETE FROM CTE WHERE DuplicateCount > 1

ROW_NUMBER function to SQL delete duplicate rows

  DELETE e1 FROM dbo.Table1 E
  INNER JOIN (SELECT * ,ROW_NUMBER() OVER(PARTITION BY col1 ,  col2 ,  col3 ,  col4 ,  col5 ,  col6 ,  col7 ORDER BY col1) AS RANKNUMBER 
  FROM dbo.Table1) E1
  ON E.col1=E1.col1
  WHERE RANKNUMBER>1

Write an SQL query to remove duplicates from a table without using a temporary table.
Ans. Here, we can use delete with alias and inner join. We will check for the equality of all the matching records and them remove the row with higher EmpId.

DELETE E1 FROM EmployeeDetails E1
INNER JOIN EmployeeDetails E2
WHERE E1.EmpId > E2.EmpId
AND E1.FullName = E2.FullName
AND E1.ManagerId = E2.ManagerId
AND E1.DateOfJoining = E2.DateOfJoining
AND E1.City = E2.City;

Write an SQL query to fetch only odd rows from the table.

Ans. In case we have an auto-increment field e.g. EmpId then we can simply use the below query

SELECT* FROM EmployeeDetails WHERE MOD(EmpId, 2) <> 0;

In case we don’t have such a field then we can use the below queries.

SELECT E.EmpId, E.Project, E.Salary 
FROM(SELECT*, Row_Number() OVER(ORDERBYEmpId) AS RowNumber 
FROM EmployeeSalary) E 
WHERE E.RowNumber % 2 = 1;

Write an SQL query to find the nth highest salary from table.

SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      )
ORDER BY Salary ASC;

Write SQL query to find the 3rd highest salary from a table without using TOP/limit keyword.

 we will use a correlated subquery. In order to find the 3rd highest salary, we will find the salary value until the inner query returns a count of 2 rows having the salary greater than other distinct salaries.

SELECT Salary
FROM EmployeeSalary Emp1
WHERE 2 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )

-------------------------------------
For nth highest salary-

SELECT Salary
FROM EmployeeSalary Emp1
WHERE N-1 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )

Write a query to find the third-highest salary from the EmpPosition table

SELECT TOP 1 salary
FROM(
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;

Write an SQL query to fetch the last five records from a table.

SELECT * FROM Worker WHERE WORKER_ID <=5
UNION
SELECT * FROM (SELECT * FROM Worker W order by W.WORKER_ID DESC) AS W1 
WHERE W1.WORKER_ID <=5;

Write an SQL query to print the name of employees having the highest salary in each department.

SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary 
from (SELECT max(Salary) as TotalSalary, DEPARTMENT from Worker group by DEPARTMENT ) as TempNew 
Inner Join Worker t 
on TempNew.DEPARTMENT = t.DEPARTMENT 
 and TempNew.TotalSalary = t.Salary;

SQL query to find second highest salary?

Consider below simple table:

Below is simple query to find the employee whose salary is highest.

SELECT name, MAX(salary) as salary FROM employee 

We can nest the above query to find the second largest salary.

SELECT name, MAX(salary) AS salary
  FROM employee
 WHERE salary < (SELECT MAX(salary)
                 FROM employee); 
SELECT name, MAX(salary) AS salary 
FROM employee 
WHERE salary IN
(SELECT salary FROM employee MINUS SELECT MAX(salary) 
FROM employee); 

https://www.techbeamers.com/sql-query-questions-answers-for-practice/

https://javarevisited.blogspot.com/2016/01/4-ways-to-find-nth-highest-salary-in.html

https://www.java67.com/2013/04/10-frequently-asked-sql-query-interview-questions-answers-database.html

SQL Query to find Max Salary from each department.

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

These questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case, you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well.  Here is the query

SELECT DeptName, MAX(Salary) 
FROM Employee e 
RIGHT JOIN Department d 
ON e.DeptId = d.DeptID 
GROUP BY DeptName;

18) State the difference between UNION and UNION ALL?

UNION – It is used to select the related information. It is similar to that of JOIN command.
UNION ALL – It is similar to that of UNION command, but it selects all the values.
It does not remove the values from the table but will retrieve the data.

Write an SQL query to fetch three max salaries from a table.

SELECT distinct Salary 
from worker a 
WHERE 3 > = (SELECT count(distinct Salary) 
              from worker b 
              WHERE a.Salary <= b.Salary) 
order by a.Salary desc;

Write an SQL query to fetch three min salaries from a table.

SELECT distinct Salary 
from worker a 
WHERE 3 >= (SELECT count(distinct Salary) 
            from worker b 
            WHERE a.Salary >= b.Salary) 
order by a.Salary desc;

Write an SQL query to show the last record from a table.

Select * from Worker 
where WORKER_ID = (SELECT max(WORKER_ID) 
                     from Worker
                  );

Write an SQL query to fetch the list of employees with the same salary.

Select distinct W.WORKER_ID, W.FIRST_NAME, W.Salary 
from Worker W, Worker W1 
where W.Salary = W1.Salary and W.WORKER_ID != W1.WORKER_ID;

Suppose a Student table has two columns, Name and Marks. How to get names and marks of the top three students.

SELECT Name, Marks 
FROM Student s1 
where 3 <= ( SELECT COUNT(*) 
              FROM Students s2 
               WHERE s1.marks = s2.marks
           )

Write an SQL query to fetch the names of workers who earn the highest salary.

SELECT FIRST_NAME,SALARY from Worker 
WHERE SALARY=(SELECT max(SALARY) from Worker);

Write an SQL query to fetch departments along with the total salaries paid for each of them.

SELECT DEPARTMENT, sum(Salary) from worker group by DEPARTMENT;

DECLARE @ATT TABLE (EMP INT,DATEPRESENT VARCHAR(50)) INSERT @ATT VALUES (1,’1,2,3′),(2,’2,4,5,6′),(3,’1,2,5,4,7,9′)

SELECT *, LEN(DATEPRESENT) – LEN(REPLACE(DATEPRESENT,’,’,”) )+1 FROM @ATT

DECLARE @TBL TABLE(ID VARCHAR(100),NAME VARCHAR(100))
INSERT @TBL VALUES(1,'AA'),(2,'BB'),(3,'CC')

SELECT * FROM @TBL 

UPDATE @TBL
SET ID=NAME,NAME=ID

SELECT * FROM @TBL