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

Purchasing Interview Questions:

Q. AME is possible in PO approval ??

NO. AME for Req approval

Q. Can we create Position heiracchy for Requisition Approval

Yes

Q. vacation rule has to be created by the same user...if being as a first person in the approver  goup the user not able to create vacation rule and goes on leave...then any other way, by  which we can handle that out of scope

Q. Can I get the recording version of yesterda's session?

No.

Q. Where did we do setups for taxes

Ebiz Tax

Q. Is AME diff in 11i and R12

Conceptually, AME is same or 11i and R12 but the look and feel and navigation is  completely different.

Q. Any profile for confidentiality of Quotation?

No. Anybody with access to quote and quote analysis screen has access to quotations

Q. when we delete approved record, if it is by other user, will he be notified for the same

No.

Q. How to protct cofidentialiy and modification of quot from others

- to prevent access to quote screen

- make the quote screen read only

Q. is there any work flow proces behind this?

no

Q. Can we have PO without RFQ or quotes for that matter.

Yes. We can have PO with RFQ and quotes. But its not a good practice.

Purchase Manager can be questioned why he gave order to some supplier. He will always have 2-3 quotes with him.

Q. but for blanket type of PO do you think quotes are required?

2 ways to determine price for an item

            - Quotations  - document provided by vendor to supply at a price

            - Blanket PO - agreement between vendor and us

Q. If we dont want other buyer can see qout of other buyer even they have access to RFQ screen ??

only related buyer only can see his Quot not other one which is not related to him ??

Personalization. Without personalization, its not possible.

Q. Can we copy Catalog RFQ to Standard quotation?

Yes. Copy Catalog RFQ to Catalaog quote or Standard quotation. Copy Bid RFQ to Bid quotation.

Q. Can you show us Creating PO from Requisition having RFQ as Source Doc.

RFQ cannot be source doc. We can have Quote as Source and we will be creating PO from Req using Quote as Source.

Q. Yes but not for release.

Question not clear

Q. What about negotion process in this ??

Negotiation is possible in Oracle Sourcing. Out of Scope.

Q. Are u going to take Planned Purchase order

Yes. That will be covered later.

Q. Will we cover Schedule release VMIand Consigned.?

Out of scope. Inventory related. Kindly attend Inventory training.

Q. Is there  any analytics available in this release to freeze the minimum quote automatically?

No. Quote analysis and approval is a manual process. No automation is provided.

Q. Can you cover the Advance Pricing intergration in Purchasing?

This will be covered conceptually. There wont be any practicals on the same.

Q. What about Bill of distribution??

Sourcing Rules  - ASL is required. Will cover Sourcing rules later.

Q. Sourcing means for RFQ ??

Selecting the Source of Supply of material.

Sourcing - Sourcing Rules - automatically create PO from requisitions

Q. While using ASL for Commodity theall items listed under that commodity will get imapcted . believe it's true can u confirm .

Yes. I will show you guys a chart

Q. How the Supplier status behave differently in Global & Local ASL?

Local ASL will override Global

Local (M1) - debarred Global - Approved

PO for M1 - will not allow

PO for M2 - should allow

But local is specific to that org not for others. so for M2 it should allowed.

Q. what is category level supplier?

The supplier will be ASL for all items which fall under that category.

Q. then where do we define that?

That is defined in ASL

Q. Where do we define the range of items under that category?

Linked while defining Items

Q. AND is approved for Item not for category so Item shoud be presidance. and it should allowed. Item shoud have more presidance then category.

NO. Category is having higher precedence. So, if a Supplier is debarred for a category then he is debarred for all items under that category.

Q. Can we create Req to Release Item if is not matching on Requisition and BPA. but ASL is set.

Question is not clear

Q. what if allocation for ASL is <100 %

For manual PR, 100% allocation does not matter. 100% allocation is required only for planned orders. For manual PR the PO will be raised against the supplier with highest allocation.

Q. What does Sourcing Assignment signify?

Sourcing Assignment will determine which supplier to source an item for a given ORG

Q. If we have one org then can we do transaction in Master Org

Yes. No restriction. Best practice that we should not do any transaction in Master Inv Org.

Q. What isTCA??

Trading Community Architecture. Covered in AR. This is party, customer relationship and stored in HZ tables.

Q. How intercompany sourcing happens and how PO will be managed in two companies?

Internal Requisition/Internal Sales Orders

Q. What is the need to add the price into price list...when we have given price for the item in Inventory setup?

Price that we mentioned is the Purchase list price. But for OM, it picks the price from Price list.

Q. While giving the value in Price list...do we add our all the cost and profit in that

For internal items, the transfer price is calculated based on the cost + the transfer options.

Q. What does create internal order conc request do?

Create internal order request will transfer the IR info to OM interface tables.

Q. Why is the cost zero for Internal Orders?

We did not enter any cost for the item, so the cost was zero.

Q. could you please show this cost thing by raising external sales order?

Out of Scope. OM class is required.

Q. Cost is zero for internal sales order, however you told that it will be 10% addition of the cost of that item then why it is not 10 % addition in the cost?

Q. Why is the cost of the Item zero?

The cost of the Item was zero because we did not enter the cost of the item.

Q. Where do we enter the cost?

We enter the cost while defining the Item.

Q. How does the price default while creating IR?

The price of the item while creating IR, is the cost of the Item.

Q. Can you clarify where we set up zero cost for an Item for IR?

We did not specify the zero for IR. If we do not enter the cost, it will be considered as zero.

Q. Can we change PO Currency?

Yes. We can change PO currency. It defaults from supplier setup but we can change it.

Q. Can you explain what is confirming order ?

Sometimes, the Purchase manager requests for certain items over phone. Then he sends the PO just to confirm the verbal order. If the vendor thinks that this is a duplicate order and delivers double, it could cause problem, so he sends a PO with a message that it is confirming order.

What is Difference Between Blanket release and Planned Release?

Blanket release is against blanket purchase agreement and planned release is against planned orders.

Under what Circumstance we'll use Blanket and Planned Agreement

In blanket, the price of the agreement is fixed but the quantity is not fixed. We get price breaks in Blanket for volume discounts.

In planned, the shipment is tentative. It does not have any price breaks so no volume discounts.

So, blanket is for high volume transactions.

Q. For planned orders, Schedule is available but blanket is for as and when required?

The schedule in planned Orders is just tentative. The actual schedule is determined later.

Blanket can have an effective date range. Planned does not have any effective date range.

Q. Can you cover Advance Pricing?

Not in scope.

Q. What is the way for adding items or modify BPA from supplier point of view..are they need have oracle apps installed with Oracle iSupplier portal?

They will have access to Oracle iSupplier portal which is a web based application. No installation is required.

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