Thursday, February 28, 2019

Second highest salary


SELECT (select
 DISTINCT(Salary)
from employee
 order by Salary desc
limit 1,1) as SecondHighestSalary

------------------------------------------
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary
--------------------------------

Note:
limit 0,1  - Top max salary

limit 1,1  - Second max salary

limit 2,1  - Third max salary

limit 3,1  - Fourth max salary


SQL Server

SELECT * FROM tblEmployees WHERE SALARY=( SELECT MIN(SALARY) FROM (SELECT TOP 2 SALARY FROM tblEmployees ORDER BY SALARY DESC)T)

Two queries which can decide your future

1) Query to find our SID in Subjects Maths and Science.
SELECT a.SID FROM `student` s inner join student a on (s.SID=a.SID) where s.Subject='Maths' and a.Subject='Science';

2) Query to find SID with marks greater than 200
SELECT DISTINCT `SID` FROM `studentmapping` where SID IN (select SID  FROM `studentmapping` group by SID HAVING SUM(Marks)>200) ;

GEN AI

  Stay Tuned....