How to find highest and second highest record or column value in sql
Filed under: MySQL Interview Questions, Oracle Interview Questions, SQL SERVER Interview Questions
MySQL: In mysql to get the highest and second highest column or record we can use order by and limit
For example : The below query will give me the highest record:
SELECT * FROM `student` where class=’tenth’ ORDER BY mark desc LIMIT 0,1
To get the second highest record:
SELECT * FROM `student` where class=’tenth’ ORDER BY mark desc LIMIT 0,1
To get highest record or second highest record in Oracle we can use ROWNUM . Oracle doesn’t have LIMIT and ROWNUM works in the same way as LIMIT works in MYSQL.
Below query will give me the highest or the max student record with highest marks
SELECT * FROM `student` where class=’tenth’ and ROWNUM == 1 ORDER BY marks
Second highest record can be get through below command
SELECT * FROM `student` where class=’tenth’ and ROWNUM == 2 ORDER BY marks
Tedious way would be to write nested sql query to find the second highest record
SELECT score,player FROM scores WHERE score=(SELECT max(score) FROM scores WHERE score< (SELECT max(score) FROM scores));
The above finds the second highest score in the scores table and the name of the player.
Reading from right to left:
1. The first select finds the highest score.
2. The second select find the highest from the rest.
3. The last select finds the name of the player.
How to find duplicate entry or duplicate row in a table
Filed under: MySQL Interview Questions, Oracle Interview Questions, PostgreSQL Interview Questions, SQL SERVER Interview Questions
In this sql command we are trying to find the duplicate entry with duplicate name. This sql command will give all the MIN id which has duplicate entry
SELECT MIN(id) AS dupid,COUNT(name) AS dupcnt
FROM tablename
GROUP BY name HAVING dupcnt>1
It should work on Oracle, Mysql
What is First Normal Form (1NF)
Filed under: Oracle Interview Questions, Relational Database Interview Questions
First normal form (1NF) is the first basic steps for normalization for relational database:
- Eliminate duplicative columns from the same table t0 avoid redundancy .
- There should be separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
What is Normalization ?
Filed under: Oracle Interview Questions, Relational Database Interview Questions
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.