Top 40 SQL Queries & Syntax Updated For Manual & Automation Testers

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 second alphabet A in their names
Ans: Select Ename
From Emp
Where Ename Like ‘_s%’;

Display 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;

Read Also: SQL Syntax

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;

Read Also: SQL Joins ( Inner + Outer Joins )

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

Searches: Important 40 SQL Queries, 40 SQL Queries & Questions, Most Searched 40 SQL Queries, Best 40 SQL Queries, Top 40 SQL Queries