Skip to main content

SQL Query and Answers - Part2

26.Display the total number of employees working in the company.
  Select count(*) from emp;


27. Display the total salary being paid to all employees.
  Select sum(sal)+sum(nvl(comm.,0)) form emp;


28. Display the maximum salary from emp table.
Select max(sal ) from emp;


29. Display the minimum salary from emp table.
Select min(sal) from emp;


30. Display the average salary from emp table.
Select avg(sal) from emp;


31. Display the maximum salary being paid to CLERK.
Select max(sal ) from emp  where job=’clerk’;


32. Display the maximum salary being paid in dept no 20.
Select max(sal ) from emp  where deptno=20;


33. Display the min salary being paid to any SALESMAN
Select max(sal ) from emp  where job=’ SALESMAN’;


34. Display the average salary drawn by managers.
Select avg(sal) from emp where job=’MANAGER’;


35. Display the total salary drawn by analyst working in dept no 40.
Select sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;


36. Display the names of employees in order of salary i.e. the name of the employee earning  lowest salary should appear first.
Select ename from emp order by sal;


37.  Display the names of employees in descending order of salary
Select ename from emp order by sal desc;


38. Display the details from emp table in order of emp name.
Select ename from emp order by ename;


39. Display empno,ename,deptno and sal .Sort the output first based on name and within name by deptno  and withindeptno by sal;
 Select * from emp order byename,deptno,sal;


40. Display the name of the employee along with their annual salary (sal*12).The name of the employee earning highest annual salary should appear first.
  Select ename,12*(sal+nvl(comm,0)) Annual from emp order by  12*(sal+nvl(comm,0)) desc;


41. Display name ,Sal,hra,pf,da,total sal for each employee.The Output should be in the order of total sal, hra 15% of sal , da 10% of sal , pf 5% of sal total salary will be (sal*hra*da)-pf.
Select ename,sal,sal*15/100 HRA, SAL*5/100 pf,SAL*10/100 da,sal+sal*15/100-sal*5/100 Total_SALARY from emp


42. Display dept numbers and total number of employees within each group.
Select deptno,count(*) from emp group by deptno;


43. Display the various jobs and total number of employees with  each job group.
Select  job,count(*) from emp group by job;


44. Display department numbers and total salary for each department.
Select deptno, sum(sal)  from emp group by deptno;


45. Display department numbers and maximum salary for each department.
Select deptno, max(sal)  from emp group by deptno;


46. Display the various jobs and total salary for each job.
Select  job, sum(sal)  from emp group by job;


47. Display each jobs along  with minimum sal being paid in each job group.
Select  job,min(sal)  from emp group by job;


48.Display the department numbers with more than three employees  in each dept.
Select deptno,count(*) from emp group by deptno having count(*)>3;


49. Display the various jobs along with total sal for each of the jobs where total sal is greater than 40000
Select  job, sum(sal)  from emp group by job having sum(sal)>40000;


50. Display the various jobs along with total number of employee in each job.the output should contain only those jobs with more than three employees.
  Select job,count(*) from emp group by job having count(*)>3.

Comments

Popular posts from this blog

OBIEE 11.1.1.7.0 Step by Step Installation

This post will guide us through the  step by step installation of OBIEE 11.1.1.7.0. Here I don't find any difference in RCU installation between in previous version with this version. Still if you want to know the RCU installation stepsclick here
Step1: Download the OBIEE 11g versionfrom here or from oracle e-delivery site and extract all the four disk into a single folder and click setup.exe from the Disk1
Universal Installer window will start and will check for the initial set up and system requirement. After the successful precheck the first step of our OBIEE 11.1.1.7.0 starts with the below window. Just Click 'Next'
Step2:
If you have oracle Support username and password then update then use that in this step or just select first option 'Skip Software updates' and click 'Next'
Step3:
Select the appropriate installation step here. I am going to install all the component of BI so selected here 'Enterprise Install' and then click 'Next'
Step4:
This …

Step by Step Installation of Oracle 11g 64bit in Windows7 64bit

This Post will give you step by step information of Oracle database 11g 64 bit version installation in windows 7 64 machine. If you prefer with Oracle Database 11g installation in Windows 64bit machine, This post will help you to move forward.
Prerequisites Checks: RequirementMinimum ValuePhysical memory (RAM)1 GB minimum
On Windows 7 and Windows 8, 2 GB minimumVirtual memoryIf physical memory is between 2 GB and 16 GB, then set virtual memory to 1 times the size of the RAM
If physical memory is more than 16 GB, then set virtual memory to 16 GB
Disk spaceTotal: 5.39 GBProcessor TypeAMD64, or Intel Extended memory (EM64T)Video adapter256 colorsScreen Resolution1024 X 768 minimum
Download oracle Database from oracle site and unarchive the zipped download file.
Step1:  Just double click on setup.exe from the unarchived folder of oracle database source. Step2: Oracle Universal Installer will check the disk space and initiates the installation Step3: If you have a oracle metalink ID then type your em…

ADF_FACES-60097 : For more information, please see the server's error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1