SQL Queries Interview Questions

SQL Queries Interview Questions: SQL (Structured Query Language) is a programming language widely used for managing and manipulating data in relational databases. It is an essential skill for anyone working with databases, and SQL queries interviews are common for data analysis, database administration, and software development jobs. SQL queries extract data from databases, allowing users to filter, sort, and aggregate data based on specific criteria.

In this context, sql queries interview questions are designed to test a candidate’s knowledge of SQL syntax, ability to write complex queries, and understanding of database concepts. This article will explore some of the most common sql queries interview questions and provide tips on preparing for them.

SQL Queries Interview Questions

Display employee number and total salary for each employee
Ans: Select Empno, Sal + Comm
From Emp;

Display the names of employees whose name starts with alphabet S
Ans: Select Ename
From Emp
Where Ename Like ‘s%’;

Display the names of employees whose names have the second alphabet A in their names.
Ans: Select Ename
From Emp
Where Ename Like ‘_s%’;

Display the employee name and department name for each employee
Ans: Select Ename, Dname
From Emp E, Dept D
Where E.deptno = D.deptno;

Display employee number, name and location of the department in which he is working
Ans: Select Empno, Ename, Loc
From Emp E, Dept D
Where E.detpno = D.deptno;

Display ename,dname even if there no employees working in a particular department(use outer join)
Ans: Select name, Dname
From Emp E, Dept D
Where E.deptno (+) = D.deptno;

select ename if ename exists more than once
Ans: Select Distinct(Ename)
From Emp E
Where Ename In (Select Ename From Emp Where E.empno<>empno);

Display the dept no with highest annual remuneration bill as compensation
Ans: Select Deptno, sum(Sal)
From Emp
Group By Deptno Having Sum(Sal)=(Select Max(Sum(Sal)) From Emp Group By Deptno);

List out the lowest-paid employees working for each manager, exclude any groups where minsal is less than 1000 sort the output by sal
Ans : Select E.ename,e.mgr,e.sal
From Emp E
Where Sal In (Select Min(Sal) From Emp Where Mgr=e.mgr) And
E.sal>1000 Order By Sal;

Display current date
Ans: Select Sysdate
From Dual;

Display various jobs along with the total salary for each of the job where the total salary is greater than 40000?
Ans: Select Job, sum(Sal)
From Emp
Group By Job Having Sum(Sal)>40000;

Display the name of an employee who earns the Highest Salary?
Ans: Select Ename, Sal
From Emp
Where Sal>=(Select Max(Sal) From Emp );

Display the employee number and name for an employee working as a clerk and earning the highest salary among the clerks?
Ans : select ename,empno
from emp
where sal=(select max(sal) from emp wherejob=’CLERK’) and job=’CLERK’ ;

Display the employee names who are Working in Chicago?
Ans: Select E.ename, D.loc
From Emp E, Dept D
Where E.deptno = D.deptno And D.loc= ‘Chicago’;

Display the job groups having Total Salary greater than the maximum salary for Managers?
Ans: Select Job, sum (Sal)
From Emp
Group By Job Having Sum(Sal) >( Select Max ( Sal) From Emp Where Job=’manager’);

Display the names of employees from department number 10 with a salary greater than that of ANY employee working in other departments?
Ans: Select Ename, Deptno
From Emp
Where Sal>any(Select Min(Sal) From Emp Where Deptno!=10 Group By Deptno) And Deptno=10 ;

Display the names of employees from department number 10 with a salary greater than that of ALL employee working in other departments?
Ans: Select Ename, Deptno
From Emp where Sal > All ( Select Max ( Sal) From Emp Where Deptno !=10 Group By Deptno) And Deptno=10 ;

Display the names of employees in Upper Case?
Ans: Select Upper ( Ename)
From Emp;

Display the names of employees in Lower Case?
Ans: Select Lower ( Ename)
From Emp;

Display the length of all the employee names?
Ans: Select Length ( Ename) From Emp;

Display the name of employee Concatenate with Employee Number?
Ans : Select Ename ||’ ‘|| Empno
From Emp;

Display the information from the employee table. Where ever the job Manager is found it should be displayed as Boss?
Ans : Select Ename, Replace ( Job, ‘manager’, ‘boss’)
From Emp;

Display those who are not managers?
Ans: Select Ename
From Emp
Where Job != ‘manager’;

Display the details of those employees who are in the sales department and grade is 3?
Ans: Select E.ename, D.dname, Grade
From Emp E, Dept D, Salgrade
Where E.deptno = D.deptno And Dname= ‘sales’ And Grade=3;

Display that department whose name starts with”S” while location name ends with “K”?
Ans: Select E.ename, D.loc
From Emp E, Dept D
Where D.loc Like(‘%k’) And Ename Like(‘s%’);

Display those employees whose manager name is Jones?
Ans: Select E.ename Superior, E1.ename Subordinate
From Emp E, E1
Where E.empno= E1.mgr And E.ename=’jones’;

Display those employees whose salary is more than 3000 after giving 20% increment?
Ans: Select Ename, Sal, ( Sal + ( Sal *0.20))
From Emp
Where ( Sal + ( Sal *0.20))>3000;

Display employee name, dept name, salary, and commission for that sal in between 2000 to 5000 while the location is Chicago?
Ans : Select E.ename, D.dname, E.sal, E.comm
From Emp E, Dept D
Where E.deptno = D.deptno And Sal Between 2000 And 5000;

Display those employees whose salary is greater than his manager’s salary?
Ans : Select E.ename, E.sal, E1.ename, E1.sal
From Emp E, E1
Where E.mgr = E1.empno And E.sal > E1.sal;

Display the grade and employees name for the deptno 10 or 30, but the grade is not 4 while joined the company before 31-DEC-82?
Ans : Select Ename,grade,deptno,sal
From Emp, salgrade
Where ( Grade, sal) In ( Select Grade, sal From Salgrade, emp Where Sal Between Losal And Hisal)
And Grade!=4 And Deptno In (10,30) And Hiredate<’31-Dec-82′;

Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
Ans : Select E.ename,e.hiredate,d.loc
From Emp E,dept D
Where E.deptno=d.deptno And Hiredate<’31-dec-82′ And D.loc In(‘new York’,’chicago’);

List out all the employee names ,job,salary,grade and deptname for every one in a company except ‘CLERK’ . Sort on salary display the highest salary?
Ans : Select E.ename, E.job, E.sal, D.dname, Grade
From Emp E,salgrade, dept D
Where (E.deptno = D.deptno And E.sal Between Losal And Hisal )
Order By E.sal Desc;

Display employee name, sal, comm, and netpay for those employees whose netpay is greater than or equal to any other employee salary of the company?
Ans : Select Ename,sal,nvl(Comm,0),sal+nvl(Comm,0)
From Emp
Where Sal+nvl(Comm,0) >any (Select E.sal From Emp E );

Display those employees whose salary is less than his manager but more than the salary of other managers?
Ans : Select E.ename Sub,e.sal
From Emp E,e1,dept D
Where E.deptno=d.deptno And E.mgr=e1.empno And E.sal<e1.sal And E.sal >any (Select E2.sal From Emp E2, E,dept D1
Where E.mgr=e2.empno And D1.deptno=e.deptno);

Delete those records from emp table whose deptno not available in dept table?
Ans: Delete From Emp E
Where E.deptno Not In (Select Deptno From Dept);

Display those enames whose salary is out of grade available in a salgrade table?
Ans: Select Empno, sal
From Emp
Where Sal<(Select Min(Losal) From Salgrade )
Or Sal>(Select Max(Hisal) From Salgrade);

Display those employees whose salary is a unique value?
Ans: Select Ename, Sal
From Emp
Where Mod(Sal,2)!=0;

Display those employees who joined the company in Dec?
Ans: Select Empno,ename
From Emp
Where Trim(To_char(Hiredate,’mon’))=trim(‘dec’);

Delete those records where no of employees in a particular department is less than 3?
Ans: Delete From Emp
Where Deptno In (Select Deptno From Emp Group By Deptno Having Count (*) < 3 ;

Display empno,ename,deptno from emp table. Instead of display department numbers display the related department name(Use decode function)?
Ans : Select Empno,ename,deptno,decode (Deptno,10,’accounting’ ,20,’research’,30,’sales’,’operations’)
Dname From Emp;

Display the Second maximum salary from the table using subquery.
Ans: Select Max(Salary)
From Employee
Where Salary Not In (Select Max(Salary) From Employee);

Display the Second maximum salary from the table using a subquery.
Ans: Select Top 1 Salary From ( Select Top 2 Salary From Employees Order By Salary Desc) As Emp
Order By Salary Asc

Conclusion:

In our experience, mastering SQL queries is crucial for any database professional, and candidates who excel in this area are highly sought after. Additionally, SQL queries interview questions provide a platform for candidates to showcase their problem-solving skills and ability to work with large and complex databases.

We welcome you to share your interview experience, and sql queries interview questions in the comment section. Your input will help us improve our interview process and provide valuable insights to fellow readers. Please feel free to share your thoughts and feedback; we will be happy to address any queries or concerns.