Oracle Practice SQL Queries 06:

 Oracle Practice SQL Queries 06:               

101) Display those employees who are working as manager?
                select e2.ename from emp e1,e2 where e1.mgr=e2.empno and e2.empno is not null
               
102) Count th number of employees who are working as managers (Using set opetrator)?
           
            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)
                           
103) Display the name of the dept those employees who joined the company on the same date?
                select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno
               
104) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?

                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
               
105) Count the no of employees working as manager using set operation?

                SELECT COUNT (empno)
                  FROM emp
                 WHERE empno IN (SELECT a.empno
                                   FROM emp a
                                 INTERSECT
                                 SELECT b.mgr
                                   FROM emp b)        
                                         
106) Display the name of employees who joined the company on the same date?
                select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno;
               
107) Display the manager who is having maximum number of employees working under him?
               
                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)

108) List out the employee name and salary increased by 15% and express as whole number of Dollars?
                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 'kkkkk'
                            END
                           )
                      FROM emp
                     
150) Which query to calculate the length of time any employee has been with the company
Ans: select hiredate, to_char (hiredate,' HH:MI:SS') 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'?
Ans:

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 (NEXT_DAY (ADD_MONTHS (hiredate, 1), 'Friday'))
                            END
                           )
                      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'

Oracle Practice SQL Queries 05:

 Oracle Practice SQL Queries 05:

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

    Select count (*) from emp group by deptno having count (*)>3

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

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


73) Display name of those managers name whose salary is more than average salary of Company?

            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);
                                       
74) Display those managers name whose salary is more than average salary of his employees?

                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);
                                             
75) Display employee name, sal, comm and net pay for those employees whose net pay is greater than or equal to any other employee salary of the company?

                SELECT ename, sal, NVL (comm, 0), sal + NVL (comm, 0)
                  FROM emp
                 WHERE sal + NVL (comm, 0) > ANY (SELECT e.sal
                                                    FROM emp e);
                                                   
76) Display those employees whose salary is less than his manager but more than salary of other managers?

            SELECT e.ename sub, e.sal
              FROM emp e, e1, dept d
             WHERE e.deptno = d.deptno
               AND e.mgr = e1.empno
               AND e.salary IN (SELECT e2.sal
                                  FROM emp e2, e, dept d1
                                 WHERE e.mgr = e2.empno AND d1.deptno = e.deptno);
                    
77) Display all employees’ names with total sal of company with each employee name?

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

79) Find out the number of employees whose salary is greater than their managers salary?
       
            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;
            
80) Display the manager who are not working under president but they are working under any other manager?

                SELECT e2.ename
                  FROM emp e1, emp e2, emp e3
                 WHERE e1.mgr = e2.empno AND e2.mgr = e3.empno AND e3.job ! = 'PRESIDENT';
                
81) Delete those department where no employee working?
        Delete from emp where empno is null;
       
82) Delete those records from emp table whose deptno not available in dept table?
        Delete from emp e where e.deptno not in (select deptno from dept)

83) Display those enames whose salary is out of grade available in salgrade table?
  
            SELECT empno, sal
              FROM emp
             WHERE sal < (SELECT MIN (losal)
                            FROM salgrade) OR sal > (SELECT MAX (hisal)
                                                       FROM salgrade)
                                                      
84) Display employee name, sal, comm and whose net pay is greater than any other in the company?

            SELECT ename, sal, comm, sal + comm
              FROM emp
             WHERE sal + comm > ANY (SELECT sal + comm
                                       FROM emp)
                                      
85) Display name of those employees who are going to retire 31-Dec-99 if maximum job period is 30 years?

                SELECT empno, hiredate, SYSDATE,
                       TO_CHAR (SYSDATE, 'yyyy') - TO_CHAR (hiredate, 'yyyy')
                  FROM emp
                 WHERE TO_CHAR (SYSDATE, 'yyyy') - TO_CHAR (hiredate, 'yyyy') = 30
                
86) Display those employees whose salary is odd value?
        Select ename, sal from emp where mod (sal, 2)! =0
       
87) Display those employees whose salary contains atleast 3 digits?
        Select ename, sal from emp where length (sal)=3

88) Display those employees who joined in the company in the month of Dec?
            Select empno, ename from emp where trim (to_char (hiredate,'Mon'))=trim ('DEC')
           
89) Display those employees whose name contains A?
            Select ename from emp where ename like ('%A%')

90) Display those employees whose deptno is available in salary?
            Select ename, sal from emp where deptno in (select distinct sal from emp);
           
91) Display those employees whose first 2 characters from hiredate - last 2 characters sal?
            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))
           
92) Display those employees whose 10% of salary is equal to the year joining?
            Select ename, sal, 0.10*sal from emp where 0.10*sal=trim (to_char (hiredate,'yy'))
           
93) Display those employees who are working in sales or research?
            Select e.ename from emp e, dept d where e.deptno=d.deptno and d.dname in ('SALES','RESEARCH');

94) Display the grade of Jones?
            SELECT ename, grade
              FROM emp, salgrade
             WHERE (grade, sal) = (SELECT grade, sal
                                     FROM salgrade, emp
                                    WHERE sal BETWEEN losal AND hisal AND ename = 'JONES')

95) Display those employees who joined the company before 15th of the month?
            select ename ,hiredate from emp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';

96) Display those employees who has joined before 15th of the month?
            select ename ,hiredate from emp where hiredate<'15-Jul-02'

97) Delete those records where no of employees in particular department is less than 3?
delete from emp where deptno in (select deptno from emp group by deptno having count(*) <3

98) Delete those employeewho joined the company 10 years back from today?
delete from emp where empno in (select empno from emp where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10)

99) Display the deptname the number of characters of which is equal to no of employee in any other department?

100) Display the deptname where no employee is working?
select deptno from emp where empno is null;


For More Quries  : Oracle Practice Sql-queries -06