SQL Interview Questions


SQL Interview Questions

This tutorial would walk you through the SQL interview questions for freshers and experienced, scope, and opportunities.

SQL Interview Questions
1. How to find the third-highest salary from the given employee table?
select max(salary) from employee where salary < (select max(salary) from employee where salary < (select max(salary) from employee where salary))

You have fulfilled your requirement ?. But is this a feasible solution? If I ask to find the 50th highest salary from the given employee table, we need to write 49 inner queries right? This will reduce the performance. So this is not the right thing to do. What is the right thing is, now I will explain.

  • First of all order, everything by salary and then use limit. 
SELECT SALARY FROM EMPLOYEE ORDER SALARY DESC LIMIT 2, 1

Here 2 is the index and 1 is the number of rows to fetch. (Index starts from 0).

SELECT SALARY FROM EMPLOYEE ORDER SALARY DESC LIMIT 0, 1 ==> First Highest

SELECT SALARY FROM EMPLOYEE ORDER SALARY DESC LIMIT 2, 1 ==> Second Highest

SELECT SALARY FROM EMPLOYEE ORDER SALARY DESC LIMIT 0, 5 ==> Top 5 highest salaries
2. What are the indexes and how can you create an index in SQL?
  • Indexes are database objects which help in retrieving records quickly and more efficiently.
  • Column indexes can be created on both tables and views. By declaring a column as an index within a table/view, the user can access those records quickly by executing the index.
  • Indexes with more than one column are called clustered indexes.

The syntax for creating an index

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN 1, COLUMN 2);

In general, it is easy in terms of performance quickness in just a small data table. But when it comes to thousands of rows in a table and suppose you have very bad luck and that row comes to the end of your table. How quickly will you be able to find it? It is not that quickly right? So to reduce this retrieval time and this full table scan we need to use indexes.

The syntax for dropping an index

DROP INDEX index_name ON table_name;
3. How do these indexes work?

Suppose we need to search by employee name = Ashok

  • What goes on behind the scenes is every single row is checked to see if the employee_name matches Ashok. This effectively means that the entire table will have to be scanned (Known as the full table scan).
  • An index is a data structure that stores the values for a certain specific column of a table and helps us avoid a full table scan.
  • Database indexing, in reality, allows us to cut down the number of rows/records that need to be examined when a select query with a where clause is executed.

Few data structures are

  • B-tree: B-trees are the most commonly used data structures for indexes as they are time-efficient for lookups, deletions, and insertions. All these operations can be done in logarithmic time. Data that is stored inside of a B-tree can be stored.
  • Hash Tables
  • Bitmap Index

Database indexes will also store pointers that simply reference information for the location of the additional information in memory.
The query looks for the specific row in the index; the index refers to the pointer which will find the rest of the information.

4. What are the disadvantages of indexes?
  • The index takes up additional space, so the larger the table, the bigger the index.
  • Every time you perform an add, delete, or update operation, the same operation will need to be performed on the index as well.
5. How to tune your SQL Queries/Increase the performance of a bad application?
  • SELECT fields instead of using SELECT *
  • Avoid SELECT DISTINCT
  • Create joins with INNER JOIN (not WHERE)
  • Use WHERE instead of HAVING to define filters
  • Avoid too many JOINs on your query. Use only what is necessary
  • Avoid cursor at all costs
6. What is Database?

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.

7. What is DBMS?

DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, update, and management of the database. It ensures that our data is consistent, organized, and is easily accessible by serving as an interface between the database and its end-users or application software.

8. What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. The key difference is, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

SQL Interview Questions
Scroll to top