5 Complex SQL Queries to Nail Your Job Interview

As we know, SQL (Structured Query Language) is a vital tool in a data scientist's toolbox. Of course, mastering SQL is essential for interviews, but a good understanding of SQL by solving complex queries will help you a great deal in different phases of your career.

Here are five complex solutions for all the data enthusiasts out there! 

Note: For the following queries, extensive data tables will be available that need to be fed into an SQL editor. With their respective inputs, the output is generated.   

1. Query to fetch 'EmpId' and 'FullName' of the employees working under the Manager with id – '03'.

Answer: For this, the EmployeeDetails table can be used to fetch the employee details with a where clause for the Manager.

(Input)

SELECT EmpId, FullName

FROM EmployeeDetails

WHERE ManagerId = 03;

Here's an example.

2. Query to fetch the first record from a table

Answer: There are two ways to 'fetch the first record', 

(Input)

select * from Student where RowID = select min(RowID) from Student;

The second method is by printing just one (first) row of the table:

select * from Student where Rownum = 1;

Here's an example. 

3. Show only common records between two tables.

Answer: Feed the following input in the editor:

(Input)

Select * from Student;

Intersect

Select * from StudentA;

Here's an example. 

4. Query to find the record in Table A, not Table B, without using the NOT IN operator.

Consider two tables:

Answer: We can use the MINUS operator for Oracle and EXCEPT for SQL Server.

The query will be as follows:

(Input)

SELECT * FROM Table_A

MINUS

SELECT * FROM Table_B

Here's an example. 

5. Differentiate between UNION and UNION ALL

Answer: The major difference between UNION and UNION ALL is:

UNION eliminates duplicate records. On the other handUNION, ALL does not.

For example, consider two tables:

UNION of A and B = 20, 30, 25

UNION ALL of A and B = 20, 25, 25, 30

The performance of UNION ALL is considered better than UNION since UNION ALL does not require additional work of removing duplicates.

Note: The above queries enhance your knowledge as an aspiring data science professional (from data architects to software engineers) and add to your existing skills. As a result, SQL is one of the most in-demand skills among all jobs in the domain, appearing in 42.7% of all data science job postings.