20 SQL Coding Interview Questions

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

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

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

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 – 

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 –

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.

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 –

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 – 

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 – 

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 – 

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 – 

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 – 

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 – 

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.

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 – 

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 – 

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. 

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;

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 – 

Answer: c, d

 

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

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.

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