20 Most Common SQL Query Interview Questions & Answers [For Freshers]

Structured Query Language (SQL) is a popular and extensively used programming language for managing, manipulating, and querying data in relational databases. While SQL is not exactly the fastest language for communicating with relational databases, it is undoubtedly the most efficient means of handling structured data. 

Compared to traditional read-write APIs like ISAM or VSAM, SQL extends two core benefits: 

• It allows you to access multiple records using a single command.
• It eliminates the need to specify how to access a record (whether or not to use an index) from a database.

An SQL query is a question or request for accessing data stored in a database. A database stores information in a tabular format. Thus, it contains rows and columns. While the database fields form the columns that direct the database regarding what information it can store, the rows contain the data. Here’s a sample dataset called “tblAlbum” to put things into perspective:

Source

Let’s create an SQL query using this sample data. We’ll focus on choosing only those albums with a 9 or above rating. So, we’re essentially asking the query to produce a list of album titles from the database having a rating equal to or greater than 9. You will use relevant keywords to get the desired answer.

The keywords in this scenario are – SELECT (dictating to fetch information from the database), FROM (the specific table containing the said data), and WHERE (dictating the criteria for accessing the information). When you use these keywords, your SQL query will look like this:

SELECT albumTitle FROM tblAlbums

WHERE rating >= 9;

Thus, you are choosing (SELECT) the data from (FROM) the table “tblAlbums” where (WHERE) the album rating is 9 or above. The WHERE statement can either be very simple or overly complex if there’s a substantial amount of nested logic involved. On running the query, you’ll get the following result:

albumTitle

Symphony in D Minor

Poet’s Heart

Now that you have a basic understanding of what SQL queries are and how they function let’s check out twenty SQL query interview questions!

Before we get started with the SQL query interview questions, check out these reference tables – our queries will be based on these tables.

EmployeeInfo table:

EmployeePosition table:

SQL Query Interview Questions

1. Write a query to get the EmpFname from the EmployeeInfo table in the upper case using the alias name as EmpName.

The query for this condition is:

SELECT UPPER(EmpFname) AS EmpName FROM EmployeeInfo;

2. Write a query to get the number of employees working in the department ‘HR’.

Here’s the query for this demand:

SELECT COUNT(*) FROM EmployeeInfo WHERE Department = ‘HR’;

3. What query will you write to fetch the current date?

To fetch the current date, you can write this query in the SQL server:

SELECT GETDATE();

To fetch the current date, you can write this query in MySQL:

SELECT SYSTDATE();

4. Write a query to fetch only the place name(string before brackets) from the Address column of the EmployeeInfo table.

You can use the MID function in MySQL to create the following query:

SELECT MID(Address, 0, LOCATE(‘(‘,Address)) FROM EmployeeInfo;

You can use SUBSTRING to create the following query:

SELECT SUBSTRING(Address, 1, CHARINDEX(‘(‘,Address)) FROM EmployeeInfo;

Read More: SQL for Data Science: Why SQL

5. Write a query to create a new table whose data and structure are copied from another table.

You can use the SELECT INTO command to create the following query:

SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0;

You can use the CREATE command in MySQL to create the following query:

CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;

6. Write a query to display the names of employees that begin with ‘S’.

This is the query you need to write to get the names of the employees whose names start with’S’:

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE ‘S%’;

7. Write a query to retrieve the top N records.

You can write a query using the TOP command in SQL Server:

SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC;

You can also create a query using the LIMIT command in MySQL:

SELECT * FROM EmpPosition ORDER BY Salary DESC LIMIT N;

8. Write a query to obtain relevant records from the EmployeeInfo table ordered by Department in ascending order and EmpLname in descending order.

You need to use the ORDER BY statement in SQL for this purpose;

SELECT * FROM EmployeeInfo ORDER BY Department asc, EmpFname desc;

9. Write a query to get the details of employees whose EmpFname ends with ‘A’.

You can use the LIKE operator in SQL to create a query for this issue:

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE ‘____a’;

10.Create a query to fetch details of employees having “DELHI” as their address.

SELECT * FROM EmployeeInfo WHERE Address LIKE ‘DELHI%’;

Read: Top 9 Data Science Tools in 2020

11. Write a query to fetch all employees who also hold the managerial position.

Here’s the query to get the employees who hold the managerial position:

SELECT E.EmpFname, E.EmpLname, P.EmpPosition

FROM EmployeeInfo E INNER JOIN EmployeePosition P ON

E.EmpID = P.EmpID AND P.EmpPosition IN (‘Manager’);

12. Create a query to generate the first and last records from the EmployeeInfo table.

Here’s the query to fetch the first record from the EmployeeInfo table:

SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MIN(EmpID) FROM EmployeeInfo);

Here’s the query to fetch the last record from the EmployeeInfo table:

SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MAX(EmpID) FROM EmployeeInfo);

13. Create a query to check if the passed value to the query follows the EmployeeInfo and EmployeePosition tables’ date format.  

You can use the IsDate() function in SQL to check whether the passed value follows the specified format or not. It returns 1(true) or 0(false) accordingly. Here’s how the query will look:

SELECT ISDATE(’01/04/2020′) AS “MM/DD/YY”;

Running this query will return 0 since the passed value does not match the specified format. 

14. Create a query to obtain display employees having salaries equal to or greater than 150000.

The query for this request will be:

SELECT EmpName FROM Employees WHERE Salary>=150000;

15. Write a query to fetch the year using a date.

You can get the year from a date in an SQL server by running the following query:

SELECT YEAR(GETDATE()) as “Year”;

16. Create an SQL query to fetch EmpPostion and the total salary paid for each employee position.

The query for this request is:

SELECT EmpPosition, SUM(Salary) from EmployeePosition GROUP BY EmpPosition;

17. Write a query to find duplicate records from a table.

SELECT EmpID, EmpFname, Department COUNT(*) 

FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department 

HAVING COUNT(*) > 1;

18. Create a query to fetch the third-highest salary from the EmpPosition table.

SELECT TOP 1 salary

FROM(

SELECT TOP 3 salary

FROM employee_table

ORDER BY salary DESC) AS emp

ORDER BY salary ASC;

19. Write an SQL query to find even and odd records in the EmployeeInfo table.

You must use the MOD() function to fetch the even and odd records from a table. For even records, the query will be:

SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=0;

For odd records, the query will be:

SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=1;

20. Create a query to fetch the list of employees of the same department.

Here’s the query for this request:

Select DISTINCT E.EmpID, E.EmpFname, E.DepartmentFROM EmployeeInfo E, Employee E1 

WHERE E.Department = E1.Department AND E.EmpID != E1.EmpID;

Conclusion

With that, we come to the end of our SQL query interview questions list. We hope that these queries give you a fair idea of the standard SQL query pattern and how to create SQL queries. The more you practice, the better you will get at writing SQL queries to access and manipulate data in a data table. 

If you are curious to improve your SQL knowledge, and more about full stack development, check out IIIT-B & upGrad’s PG Diploma in Full Stack Software Development which is created for working professionals and offers 10+ case studies & projects, practical hands-on workshops, mentorship with industry experts, 1-on-1 with industry mentors, 400+ hours of learning and job assistance with top firms.