60 SQL Server Interview Question on Query for experienced

SQL SERVER

In this post, we will explore 60 SQL Server Interview Question on Query for experienced

Part 2 : sql-server-interview-questions-on-query-for-experienced

Consider These two tables for All Queries

Employess_Details Table

Table:Employess_Details

Department Table

Table:Department

1. What are alternative of Top clause in SQL Server

Answer

There are two alternative of Top Clause in SQL server

  • Row_number()
with Row_count as
(
   SELECT empid,empname,ROW_NUMBER() OVER(ORDER BY salary) as Row_num
   FROM Employess_Details
) 

select *from  Row_count where Row_num =3
  • Rowcount
SET ROWCOUNT 3

select * from Employess_Details order by salary

2.What is temp table and Temp variable

Answer

Temp VariableTemp table
Table variable is create using Declare statement and value inserted using SET, SELECT statement.

Declare @employee TABLE
(
EmpID int,
EmpName varchar(50)
)
The temp table is created using create table, select into command.

CREATE TABLE #employee
(EmpID int, EmpName VARCHAR(50))
Table variable create in TempdbThe temp table created in Tempdb
A table variable can be used as a parameter to the user-defined function and stored procedure.The temp table can not is passed as a parameter to the User-defined function and stored procedure.
Temp variable support indexes, which are created during declaring statement those includes (Primary key, unique key). We can’t add explicit indexes to the Temp variable.It supports primary key, unique key indexes as well as we can add indexes explicitly to the Temp table.
We cannot drop, truncate, alter a table variable.The temp table can be dropped and truncate,alter after creation.
We can’t apply transaction on temp variable example
Begin Transaction employee_trans
Rollback Transaction employee_trans

But Temp variables can be used with transaction.
The scope of temp variable within current batch/stored procedures. temp variables are drop automatically when batch or stored procedure completely executed.There are two types Temp tables

Local Temp table
It is created with #tablename. The scope of this is within-session in which it created. It drops automatically when the session out.

Global Temp table
It is created with ##tablename. It is not limited to the session in which it created but available to other sessions. Because it scope it global.

3.What is NULLIF in SQL server

Answer

NULLIF() methods take two expressions. If both the expressions are equal it returns NULL, else it returns the first expression.

Example

SELECT   NULLIF('hi', 'hi') Output_result;

#Output_result :NULL

4. Find Common records from Employee_Details and Department Tables.

Answer

when need to fetch common records from two tables then the inner join is used. Therefore we are using the inner join to fetch the common records in the below query.

SELECT 
e.empid,e.empname,d.depart_id,d.Department_name 
FROM Employess_Details e 
inner JOIN  Department d
ON e.depart_id = d.depart_id

5.What is Left Join. Find all employees and employees do not assign to any department.

Answer

Whenever the need to fetch all records from the left table that is Employess_Details and matching record or Null for none matching record from right tables Department, In case left join is used. So in the below Query, We have used left Join.

SELECT e.empid,e.empname,d.depart_id,d.Department_name 
FROM Employess_Details e 
left JOIN  Department d
ON e.depart_id = d.depart_id 

6.What is Right Join, Find all department and department does not have any employee

Answer

The right join is used when need fetches all records from right tables but matching records, null for none matching records. We have to find all the departments which have employees and the department does not have any employee assign. We are using the right join to achieve this

SELECT d.depart_id,d.Department_name 
FROM Employess_Details e 
right JOIN  Department d
ON e.depart_id = d.depart_id 

7. Select Employee which has joined in year 2017

Answer

SELECT empid,empname, dateofjoining FROM Employess_Details where year(dateofjoining) = 2017

8. Different ways to find Second highest salary

Answer

in these queries we are using the subquery to find the Second highest salary

----way 1

Select max(salary) from Employess_Details  
where salary not in (Select max(Salary) from Employess_Details) 

-----way 2 

SELECT max(salary) FROM Employess_Details WHERE salary < (SELECT max(salary) FROM Employess_Details); 

---way 3

SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM Employess_Details ORDER BY salary DESC) AS emp ORDER BY salary ASC 

9. Find the nth Highest salary using DENSE_RANK()

Answer

In this query, we are fetching the 3 third highest salary from the employee_details table, But this query can be used to find any of Nth highest salary to get this, we can change the values of sal_rank according to our requirement.

-----Find the nth Highest salary using DENSE_RANK() 

WITH CTE AS
(
SELECT empid ,empname,salary,DENSE_RANK() OVER(ORDER BY Salary DESC) sal_rank from Employess_Details
) 
SELECT empid ,empname,salary
FROM CTE
WHERE sal_rank = 3

-----------------

10. Find the nth highest salary using CTE and Row_Number()

Answer

In this query, we are fetching the 3 third highest salary from the employee_details table, But this query can be used to find any of Nth highest salary to get this, we can change the values of sal_rank according to our requirement.

-----Find the nth highest salary using Row_Number()


WITH CTE AS
(
SELECT empid ,empname,salary,Row_Number() OVER(ORDER BY Salary DESC) sal_rank from Employess_Details
) 
SELECT empid ,empname,salary
FROM CTE
WHERE sal_rank = 3

11.Find Average salary under a Manager,if mangId,empID present in same table.

Answer

select m.empid as Mang_id,
          m.empname as Mang_name, 
          avg(e.salary) as averg_salary
from Employess_Details e, 
     Employess_Details m
where e.mang_id = m.empid
group by m.empid, m.empname
order by m.empid

12. Query to find Nth highest salary without using subquery and CTE

Answer

Yes, we can use OFFSET to get the nth highest salary in SQL. But the offset work number of rows(N-1), Mean if we have to fetch 3 third highest salary the OFFSET will be calculated(3-1) and it will be 2, and so on for to find nth highest salary.

---------find Nth highest salary without using subquery and CTE.....


SELECT empid ,empname,salary from Employess_Details order by salary DESC
OFFSET 3 ROWS
FETCH NEXT 1 ROW ONLY


------------------------end here

13.Find employee count by department

Answer

---Find employee count in each department

Select e.depart_id, d1.Department_name as deptname,count(e.empid) empcount from Employess_Details e inner join Department d1 on e.depart_id=d1.depart_id  group by e.depart_id,Department_name,e.empname

-----------End here

14. What will output of this Query

Answer

This select query has only two columns selected, but we are using order clause desc by 3 columns, therefore there should be at least three columns selected

-----

select empname,salary from Employess_Details order by 3 desc;


--Result_output:
--Error: The ORDER BY position number 3 is out of range of the number of items in the select list

15. Find Employee name start with letter T,J,Y

Answer

----finding Employee name start with letter T,J,Y

select empname,empId from Employess_Details where empname like '[T,J,Y]%'

16. What will the output of this query if Table has record 6 records.

Answer

select sum(1) from Employess_Details 

----Result: 6

17.Can we insert values in the identity column, how give an example?

Answer

SET IDENTITY_INSERT Table_name  ON

INSERT INTO Table_name(col1, col2, col3,col4,col5,col6)VALUES(val1,val2,val3,val4,val5)  

SET IDENTITY_INSERT OFF

18. How to select distinct records from a table without using distinct keyword

Answer

Select empId,empname from Employess_Details1 group by empId,empname

19. How to fetch the top N records from a table

Answer

-----How to fetch the top N records from a table

SELECT N  *
FROM tablename
ORDER BY columname DESC;



---------Example Fetch Top  5 records from Employess_Details

SELECT TOP 5*
FROM Employess_Details
ORDER BY dateofjoining DESC;

20. What are the execution sequence of query

Answer

 FROM
  ON
 JOIN
 WHERE
 GROUP BY
 HAVING
 SELECT
 DISTINCT
 ORDER BY
 TOP

21.Find Employee whose name is ‘Jack’,’Rack’.

Answer

Select * from Employess_Details where empname in('Jack','Rack');

22.How to find an Employee whose salary between 12000 to 50000

Answer

Select empId, empname,salary from Employess_Details where salary between 12000 and 50000 

23. What will output of this query

Answer

It will fetch all the record which has value 3 in any of given columns

SELECT * FROM Employess_Details
WHERE '3' IN (empId, empname,salary,depart_Id)

24. How to convert date to string vice-versa in SQL Server

Answer

------------------- date to string

select CAST(GETDATE() as VARCHAR(50)) as Tdy_date_tostr

--------------------string to date

select cast('Feb 26 2021 12:53AM' as date) string_To_date

25. Which function of SQL server used to check date is of a given format

Answer

The IsDate() used to check if Date is given format If the format is matched it returns 1 is true else 0 is false.

SELECT  ISDATE('2017/01/06') AS "YYYY/MM/DD"

-----Result:1


26. What is different ways to Replace NULL values in SQL

Answer

-------one way: using ISNULL()

 Select empname, ISNULL(city,'no city ') as Gender
From Employess_Details


---------Second way: Using case


 Select empname, Case  When city IS NULL Then 'No city' Else city End as Gender
From Employess_Details


------Third Way: Coalesce()
 
Select empname, Coalesce(city, 'No city') as city
From Employess_Details

27. What is COALESCE in SQL Server

Answer

The SQL COALESCE function is used to handle the NULL in SQL Server. It is a syntactic shortcut of the Case statement. It accepts multiple arguments and evaluates them in sequence, It returns the first NON-NULL Values from the arguments(during evaluation action NULL values are replaced by user-defined value).

Example

SELECT COALESCE (NULL,'X','Y')
SELECT COALESCE (NULL,10,NULL,30)
SELECT COALESCE (NULL,1,NULL,NULL,1,'dev')

-----Result
  X
 10
 1


28. What is ISNULL() in SQL Server

The ISNULL() function is used to replace the NULL values with user passed values.

select ISNULL(NULL, 20)
select ISNULL(NULL, 'HI')

----Result 20
           HI

29. Data Type use to store video in Sql Server DB

Answer

  • Using VARBINARY type: It is used for size below 256K size
  • Using FileStream type: It is used in SQL Server 2008 and above
  • FileTables: it is introduced in SQL Server 2012, builds on top of SQL Server FILESTREAM technology

30.How to fetch emp name uppercase and city lower case in SQL

Answer

Select upper(empname),lower(city) from Employess_Details