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_ID | FIRST_NAME | LAST_NAME | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | |
---|---|---|---|---|---|---|---|---|---|---|
100 | Steven | King | SKING | 515.123.4567 | 17-JUN-03 | AD_PRES | 24000 | - | - | 90 |
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 21-SEP-05 | AD_VP | 17000 | - | 100 | 90 |
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 13-JAN-01 | AD_VP | 17000 | - | 100 | 90 |
103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 03-JAN-06 | IT_PROG | 9000 | - | 102 | 60 |
104 | Bruce | Ernst | BERNST | 590.423.4568 | 21-MAY-07 | IT_PROG | 6000 | - | 103 | 60 |
JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY |
---|---|---|---|
AD_PRES | President | 20080 | 40000 |
AD_VP | Administration Vice President | 15000 | 30000 |
AD_ASST | Administration Assistant | 3000 | 6000 |
FI_MGR | Finance Manager | 8200 | 16000 |
FI_ACCOUNT | Accountant | 4200 | 9000 |
select * from departments fetch first 5 rows only;
DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID |
---|---|---|---|
10 | Administration | 200 | 1700 |
20 | Marketing | 201 | 1800 |
30 | Purchasing | 114 | 1700 |
40 | Human Resources | 203 | 2400 |
50 | Shipping | 121 | 1500 |
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_ID | JOB_TITLE | DEPARTMENT_NAME | AVG_SAL |
---|---|---|---|
AD_PRES | President | Executive | 24000 |
AD_VP | Administration Vice President | Executive | 17000 |
MK_MAN | Marketing Manager | Marketing | 13000 |
SA_MAN | Sales Manager | Sales | 12200 |
FI_MGR | Finance Manager | Finance | 12008 |
AC_MGR | Accounting Manager | Accounting | 12008 |
PU_MAN | Purchasing Manager | Purchasing | 11000 |
PR_REP | Public Relations Representative | Public Relations | 10000 |
SA_REP | Sales Representative | Sales | 8396.55 |
AC_ACCOUNT | Public Accountant | Accounting | 8300 |
FI_ACCOUNT | Accountant | Finance | 7920 |
ST_MAN | Stock Manager | Shipping | 7280 |
HR_REP | Human Resources Representative | Human Resources | 6500 |
MK_REP | Marketing Representative | Marketing | 6000 |
IT_PROG | Programmer | IT | 5760 |
AD_ASST | Administration Assistant | Administration | 4400 |
SH_CLERK | Shipping Clerk | Shipping | 3215 |
ST_CLERK | Stock Clerk | Shipping | 2785 |
PU_CLERK | Purchasing Clerk | Purchasing | 2780 |
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_ID | FIRST_NAME | LAST_NAME | SALARY | JOB_ID | JOB_TITLE | AVG_SALARY |
---|---|---|---|---|---|---|
145 | John | Russell | 14000 | SA_MAN | Sales Manager | 12200 |
146 | Karen | Partners | 13500 | SA_MAN | Sales Manager | 12200 |
150 | Peter | Tucker | 10000 | SA_REP | Sales Representative | 8396.55 |
163 | Danielle | Greene | 9500 | SA_REP | Sales Representative | 8396.55 |
162 | Clara | Vishney | 10500 | SA_REP | Sales Representative | 8396.55 |
158 | Allan | McEwen | 9000 | SA_REP | Sales Representative | 8396.55 |
157 | Patrick | Sully | 9500 | SA_REP | Sales Representative | 8396.55 |
156 | Janette | King | 10000 | SA_REP | Sales Representative | 8396.55 |
152 | Peter | Hall | 9000 | SA_REP | Sales Representative | 8396.55 |
151 | David | Bernstein | 9500 | SA_REP | Sales Representative | 8396.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
RN | JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY |
---|---|---|---|---|
1 | AD_PRES | President | 20080 | 40000 |
3 | AD_ASST | Administration Assistant | 3000 | 6000 |
5 | FI_ACCOUNT | Accountant | 4200 | 9000 |
7 | AC_ACCOUNT | Public Accountant | 4200 | 9000 |
9 | SA_REP | Sales Representative | 6000 | 12008 |
11 | PU_CLERK | Purchasing Clerk | 2500 | 5500 |
13 | ST_CLERK | Stock Clerk | 2008 | 5000 |
15 | IT_PROG | Programmer | 4000 | 10000 |
17 | MK_REP | Marketing Representative | 4000 | 9000 |
19 | PR_REP | Public Relations Representative | 4500 | 10500 |
1 Comments
The interview set will be very helpful for cracking interviews rekated to Sql
ReplyDeletePost a Comment