SQL Server Interview Questions On Query for Experienced

SQL SERVER

Part 1 : SQL Server Interview Questions On Query for Experienced

31.How to find the duplicate from a table

Answer

Way 1 : Using group by and having clause

SELECT 
    empid, 
    empname,salary,city, COUNT(*) occurrences
FROM Employess_Details1
GROUP BY
     empid, 
    empname,salary,city
HAVING 
    COUNT(*) > 1;

Way 2: using Row_number():

---Query start here

SELECT * FROM
(
    SELECT empid, 
    empname,salary,city, Row_Number() OVER(PARTITION BY empName, salary ORDER By salary)
        AS salaryrank 
        FROM Employess_Details1
) AS tabl WHERE salaryrank>1

--end here

32.How to delete duplicate rows from a table in SQL

Answer

-----start here
	
WITH myCTE AS (
    SELECT 
        empid, empname,salary,city, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                empid, empname,salary,city
            ORDER BY 
               empid, empname,salary,city
        ) dup_count
     FROM 
        Employess_Details1
)
DELETE FROM myCTE
WHERE dup_count > 1;

----end here

33.What are the string function in SQL server

Answer

  • REPLACE(): It replaces all the occurrences old string with a new string.
  • LEN(): It returns the length of the passed string.
  • LOWER(): It converts the given string in the lower case letter.
  • UPPER(): It converts the given string in the upper case letter.
  • SUBSTRING(): It extracts some characters from the given string.
  • LTRIM(): It trims the left space of a given string.
  • RTRIM() :It trims right-space of given string.
  • CONCAT(): It merges the passed strings.

34.The output of the query if depart_id(1,2,3) record exists in employee_details and Department tables.

Answer

As per assumption it would returns record except depart_id Not in (4, NULL).But This query will not return any rows. It returns an empty result set.Even the data for depart_id (1,2,3)exist in employee_details table

SELECT * FROM Employess_Details WHERE depart_id Not in (4, NULL)

---Output_Result: nothing

35.What will be output of this query

Answer

select rowcount(*)

----Result: 1


SELECT 16 +20

----Result: 36


Select 'hello'

----Result: hello


Select ('hello')

----Result: hello


Select sum(3*4)

----Result: 12



Select power(3,4)

----Result: 81



36.What will be output of following Query

Answer

This query will fetch all records except records that have depart_id NULL.

SELECT * FROM Employess_Details WHERE depart_id <>4

if need to fetch the record which has NULL depart_id We have to use IS NULL and <>(not equal) operator.

SELECT * FROM Employess_Details WHERE depart_id IS NULL or  depart_id <>4

37.Write a SQL query to fetch only even row from a table

Answer

-------query start here

---way 1: based on Row_Number()


SELECT e.empid,e.empname, e.salary,e.city
FROM (
SELECT *, Row_Number() OVER(ORDER BY empid) AS RowNumber
FROM Employess_Details
) e
WHERE E.RowNumber % 2 = 0;



---Way 2:based on EmpID we can fetch this way

select * from Employess_Details
where empid %2 =0  

--end here

38.Get employee manager_name if managerId and employeeID present in the same table

Answer

In this query, self-join is used, if we have a table that references itself then self-join is used.Employess_Details table joins to itself to find the employees which are Manager too( Manager Id, Manager Name).In Employess_Details table mang_id is a reference in emp_id so it know as referencing iteself.It is perfect example of self join.

---------------Get employee manager_name if managerId


SELECT e.empid,e.empname, m.empname as ManagerName FROM Employess_Details e, Employess_Details m WHERE e.mang_id = m.empid



-----End here

39.Query to find Manager_ID, ManagerName, Depart_ID, Number of Employee under a Manager

Answer

--- Query to find Manager_ID, ManagerName

SELECT mang.empid as mang_Id,mang.empname as mang_name,
mang.depart_id ,COUNT (mang.empid) AS number_of_employees
FROM Employess_Details e
JOIN Employess_Details mang
ON e.mang_id = mang.empid
GROUP BY mang.empid,mang.empname,mang.depart_id;

------end here

40.Write a query to get detail of employee have not manager.

Answer

----query to get detail of employee have not manager.

SELECT e.empid,e.empname,mang.empid as mang_Id,mang.empname as mang_name
FROM Employess_Details e
left JOIN Employess_Details mang
ON e.mang_id = mang.empid

------query End here

41. Write a query to find employee join between 2016-01-01 to 2018-05-06

SELECT * FROM Employess_Details WHERE dateofjoining BETWEEN '2016-01-01' AND '2018-05-06'

42.How many rows will display in the output result when If employee_Details has 6 records and the department has 5 records

Answer

The Number of row display will be 30, because it produced the Cartesian produced of both tables.

select * from employee_Details,department

43. How to achieve if—then—else logic in SQL Server

In Sql Server this can be achieve using two ways

  • Case statement
  • IIF() Function

44.Give Tables have one column name salary with following data write a query to if the salary is divisible by 3 then add 3 else add 2

3, 2, 3, 3, 3, 2, 3, 2, 2, 3, 2, 3, 2

Answer

Query using case statement

update Table_name set salary = case when salary%3 = 0 then salary+3 else salary+2 end;

45. Write a Query to find the servicing year of an employee

Answer

To find the servings year we are using the datediff() function and find the year’s difference between today’s date and joining date.

---Query to find servicing year

select empname, datediff(year,dateofjoining, cast(GETDATE() as date)) as year_served FROM Employess_Details

------End here

46. Find the maximum, minimum salary employee by department

Answer

-------query to find max,min salary

Select d1.Department_name,e.depart_id,max(e.salary) as max_salary_by_dept,min(e.salary) min_salary_by_dept
from Employess_Details e
right join Department d1 on e.depart_id=d1.depart_id  
group by e.depart_id,Department_name

---end here

47.What is pivot in SQL Server give an example

Answer

The pivot operator uses to convert(transpose) rows to Columns. It introduced in SQL server 2005. It transposes unique values from one column into multiple columns in the output and performs aggregation on any remaining column values.

All these perform in three steps

  • Separate the rows
  • Aggregate required data using aggregate function of SQL Sum(), Max(), Min(),Avg() etc.
  • The final step, convert aggregate data into columns.

Example

SELECT * FROM   
(
---separating rows
    SELECT 
       
       department.Department_name,emp.empid,emp.dateofjoining
        
    FROM 
        Employess_Details emp
        INNER JOIN department
            ON emp.depart_Id = department.depart_Id
	group by   department.Department_name,emp.empid,emp.dateofjoining
) t 
PIVOT(

------Aggregate required data

    COUNT(empid) 
    FOR Department_name IN (

-------converting rows into columns
        [IT], 
        [Admin],[HR])

) AS res_pivot_table;

48.What is merge/upsert statement in SQL

Answer

The merge statement in SQL Server also knows as upsert. As usual, practice updating records in the table, first search the records using select statement if records exist then update records, Insert if does not exist. It is known as the Select-update-Insert pattern. But the disadvantage of this it required two round-trips to the database instead of one.

The merge introduced in SQL Server 2008, update records if exist and insert if do not exist in a single trip from source to a target table.

MERGE TARGET_TABLE USING SOURCE_TABLE 
USING ON CONDITION

----when match then update the rows in target table

WHEN MATCHED then:
run update statement to update row if exist

----when not matched insert the rows in target table

WHEN NOT matched  BY TARGET then:
run insert statement to insert row does not exist


---- if not matched rows with source then delete rows

WHEN NOT MATCHED BY SOURCE THEN
 DELETE;

Example

----T  is target table 
---- s is source table

MERGE [Employess_Details1]  t
USING  [Employess_Details]  s
ON (s.empid = t.empid)

WHEN MATCHED  THEN

  ---updating 

  UPDATE SET t.empname= s.empname,
  t.salary = s.salary


WHEN NOT MATCHED BY TARGET THEN

---inserting

INSERT ([empid],[empname],[depart_Id] ,[Mang_Id],[salary], [dateofjoining],[city])

VALUES(s.[empid],s.[empname],s.[depart_Id],s.[Mang_Id],
 s.[salary], s.[dateofjoining],s.[city])


WHEN NOT MATCHED BY SOURCE THEN
 DELETE;


49. How to select row by range between 3 to 7 in SQL Server

Answer

-----way 1: using row_number() 

SELECT * FROM
(
   SELECT ROW_NUMBER() OVER(ORDER BY salary) Row_num,
   * FROM Employess_Details
) result_set
WHERE Row_num >3 AND Row_num <7


-----way 2: using offset

select * from Employess_Details order by salary offset 3 rows fetch next 3 rows only

50.What is SQL Server IIF()

The IIF() is the short syntax of writing a case statement also know as the if-else statement in an SQL server. it is introduced in SQL Server 2012.

Syntax to use IIF

IIF(expression,true,false)

  • Expression: a boolean expression that returns true or false.
  • True: If the boolean expression evaluated to True, It returns True.
  • False: If the boolean expression evaluated to False, It returns False.

Example

SELECT IIF(5> 3, 'TRUE', 'FALSE' )

-------Result: TRUE

51.What will be output of this query

We cannot pass NULL to both (True and False) parameters of the IIF Statement, at least one parameter must be some expression. otherwise, we will get the following error.

SELECT  IIF(200 > 100, NULL, NULL) AS Result;

Error :At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.

52. How to search any value in SQL if don’t have exact values to search for

Answer

We can search any values in the SQL server when we do not have an exact value by using the Like operator with the wild card character (%) and _(underscore), set of character[charlist],[^charlist]

53.What will happen, miss out where clause while updating the query

Answer

All the rows of given table will be modified in .

54.What is a sequence in SQL. Generate a sequence of even numbers 0 to 50.

The Sequence object introduces in SQL Server 2012, generates seuence numeric values in ascending and descending order according to define the interval and can be configure to restart when exhausted. Unlike the IDENTITY column, it can be used independently,it does not attach to any table, values can be accessed without insertion of the new row using NEXT VALUE FOR

Example

;with even_seq 
AS  
(  
SELECT 0 [SEQUENCE]  
  
UNION ALL  
  
SELECT [SEQUENCE] + 2 FROM even_seq WHERE [SEQUENCE] <50
)  
  
SELECT * FROM even_seq

55.What is trigger In SQL SERVER

Answer

A Trigger in SQL SERVER is a special kind of stored procedure that executes in response to some event that happens in the database (Insert, update, deletes, create).

There are three types of triggers in SQL

  • DML Trigger: DML trigger run automatically response to DML statement(Insert,Update,Delete)
    • After trigger
    • Instead of trigger

  • DDL Trigger: The DDL triggers run automatically in response to the Server or database event rather than the table. This event occurs when Create, Alter, Grant, Denied, Revoke command used on the database.

  • Logon Trigger: “Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established” Microsoft Doc

56.What are Magic tables in SQL SERVER

Answer

The magic tables are virtual tables are managed by SQL Server in the context of a trigger that holds the temporary information of recently inserted or deleted. The two special tables are created in response to the DML statement (INSERT, DELETE, UPDATE) also known as magical tables.

There are two types of magical tables in the SQL server

  • INSERTED Table: It holds the rows which are inserted or updated by INSERT and UPDATE statements.
  • DELETED Table: It holds the row deleted by DELETE or UPDATE statement.When use UPDATE on any row first it internally calls deletion of the existing row and insertion of a new row.

57.What will Output of these statement

Answer

SELECT NULL+7
SELECT NULL+'test'

--Output: NULL
--Output: NULL

58.How is @SPID SQL Server

It return SessionID of the current User.

59.What is Different between Where and Having clause

ANSWER

Where clauseHaving Clause
WHERE clause used with from clause and applied on each row in the table.HAVING clause used with GROUP BY, filter the records from the group
It is used in SELECT, UPDATE, DELETE
to filter record based on condition
It is only used with SELECT query.
In the case of syntax, WHERE clause is used before GROUP BY ClauseIn the case of syntax, the HAVING clause is used after GROUP BY Clause
WHERE use with HAVING clause in the SELECT statement with aggregate functions apply the filter on each row that form a group.Having clause applied on group create by WHERE clause to filter the records from the group based on condition.

60. How to fetch EmployeeName which name start and End with a vowel.

Answer

We will use LIKE operator to fetch employee name

select empid, empname from Employess_Details where empname like '[aeiou]%[aeiou]'