How to find the second highest or second maximum salary of an Employee is one of the most frequently asked SQL interview questions similar to finding duplicate records in table and when to use truncate vs delete. There are many ways to find second highest salary based upon which database you are using as different database provides different feature which can be used to find the second maximum or Nth maximum salary of employee. Well this question can also be generalized with other scenario like finding second maximum age etc. In this SQL tutorial we will see different example of SELECT SQL query to find second highest salary independent of databases or you may call in ANSI SQL and other SQL queries which uses database specific feature to find second maximum salary.
Many times we need to create backup or copy of tables in databases like MySQL, Oracle, or PostgreSQL while modifying table schema like adding new columns, modifying columns, or dropping columns. Since it's always best to have a backup of a table that can be used in any event. I was looking for an easy way to create an exact copy or duplicate tables which must be the same in the schema as well as in data, similar to creating a copy of the folder. Luckily there is an easy SQL query "CREATE table table_name AS" which allows you to create an exact copy of the table by executing just one SQL query. Yes, you read it correctly, no tool is required to create a backup of the table you just need to execute an SQL query.
How to find duplicate records in a table is a popular SQL interview questions which have been asked as many times as difference between truncate and delete in SQL or finding second highest salary of employee. Both of these SQL queries are must know for any one who is appearing on any programming an interview where some questions on database and SQL are expected. In order to find duplicate records in the database table you need to confirm the definition of duplicates, for example in below contact table which is suppose to store name and phone number of the contact, a record is considered to be duplicate if both name and phone number is the same but unique if either of them varies.