SQL Query to find Max Salary from each department.
Finding
the nth highest salary of an employee.
To find the nth highest salary, replace the top 3 with top n (n being an integer 1,2,3 etc.)
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;
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
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 1 Raj 1 Raj 1 Raj 2 Ankit 1 Raj 3 Riya 1 Raj 4 Aman 1 Raj 5 Rahul Given the following tables: sql> SELECT * FROM runners;
+----+--------------+
| id | name |
+----+--------------+
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | Alice Jones |
| 4 | Bobby Louis |
| 5 | Lisa Romero |
+----+--------------+
sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event | winner_id |
+----+----------------+-----------+
| 1 | 100 meter dash | 2 |
| 2 | 500 meter dash | 3 |
| 3 | cross-country | 2 |
| 4 | triathalon | NULL |
+----+----------------+-----------+ What will be the result of the query below? SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races) Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table. Knowing this, a query that avoids this issue would be as follows: SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races WHERE winner_id IS NOT null) Note, this is assuming the standard SQL behavior that you get without modifying the default ANSI_NULLS setting. interview-questions/sql-interview-questions
Best of Luck
|
0 comments:
Post a Comment