SQL QUERIES - FOR ANALYSIS: June 2017

1) Display the details of all employees

ANS: select * from emp;

. 2) Display the dept information from department table

ANS: select * from dept;

3. 3) Show the Employee Name, Employee No, Salary

ANS: select ename, empno,sal from emp;

5. 4) SHOW EMPLOYEES WISE TOTAL MONTHLY SALARY ( MONTHLY SALARY = SAL + COMM)

FOR SOME EMPLOYEES COMMISSION IS NULL, WE HAVE TO REPLACE THEM BY 0(ZERO) TO PERFORM ARITHMETIC CALCULATION

ANS: SELECT ROWNUM, ENAME, EMPNO,SAL, COMM,SAL+ NVL(COMM,0) AS TOTAL_SALARY FROM SCOTT.EMP ;

7. LIST OUT THE EMPLOYEES WHO ARE WORKING IN DEPT NO 10

ANS: SELECT ROWNUM, a.* FROM SCOTT.EMP a WHERE DEPTNO = 10 ;

-- LIST OUT THE EMPLOYEES WHO ARE WORKING IN DEPT NO =10 OR DEPT NO =20

SELECT ROWNUM, a.* FROM SCOTT.EMP a WHERE DEPTNO IN ( 10, 20) ORDER BY DEPTNO ;

===============================

-- LIST OUT THE EMPLOYEES WHOSE SALARY IS MORE THAN ARE EQUAL TO 2000

SELECT ROWNUM, a.* FROM SCOTT.EMP a WHERE SAL >= 2000 ;

-- LIST OUT THE EMPLOYEES WHOSE JOB IS MANAGER AND SALARY >=3000

SELECT ROWNUM, a.* FROM SCOTT.EMP a WHERE SAL >=3000 AND JOB = 'MANAGER' ;

-- >, <, >=, <= , = ARE CALLED COMPARISON OPERATORS IN ORCLE ( ALSO IN OTHER LANGUAGES)

SELECT ROWNUM, a.* FROM SCOTT.EMP a ORDER BY DEPTNO ASC, SAL DESC ;

TO ANSWER THIS QUESTION, LET US FIRST LOOK AT THE SALARY & JOB OF THE ENTIRE TABLE THEN FILTER AS WE WANT.

----------------

-- LIST OUT THE EMPLOYEES DETAILS WHOSE SAL<2000

SELECT ROWNUM, a.* FROM SCOTT.EMP a WHERE SAL < 2000 ;

---------------------------

THE BELOW IMAGE IS SAYING THAT THERE IS NO EMPLOYEE AS MANAGER WITH SALARY > 3000

------------------------------

-- LIST OUT THE EMPLOYEES WHOSE JOB IS ANALYST AND SALARY >=3000

SELECT ROWNUM, a.* FROM SCOTT.EMP a WHERE SAL >=3000 AND JOB = 'ANALYST' ;

THE BELOW IMAGE IS SAYING THAT THERE 2 EMPLOYEES MEETING THE REQUIREMENT OF ' JOB IS ANALYST & SAL >=3000'

=====================================

SHOW THE EMPLOYEES NAME LOWER, UPPER CASES.

----------------------- -- SHOW THE EMPLOYEES WHOSE NAME CONTAINS EXACTLY 5 CHARACTERS

SELECT ROWNUM, LOWER(ENAME) NAME_LOWER_CASE, UPPER(ENAME) NAME_UPPER_CASE, LENGTH(ENAME) LENGTH FROM SCOTT.EMP

WHERE LENGTH(ENAME) = 5 ;

--------------------- SHOW THE EMPLOYEES WHOSE NAME CONTAINS >=6 CHARACTERS

=================

SHOW THE EMPLOYEES WHOSE NAME STARTS WITH LETTER 'S'

---------------

SHOW THE EMPLOYEES NAMES ENDS WITH LETTER 'S'

=-------------------

SHOW THE EMPLOYEES NAMES EITHER STARTS OR ENDS WITH LETTER 'S'

====================================

-- Display the names of employees who are not working as SALESMAN or CLERK or ANALYST

SELECT ROWNUM, ENAME,JOB,MGR FROM SCOTT.EMP

WHERE JOB NOT IN('CLERK','ANALYST','SALESMAN');

========================== ===============

ARITHMETIC CALCULATIONS

==================================

LOGARITHMIC CALCULATIONS

=========================================

NEAREST INTEGER VALUES

==========================

SHOW THE NUMBER OF EMPLOYEES DEPT WISE

=====================

DEPT WISE SALARY SUMMARY STATISTICS

======================================================

-- LIST OUT THE DEPT WHICH ARE HAVING MORE THAN 3 EMPLOYEES

SELECT DEPTNO, COUNT(ENAME) NUMBER_OF_EMPLOYEES FROM SCOTT.EMP

GROUP BY DEPTNO

HAVING COUNT(ENAME)> 3

ORDER BY DEPTNO ASC;

-----------------------------------

LIST OUT THE EMPLOYEES WHOSE JOB IS SALESMAN
& SAL SHOULD BE > MAX SAL OF THE CLERK JOB

------------------

-- LIST OUT THE EMPLOYEES WHOSE JOB IS CLERK
& SAL SHOULD BE > MIN SAL OF THE SALESMAN JOB

---------------------

LIST OUT THE EMPLOYEES WHOSE JOB IS SALESMAN
& SAL SHOULD BE < MAX SAL OF THE CLERK JOB

------------------------------

-- LIST OUT THE EMPLOYEES WHOSE SALARY IS MAXIMUM IN THEIR RESPECTIVE DEPT.

THERE ARE 2 WAYS TO SOLVE THIS QUESTION :
1) RANK (ANALYTICAL FUNCTION)
2) NUMERICAL COMPARISON

NUMERICAL COMPARISON

------ ANALYTICAL FUNCTION

WHOSE SALARY IS HIGHEST IN THEIR RESPECTIVE DEPT NO

--==================================

-- LIST OUT THE EMPLOYEES WHOSE SALARY IS
GREATER THAN 'JONES'IN HIS RESPECTIVE DEPT

==================================

SHOW IF SALARY < 1000 AS 'LOW SAL', 1000<SALARY <3000 AS 'MEDIUM SALARY',

SALARY >3000 & AS 'HIGH SALARY'

============================================

SHOW DEPT WISE MAX, MIN, AVG SALARIES ALONG WITH EACH EMPLOYEE DETAILED INFORMATION

===================================

LIST OUT THE EMPLOYEES WHOSE SALARY IS MORE THAN AVG OF THEIR DEPARTMENT SALARY **************************

DEPT WISE SALARY STATISTICS

EMPLOYEE SALARY IS >= DEPT AVG SALARY

23) DISPLAY THE NAMES OF EMPLOYEES WHO ARE NOT WORKING AS MANAGERS

ANS: select * from emp minus (select * from emp where empno in (select mgr from emp));

Select * from emp where empno not in (select mgr from emp where mgr is not null);

Select * from emp e where empno not in (select mgr from emp where e.empno=mgr);

24) DISPLAY THE NAMES OF EMPLOYEES WHO ARE NOT WORKING AS SALESMAN OR CLERK OR ANALYST

ANS: SELECT ENAME, JOB FROM SCOTT.EMP WHERE JOB NOT IN ('CLERK','ANALYST','SALESMAN');

25. Display all rows from emp table. The system should wait after every screen full of information

ANS: set pause on;

26. Display the total number of employees working in the company

ANS: select count(*) from emp;

27. Display the total salary and total commission to all employees

ANS: select sum(sal), sum(nvl(comm,0)) from emp;

28. 28) DISPLAY THE MAXIMUM SALARY FROM EMP TABLE & ANOTHER SQL TO GET MAX SAL PER DEPT

ANS: SELECT MAX(SAL) FROM SCOTT.EMP;
SELECT DEPTNO, MAX(SAL) FROM SCOTT.EMP GROUP BY ;

29. Display the minimum salary from emp table

ANS: select min(sal) from emp;

30. Display the average salary from emp table

ANS: select avg(sal) from emp;

31. Display the maximum salary being paid to CLERK

ANS: select max(sal) from emp where job='CLERK';

32. Display the maximum salary being paid in dept no 20

ANS: select max(sal) from emp where deptno=20;

33. Display the minimum salary being paid to any SALESMAN

ANS: select min(sal) from emp where job=' SALESMAN’

34. Display the average salary drawn by managers

ANS: select avg(sal) from emp where job=' MANAGER’

35. Display the total salary drawn by analyst working in dept no 40

ANS: 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

ANS: select ename from emp order by sal;

37. Display the names of employees in descending order of salary lowest salary should appear first

ANS: select ename from emp order by sal desc;

38. Display the details from emp table in order of emp name

ANS Select * from emp order by ename;

39.Display empnno,ename,deptno and sal. Sort the output first based on name and within name by deptno and within deptno by sal;

ANS: select * from emp order by ename, deptno, sal;

40) Display the name of employees along with their annual salary(sal*12)

the name of the employee earning highest annual salary should appear first?

ANS: select ename, sal, sal*12 CTC from emp order by CTC desc;

41) Display name,salary,Hra,pf,da,TotalSalary for each employee.

The output should be in the order of total salary ,hra 15% of salary ,DA 10% of salary .pf 5% salary Total Salary will be (salary + hra + da)-pf?

ANS: select ename,sal SA,sal*0.15 HRA,sal*0.10 DA,sal*5/100 PF, sal+(sal*0.15)+(sal*0.10)-(sal*.05) TOTALSALARY from emp ORDER BY TOTALSALARY DESC;

42. Display the Department numbers and total number of employees working in each Department.

ANS: select deptno,count(*) from tvsemp group by deptno;

43) Display the various jobs and total number of employees working in each job group?

ANS: select job, count (*) from emp group by job;

44) Display department numbers and Total Salary for each Department?

ANS: select dept, sum(sal) from emp group by dept;

45) Display department numbers and Maximum Salary from each Department?

ANS: select dept, max(sal) from emp group by dept;

46) Display various jobs and Total Salary for each job?

ANS: select job, sum(sal) from emp group by job;

47) Display each job along with min of salary being paid in each job group?

ANS: select job, min(sal) from emp group by job;

48) Display the department Number with more than three employees in each department?

ANS: select deptno ,count(*) from emp group by deptno having count(*)>3;

49) Display various jobs along with total salary for each of the job where total salary is

greater than 40000?

ANS: select job, sum(sal) from emp group by job having>=40000;

50) Display the various jobs along with total number of employees in each job.The

Output should contain only those jobs with more than three employees?

ANS: select job, count(*) from emp group by job having count(*)>3;

51) Display the name of employees who earn Highest Salary?

ANS: select ename, sal from emp where sal>=(select max(sal) from emp );

52) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?

ANS: select ename,empno from emp where sal=(select max(sal) from emp where job='CLERK') and job='CLERK' ;

53) Display the names of salesman who earns a salary more than the Highest Salary of the clerk? ANS: select ename,sal from emp where sal>(select max(Sal) from emp where job='CLERK') AND job='SALESMAN';

54) Display the names of clerks who earn a salary more than the lowest Salary of any salesman?

ANS: Select ename, Sal from EMP where Sal> (select min (Sal) from emp where job=’SALESMAN’) and job=’CLERK’;

55) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?

ANS: Select ename from emp where sal>all( select sal from emp where ename=’Jones’ or ename=’Scott’)

56) Display the names of employees who earn Highest salary in their respective departments?

ANS: select ename, job, dept from emp where sal in( select max(sal) from emp group by dept)

57) Display the names of employees who earn Highest salaries in their respective job Groups?

ANS: select ename, job, Sal, Dno from emp where sal in (select max(sal) from emp group by job)

58) Display employee names who are working in Accounting department?

ANS: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';

59) Display the employee names who are working in Chicago?

ANS: select e.ename,d.loc from emp e,tvsdept d where e.deptno=d.deptno and d.loc='CHICAGO';

60) 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');

61) Display the names of employees from department number 10 with 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 ;

62) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?

ANS: Select ename from emp where deptno=10 and sal>(select max(sal) from emp where deptno!=10 group by deptno)

63) Display the names of employees in Upper Case?

ANS: select upper(ename) from emp;

64) Display the names of employees in Lower Case?

ANS: select Lower(ename) from emp;

65) Display the names of employees in Proper case?

ANS: select InitCap(ename)from emp;

66) Find the length of your name using Appropriate Function?

ANS: select lentgh('RAMA') from dual;

67) Display the length of all the employee names?

ANS: select lentgh(ename) from emp;

68) Display the name of employee Concatinate with Employee Number?

ANS: select ename||' '||empno from tvsemp;

69) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?

ANS: select substr('Oracle',3,2) from dual;

70) Find the first occurance of character a from the following string Computer Maintenance Corporation?

ANS: select str('Computer Maintenance Corporation','a' ) from dual;

71) replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)?

ANS: select translate('Alliens','A','B') from Dual;

72) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?

ANS: select ename ,replace(job,'MANAGER','BOSS') from tvsemp;

73) Display empno,ename,deptno from tvsemp 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 tvsemp;

74) Display your Age in Days?

ANS: select sysdate-to_date('30-jul-1977') from dual;

75) Display your Age in Months?

ANS: select months_between(sysdate,to_date('30-jul-1977')) from dual;

76) Display current date as 15th August Friday Nineteen Nienty Seven?

ANS: select To_char(sysdate,'ddth Month Day year') from dual;

78) Scott has joined the company on 13th August ninteen ninety?

ANS: select empno,ename,to_char(Hiredate,' Day ddth Month year') from tvsemp;

79) Find the nearest Saturday after Current date?

ANS: select next_day(sysdate,'Saturday') from dual;

80) Display the current time?

ANS: select To_Char(sysdate,'HH:MI:SS') from dual;

81) Display the date three months before the Current date?

ANS: select Add_months(sysdate,-3) from dual

82) Display the common jobs from department number 10 and 20?

ANS: select job from emp where job in (select job from tvsemp where deptno=20) and deptno=10;

83) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?

ANS: select Distinct job from emp where deptno in(10,20);

84) Display the jobs which are unique to department 10?

ANS: Select distinct (job) from emp where deptno=10;

85) Display the details of those employees who do not have any person working under him?

ANS: select empno,ename,job from emp where empno not in (select mgr from emp where mgr is not null );

86) Display the details of those employees who are in 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;

87) Display thoes who are not managers?

ANS: select ename from tvsemp where job!='MANAGER';

88) Display those employees whose name contains not less than 4 characters?

ANS: select ename from emp where length(ename)>=4 ;

89) Display those department whose name start 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%')

90) Display those employees whose manager name is Jones?

ANS: select e.ename Superior, e1.ename Subordinate from emp e, emp e1 where e.empno=e1.mgr and e.ename='JONES';

91) Display those employees whose salary is more than 3000 after giving 20% increment?

ANS: select ename, sal, (sal+(sal*0.20)) from tvsemp where (sal+(sal*0.20))>3000;

92) Display all employees with their department names?

ANS: select e.ename, d.dname from tvsemp e, tvsdept d where e.deptno=d.deptno

93) Display ename who are working in sales department?

ANS: select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno and d.dname='SALES';

94) Display employee name,dept name,salary,and commission for those sal in between 2000

to 5000 while location is Chicago?

ANS: Select e.ename, d.dname, e.sal, e.comm from tvsemp e, dept d where e.deptno=d.deptno and sal between 2000 and 5000;

95) Display those employees whose salary is greater than his managers 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;

96) Display those employees who are working in the same dept where his manager is work?

ANS: select e.ename, e.deptno, e1.ename, e1.deptno from emp e,e1 where e.mgr=e1.empno and e.deptno=e1.deptno;

97) Display those employees who are not working under any Manager?

ANS: select ename from emp where mgr is null;

98) Display the grade and employees name for the deptno 10 or 30 but 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';

99) Update the salary of each employee by 10% increment who are not eligible for commission?

ANS: update emp set sal= (sal+(sal*0.10)) where comm is null;

100) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?

ANS: delete e.ename, e.hiredate, d.loc from emp e,tvsdept d where e.deptno=d.deptno and hiredate<'31-Dec-82' and d.loc in ('NEWYORK','CHICAGO');

101) Display employee name ,job,deptname,loc for all who are working as manager?

ANS: select e.ename,e.job,d.dname,d.loc from emp e,tvsdept d where e.deptno=d.deptno and e.empno in (select mgr from emp where mgr is notnull);

102) Display those employees whose manager name is jones and also display their manager name? ANS: select e.ename sub,e1.ename from emp e,e1 where e.mgr=e1.empno and e1.ename='JONES';

103) Display name and salary of ford if his salary is equal to hisal of his grade?

ANS: select ename, grade, hisal, sal from emp, salgrade where ename='FORD' and sal=hisal;

104) Display employee name, job, deptname, his manager name ,his grade and make an under department wise?

ANS: select e.ename sub,e1.ename sup, e.job, d.dname ,grade from emp e,e1,salgrade,dept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno group by d.deptno,e.ename,e1.ename,e.job,d.dname,grade;

select e.ename sub,e1.ename sup,e.job,d.dname ,grade from emp e,e1,salgrade,dept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno;

105) 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 tvsemp e,salgrade, tvsdept d where (e.deptno=d.deptno and e.sal between losal and hisal ) order by e.sal desc;

106) Display employee name,job and his manager .Display also employees who are with out managers? ANS: Select e. ename, e. job, e1. Ename, 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;

107) Display Top 5 employee of a Company?

ANS: Select level , ename, job, mgr from emp where level <= 5 connect by prior empno = mgr start with mgr is null ;

108) Display the names of those employees who are getting the highest salary?

ANS: select ename,sal from emp where sal = (select max(sal) from emp);

109) Display those employees whose salary is equal to average of maximum and minimum?

ANS: select * from emp where Sal = (select (max(sal)+min(sal))/2 from emp;

110) Select count of employees in each department where count >3?

ANS: select count(*) from emp group by deptno having count(*)>3

111) Display dname where atleast three are working and display only deptname?

ANS: select d.dname from dept d, emp e where e.deptno=d.deptno group by d.dname having count(*)>3;

112) Display name of those managers name whose salary is more than average salary of Company? ANS: select distinct e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal> (select avg(sal) from emp);

113) Display those managers name whose salary is more than average salary salary of his employees? ANS: select distinct e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal > any (select avg(sal) from emp group by deptno);

114) 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 );

115) Display those employees whose salary is less than his manager but more than 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);

116) Display all employees names with total sal of company with each employee name?

ANS: Select ename, (select sum(sal) from emp) total_sal from emp;

117) Find the last 5(least) employees of company?

ANS: select rn, lev,empno,sal,job from (Select rownum rn, lev, empno,sal, job, mgr from ( Select level lev , empno, ename, sal, job, mgr from emp connect by prior empno = mgr start with mgr is null order by level desc) ) where rn <=5;

118) Find out the number of employees whose salary is greater than their managers salary?

ANS: Select e.ename,e.sal,e1.ename,e1.sal from emp e,e1,dept d where e.deptno=d.deptno and e.mgr=e1.empno and e.sal>e1.sal;

119) Display the manager who are not working under president but they are working under any other manager?

ANS: select e2.ename from emp e1,emp e2,emp e3 where e1.mgr=e2.empno and e2.mgr=e3.empno and e3.job!='PRESIDENT';

120) Delete those department where no employee working?

ANS: delete from emp where empno is null;

121) 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);

122) Display those enames whose salary is out of grade available in salgrade table?

ANS: select empno,sal from emp where sal<(select min(LOSAL) from salgrade ) ;

OR sal>(select max(hisal) from salgrade);

123) Display employee name, sal, comm and whose netpay is greater than any other in the company? ANS: select ename,sal,comm,sal+comm from emp where sal+comm> any (select sal+comm from emp);

124) Display name of those employees who are going to retire 31-Dec-99 if maximum job period is 30 years?

ANS: select empno, hiredate,sysdate, to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy') from emp where to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')=30;

125) Display those employees whose salary is odd value?

ANS: select ename ,sal from emp where mod(sal,2)!=0;

126) Display those employees whose salary contains atleast 3 digits?

ANS: select ename,sal from emp where length(sal)=3;

127) Display those employees who joined in the company in the month of Dec?

ANS: Select empno, ename from emp where trim(to_char(hiredate,'Mon')) =trim('DEC');

128) Display those employees whose name contains A?

ANS: select ename from emp where ename like('%A%');

129) Display those employees whose deptno is available in salary?

ANS: select ename,sal from emp where deptno in (select distinct sal from emp);

130) Display those employees whose first 2 characters from hiredate - last 2 characters sal?

ANS: select empno, hiredate, sal from emp where trim(substr(hiredate,1,2)) = trim(substr(sal,-2,2));

or select hiredate, sal from emp where to_Char(hiredate,'dd')=trim(substr(sal,-2,2))

131) Display those employees whose 10% of salary is equal to the year joining?

ANS: select ename ,sal,0.10*sal from emp where 0.10*sal=trim(to_char(hiredate,'yy'));

132) Display those employees who are working in sales or research?

ANS: select e.ename from emp e, dept d where e.deptno=d.deptno and d.dname ('SALES','RESEARCH');

133) Display the grade of jones?

ANS: select ename, grade from emp, salgrade where ( grade,sal) = (select grade, sal from salgrade, emp where sal between losal and hisal and ename='JONES')

select ename ,hiredate from emp where hiredate<'15-Jul-02' and hiredate

134) Display those employees who joined the company before 15th of the month?

ANS: select ename ,hiredate from emp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';

135) Display those employees who has joined before 15th of the month?

ANS: Select ename ,hiredate from emp where hiredate<'15-Jul-02'

136) Delete those records where no of employees in particular department is less than 3?

ANS: delete from emp where deptno in (select deptno from emp group by deptno having count(*) <3 ;

137A) Delete those employee who joined the company 10 years back from today?

137B) Display the deptname the number of characters of which is equal to no of employee

in any other department?

138) Display the deptname where no employee is working?

ANS: select deptno from emp where empno is null;

139) Display those employees who are working as manager?

ANS: select e2.ename from emp e1,e2 where e1.mgr=e2.empno and e2.empno is not null

140) Count the number of employees who are working as managers (Using set operators)?

ANS: select d.dname from dept d where length(d.dname) in (select count(*) from emp e where e.deptno!=d.deptno group by e.deptno)

141) Display the name of the dept those employees who joined the company on the same date?

ANS: select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno

142) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?

ANS: select ename,sal,grade ,substr(sal,grade,1) from emp,salgrade where grade!=substr(sal,1,1) and grade = substr(sal,grade,1) and sal between losal and hisal

143) Count the no of employees working as manager using set operation?

ANS: Select count(empno) from emp where empno in (select a.empno from emp a

intersect

select b.mgr from emp b)

144) Display the name of employees who joined the company on the same date?

ANS: select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno;

145) Display the manager who is having maximum number of employees working under him?

ANS: select e2.ename,count(*) from emp e1,e2 where e1.mgr=e2.empno group by e2.ename Having count(*)=(select max(count(*)) from emp e1,e2 where e1.mgr=e2.empno group by e2.ename)

146) List out the employee name and salary increased by 15% and express as whole number of Dollars?

ANS: select ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from emp

147) Produce the output of the emptable "EMPLOYEE_AND JOB" for ename and job ?

ANS: select ename"EMPLOYEE_AND",job"JOB" FROM EMP;

148) List of employees with hiredate in the format of 'June 4 1988'?

ANS: select ename,to_char(hiredate,'Month dd yyyy') from emp;

149) print list of employees displaying 'Just salary' if more than 1500 if exactly 1500

display 'on taget' if less than 1500 display below 1500?

ANS: select ename,sal, ( case when sal < 1500 then 'Below_Target'

when sal=1500 then 'On_Target'

when sal > 1500 then 'Above_Target' else ‘kk' End ) Sal Grade from emp

151) Given a string of the format 'nn/nn' . Verify that the first and last 2 characters are numbers .And that the middle character is '/' Print the expressions 'Yes' IF valid

'NO' of not valid . Use the following values to test your solution'12/54',01/1a,'99/98'?

152) Employes hire on OR Before 15th of any month are paid on the last friday of that month

those hired after 15th are paid the last friday of th following month .print a list of employees .their hiredate and first pay date sort those who se salary contains first

digit of their deptno?

ANS: select ename,hiredate, LAST_DAY ( next_day(hiredate,'Friday')),

case when to_char(hiredate,'dd') <=('15') then LAST_DAY ( next_day(hiredate,'Friday'))

when to_char(hiredate,'dd')>('15') then LAST_DAY(ext_day(add_months(hiredate,1),'Friday')) end case from emp ;

153) Display those managers who are getting less than his employees salary?

ANS: select a.empno,a.ename ,a.sal,b.sal,b.empno,b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal

154) Print the details of employees who are subordinates to BLAKE?

ANS: select a.empno,a.ename ,b.ename from emp a, emp b where a.mgr=b.empno and b.ename='BLAKE'

155. Display those who working as manager using co related sub query

ANS: select * from emp where empno in (select mgr from emp);

156.Display those employees whose manager name is JONES and also with his manager name

ANS: select * from emp where mgr=(select empno from emp where ename='JONES') union select * from emp where empno = (select mgr from emp where ename='JONES');

157.Define variable representing the expressions used to calculate on employees total annual renumaration

ANS: select * from emp where &emp_ann_sal>30000;

158.Use the variable in a statement which finds all employees who can earn 30000 a year or more

ANS:select * from emp where &emp_ann_sal>30000;

159.Find out how many managers are there with out listing them

ANS: select count(*) from emp where empno in (select mgr from emp);

160.Find out the avg sal and avg total remuneration for each job type remember

salesman earn commission define emp_ann_sal=(sal+nvl(comm,0))*.12

161.Check whether all employees number are indeed unique

162.List out the lowest paid employees working for each manager, exclude any groups where minsal is less than

1000 sort the output by sal

163.List ename, job, annual sal, deptno, dname and grade who earn 30000 per year and who are not clerks

164.Find out the job that was falled in the first half of 1983 and the same job that was filled during the same period on 1984

165.Find out the all employees who joined the company before their manager

ANS: select * from emp e where hiredate < (select hiredate from emp where empno=e.mgr);

166.List out the all employees by name and number along with their manager's name and number also display 'NO MANAGER' who has no manager

ANS: select e.empno,e.ename,m.empno Manager,m.ename ManagerName from emp e,emp m where e.mgr=m.empno;

167.Find out the employees who earned the highest sal in each job typed sort in descending sal order ANS: select * from emp e where sal=(select max(sal) from emp where job=e.job);

168.Find out the employees who earned the min sal for their job in ascending order

ANS: select * from emp e where sal=(select min(sal) from emp where job=e.job) order by sal;

169.Find out the most recently hired employees in each dept order by hire date

ANS: select deptno, max(hiredate) from emp group by deptno order by hiredate desc;

170. Display ename, sal and deptno for each employee who earn a sal greater than the avg of their department order by deptno

ANS: select ename,sal,deptno from emp e where sal>(select avg(sal) from emp where deptno=e.deptno) order by deptno;

171. Display the department where there are no employees

ANS: select deptno,dname from dept where deptno not in (select distinct(deptno) from emp);

172. 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);

173. In which year did most people join the company. Display the year and number of employees

ANS: select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');

174. Display avg sal figure for the dept

ANS: select deptno, avg(sal) from emp group by deptno;

175. Write a query of display against the row of the most recently hierd employee.display ename hire date and column max date showing

ANS: select empno, hiredate from emp where hiredate=(select max(hiredate) from emp);

176.Display employees who can earn more than lowest sal in dept no 30.

ANS: select * from emp where sal > (select min(sal) from emp where deptno=30);

177. Find employees who can earn more than every employees in dept no 30. select dept name and deptno and sum of sal break on deptno on dname;

ANS: select * from emp where sal>(select max(sal) from emp where deptno=30);

178. Find out avg sal and avg total remainders for each job type

179. Find all dept's which have more than 3 employees

ANS: select deptno from emp group by deptno having count(*)>3;

/*If the pay day is next Friday after 15th and 30th of every month. What is the next pay day from their hire date for employee in emp table /*

/*If an employee is taken by you today in your organization and is a policy in your company to have a review after 9 months the joined date (and of 1st of next month after 9 months) how many days from today your employee has to wait for a review*/

180.Display the 10 th record of emp table (without using rowid)

ANS: select * from emp where rownum < 11 minus select * from emp where rownum< 10;

181.Display the half of the enames in upper case and remaining lower case

ANS: select ename, upper(substr(ename,0,length(ename)/2)) || lower(substr(ename,length(ename)/2+1, length(ename))) UP_lo from emp

182.Display the 10th record of emp table without using group by and rowed

ANS: select * from emp where rownum < 11 minus select * from emp where rownum< 10;

183.Delete the 10th record of emp table

ANS: delete from emp where empno = ( select empno from emp where rownum < 11 minus select empno from emp where rownum< 10);

184. Create a copy of emp table

ANS: create table copyEMP as select * from emp;

185. 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);

186. Display all enames in reverse order

ANS: select ename from emp order by ename desc;

187. Display those employee whose joining of month and grade is equal

ANS: select empno,ename from emp e,salgrade s where e.sal between s.losal and s.hisal and to_char(hiredate, 'mm')=grade;

188. Display those employee whose joining date is available in deptno

ANS: select * from emp where to_char(hiredate,'dd') =deptno;

189. Display those employee name as follows A ALLEN, B BLAKE

ANS: select substr(ename,1,1)||''||ename from emp;

190. List out the employees ename,sal,pf from emp

ANS: select ename,sal,sal*15/100 pf from emp;

191. Display RSPS from emp without using updating, inserting */

192. Create table emp with only one column empno

ANS: create table emp (empno number(5));

193. Add this column to emp table ename varchar2(20)

ANS: alter table emp add ename varchar2(20) not null;

194. OOPSI i forget to give the primary key constraint. Add it now

ANS: alter table emp add constraint emp_empno primary key (empno);

195. Now increase the length of ename column to 30 characters

ANS: alter table emp modify ename varchar2(30);

196. Add salary column to emp table?

ANS: alter table emp add sal number(7,2);

197. I want to give a validation saying that sal can not be greater 10000(note give a name to this column)

ANS: alter table emp add constraint emp_sal_check check(sal<10000);

198. For the time being i have decided that i will not impose this validation. My boss has agreed to pay

more than 10000

ANS: alter table emp disable constraint emp_sal_check;

199. My boss has changed his mind. Now he doesn't want to pay more than 10000 So revoke that salary constraint

ANS: alter table emp add mgr number(5);

200. Add column called as mgr to your emp table

ANS: Alter table emp add constraint emp_mgr foreign key (empno;

201. Oh! This column should be related to empno, Give a command to add this constraint

ANS:

202. Add dept no column to your emp table

ANS: alter table emp add deptno number(3);

203. This deptno column should be related to deptno column of dept table

ANS: alter table emp1 add constraint emp1_deptno foreign key (deptno) references dept(deptno);

204. Create table called as new emp. Using single command create this table as well as to get data into

this table (use create table as)

ANS: create table newemp as select * from emp;

205. Create table called as newemp. This table should contain only empno, ename, dname ?

ANS: create table newemp as select empno,ename,dname from emp e,dept d where e.deptno=d.deptno;

206. Delete the rows of employees who are working in the company for more than 2 years

ANS: delete from emp where floor(sysdate-hiredate)>2*365;

207. Provides a commission to employees who are not earning any commission

ANS: select emp set comm=300 where comm is null;

208. If any employee has commission his commission should be incremented by 100% of his salary

ANS: update emp set comm=comm*10/100 where comm is not null;

209. Display employee name and department name for each employee

ANS: select ename,dname from emp e,dept d where e.deptno=d.deptno;

210. 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;

211. Display ename,dname even if there no employees working in a particular department(use outer join)

ANS: select ename,dname from emp e,dept d where e.deptno(+)=d.deptno;

212. Display employee name and his manager name.

ANS: select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;

213. Display the department name along with total salary in each department

ANS: select deptno,sum(sal) from emp group by deptno;

214. Display the department name and total number of employees in each department

ANS: select deptno,count(*) from emp group by deptno;

215. Select child tables for a given parent table?

ANS:select table_name from user_constraints where R_constraint_name IN (select constraint_name FROM USER_CONSTRAINTS WHERE TABLE_NAME = '&PARENTTABLENAME’) .

-------------------------------------------------------

To find the nth row of a table

SQL> Select *from emp where rowid = (select max(rowid) from emp where rownum

<= 4);

SQL> Select *from emp where rownum <= 4 minus select *from emp where rownum

<= 3;

To find duplicate rows

SQL> Select *from emp where rowid not in (select max(rowid) from emp group by

empno, ename, mgr, job, hiredate, comm, deptno, sal);

SQL> Select empno,ename,sal,job,hiredate,comm , count(*) from emp group by

empno,ename,sal,job,hiredate,comm having count(*) >=1;

To delete duplicate rows

SQL> Delete from emp where rowid not in (select max(rowid) from emp group by

empno,ename,mgr,job,hiredate,sal,comm,deptno);

To find the count of duplicate rows

SQL> Select ename, count(*) from emp group by ename having count(*) >= 1;

How to display alternative rows in a table?

SQL> select *from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp);

Getting employee details of each department who is drawing maximum sal?

SQL> select *from emp where (deptno,sal) in

( select deptno,max(sal) from emp group by deptno);

How to get number of employees in each department , in which department is having more than 2500 employees?

SQL> Select deptno,count(*) from emp group by deptno having count(*) >2500;

To reset the time to the beginning of the day

SQL> Select to_char(trunc(sysdate),’dd-mon-yyyy hh:mi:ss am’) from dual;

To find nth maximum sal

SQL> Select *from emp where sal in (select max(sal) from (select *from emp order

by sal) where rownum <= 5);

Which Employees are not Managers?

SQL> select * from Emp e where e.empno not in ( select mgr from emp e1 where e.empno= e1.mgr);

Display Names who earns highest Salary?

SQL> Select ename, sal from emp where sal > ( select max(sal) from emp);

Display Em- from Dept 10 & Greater than any of employee working in other Dept.

SQL> Select ename, Dept, Sal from emp where sal > ( select max(sal) from emp where deptno = 10) and deptno = 10 group by deptno ;

Display Employees whose salary is more than their Managers

SQL> select e.Sal, e1.sal, e.ename, e1.ename from emp e, emp e1 where e.ename=e1.mgr and e.sal>e.sal;

Which Job group having total salary greater than Max Sal for Manager

SQL> select ename, Job,sum(Sal) from emp group by ename, Job having sal> (select max(sal) from emp where job= ‘Manager’);

Display common jobs in Dept 10, 20

SQL> select Job from emp where job in ( select job from emp where deptno=20) and deptno=10;

Display top 5 Employees ( Designation)

SQL> Select level, ename, job, mgr from emp where level<=5 connect by prior empno=mgr start with mgr is null;

Display the Employees who joined in same day?

SQL> select e.ename, e1.ename from emp e,e1 where e.hiredate = e1.hiredate and e.ename != e1.ename

1) How to Get the Difference between 2 Timestamps Into a New Column?

Sol:

TO_TIMESTAMP( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_TIMESTAMP( your_column1, 'MM/DD/YYYY HH24:MI'

( TO_DATE( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_DATE( your_column1, 'MM/DD/YYYY HH24:MI') ) * 24

2) I need to replace multiple commas  input:  11,0000,AB,015,1111,,,066,5555,,,901,6666,,100,,40 0,7777,,,,5,V,140609,  Output:  11,0000,AB,015,1111,,,~066,5555,,,,,~901,6666,,~10 0,,400,7777,,,~5,V,140609, 

SOL:

replacestr(0,replacestr(0,replacestr(0,input_port, ',,,,',',~'),',,,',',~'),',,',',~')

3) Sorting the columns using multiple columns

select *

from (select col1, col2, col3, row_number() over(order by col1, col2, col3) rn

from ...

where ...

)

where rn < 300 ;

1) i have a table like this emp id empname empse  12 ram 1000  13 rohan 1200  14 mohan 1300  i want to output like that    emp id empname empse empadd  12 ram 1000 1000  13 rohan 1200 2200  14 mohan 1300 2500     Sol: You can do it as follows:  select emp_id, empname, empse,  sum(empse)  over(order by emp_id  rows between 1 preceding and current row)  from your_table  order by emp_id  /  or, doing the same with a shorter syntax:  select emp_id, empname, empse,  sum(empse)  over(order by emp_id  rows 1 preceding )  from your_table  order by emp_id  /  You should be careful and specify exactly your required ORDER BY  inside the analytic SUM function. 

1) How to Get the Difference Between 2 Timestamps Into a New Column?

Sol:

TO_TIMESTAMP( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_TIMESTAMP( your_column1, 'MM/DD/YYYY HH24:MI'

( TO_DATE( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_DATE( your_column1, 'MM/DD/YYYY HH24:MI') ) * 24

SOL:

replacestr(0,replacestr(0,replacestr(0,input_port, ',,,,',',~'),',,,',',~'),',,',',~')

3) Sorting the columns using multiple columnsselect * from (select col1, col2, col3, row_number() over(order by col1, col2, col3) rn from ... where ... ) where rn < 300 ; 3)Retrieve Only Duplicate Records 

Sol: 

SELECT * FROM emp aWHERE rowid = (SELECT max(rowid) FROM emp  WHERE empno = a.empno  GROUP BY empno  HAVING count(*) >1)

4) How can we remove the Duplicate records in Oracle?

SOL:

SYNTAX

delete from <table_name> where rowid not in                     ( select min(rowid)                       from exp group by column1..,column2,...column3..);

EX:

delete from EMP where rowid not in                     ( select min(rowid)                       from EMP group by Student_Name);

=====================

GETTING CUMULATIVE SALARIES OF ENTIRE TABLE & W.R.T. DEPT NO.

AND GETTING BACK ACTUAL SALARIES FROM CUMULATIVE SALARIES

SQL QUERIES - FOR ANALYSIS

Sunday, 4 June 2017

IMP SQL QUERIES FOR PRACTICE & TO BECOME EXPERTISE