While learning SQL for my testing purpose, I came across many problems.
I know most often in interview there is a question "how to select max, second max or third max salary from table........ect"
I tried my hand on this and came with the final solution taking help from colleague and google devata.
really facing problems finally came with more experience and knowledge.
here I want to share some queries on select statement. Hope these will help specially for beginners
try to execute all the given scenarios , this will help you to understand better.....
1- Create a table first by using the following statement. This will create a table named 'employee' with two columns 'name' and 'salary'.
CREATE TABLE employee (name varchar(256) , salary int)
2- Insert some data in this table. for this use
INSERT INTO employee (name, salary)
VALUES ('vishal' , 32000)
insert more data to this table.for example ('amit', 20000), ('nitin', 25000), ('raju', 30000), ('david', 27000), ('marc', 18000)
3- Selecting max salary from table:
SELECT max(salary) FROM employee
4- Selecting second highest salary from table:
SELECT max(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee)
5- Selecting third highest salary :
SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee))
6- Selecting fourth highest salary:
SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee)))
and so on........................
But it seems to heavy and to complicated..............
here I am going to write a query for Nth highest salary....
SELECT salary FROM employee e1
WHERE (N=(SELECT count(distinct(e2.salary)) FROM employee e2 WHERE e2.salary >= e1.salary))
You can put 1, 2 ,3, 4......for first, second, third.......in place of N.
hope you will be happy now.........
