Oracle Practice SQL Queries 04:

Oracle Practice SQL Queries 04:
    
51) Display those employees whose manager name is Jones?

        SELECT e.ename superior, e1.ename subordinate
          FROM emp e, e1
         WHERE e.empno = e1.mgr AND e.ename = 'JONES'

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

        SELECT ename, sal, (sal + (sal * 0.20))
          FROM emp
         WHERE (sal + (sal * 0.20)) > 3000;
        
53) Display all employees with their department names?
    Select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno


54) Display ename who are working in sales department?

        SELECT e.ename, d.dname
          FROM emp e, dept d
         WHERE e.deptno = d.deptno AND d.dname = 'SALES'


55) Display employee name, dept name, salary, and commission for those sal in between 2000 to 5000 while location is Chicago?

        SELECT e.ename, d.dname, e.sal, e.comm
          FROM emp e, dept d
         WHERE e.deptno = d.deptno AND sal BETWEEN 2000 AND 5000
        
56) Display those employees whose salary is greater than his manager’s salary?
             SELECT e.ename, e.sal, e1.ename, e1.sal
              FROM emp e, e1
             WHERE e.mgr = e1.empno AND e.sal > e1.sal
            
57) Display those employees who are working in the same dept where his manager is work?

        SELECT e.ename, e.deptno, e1.ename, e1.deptno
          FROM emp e, e1
         WHERE e.mgr = e1.empno AND e.deptno = e1.deptno
        
58) Display those employees who are not working under any Manager?
        Select ename from emp where mgr is null;
       
       
59) 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?

                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'
                  
60) Update the salary of each employee by 10% increment that are not eligible for commission?
            Update emp set sal= (sal+(sal*0.10)) where comm is null

61) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
            SELECT e.ename, e.hiredate, d.loc
              FROM emp e, dept d
             WHERE e.deptno = d.deptno
               AND hiredate < '31-Dec-82'
               AND d.loc IN ('NEW YORK', 'CHICAGO')
              
62) Display employee name, job, deptname, and loc for all who are working as manager?

            SELECT e.ename, e.job, d.dname, d.loc
              FROM emp e, dept d
             WHERE e.deptno = d.deptno AND e.empno IN (SELECT mgr
                                                         FROM emp
                                                        WHERE mgr IS NOT NULL)

63) Display those employees whose manager name is Jones and also display their manager name?

                    SELECT e.ename sub, e1.ename
                      FROM emp e, emp e1
                     WHERE e.mgr = e1.empno AND e1.ename = 'JONES'
                    
64) Display name and salary of ford if his salary is equal to hisal of his grade?
            Select ename, grade, hisal, sal from emp, salgrade where ename='FORD' and sal=hisal;
            OR
            Select grade, sal, hisal from emp, salgrade where ename='FORD' and sal between losal and hisal;
            OR
             SELECT ename, sal, hisal, grade
              FROM emp, salgrade
             WHERE ename = 'FORD' AND (grade, sal) IN (SELECT grade, hisal
                                                         FROM salgrade, emp
                                                        WHERE sal BETWEEN losal AND hisal);
                                           
                                           
65) Display employee name, job, deptname, his manager name, his grade and make an under department wise?

                SELECT   e.ename sub, e1.ename sup, e.job, d.dname, grade
                    FROM emp 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
                OR
                
                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
                  
66) 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?

            SELECT   e.ename, e.job, e.sal, 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
           
           
67) Display employee name, job and his manager. Display also employees who are with out managers?

        Select e.ename, e1.ename, e.job, e.sal, d.dname from emp e, emp e1, dept d where e.mgr=e1.empno (+) and e.deptno=d.deptno

68) Display Top 5 employee of a Company?

69) Display the names of those employees who are getting the highest salary?
            Select ename, sal from emp where sal in (select max (sal) from emp)

70) Display those employees whose salary is equal to average of maximum and minimum?
            Select * from emp where sal=(select (max (sal)+min (sal))/2 from emp)

Oracle Practice SQL Queries 03:

 Oracle Practice SQL Queries 03:  
        
31) Find the first occurance of character a from the following string Computer Maintenance Corporation?
                select lstr('Computer Maintenance Corporation','a' ) from dual;

32) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)
                select translate('Alliens','A','B') from Dual;

33) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?
                select ename ,replace(job,'MANAGER','BOSS') from emp;

34) Display empno,ename,deptno from emp table. Instead of display department numbers display the related department name(Use decode function)?

                SELECT empno, ename, deptno,
                       DECODE (deptno,
                               10, 'ACCOUNTING',
                               20, 'RESEARCH',
                               30, 'SALES',
                               'OPERATIONS'
                              ) dname
                  FROM emp;
                 
                 
35) Display your Age in Days?
            select sysdate-to_date('30-jul-1977') from dual;
           
36) Display your Age in Months?

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

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

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

39) Scott has joined the company on 13th August ninteen ninety?
            select empno,ename,to_char(Hiredate,'Day ddth Month year') from emp;

40) Find the nearest Saturday after Current date?
            select next_day(sysdate,'Saturday') from dual;
           
           
41) Display the current time?
            select To_Char(sysdate,'HH:MI:SS') from dual;
           
           
42) Display the date three months before the Current date?

            select Add_months(sysdate,-3) from dual;
           
43) Display the common jobs from department number 10 and 20?

            select job from emp where job in (select job from emp where deptno=20) and deptno=10;
           
44) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?

            select Distinct job from emp where deptno in(10,20);
           
45) Display the jobs which are unique to department 10?
            select job from emp where deptno=10;
           
46) Display the details of those employees who do not have any person working under him?

            SELECT empno, ename, job
              FROM emp
             WHERE empno NOT IN (SELECT mgr
                                   FROM emp
                                  WHERE mgr IS NOT NULL);
                                 
47) Display the details of those employees who are in sales department and grade is 3?

                SELECT e.ename, d.dname, grade
                  FROM emp e, dept d, salgrade
                 WHERE e.deptno = d.deptno AND dname = 'SALES' AND grade = 3
                
48) Display thoes who are not managers?
                select ename from emp where job!='MANAGER';
               
49) Display those employees whose name contains not less than 4 characters?
                Select ename from emp where length (ename)>=4
               
50) Display those department whose name start with 'S' while location name ends with 'K'?
                Select e.ename, d.loc from emp e, dept d where d.loc like ('%K') and enamelike ('S%');

Oracle Practice SQL Queries 02:

Last Post Oracle Practice SQL Queries 01:

11) 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?

SELECT job, COUNT (*)
FROM emp
GROUP BY job
HAVING COUNT (*) > 3;


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

SELECT ename, sal
FROM emp
WHERE sal >= (SELECT MAX (sal)
FROM emp);


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

SELECT ename, empno
FROM emp
WHERE sal = (SELECT MAX (sal)
FROM emp
WHERE job = 'CLERK') AND job = 'CLERK';


14) Display the names of salesman who earns a salary more than the Highest Salary of the Clerk?

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MAX (sal)
FROM emp
WHERE job = 'CLERK') AND job = 'SALESMAN';


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

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MIN (sal)
FROM emp
WHERE job = 'SALESMAN') AND job = 'CLERK';


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

SELECT ename, sal
FROM emp
WHERE sal > ALL (SELECT sal
FROM emp
WHERE ename = 'JONES' OR ename = 'SCOTT');


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

SELECT ename, sal, deptno
FROM emp
WHERE sal IN (SELECT MAX (sal)
FROM emp
GROUP BY deptno);

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

SELECT ename, job
FROM emp
WHERE sal IN (SELECT MAX (sal)
FROM emp
GROUP BY job);

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

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.dname = 'ACCOUNTING';

20) Display the employee names who are Working in Chicago?

SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.loc = 'CHICAGO';


21) Display the job groups having Total Salary greater than the maximum salary for Managers?

SELECT job, SUM (sal)
FROM emp
GROUP BY job
HAVING SUM (sal) > (SELECT MAX (sal)
FROM emp
WHERE job = 'MANAGER');


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

SELECT ename, deptno
FROM emp
WHERE sal > ANY (SELECT MIN (sal)
FROM emp
WHERE deptno != 10
GROUP BY deptno) AND deptno = 10;


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

SELECT ename, deptno
FROM emp
WHERE sal > ALL (SELECT MAX (sal)
FROM emp
WHERE deptno != 10
GROUP BY deptno) AND deptno = 10;

24) Display the names of Employees in Upper Case?
select upper(ename) from emp;

25) Display the names of employees in Lower Case?

select Lower(ename) from emp;

26) Display the names of employees in Proper case?
select InitCap(ename)from emp;

27) Find the length of your name using Appropriate Function?
select lentgh('RAMA') from dual;

28) Display the length of all the employee names?
select length(ename) from emp;

29) Display the name of employee Concatinate with Employee Number?
select ename' 'empno from emp;

30) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?
select substr('Oracle',3,2) from dual;

For More Check Oracle Practice SQL Queries 03:

Oracle Practice SQL Queries 01:

Oracle Practice SQL Queries 01:


1) Display the name of employees along with their annual salary (sal*12) the name of the employee earning highest annual salary should appear first?

                    SELECT   ename, sal, sal * 12 "Annual Salary"
                        FROM emp
                    ORDER BY "Annual Salary" DESC;

2) Display name, salary, Hra, pf, da, TotalSalary for each employee. The out put 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?

                    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;

3) Display Department numbers and total number of employees working in each Department?

                    SELECT   deptno, COUNT (*)
                        FROM emp
                    GROUP BY deptno;

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

                    SELECT   job, COUNT (*)
                        FROM emp
                    GROUP BY job;

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

                SELECT   deptno, SUM (sal)
                    FROM emp
                GROUP BY deptno;

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

            SELECT   deptno, MAX (sal)
                FROM emp
            GROUP BY deptno;

7) Display various jobs and Total Salary for each job?
   
                SELECT   job, SUM (sal)
                    FROM emp
                GROUP BY job;

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

                SELECT   job, MIN (sal)
                    FROM emp
                GROUP BY job;
               
9) Display the department Number with more than three employees in each department?

                SELECT   deptno, COUNT (*)
                    FROM emp
                GROUP BY deptno
                  HAVING COUNT (*) > 3;

10) Display various jobs along with total salary for each of the job where total salary is greater than 40000?
                
                SELECT   job, SUM (sal)
                    FROM emp
                GROUP BY job
                  HAVING SUM (sal) > 40000;

For more Please Check  Oracle Practice SQL Queries 02: