Top 10 Interview Queries on SQL

This article was published as a part of the Data Science Blogathon.

Introduction

With the advancement of technology and data, job opportunities are increasing rapidly. Nowadays, everyone is more interested in implementing practical knowledge than notebook theories. Likewise, interviewers are also getting smarter, now, they are no longer interested in knowing what SQL is, what are its benefits, pros or cons of SQL, but they are pretty interested in knowing how can use the basic concepts to solve real-world problems. Interviewers ask you to share your screen, open a notepad, and start writing the solution to asked queries. So, in this blog, we will cover some frequently asked SQL queries.

Source:- https://passmyinterview.com/sql-interview-questions-and-answers/

What is SQL?

Structured Query Language or SQL is the standard language for relational database management systems (RDBMS) used to interact or communicate with the database. It was invented in 1970 and is usually pronounced as “Sequel.” In terms of usage, SQL is well-known for maintaining relational databases. With SQL, one can create databases, and tables, insert records, delete records, fetch records, update records, etc.

SQL plays an important role in firms as many companies use databases to store huge amounts of data, and SQL helps them to maintain their records smoothly.

Data

As we know, SQL is based on RDBMS, so we have to create a few tables from which we can fetch the data. Most interviewees share their desktops and show two or three demo tables on which we have to perform queries. This blog considers two tables, “Employee_detail” and “Employee_salary”. The first table contains the essential detail of the IT employee like ID, name, designation, city, and date of joining. The second table includes the salary detail of the employee, like net pay, variable, and ID.

Table – Employee_detail

Table – Employee_salary

Interview Questions on SQL

Q1-Give at least two approaches to finding out the second highest salary of an employee with the help of the above tables.

Solution:

1. Query using the N-1 rule

Select distinct NetPay from Employee_salary e1 where 1=Select count(distinct Salary) from Employee_salary e2 where e2.NetPay>e1.NetPay;

Explanation:- According to the N-1 rule, whatever the highest salary we need to find, replace that with N, subtract it by 1, and put it into the where condition. Firstly the inner query executes and counts the unique highest salary of the employee. Lastly, it compares the value of count with N-1 and if both are equal, then displays the output.

In simple terms, we are creating two aliases e1, and e2 of the same table Employee_salary, and then comparing a single row of e1 with all the rows of e2.

Note:- Here, the value of N is 2

2. Query using the max aggregate function

select max(NetPay)from Employee_salary where NetPay Not in(Select  max(NetPay) from Employee_salary );

Explanation:- By executing the inner query, we simply get the first highest salary but when it is compared with the where condition, it fails, so next time it will fetch the second highest salary.

Q2-Give at least two approaches to fetch the employee details whose salary is more than 60 k and less than 95 k.

Solution:

1. Query with the help of Greater than and less than operators

Select * from Employee_salary where NetPay >= 60000 and NetPay <= 95000;

2. Query with the help of Between and operator

Select * from Employee_salary where NetPay between 60000 and 95000;

Explanation:- In this query, we use simple relational operators and between.. and keywords that check the salary range.

Q3-Write a SQL query to fetch the details of Employees whose name does not start with V, R, or S.

Solution :

1. Query using the symbol of Not operator

Select * from Employee_detail where FullName like ‘[!VRS]%’;

2. Query using the Not Operator as a keyword

Select * from Employee_detail where FullName not like ‘[VRS]%’;

Explanation:- ‘Like’ keyword is used for pattern matching, it compares the first letter of the Full Name with the characters V, R, and S, and if they match, it won’t display that row.

 Create table Employee_details2 as Select * from Employee_detail where 1=2;

Create table Employee_details2 as select * from Employee_detail;

select row_num, e1.* from Employee_detail e1 where row_num<=(select count(*)/2 from Employee_detail);

Select row_num,e1.* from Employee_detail e1
minus
Select row_num,e2.* from Employee_detail e2 where row_num<=(Select count(*)/2) from Employee_detail)

select distinct * from Employee_detail;

select * from Employee_detail union select * from Employee_detail;

Select Substr(‘SHIKHA’,Level,1) From Dual Connect By Level<= Length(‘SHIKHA’);

SELECT Email_id FROM Employee_detail where NOT REGEXP_LIKE(Email_id, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

Select EmpId, count (EmpId) from Employee_detail Group by EmpId Having count (EmpId)>1 Order by count (EmpId) desc;

Select EmpId FROM Employee_detail e1 WHERE EmpId 
(Select max (EmpId) from Employee_detail e2 where e1.EmpId=e2.EmpId);

Delete FROM Employee_detail e1 WHERE EmpId  (Select max (EmpId) from Employee_detail e2 where e1.EmpId=e2.EmpId);

Select * from(Select row_num as rn, Ed.* from Employee_detail Ed) where Mod(rn,2)=0;

Select * from(Select row_num as rn, Ed.* from Employee_detail Ed) where Mod(rn,2)=1;

Conclusion

SQL is a mandatory language for any data enthusiast. It is the way to handle the huge volume of structured data. If you want to ace any data interview or software development interview, you should be at least familiar with all the SQL commands. This blog discussed the frequently asked queries in Data Engineering interviews. Key insights are:

1. What is SQL, and what are the possible ways to find the nth highest salary of an employee?

2. We have seen what the ways to perform pattern matching are.

3. We discussed how we could create a table from another existing table without data.

4. We discussed a brief about regular expressions.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.