31.How to find the duplicate from a table
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
-----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
- 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.
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
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
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
-------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
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
--- 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.
----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
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
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
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
47.What is pivot in SQL Server give an example
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.
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
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;
----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
-----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
- 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.
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
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
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
;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
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
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
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
|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.
We will use LIKE operator to fetch employee name
select empid, empname from Employess_Details where empname like '[aeiou]%[aeiou]'