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
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 clause | Having 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 Clause | In 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]'