Vikas Verma — Published On June 28, 2022 and Last Modified On July 12th, 2022
Intermediate Interview Questions Interviews SQL

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

Introduction

SQL stands for Structured Query Language. It’s a programming language to interact/query and manage RDBMS (Relational Database Management Systems). SQL skills are highly preferred and required as it’s used by many organizations in a large variety of software applications.

So as a fresher or experienced candidate if you are planning for an upcoming SQL interview, you need to make sure that you are prepared well for all sorts of conceptual, theoretical, and coding SQL questions. This article consists of 20 real-time scenario-based SQL coding interview questions that will help you to test your SQL skills and boost your confidence.

NOTE – All the SQL queries used here are compatible with Oracle database versions like 11g, 12c, 18c, etc.

SQL Coding Interview Questions

Consider the student table as shown below for question no. 1, 2, and 3.

SQL Coding
                                                              Student table

Q1. Write a query to extract username(characters before @ symbol) from the Email_ID column.

Answer: 

SELECT SUBSTR(Email_ID, 1, INSTR(Email_ID, '@') - 1) FROM STUDENT;

 

Extract the position of @ from the email id first using INSTR() function then pass this position(after subtracting 1) as an argument for length in SUBSTR() function. 

Output – 

SQL Coding

Q2. Write a query to extract domain name like .com, .in, .au etc. from the Email_ID column.

Answer: 

SELECT SUBSTR(Email_ID, INSTR(Email_ID, '.')) FROM STUDENT; 

Extract the position of . (dot character) from the email id first using INSTR() function then pass this position as an argument for starting position in SUBSTR() function.

Output – 

SQL Coding

Q3. Write a query to extract email service provider names like google, yahoo, outlook, etc. from the Email_ID column.

Answer: 

SELECT SUBSTR(Email_ID, INSTR(Email_ID, '@') + 1, INSTR(Email_ID, '.') - INSTR(Email_ID, '@') - 1) FROM STUDENT; 

Extract the position of @ from the email id first using INSTR() function, and pass it (after adding 1) as an argument for starting position in SUBSTR() function.

Now extract this position of . (dot character) and subtract it from the earlier extracted @ position and pass it (after subtracting 1) as an argument for length in SUBSTR() function.

Output – 

SQL Coding

Q4. What is(are) the output of the following query?

SELECT CEIL(-12.43), FLOOR(-11.92) FROM DUAL;

a. -13, -12

b. -12, -12

c. -12, -11

d. -13, -11

Answer: b

CEIL() function returns the smallest integer number that is greater than or equal to the given number. So if we pass -12.43 in ceil, it returns the smallest integer value that is >= -12.43 i.e. -12.

FLOOR() function returns the largest integer number that is less than or equal to the given number. So if we pass -11.92 in floor, it returns the largest integer value that is <= -11.92 i.e. -12.

Output – 

SQL Coding

Q5. Write a query to extract all the consonants present in your name.

Answer: 

SELECT TRANSLATE('Narendra', 'xaeiou', 'x') FROM DUAL;

First, extract the consonants from the input name as extracted above, then concatenate these consonants with the character ‘a’ in from_string argument to remove the consonants by not specifying corresponding characters in to_string argument. So if we pass Narendra as the name in the above query, it returns vowels (a, e, a).

Output –

Q6. Write a query to extract all the vowels present in your name.

Answer:

SELECT TRANSLATE('Narendra', 'a' || TRANSLATE('Narendra', 'xaeiou', 'x'), 'a') FROM DUAL; 

First, extract the consonants from the input name as extracted above, then concatenate these consonants with character ‘a’ in from_string argument to remove the consonants by not specifying corresponding characters in to_string argument. So if we pass Narendra as the name in the above query, it returns vowels (a, e, a).

Output – 

Refer to the emp table as shown below for question no. 7 to 14.

SQL Coding
                                                              emp table

Q7. Write a query to extract the employees’ details who joined in the year 1981.

Answer: 

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, 'YY') = 81;

Use TO_CHAR() to extract the year part from the hiredate column and select all the employees who were hired in 1981 by using WHERE clause.

Output –

SQL Coding

Q8. Write a query to find the hiked salary for each employee after adding the commission.

Answer: 

SELECT EMPNO, ENAME, NVL2(COMM, SAL+COMM, SAL) AS HIKEDSAL FROM EMP;

Since the commission column contains null values so directly adding it to salary will return null wherever the commission is null.

Use NVL2() function to determine hiked salary based on whether the commission is null or not null. If COMM (expr1) is not null then it returns SAL+COMM (expr2). If COMM is null then it returns SAL (expr3).

Output – 

SQL Coding

Q9. Write a query to find out the employees drawing salary more than their managers.

Answer: 

SELECT E.EMPNO, E.ENAME, E.SAL, M.EMPNO, M.ENAME, M.SAL FROM EMP E, EMP M WHERE E.MGR = M.EMPNO AND E.SAL > M.SAL;

Self join the emp table with itself to compare employees’ salary with their manager’s salary.

Output – 

Q10. Write a query to find out the subordinates (reportees) who joined the organization before their managers.

Answer: 

SELECT E.EMPNO, E.ENAME, E.HIREDATE, M.EMPNO, M.ENAME, M.HIREDATE FROM EMP E, EMP M WHERE E.MGR = M.EMPNO AND E.HIREDATE < M.HIREDATE;

Self join the emp table with itself to compare employees hiredate with their manager’s hiredate.

Output – 

Q11. Write a query to find out the employees who don’t have any subordinates (reportees) i.e. the employees who are not the managers.

Answer: 

SELECT * FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT NVL(MGR, 0) FROM EMP);

Using simple subquery first find out the list of distinct managers EMPNOs, then select the EMPNO which does not belong to managers EMPNOs.

Output – 

Q12. Write a query to find out 2nd senior-most employee i.e. who joined the organization second as per hire date.

Answer: 

SELECT * FROM EMP E WHERE 2 = (SELECT COUNT(DISTINCT M.HIREDATE) FROM EMP M WHERE E.HIREDATE >= M.HIREDATE);

Using correlated subquery we can get 2nd senior-most employee by comparing the inner query output against 2 in WHERE clause.

Output – 

Q13. Write a query to find out the 5th maximum salary.

Answer: 

SELECT * FROM EMP E WHERE 5 = (SELECT COUNT(DISTINCT M.SAL) FROM EMP M WHERE E.SAL <= M.SAL);

Using correlated subquery we can get the 5th maximum salary by comparing the inner query output against 5 in WHERE clause.

Output – 

Q14. Write a query to find out the deviation from average salary for the employees who are getting more than the average salary.

Note – Round the average salary, salary difference up to two digits.

Answer:

SELECT ENAME, SAL, ROUND((SELECT AVG(SAL) FROM EMP),2) AS AVG, ROUND(SAL - (SELECT AVG(SAL) FROM EMP),2) AS DIFF FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);

First, select the employees who are getting more than the average salary, then calculate the deviation from average salary for such employees.

Output – 

Refer to the dept table along with the above emp table for question no. 15 and 16.

                                                                      dept table

Q15. Write a query to find out the employees who are getting the maximum salary in their departments.

Answer: 

SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

Using simple subquery first get a list of maximum salary for each department using group by operation, then select the employees who are getting salary as per that list.

Output – 

Q16. Write a query to find out department-wise minimum salary, maximum salary, total salary, and average salary.

Answer: 

SELECT D.DEPTNO, MIN(SAL), MAX(SAL), SUM(SAL), AVG(SAL) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY D.DEPTNO;

First inner join employee and department table then group by DEPTNO to find out minimum, maximum, total and average salary for each department.

Output – 

Q17. Consider the present table structure and desired table structure of the customer table as shown below.

 

                 customer table – present and desired schema

Choose the correct statement(s) that will result in the desired table.

a. ALTER TABLE CUSTOMER RENAME CustName to Name;

b. ALTER TABLE CUSTOMER RENAME COLUMN CustName to Name;

c. ALTER TABLE CUSTOMER ADD Email VARCHAR2(35);

d. ALTER TABLE CUSTOMER MODIFY Email VARCHAR2(35);

e. ALTER TABLE CUSTOMER DROP FamilySize;

f. ALTER TABLE CUSTOMER DROP COLUMN FamilySize;

Answer: b, d, e

Rename CustName column as Name using –

ALTER TABLE CUSTOMER RENAME COLUMN CustName to Name;

Modify Email column datatype from VARCHAR2(25) to VARCHAR2(35) using –

ALTER TABLE CUSTOMER MODIFY Email VARCHAR2(35);

Drop FamilySize column using –

ALTER TABLE CUSTOMER DROP FamilySize;

Q18. Consider the following table schema and data for the transaction table. 

transaction table
                       transaction table – schema and data

Choose the valid UPDATE statement(s)

a. UPDATE TRANSACTION SET PrimeStatus = ‘Yes’ WHERE TransactionID = 1102348

b. UPDATE TRANSACTION SET PrimeStatus = ‘VALID’ WHERE CustName = ‘John’

c. UPDATE TRANSACTION SET TransactionID = NULL WHERE CustName = ‘John’

d. UPDATE TRANSACTION SET ShoppingDate = NULL WHERE TransactionID = 1102348

Answer: d

CHECK constraint allows only a set of predefined values so here only Y or N is allowed for PrimeStatus column.

NOT NULL constraint does not allow NULL values so TransactionID can’t be set to NULL.

We can insert NULL values in ShoppingDate Column.

Output –

UPDATE TRANSACTION SET ShoppingDate = NULL WHERE TransactionID = 1102348;

Q19. What is(are) the output of the following SQL statement?

SELECT TRANSLATE('AWESOME', 'WOE', 'VU') FROM DUAL;

a. AVESUME

b. AVSOM

c. AVSUM

d. AWESUME

Answer: c

For an input string, TRANSLATE() function replaces characters specified in from_string argument by their corresponding characters in to_string argument. If there are no corresponding characters in to_string argument then the extra characters present in from_string argument are removed from the input string.

SELECT TRANSLATE('AWESOME', 'WOE', 'VU') FROM DUAL;

Output – 

Q 20. Consider the emp and insurance table as shown below.

                            emp table and insurance table

How to get the following output?

SQL Coding

a. SELECT * FROM EMP1 LEFT JOIN INSURANCE ON (EMP1.INSURANCETYPE = INSURANCE.INSURANCETYPE);

b. SELECT * FROM EMP1 JOIN INSURANCE ON (EMP1.INSURANCETYPE = INSURANCE.INSURANCETYPE);

c. SELECT * FROM EMP1 RIGHT JOIN INSURANCE ON (EMP1.INSURANCETYPE = INSURANCE.INSURANCETYPE);

d. SELECT * FROM EMP1 FULL JOIN INSURANCE ON (EMP1.INSURANCETYPE = INSURANCE.INSURANCETYPE);

Answer: c, d

Right join returns all the records from the right table along with the matched records from the left table.

SQL Coding

Here, since all the emp (left) table’s insurance types are present in the insurance (right) table so full outer join will also return the same output.

NOTE – While analyzing the outputs, focus on the record values rather than their sequence in the output.

SQL Coding

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 *