Top 45 SQL interview questions for 2021

This tutorial covers the top SQL interview questions that will help you answer SQL questions for both freshers and experienced.

1. What is SQL?

SQL stands for Structured Query Language. It is a standard query language that performs various database operations on a relational database system. SQL supports different data manipulation operations like insert, update, delete, fetch, etc.

2. What are the differences between SQL and PL/SQL?

3. What are the different operations or uses of SQL?

We can use SQL to manipulate data in relational databases and supports the below operations:

• Create new database
• Create a new table in a database
• Insert record
• Retrieve record
• Delete record
• Execute queries
• Update record
• Create a view in a database

4. Does SQL support programming language?

SQL is a command based language that cannot be used for programming since it does not support control statements like for loop, if-else, etc. We mainly use SQL as a query for data manipulation on a relational database.

5.What are the subsets of SQL?

Below are the subsets of SQL:

• DDL – Data definition language: Defines the data structure and has commands like CREATE, ALTER, DROP, etc
• DML – Data manipulation language: Manipulates  existing data and has commands like SELECT, UPDATE, INSERT, etc
• DCL – Data control language: Controls the access to data in a database and has commands like GRANT, REVOKE

6. Define tables and fields in the database.

The table is a defined data structure that contains rows and columns. Rows represent horizontal data that contains the actual values whereas columns represent the attribute of the data. Each row is called a record and a column is called a field that contains the characteristics of the record.

7. What is a primary key?

A primary key is a unique key that helps to find unique records. A primary key may represent a single field or a group of fields. For example, we can consider EMP_ID as a primary key in the EMPLOYEE table that helps to uniquely find employee records based on the ID field. A primary key cannot contain NULL values.

8. What is a foreign key?

A foreign key refers to the primary key of another table. It establishes relations between the tables using the primary key and foreign key combinations. It also maintains ACID properties.

9. What is a database?

A database is a structured form of data that helps to maintain and access the data easily. It helps to store huge amount of data in an organized manner. Using a database, we can perform any data manipulation or operations without much effort. A database can contain multiple tables to store and organize data.

10. What are the different types of Database Management Systems(DBMS)?

Below are the different types of DBMS:

• Hierarchical Database
• Relational Database
• Network Database
• Object-oriented database

11. What is RDBMS?

RDBMS stands for Relational Database Management System that represents data in a relational model. It uses relational operators for storing and linking a collection of tables in a database. Eg: Microsoft Access, MySQL, SQLServer, Oracle, etc. RDBMS is the most commonly used database management system.

12. What are the different types of operators in SQL?

Operators are special characters that help to perform various operations in SQL queries. There are 3 different types of SQL operators:

• Arithmetic operators: + – * /
• Logical operators: ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE.
• Comparison operators: =, !=, <>, <, >, <=, >=, !<, !>

13. What is view in SQL?

A view is a virtual table that contains data from one or more tables based on the relationship between them. It does not physically exist but we can query in the same manner as a table. A view is mainly used for security.

14. What is an index in SQL?

An index is used to uniquely search for records in SQL. It increases the performance and reduces the cost of query since it results in a faster retrieval process. It reduces the number of page traversal and directly fetches the required record based on the index of the data. This is similar to the book index where we can access the required content or page directly using the index of the book.

15. What are the different types of indexes in SQL?

Below are the different types of indexes present in SQL:

• Unique index
• Cluster index
• NonClustered index
• Bit-map index
• Normal index
• Composite index
• B-tree index
• Function-based index

16. What is the difference between clustered and non-clustered indexes?

17. What is normalization?

Normalization reduces redundancy and dependency by organizing fields and tables. It is used to add, delete or modify a field. It breaks the table into smaller partitions and links them using relationships. There are different rules of normalization:

• First Normal form
• Second Normal form
• Third Normal form
• Boyce-code Normal form

18. What are the commonly used joins in SQL?

• Inner join
• Outer join – Left outer join, Right outer join
• Natural join
• Cross join
• Self-join

19. What is inner join and what are the different types of inner join?

Inner join returns the matching rows between the tables. In other words, it returns common records that are present in both the tables. It uses the INNERJOIN keyword in SQL query.

Below are the different types of inner join:

• Theta join
• Natural join
• Equi join

20. What is Right join?

Right join returns all the rows from the right side table irrespective of whether there is a match with the left side table along with the common rows. It uses the RIGHTJOIN keyword in SQL query.

21. What is Left join?

Left join returns all the rows from the left side of the table irrespective of whether there is a match with the right side table along with the common rows. It uses the LEFTJOIN keyword in SQL query.

22. What is a full join?

A full join returns rows if there is at least 1 match. In other words, it returns all rows from the left table and the right table.

23. What is a self join?

A table that joins the same table which means it contains the copy of the same table within itself.

24. What is a cross join?

A cross join returns the product of rows from 2 tables. For example, if there are n rows in table1 and m rows in table 2, it returns n*m rows. It is also called Cartesian join.

25. What is a trigger?

A trigger executes a batch of code whenever any command like insert, update, or delete gets executed. These are a set of actions that need to be performed after any SQL commands are called. These are automatically executed when any record is inserted or updated. We can use the CREATE TRIGGER to generate triggers. These are stored procedures that occur when an event occurs. It has 3 parts: Event, condition, action.

26. What are Set operators?

The different set operators in SQL are UNION, UNIONALL, INTERSECT, and MINUS. The queries that use these set operators are called compound queries.

27. What is the difference between IN and BETWEEN condition operators?

The BETWEEN operator displays all the rows that contain the values or data between a specific range whereas the IN operator returns rows that are specific to the given set of values.

28. What is a constraint?

A constraint is a specific rule or regulation that is performed against a column in a table. It stores only valid data based on the constraint and can be used while creating or altering a table. The different constraints are:

• NOT NULL
• CHECK
• DEFAULT
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY

29. Write a SQL query to fetch all Employee records whose name starts with R

SELECT * FROM EMPLOYEE WHERE EMPLOYEENAME like 'R%'

EMPLOYEE – table name

EMPLOYEENAME – column name

*: returns all columns for that specific row

30. What are the differences between DELETE and TRUNCATE statements in SQL?

31. What do you mean by ACID property in SQL?

The ACID property ensures that all transactions happen reliably during data manipulation in a database. Below are the ACID properties:

A – Atomicity:  Performs either full or does not perform anything. This means it ensures that if 1 transaction fails then all other transactions fail and the database remains unchanged.

C – Consistency: It should meet all the rules and data must be valid.

I – Isolation: Provides concurrency control

D – Durability: Long lasting which means once a transaction is complete, it will remain in the same state irrespective of any unexpected condition like power loss, crash, or errors.

32. What are the differences between NULL, zero, and blank values in SQL?

A NULL value is not equal to zero or blank value. It represents unavailable or unassigned value whereas zero is a number and blank space is a character.

33. What is the use of SQL function?

We can use SQL functions for modifying any data, perform calculations, convert data types, format data time, etc.

34. What is the case manipulation function?

Case manipulation functions are functions that we use to convert from one form to another like a lower case or upper case or mixed cases. We can use it along with any type of SQL query. The different case manipulation functions are LOWER, UPPER, INITCAP.

35. What is the use of NVL() function?

NVL() function is part of the Oracle database and converts a NULL value to other values. In SQL Server, the equivalent function is ISNULL() and MySQL it is IFNULL().

36. What is the use of the DISTINCT keyword?

DISTINCT keyword ensures that it returns always a non-duplicate value. It is used along with column fields in SQL queries.

35. What is a stored procedure?

A stored procedure is a group of statements that performs a specific task. We can use the stored procedure especially when we want to execute the same set of statements multiple times by just calling the procedure name. It can be used along with triggers and other stored procedures. It improves the performance and reduces traffic.

37. What is the difference between CHAR and VARCHAR2 datatype in SQL?

CHAR data type is used with a character for fixed-length which means char(3) allows the variable to store only 3 characters. VARCHAR data type stores characters of variable length which means varchar(3) allows variables to store characters of length 1, 2, or 3.

38. How to fetch common records from 2 tables?

SELECT colname from TABLE1 INTERSECT Select colname from TABLE2

39. Which operator is used for pattern matching in SQL query?

We can use either % or _ operator for pattern matching.

% matches 0 or more characters

_ matches exactly one character

40. What is denormalization?

Denormalization is the process of accessing data from higher to lower forms from the database. This includes the data from the related tables and adds redundancy. The denormalization process takes place after normalization.

41. What is a subquery?

A query within a query is a subquery. When there is a subquery within the main query, first it executes the subquery after which it executes the main query using the result of the subquery.

42. What are the different ways to get the count of records from the table?

//Method 1:
SELECT * from TABLENAME

//Method 2:
SELECT COUNT(*) from TABLENAME

//Method3:
SELECT rows from sysindexes WHERE id = OBJECT_ID(TABLENAME) AND indid < 2

43. What is an ALIAS command?

ALIAS command denotes the name of the table or column. We can use the ALIAS command in the WHERE clause to refer to the table name or column.

44. What is a SELECT statement?

The SELECT statement selects or retrieves the records from the database. It returns values that are stored in a ResultSet.

45. What are the common clauses used along with the SELECT statement?

Below are the common clauses that we use along with the SELECT statement:

• WHERE: Filters the records based on a certain condition
• ORDER BY: Sorts the records based on certain fields either in Ascending or Descending order
• GROUP BY: Groups the records based on a condition
• HAVING: Filters the records based on a condition and used along with GROUP BY command