In this blog we have tried to capture some scenario based sql queries or similar queries which are very frequently asked in many interviews. We have used oracle live sql for execution.

At first we are creating a set of tables, which have been used in the following section.

create table employees as select * from hr.employees;
create table jobs as select * from hr.jobs;
create table departments as select * from hr.departments;

Table created. Table created. Table created.
select * from employees fetch first 5 rows only;


EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID


100StevenKingSKING515.123.456717-JUN-03AD_PRES24000- - 90


101NeenaKochharNKOCHHAR515.123.456821-SEP-05AD_VP17000- 10090


102LexDe HaanLDEHAAN515.123.456913-JAN-01AD_VP17000- 10090


103AlexanderHunoldAHUNOLD590.423.456703-JAN-06IT_PROG9000- 10260


104BruceErnstBERNST590.423.456821-MAY-07IT_PROG6000- 10360
select * from jobs fetch first 5 rows only;
JOB_IDJOB_TITLEMIN_SALARYMAX_SALARY
AD_PRESPresident2008040000
AD_VPAdministration Vice President1500030000
AD_ASSTAdministration Assistant30006000
FI_MGRFinance Manager820016000
FI_ACCOUNTAccountant42009000

select * from departments fetch first 5 rows only;
DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_ID
10Administration2001700
20Marketing2011800
30Purchasing1141700
40Human Resources2032400
50Shipping1211500


1) Calculate average salaries for each job title present in the system. Please also show the department names associated with job title.


As the required information spreads over all tables we need to join these tables and then we will use average aggregate function to calculate the average salary by creating groups for job_id,job_title,department_name.
select emp.job_id,jb.job_title,dr.department_name,round(avg(emp.salary),2) as avg_sal from employees emp inner join departments dr on emp.department_id=dr.department_id inner join jobs jb on emp.job_id=jb.job_id group by emp.job_id,jb.job_title,dr.department_name order by avg_sal desc
or
In this query we are calculating the average salary from employees table and stores the result in a temporary table 'avg_sal' then it was joined with other two tables to fetch department and job title details.
with avg_sal as (select job_id,department_id,avg(salary)  as avg_salary from employees group by job_id,department_id)select avg_sal.job_id,jb.job_title,dr.department_name,round(avg_sal.avg_salary,2) as avg_sal from avg_sal inner join departments dr on avg_sal.department_id=dr.department_id inner join jobs jb on avg_sal.job_id=jb.job_id order by avg_sal.avg_salary desc


Result:



JOB_IDJOB_TITLEDEPARTMENT_NAMEAVG_SAL
AD_PRESPresidentExecutive24000
AD_VPAdministration Vice PresidentExecutive17000
MK_MANMarketing ManagerMarketing13000
SA_MANSales ManagerSales12200
FI_MGRFinance ManagerFinance12008
AC_MGRAccounting ManagerAccounting12008
PU_MANPurchasing ManagerPurchasing11000
PR_REPPublic Relations RepresentativePublic Relations10000
SA_REPSales RepresentativeSales8396.55
AC_ACCOUNTPublic AccountantAccounting8300
FI_ACCOUNTAccountantFinance7920
ST_MANStock ManagerShipping7280
HR_REPHuman Resources RepresentativeHuman Resources6500
MK_REPMarketing RepresentativeMarketing6000
IT_PROGProgrammerIT5760
AD_ASSTAdministration AssistantAdministration4400
SH_CLERKShipping ClerkShipping3215
ST_CLERKStock ClerkShipping2785
PU_CLERKPurchasing ClerkPurchasing2780


2) Find out the employee details whose salary is more than the average salary of the employee's job profile.



In the below SQL, at the very first we have calculated the average salary for every job_id than this temporary table joined with employees tables to compare the employee salary with the avg_salary.

with avg_sal as (select job_id,department_id,round(avg(salary),2)  as avg_salary from employees group by job_id,department_id)
select e1.employee_id,e1.first_name,e1.last_name,e1.salary,avg_sal.job_id,j1.job_title,avg_sal.avg_salary from 
avg_sal inner join employees e1 on e1.job_id=avg_sal.job_id inner join jobs j1 on avg_sal.job_id=j1.job_id where e1.salary>avg_sal.avg_salary order by avg_salary desc
fetch first 10 rows only;

*Due to long list of rows, fetching only first 10 rows.

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYJOB_IDJOB_TITLEAVG_SALARY
145JohnRussell14000SA_MANSales Manager12200
146KarenPartners13500SA_MANSales Manager12200
150PeterTucker10000SA_REPSales Representative8396.55
163DanielleGreene9500SA_REPSales Representative8396.55
162ClaraVishney10500SA_REPSales Representative8396.55
158AllanMcEwen9000SA_REPSales Representative8396.55
157PatrickSully9500SA_REPSales Representative8396.55
156JanetteKing10000SA_REPSales Representative8396.55
152PeterHall9000SA_REPSales Representative8396.55
151DavidBernstein9500SA_REPSales Representative8396.55


3)  Find out the second highest salary from employees tables.

select max(salary) from employees where salary not in (select max(salary) from employees)


MAX(SALARY)
17000


(select max(salary) from employees) --- gives the maximum salary i.e 24000





select salary from employees where salary not in (24000) --- gives all salary except 24000, i.e we are excluding the maximum salary from the salary list
select max(salary) from employees where salary not in (24000) --- this will select the maximum salary from the list of salaries mentioned above, so certainly it will give us the second highest salary.

4) Show alternative rows from job tables.


select * from (select rownum as rn,jb.* from jobs jb) where mod(rn,2) <> 0 

RNJOB_IDJOB_TITLEMIN_SALARYMAX_SALARY
1AD_PRESPresident2008040000
3AD_ASSTAdministration Assistant30006000
5FI_ACCOUNTAccountant42009000
7AC_ACCOUNTPublic Accountant42009000
9SA_REPSales Representative600012008
11PU_CLERKPurchasing Clerk25005500
13ST_CLERKStock Clerk20085000
15IT_PROGProgrammer400010000
17MK_REPMarketing Representative40009000
19PR_REPPublic Relations Representative450010500

here we are selecting odd rows , this can be done by displaying even rows by changing   the where clause as mod(rn,2) = 0