SQL - Query Related Interview Questions

SQL Query to find Max Salary from each department.
SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

Write an SQL Query to check whether date passed to Query is date of given format or not.
SELECT  ISDATE('1/08/13') AS "MM/DD/YY";

Write an SQL Query find number of employees according to gender  whose DOB is between 01/01/1960 to 31/12/1975.
SELECT COUNT(*), sex from Employees  WHERE  DOB BETWEEN 01/01/1960 ' AND ‘31/12/1975’  GROUP BY sex;


Finding the 2nd highest/Lowest salary for each Department.

Table~
empId empname DeptId    Salary
1       skm     1       2000
2       rk      1       3000
3       mk      2       4000
4       ak      2       5000
5       jk      1       6000
6       pk      2       3000
7       dk      1       7000
8       lk      2       8000
9       tk      1       9000



Second Highest Salary for each Department:
SELECT * FROM
(SELECT empid, empname, deptid, salary, DENSE_RANK () OVER (PARTITION BY deptid ORDER BY salary DESC)rn FROM employee) a
WHERE a.rn = 2

Output:-
empid empname deptid salary rn
7 dk 1 7000 2
4 ak 2 5000 2


Second Lowest Salary for each Department:

SELECT * FROM
(SELECT empid, empname, deptid, salary, DENSE_RANK () OVER (PARTITION BY deptid ORDER BY salary ASC)rn FROM employee) a
WHERE a.rn = 2


Output:-
empid empname deptid salary rn
2 rk 1 3000 2
3 mk 2 4000 2


Finding the nth highest salary of an employee.

Create a table named Employee_Test and insert some test data as:

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

It is very easy to find the highest salary as:-

--Highest Salary
select max(Emp_Sal) from Employee_Test

Now, if you are asked to find the 3rd highest salary, then the query is as:-

--3rd Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

The result is as :- 1200 

To find the nth highest salary, replace the top 3 with top n (n being an integer 1,2,3 etc.)

--nth Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

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

Related Table for Query

-- select 3rd highest Salary
select top (1) Salary from (select top (3) * from Salesperson order by Salary desc) t Order by Salary Asc

fetch the Sales person Name who has taken more then one Order.

1- select sp.Name from dbo.Salesperson sp join dbo.Orders o on sp.ID= o.salesperson_id
group by sp.Name,o.salesperson_id
having Count(o.salesperson_id) > 1

2- select Name from Orders,Salesperson where Orders.salesperson_id = Salesperson.ID
group by salesperson_id,Name
having count(salesperson_id) >1

both query returns same output.

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

-- Show "Orders" in "dd month yyyy" format, ex- "15 Feb 2013"
SELECT CONVERT(VARCHAR(20),OrderDate,106) as OrderDate FROM Orders

Output:
02 Aug 1996
30 Jan 1999
14 Jul 1995
29 Jan 1998
03 Feb 1998
02 Mar 1998
06 May 1998

-- show only time part  , ex- "02:11:12"
SELECT CONVERT(VARCHAR(20),OrderDate,108) FROM Orders

-- Get only Year part of "JoiningDate".
SELECT DATEPART(YEAR, OrderDate) FROM orders

Output:
1996
1999
1995
1998
1998
1998
1998

--23. Get system date.
SELECT GETDATE()

Output:
2014-10-03 18:13:43.087

--24. Get UTC date.
SELECT GETUTCDATE()

Output:
2014-10-03 12:43:43.087

-- Get the salesperson_id, current date, orderdate and diff between current date and order date in months.
-- DD,MM,YY
SELECT [salesperson_id], GETDATE() [Current Date], [OrderDate],
DATEDIFF(MM,OrderDate,GETDATE()) AS [Total Months] FROM Orders

Output:
salesperson_id     Current Date      OrderDate             Total Months
2      2014-10-03 18:15:17.237   1996-08-02 00:00:00.000  218
3      2014-10-03 18:15:17.237   1999-01-30 00:00:00.000 189
1      2014-10-03 18:15:17.237   1995-07-14 00:00:00.000  231
2      2014-10-03 18:15:17.237   1998-01-29 00:00:00.000  201
6      2014-10-03 18:15:17.237   1998-02-03 00:00:00.000  200
6      2014-10-03 18:15:17.237   1998-03-02 00:00:00.000  199
6      2014-10-03 18:15:17.237   1998-05-06 00:00:00.000  197

-- Get all Orders data from Orders table whose OrderDate year is 1998.
SELECT * FROM Orders WHERE DATEPART(YYYY,OrderDate) = '1998'

Output:
Number     OrderDate           CustID salesperson_id Amount
40 1998-01-29 00:00:00.000 7      2              2400
50 1998-02-03 00:00:00.000   6      6              600
60 1998-03-02 00:00:00.000   6      6              720
70 1998-05-06 00:00:00.000 9      6              150

-- Get all Orders data from Orders table whose OrderDate month is Jan(1).
SELECT * FROM Orders WHERE DATEPART(MM,OrderDate) = '1'

Output:
Number OrderDate                    CustID salesperson_id Amount
20           1999-01-30 00:00:00.000 4 3                          1800
40           1998-01-29 00:00:00.000 7            2                          2400

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

                                             Related Table for Query


-- Select second highest salary from "Employee" table.
SELECT TOP 1 Salary FROM
(
      SELECT TOP 2 Salary FROM [EmployeeData].[dbo].[Employee] ORDER BY Salary Desc
) T ORDER BY Salary ASC

-- Write the query to get the Designation and Designation wise total(sum) salary, display it in ascending order according to salary.
SELECT Designation, SUM(Salary) AS [Total Salary] FROM [EmployeeData].[dbo].[Employee]
GROUP BY Designation ORDER BY SUM(Salary) ASC

Output:
Designation Total Salary
SD                  130000
SE                  156000

-- Write the query to get the Designation, total no. of Designation, total(sum) salary with respect to Designation from "Employee" table.
SELECT Designation, COUNT(*) AS [Designation Counts], SUM(Salary) AS [Total Salary] FROM [EmployeeData].[dbo].[Employee] GROUP BY Designation

Output:
Designation Designation Counts    Total Salary
SD                        5                             130000
SE                        5                             156000

-- Get Name, Order amount orderby name from "Salesperson" and "Order" for all person Name if order is not availble then display "-No Order".
SELECT Name,ISNULL(CONVERT(VARCHAR(20),Amount),'No Order') As 'Order Amount' FROM Salesperson A LEFT OUTER JOIN Orders B
ON A.ID = B.Salesperson_Id ORDER BY Name

Output:
Name OrderAmount
Abe           460
Bob           540
Bob           2400
Chris        1800
Dan           No Order
Joe            600
Joe            720
Joe            150
Ken           No Order

-- Write down the query to fetch EmployeeName & Order who has made more than one order.
Select ID, Name, ISNULL(CONVERT(VARCHAR(20),Amount),'No Order') As 'Order Amount' from Salesperson s INNER JOIN Orders o
ON s.ID = o.salesperson_id
WHERE ID IN (SELECT salesperson_id FROM Orders GROUP BY salesperson_id HAVING COUNT(*) >1 )

Output:
ID Name OrderAmount
2 Bob         540
2 Bob         2400
6 Joe          600
6 Joe         720
6   Joe          150

-------------------------------------------------------------------------------------------------------------------------------
                                               Related Table for Query

-- What would be the output of the following query(INNER JOIN)
SELECT T1.ID, T2.ID FROM TBL_1 T1 inner JOIN TBL_2 T2 ON T1.ID = T2.ID  
-- in this query output will be same for every type of join

Output:
ID ID
1 1
1 1
1  1
1 1
1 1
1 1
1 1
1 1

                                               Related Table for Query

-- What will be the output of the following query.(Related Tables : Table_1,Table_2)
SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B
ON A.ID = B.ID --where a.ID is NULL

Output:
ID Name ID Name
1 Raj         1 Raj
2 Aman 2 Ankit
3 Riya   3 Riya

-- What will be the output of the following query.(Related Tables : Table_1,Table_2)
SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B
ON A.ID = B.ID AND A.[Name] = B.[Name]

Output:
ID Name ID Name
1 Raj 1 Raj
3 Riya 3 Riya

-- What will be the output of the following query.(Related Tables : Table_1,Table_2)
--(INNER JOIN WITH OR)
SELECT A.[ID], A.[Name],B.[ID], B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B
ON A.ID = B.ID OR A.[Name] = B.[Name]

Output:
ID Name ID Name
1 Raj         1 Raj
2 Aman 2 Ankit
2 Aman 4 Aman
3 Riya       3 Riya

-- What will be the output of the following query.(Related Tables : Table_1,Table_2)
SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B
ON A.ID IN (1)

Output:
ID Name ID Name
Raj 1 Raj
1 Raj 2 Ankit
1 Raj 3 Riya
1 Raj 4 Aman
1 Raj 5 Rahul



Best of Luck







0 comments:

Post a Comment

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (10) Web Service (1) XMl (1)