Top SQL Interview Questions for 2021

Top SQL Interview Questions for 2021

SQL stands structured query language and is a database management system. It is a widely used language. Recruiters are constantly searching for individuals with SQL skills. If you’re looking for a job and need to brush up your skills, these top SQL interview questions will help you do so.

Top SQL Interview Questions 

  1. Comparison between Truncate and Delete?
TRUNCATEDELETE
It deletes entire rows of the tableUsually, it is used to delete the row
Entire table is lockedRows of the table is locked before deleting the row (record)
Execution of truncate is faster compare to deleteExecution of delete is slower compare to truncate
There is no log file so we cannot rollback the truncate operationWe can rollback the delete operation
Where clause cannot be implemented in Truncate operationWhere clause can be implemented in Delete operation
  1. What is the difference between Truncate and Drop?
TRUNCATEDROP
It deletes entire rows of the tableDrop is used to delete the entire structure and the contents of the table
It does not remove any free space from memoryIt does remove free space from memory
View of the table is present View of the table is not present
Integrity constraints will not be removedIntegrity constraints will be removed
Syntax:TRUNCATE TABLE TABLE_NAME;Syntax:DROP TABLE TABLE_NAME;
  1. Define a Candidate key?

Candidate key is a collection of keys which is a subset of superkey. All keys which are used to identify record uniquely are known as candidate key

  1. Difference between RDBMS and DBMS?
RDBMSDBMS
It stores the information in tabular formIt stores the information as a file
Normalization has to be there in RDBMSNormalization is not there in DBMS
It has designed for multiple accessIt has designed for single access
It has designed to handle large amount of dataIt has designed to handle small amount of data
Since the data stored in RDBMS as tables, so there will be relationship between tablesSince the data stored in DBMS as file, so there will be no relationship between tables
It supports distributed databaseIt doesn’t support distributed database
  1. What is B-tree in sql?

It is a technique used to move through the rows based on the index key. It is used to represent a multilevel index. Mainly it contains three parts:

  • Block pointer    – It is used to represent the child
  • Keys – It is the searching criteria
  • Record pointer – It points to the secondary memory data
  1. What is the difference between B-Tree and B+ Tree?
B-TreeB+ Tree
Data pointer is stored in leaf as well as internal nodesData pointer is stored only in leaf nodes
Searching is slower, deletion is complexSearching is faster, deletion is easy
No redundant search key is presentRedundant keys may present
Leaf nodes not linked togetherLeaf nodes are linked together like linked list
  1. What is the difference between Group By and Order By clause?
GROUP BYORDER BY
It help us to group the rows that have same valuesIt help us to sort the result in descending or ascending order
It performs the operation on rowsIt performs the operation on columns
It can be allowed in create, view statementIt cannot be allowed in create, view statement
It is always used before order by clauseIt is always used after the group by clause
  1. Write a query to find the nth highest salary and explain it?
IDSALARY
110000
220000
320000
430000
540000
650000

Consider employee table

Query:

SELECT  ID, SALARY  FROM  EMPLOYEE  E1

    WHERE  N-1 =  (  SELECT COUNT(  DISTINCT  SALARY  )  FROM             EMPLOYEE  E2  WHERE E2.SALARY > E1.SALARY);

Consider we want to find second highest salary I.e; N=2

Therefore n-1=2-1=1

E1, E2 both represents EMPLOYEE table,

Each row from E1 is compared with E2 in sub query I.e; E2.SALARY>E1.SALARY;

Consider E1.SALARY  is 10000 and compare with all the rows of E2 in sub query. The result of sub query is 5 but 1 not equal to 5

Similarly all the rows is compared

When E1.SALARY is 40000 and compare with all the rows in E2 in sub query

The result of sub query is 1 which is equal to n-1 (where n=2)

Output of the query will be

IDSALARY
540000
  1. What are the different types of indexes?
  2. Unique Index:  It helps us to maintain data integrity by confirming that no two rows of data in a table have identical key values.For primary key, unique index is applied by default.So that confirms that the values in the index key columns are unique.
    2. Clustered Index: It reorders the normal(physical) order of the table and the search is based on the key values. We can create only one clustered index per table
    3. Non-Clustered Index:   It not alter the normal(physical) order of the table and maintains the logical order of the data. Each table can have many non-clustered indexes.
  3. Comparison of Clustered Index and Non- Clustered Index?
Clustered IndexNon-Clustered Index
Clustered Index is faster than Non-Clustered IndexNon-Clustered Index is slower than Clustered Index
One table can consist only one clustered IndexOne table can consist multiple Non-clustered Index
It changes the way records are stored in a database as it sorts out rows by the column which is set to be clustered index.It doesn’t changes the way it was sorted but it creates a separate object within a table which points back to the original table rows after searching
  1. Comparison between SQL and PL-SQL.
SQLPL-SQL
It is Structural Query Language for database.It is a programming language using SQL for a database.
It does not contains variablesIt contains variables and other data types
It does not contain for loop, while loop and if controlsIt contains for loop, while loop and if controls as well
It will execute each operation at a timeIt will execute multiple operation at a time
It can easily interacts with database serverIt cannot easily interacts with database server
It is a declarative language.It is a procedural language
It is a data oriented language.It is application oriented language
SQL is used to write queries, create and execute DDL and DML statementsPL/SQL is used to write program blocks, functions, procedures, triggers
  1. What is PL- SQL?

PL/SQL stands for procedural language extension to SQL.  It contains procedural features of programming language and SQL both. It was developed by Oracle Corporation in the early of 90’s to enhance the capabilities of SQL.

  1. What is the basic structure of PL-SQL?

PL/SQL uses block structure as its basic structure. PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block.

Three sections of PL-SQL:

  • Declaration Section (optional)
  • Execution Section (mandatory)
  • Exception handling section(optional)

This brings us to the end of the blog on SQL Interview Questions. We hope that you are now better acquainted with the concepts. All the best for your upcoming interview.