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