Top 50 SQL Interview Questions and Answers for Experienced & Freshers

Introduction

SQL is the coding language for querying data in databases. It stands for Structured Querying Language. It is used to perform several tasks including updating data on a database, retrieving the data, and more. American National Standard Institute or ANSI has declared SQL developer course the standard language for relational database management systems. Hence it is one of the most in-demand skills today. This is no easy task as recruiters regularly meet with the best of the best. You need to learn to stand apart in a good way. Here, we have prepared a set of frequently asked SQL Interview Questions that will help you crack your interview.

Top 50 SQL Interview Questions and Answers for Experienced & Freshers 

1. How would you explain SQL to someone from a non-technical background?

Ans. SQL stands for Structured Querying Language. It is a coding language that is used to communicate with a database. It is a critical tool for data professionals. They use it to upload, extract, insert and delete the millions of data points that are held within a database. 

2. What is an SQL database?

Ans. The SQL database is a set of columns and rows, and SQL is the language of the database. This data can then be analyzed by the company for its various uses. Even if the data itself is analyzed on a different platform like Python, SQL is necessary to extract it from the database.

3. Explain the difference between SQL Database and Excel.

Ans. Although both database and excel can organize data into rows and tables, the database is more powerful because:

• The database can interact with other programming languages such as Python, Java, and Ruby. This allows the user to perform powerful tasks and queries with the data.
• The database can process more data than excel. While excel can handle up to 1 million rows of data, the database can go over a billion.
• The database can be connected to the internet and so multiple persons can work on it at the same time.

4. What is SQL used for?

Ans. SQL has various functions. On top of retrieving, inserting, deleting, and updating the database, SQL is also used to create new databases or new tables in the database, create stored procedures & views in a database, 

5. Explain the difference between SQL and MYSQL

Ans. There are a number of differences between the two:

• MySQL was developed by MySQL AB but is currently owned and operated by ORACLE Corporation. Whereas, SQL is developed by Microsoft
• SQL is a programming language, mostly used to manage and retrieve data from the database. On the other hand, MySQL is a rational database system that uses SQL.
• SQL is multilingual, i.e, available in several languages whereas MySQL is only available in English.
• SQL supports user-defined functions but MySQL does not. In this sense, SQL is more flexible than MySQL.
• SQL needs relatively less time to restore large amounts of data whereas MySQL requires a lot of time for the same amount of data.

6. What is DBMS?

Ans. DBMS or Database Management System that ensures that the data remains consistent, organized, and easily accessible. It serves as an interface between the database and its end-users or application software.

7. What is RMDBS?

Ans. RMDBS is the acronym for Rational Database Management System. It helps to store data into a collection of tables that is related by common fields between the columns of the table.

8. What are tables and fields?

Ans. A set of data is organized within rows and columns is called a table. Columns are vertical and rows are horizontal. A table may contain a specified number of columns called fields, and a specified number of rows called records. 

9. List the different types of SQL queries.

Ans. The four different types of queries in SQL are as follows:

• DCL or Data Control Language is used to assign and remove permissions.
• TCL or Transaction Control Language is used to save and restore changes to a database.
• DDL Data Definition Language is used to create objects.
• DML or Data Manipulation Language is used to manipulate the data.

10. What are the Constraints in SQL?

Ans. Constraints are representations of the data in a table. The two different levels of Constraints in SQL are column-level constraints and table-level constraints. It can be applied using ALTER TABLE command during or after creating the tables. Some of the Constraints in SQL are as follows:

 

• Primary Key

It helps to identify each record in a table

• Foreign Key

Maintains the referential integrity for a record in another table

• Unique 

Ensures that unique values are inserted into the field

• Index

This field provides faster retrieval of data

• Default

If no value has been assigned to the field, it ensures that a value will be assigned by default

• Check

It ensures that the values in a field satisfy all the conditions

• Not Null

It prevents the NULL value from entering the column 

11. What are some of the different levels of Constraints?

Ans. There are two main levels of Constraints in SQL. They are:

• Table Level Constraints and
• Column Level Constraints.

12. Explain Primary key.

Ans. The primary key is a unique combination of fields that can identify a specific row in a column. It is a Constraint in SQL. A table in SQL will have one and only one Primary Key, comprised of single or multiple keys. The value of a Primary Key must not be NULL. 

13. What is a unique key?

Ans. A Unique Key provides each column a value that is different from all others. It helps to identify each row uniquely. There can be a multitude of unique constraints defined per table. Unlike Primary Key, null values are allowed here. The code syntax of Primary and Unique are quite similar and can be used interchangeably. 

14. Explain foreign key.

Ans. A Foreign Key contains a collection of keys in a table that refers to the Primary Key of another table. A relationship must be created between two tables by referencing Foreign Key with the primary key of another table. A table that contains the foreign key constraint is called the child table.

15. What is JOIN in SQL?

Ans. In SQL, the Join clause is used to combine rows or records from two or more tables based on a required condition. It can also merge two tables and retrieve its data. 

16. Name and explain different types of JOIN in SQL

Ans. There are four types of Join clauses in SQL:

• Inner Join 

It is the most common type of Join. It can select and combine all the rows from different tables as long as the condition satisfies. In the common field, the value will be the same.

• Right Join 

It is used to gather data from the right table and the matched rows or columns from the left table. It returns NULL if both tables do not contain any matched rows or columns.

• Left Join

It is used to obtain data from the left table and the matched rows or columns from the right table. It returns NULL if both tables do not contain any matched rows or columns.

• Full Outer Join

It is also called a Full Outer Join. This is the combined results of both Right Join and Left Join. The joined tables return all records from both the tables and if no matches are found in the table, it places NULL. 

17. What is an SQL server?

Ans. Microsoft developed a relational database management system called SQL Server. It is built on SQL programming language and supports a number of applications including business intelligence, transaction processing, and analytics. The system is designed and built in order to store and manage information.

18. How will you insert date in SQL?

Ans. If the RDBMS is MYSQL, we can insert the date with the code:

“INSERT INTO tablename (col_name, col_date) VALUES (‘DATE: Manual Date’, ‘2020-9-10’)”

19. What is normalization in SQL?

Ans. Normalization is the manner in which data is organized in the database. It is the process in which redundancy in a table is reduced and also data integrity is improved. All data has to be in the Normalised Form in order to initiate any interaction. Otherwise, the results will contain anomalies.

20. What is denormalization?

Ans. Denormalization is the opposite of normalization. It is an optimizing technique whereby redundancy is introduced into a table by incorporating data from the related tables. This is used to avoid costly joins in a rational database. It is applied after normalization. 

21. Explain the different types of normalization 

Ans. There are five different types of normal forms. They are as follows:

• First Normal Form or 1NF

1NF is used to solve the issue of atomicity. It ensures that every column has a unique value and that a single cell does not contain multiple values.

• Second Normal Form or 2NF

The first requirement for 2NF is to satisfy all the conditions in 1NF. The table should also avoid partial dependency. This is achieved by placing a subset of data in separate tables and creating relationships between the table using the primary key.

• Third Normal Form or 3NF

3NF is similar to 2NF because the first requirement is to satisfy the conditions of 2NF. The next requirement is that is there should be no transitive dependency for the non-prime attributes. In other words, non-prime attributes should not be dependent on other non-prime attributes in a given table.

• Boyce Codd Normal Form or BCNF

BCNF is also called 3.5NF. It is the higher version of 3NF that was developed by Raymond F. Boyce and Edgar F. Codd. It deals with those anomalies that are not dealt with in 3NF. All the conditions in 3NF must be satisfied before BCNF can be applied.

22. What is the difference between Cross Join and Self Join?

Ans.Cross join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table. This kind of result is called a cartesian product. If a WHERE clause is attached to the cross join, it will function like an Inner Join. Whereas, a Self Join allows the user to join rows of the same table so that values in the same column can be compared effectively. 

23. What is a query?

Ans. A query is a request for information sent by the user into the database. There are two types of queries in SQL, select query and action query. A query can be designed in a way that will find the specific information the user requires.

24. What are subqueries?

Ans. A subquery is also known as a nested query or inner query. It is a query within a query and used to enhance or restrict the data to be queried by the main query 

25. Name some types of subqueries.

Ans. The two types of queries are:

• Correlated

It is not an independent query but it can refer to the column in a table listed in the FROM of the main query.

• Non-correlated

It can be considered an independent query and its output can be substituted in the main query.

26. What is the key difference between DELETE and TRUNCATE statements?

Ans.There are a number of differences between DELETE & TRUNCATE. They are as follows:

• The TURNCATE command is used to delete all the rows in a table whereas the DELETE command deletes specific rows from a table.
• The user can rollback DELETE but cannot do the same for TRUNCATE.
• TURNCATE is a DDL command whereas DELETE is a DML command.
• DELETE command is faster than the TURNCATE command.

27. Differentiate between DROP and TRUNCATE statements.

Ans. Although TRUNCATE removes all the rows from a table it leaves the structure of the table intact. DROP command on the other hand removes all the data along with the structure of the table from the database. Both the commands cannot be rolled back.

28. Explain the differences between VARCHAR2 and CHAR datatype in SQL?

Ans.Although they are both used for characters datatype, varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length. 

29. What is a Cursor? 

Ans. A cursor is a controller that allows the user to navigate between rows or records of a table. It is seen as a pointer that is very useful in retrieving, adding, and removing records in the database.

30. What is data integrity?

Ans. The accuracy and consistency of the data stored in the database is data integrity. It is a critical aspect of the design, implementation, and usage of any system that is required to store process, or retrieve data. It can also enforce the rules of the business into the data when it is entered into an application or database.

31. How can you create a table in SQL?

Ans. The command to create a table in SQL is

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

   ….

);

32. How do you delete a table in SQL?

Ans. There are two different ways to delete a table in SQL.

• DROP TABLE table_name; This command lets you delete all the data present in the table along with the table itself.
• DROP TABLE table_name; this command lets you delete the data within the table and leave the table behind.

33. Can you change a table name in SQL?

Ans. The command to change the table name in SQL in ALTER TABLE table_name. 

34. How can you count the number of records in a table?

Ans.You can count the number of records in a table with different commands. Some of them are: 

• SELECT * FROM table1
• SELECT COUNT(*) FROM table1
• SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

35. What are the different operators available in SQL?

Ans. There are three main operators in SQL and they are:

• Comparison Operators
• Arithmetic Operators
• Logical Operators

36. What is a Clause in SQL?

Ans. A Clause is used in SQL to limit the results. They are set as conditions to be met to achieve the desired outcome. It helps to filter the information.

37. What are user defined functions and name some of its types.

Ans.Functions defined by the user to perform a specific task are called user defined functions. They are similar to functions in any other programming language that accepts parameters, performs complex calculations, and returns a value. Once written, they can be recalled anytime by the user.  The three types of user defined functions in SQL are as follows:

• Scalar Function,
• Inline Table valued functions, and
• Multi statement valued functions. 

38. What is the purpose of SQL functions?

Ans.SQL functions are used for varying purposes, some of which are:

• To convert the data types
• To manipulate the result
• To make some calculations on the data
• To modify individual data items
• To format numbers and dates

39. Explain Aggregate and Scalar Functions.

Ans. Mathematical calculations are done by the Aggregate function. It returns a single value. The calculations can be done from within a column of a table. Some widely used Aggregate functions in SQL are:

• SUM() 
• FIRST()
• LAST()
• MAX()
• MIN()
• COUNT()
• AVG()

On the other hand, Scalar functions return a single value based on the input. Some widely used Scalar functions in SQL are:

• FORMAT() sets the format to display a collection of values.
• ROUND() calculates the round-off integer value for a decimal
• LEN() finds the length of a given field
• NOW() returns the current date and time
• UCASE() converts a large set of values to upper case characters
• LCASE() converts a large set of values to lower case characters.

40. What is a Stored Procedure?

Ans. A stored procedure is a subordinate function that is available to applications that can access a relational database management system (RDBMS). It consists of many SQL statements. These statements are consolidated into the stored procedures to be used as and when necessary.

41. Explain the advantages and disadvantages of Stores Procedure.

Ans. One of the major advantages of Stored Procedures is that it can be used as modular programming, meaning, once created, it can be stored and recalled at any time. This makes way for faster execution, and also reduces network traffic, and provides better security to the data. 

The main disadvantage is that it can only be executed within the database and requires memory space for storage in the database server. 

42. What is a recursive stored procedure?

Ans. Recursive Stored Procedure is a stored procedure that calls by itself until it reaches a boundary condition. It helps the programmers deploy the same set of code as many times as required without effort.   

43. What is the command to fetch common records from two tables?

Ans.To fetch common records from two tables the following command can be used.

Select StudentID from student INTERSECT Select StudentID from Exam

44. What is the command to fetch alternate records from two tables?

Ans. To fetch alternate records from two tables the following command can be used.

Select StudentID from (Select rowno, StudentID from student) where mod(rowno,2)=0

45. What is an ALIAS command in SQL?

Ans. ALIAS is a temporary name given to a table or a table column for the purpose of a particular SQL query. The temporary name can be referred to in the WHERE clause to identify a table or column. A table alias is also known as a correlation name.

46. Explain OLTP.

Ans. OLTP or Online Transaction Processing is a class of transaction-based applications that is able to support transaction-oriented programs for data entry, retrieval, and processing. It makes managing data more efficient and simple.

47. What is OLAP?

Ans. Online Analytics Processing is a class of software that is characterized by the relatively low frequency of online transactions. This system is mostly used for data mining and maintaining aggregated, historical data, usually in multi-dimensional schemas. Its queries are often complex and contain several aggregations. 

48. What is the command to insert multiple rows in SQL?

Ans.INSERT INTO table_name (column1, column2,column3…)

VALUES

    (value1, value2, value3…..),

    (value1, value2, value3….),

    …

    (value1, value2, value3);

49. How can you create empty tables with the same structure as another table?

Ans. This can be done simply by fetching the records of one table using the INTO operator while fixing a WHERE clause to be false for all records. This way SQL prepares the new table with a duplicate structure to accept the fetched records. But no records will be fetched since the WHERE clause is in action, and nothing will be inserted into the new table.

50. Explain the meaning of Datawarehouse.

Ans. A data warehouse is the central repository of data. It is the place where information from multiple sources is held. At the Datawarehouse the data is consolidated, transformed, and made available for mining and online processing.

An SQL Certification can make way for future job opportunities. Attending an SQL Developers Course will also immensely aid you in acing your interview. Henry Harvin has one of the best SQL Developers Courses in India. 

Henry Harvin Coding Academy

Henry Harvin is a winner of the Top Corporate Training Award and Game-Based Learning Company of the under 40 Business World Award. Government of India. The Henry Harvin Coding Academy was set up with the main objective of equipping professionals with the knowledge and skills for career advancement by upskilling themselves. This is achieved through action-oriented learning techniques that are prepared by industry experts.

SQL Developers Course

SQL has been declared the standard programming language for databases by ANSI(American National Standard Institute). The government of India has also recognized Henry Harvin as an online university.

This course will ensure that you will be able to gain a thorough understanding of SQL and Database Organization. It will teach you how to create rows & tables, change schema, and other important concepts such as JOIN, SQL Strings, Date and Time-related functions, and more.

Benefits of SQL Developers Course

• 16 hours of live interactive training sessions online or offline
• Be trained by industry experts with over 10 years of experience with Global Certification
• Learn through live projects guided by industry experts
• E-Learning Access to several tools and resources 
• Regular Bootcamps for the following year
• One year Gold Membership to Henry Harvin Coding Academy
• Be a part of the Elite Coding Academy of Henry Harvin which has an 18,000+ alumni network worldwide.

Conclusion

The rising importance of data and information today has created numerous job opportunities for SQL Developers. As a career, it offers high salaries, promising career opportunities, and contact with the latest technologies.

 

Acing an interview is not easy. Out of hundreds of applicants, you need to stand out to get the employer’s attention. The way you conduct yourself at the interview can hence be more important than your papers. Be confident and assertive without being rude or arrogant. This fine balance will ensure your success. 

 

Above all be sure to research and read as much as possible well before your interview. This short article is not sufficient to inform you about interview etiquette. Reading more articles about SQL Interview Questions will not go in vain either. 

I sincerely wish you the very best for your interview.

Also, Check Henry Harvin Review:

Recommended Reads

1. What is SQL? Learn SQL Basics

2. MS-SQL Full form | Difference Microsoft SQL and Microsoft SQL Server

3. What is a Database? What is a Database Management System and its Types?