SQL Interview Questions

In today’s world, data is king, and database management plays a crucial role in the thrift of all big companies. SQL (Structured Query Language) developers are just as significant for the company as software developers.

Here, we have collected for you a series of real-world SQL interview questions asked by these big names. Each question has a perfectly written answer to save your time.

SQL Interview Questions and Answers (Basic to Advanced)

For your ease, we have divided these SQL query interview questions into various levels from basic to advanced. We’ve also added some top SQL interview questions surely to be asked.

1. What is the difference between CHAR and VARCHAR data types in SQL?

2. What is SQL? What are the different subsets of SQL?

This is one of the basic SQL interview questions surely to be asked. SQL is the abbreviation for Structured Query Language. It is a standardized language used by all DBMS (database management systems) to help users extract and change the organized data.

Different subsets of SQL are as follows:

1. Data definition language (DDL) comprises statements you can use to define a database.
2. Data Manipulation Language (DML) consists of statements used to manipulate data in a database.
3. Data Control Language (DCL): It consists of statements that deal with rights, permissions, and other controls of the database.
4. Transaction Control Language (TCL): It includes commands that control the transaction part of the database.

3. What do you mean by DBMS? What are its different types?

DBMS is an acronym for Database Management System. It is a software application built to create an interface between the user and the database. It can modify, retrieve data, and delete the database when the user pleases.

The different types of databases are as follows:

1. Hierarchical
2. Network
3. Relational
4. Object-Oriented

4. What do you mean by a CLAUSE in SQL?

An SQL Clause limits the result set by using a condition in the query. Example: Where & Having.

5. Write a SQL query to get the third-highest salary of an employee from employee_table?

Such SQL questions will be asked in your interview to check how well you can apply your theoretical knowledge. You can do this by writing the following commands:

SELECT TOP 1 salary

FROM(

SELECT TOP 3 salary

FROM employee_table

ORDER BY salary DESC) AS emp

ORDER BY SALARY ASC;

6. Are NULL values the same as zero or the blank space?

No. NULL is an unavailable, unknown, unassigned, or not applicable value. On the other hand, a zero is a number and a blank space.

7. Define tables, fields, and records.

In relational database management systems, data is stored in tables.

Table - Table is a collection of data in rows and columns.

Field - The vertical columns in a table are called fields.

Records - The horizontal rows in a table are called records.

8. What are group functions in SQL? Define some.

Group functions work on an entire field and return a value. Some standard group functions are:

1. AVG: Gives the average of the column.
2. COUNT: Counts the number of values in the field.
3. MAX: Gives the maximum value in the field.
4. MIN: Gives the minimum value in the field.
5. SUM: Gives the sum of values in the field.
6. VARIANCE: Gives the variance of values in the field.

9. What is a JOIN? What are the different types of JOIN?

JOIN is one of the benefits of a relational database management system. A JOIN clause in SQL joins records from two or more tables based on common fields (columns).

There are four different types of JOIN:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

Let’s understand these with an example. Suppose we have two tables, as follows:

TABLE A

TABLE B

Now, by definition:

• INNER JOIN gives back records that have matching values in both the tables.
  

In our case, A INNER JOIN B will give the following:

• LEFT JOIN retrieves everything from the left table and gives null values for the fields in the right table which have no match.

In our case, A LEFT JOIN B will provide the following:

• RIGHT JOIN does the same thing for the right column. A RIGHT JOIN B will give the following:

• FULL JOIN retrieves all the records with a match and fills NULL where the data is missing. A FULL JOIN B will give:

10. What is a View? How are they helpful?

A View is a virtual table, basically a subset of the data stored in one or more tables of a database.

The purpose of a database is also to protect the data stored. A View helps achieve that in the following ways:

1. It restricts access to data.
2. It simplifies complicated queries.
3. It ensures the independence of data.
4. It provides different views of the same data.

11. What is an index? Explain the different types of indexes.

An index is a performance tuning method that allows faster retrieval of records from a given table. Indexes create an entry for each value.

Indexes can be classified as:

• Unique and Non-Unique Index:

Unique indexes do not allow the field to have duplicate values. In contrast, non-unique indexes are used to exclusively improve the query’s performance. They do so by maintaining a sorted order of data values used frequently.

• Clustered and Non-Clustered Index:

Clustered indexes have the order of their rows tallying with the order of the rows in the index. Non-clustered indexes are all those indexes that do not have this constraint.

12. What is the difference between DELETE and TRUNCATE statements?

13. What is the ACID property in a database?

ACID is an acronym for the following:

• A: Atomicity

Any transaction in the database will either be done entirely or will fail; nothing in between. A transaction is a single logic operation on data.

• C: Consistency

Any transaction never leaves the database without completing its stage. At all points, the database will abide by all the validation rules.

• I: Isolation

Its main goal is concurrency control. One transaction is independent of the other.

• D: Durability

A committed transaction will occur.

14. Write an SQL query to display the current date.

SELECT GETDATE();

15. List ways to count records from a table.

This is one of the easiest SQL interview questions you will be asked. You can do so in the following way:

SELECT * FROM table1;

SELECT COUNT (*) FROM table1;

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

16. What do you mean by a trigger in SQL?

SQL triggers are special stored procedures defined to execute automatically in place or after data modifications. They allow a batch of code to be executed when an insert, update, or other query is committed against a specific table. There are six types of triggers:

1. Before insert – Activated before data is inserted into the table.
2. After insert – Activated after data is inserted.
3. Before update – Activated before data is updated.
4. After update – Activated after data is updated.
5. Before delete – Activated before data is deleted.
6. After delete – Activated after data is deleted.

17. State the different types of operators available in SQL?

There are five types of operators:

1. Arithmetic Operators
2. Bitwise Operators
3. Comparison Operators
4. Compound Operators
5. Logical Operators

18. How can you insert NULL values while inserting data?

You can do so in the following two ways:

1. Implicitly, by omitting columns from the column list.
2. Explicitly, by writing NULL in the VALUES clause.

19. What is a transaction? What happens if a transaction is interrupted midway?

Any action you perform on a database is called a transaction. For example, adding, deleting, or manipulating data.

Most RDBMS these days use the “completed or never started” protocol, which means that there are only two resting states of a database. If a transaction doesn’t finish (“completed”), it will revert to its condition before the transaction (“never started”).

20. Write a SQL query to find the names of employees that begin with ‘A’.

SELECT * FROM table1 WHERE EmpName like ‘A%’

Conclusion

We hope this helped you brush up on your SQL. SQL becomes crucial when you have to understand the data structures used to store an organization’s data. Only then can you have an additional level of control over your work there. Do you want to crack tech-giant companies such as Google, Oracle, Amazon, Microsoft, etc.? If yes, we’re here to help.

We at Board Infinity are dedicated to providing personalized and job-oriented higher education coaching to students and early career professionals. From perfecting your core competency to writing a concise resume, cracking a job at such companies in a long hustle.

If you want to advance in SQL, Join our Data Science Course and Master the highly demanded technologies like SQL, Python alongside the concepts of Data Exploration, Regression Models, Hypothesis Testing. Get Personalized 1:1 mentoring from Top Data Science Industry to be job-ready.