Shikha Sharma — Published On October 10, 2022 and Last Modified On November 4th, 2022
Beginner Interview Questions 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.

SQL Interview Questions and Answers | PassMyInterview.com

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

Row_num          EmpId
FullName
Designation
DateOfJoining
City
1 121 Rachit Tomar Apprentice 01/31/2014 Paris
2 321 Jag Reddy Developer 01/30/2015 New Delhi
3 421 Vasa Trisha Team Lead 27/11/2016 Chennai

Table – Employee_salary

EmpId  NetPay Variable
121 85000 2000
321 10000 5000
421 70000 3000

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

  1. 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;
  1. 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]%’;
  1. 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.

Q4:-Write a query to create a table with the same structure as the Employee_detail table with and without data.

Solution:

  1. Query to create a table without data

 Create table Employee_details2 as Select * from Employee_detail where 1=2;
  1. Query to create a table with data

Create table Employee_details2 as select * from Employee_detail;

Explanation:- We use the ‘as’ keyword to take reference from another table and using a false condition, i.e., 1 equals 2, we restrict the entry of data in the new table.

Q5:- Write a query to display the first 50% of records and the last 50% records from the Employee_detail table.

Solution:

  1. Query to display the first 50% of records

select row_num, e1.* from Employee_detail e1 where row_num<=(select count(*)/2 from Employee_detail);
  1. Query to display the last 50% of records

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)

Explanation:- In this query, we use the count(*) function, which will count the total number of rows and then divide them into two parts using the ‘/’ operator.

Q6:- Write a SQL query to fetch distinct/unique records with or without using distinct keywords.

Solution :

  1. Query using a Distinct keyword

select distinct * from Employee_detail;
  1. Query without using the Distinct keyword

select * from Employee_detail union select * from Employee_detail;

Explanation:- In this query, we use the union keyword to compare employee_detail with itself and print only the distinct records.

Q7:-Write a SQL query to split a string ”SHIKHA” into multiple columns.

For example, Input: SHIKHA

Output:

S

H

I

K

H

A

Solution:

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

Explanation: To understand this query, we need to know about the following terms:

Substr():- This function fetches the substring from the given string.

Level:- It is used to find the hierarchy level of columns in numeric format.

Connect By:- As the name suggests, it defines the parent-child hierarchy relationship.

Length:- This function calculates the size of the string passed.

Dual:- It is nothing but a dummy table created by Oracle.

Q8:-Write a query to add the email validation with the help of only one query.

Solution:

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

Explanation:- To perform the email validation in SQL, we use the aggregate function REGEXP_LIKE. The regular expression will set a rule of valid email, and like will compare and match the string.

Q9:- Write a query to count and remove duplicate rows from the Employee_detail table.

Solution:

  1. Query to count the duplicate or redundant rows:

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

Explanation:- To count the duplicate rows, we use the aggregate functions along with Group By and Having clause. Here, we group the employee details with the same EmpId and arrange them in descending order.

  1. To remove the redundant rows:

  1. Firstly we have to select the duplicate rows:

Select EmpId FROM Employee_detail e1 WHERE EmpId 
(Select max (EmpId) from Employee_detail e2 where e1.EmpId=e2.EmpId);
  1. Lastly, delete the duplicate rows:

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

Explanation:- In this query, we’re creating the aliases of the Employee table as e1 and e2, we compare the Id of the Employee, and if it is the same, then we delete that row from the table permanently.

Q10:- Write a query to find the even rows and odd rows from the Employee_detail table.

Solution:

  1. Query to find the even rows from the Employee_detail table :

Select * from(Select row_num as rn, Ed.* from Employee_detail Ed) where Mod(rn,2)=0;
  1. Query to find the odd rows from the Employee_detail table:

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

Explanation:- In both queries, we use the aggregate function “mod,” which calculates the modulo of the row number, and if it is divisible by true, then displays in even(output of query1); otherwise, if the remainder is 1, i.e., not divisible by 2 then displays the employee information in query 2.

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.

About the Author

Our Top Authors

Download Analytics Vidhya App for the Latest blog/Article

Leave a Reply Your email address will not be published. Required fields are marked *