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'
Posted by Nilesh P at 11:32 AM 1 comment: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries, SQL
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
Posted by Nilesh P at 11:23 AM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries, SQL
Dec 15, 2010
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)
Posted by Nilesh P at 6:32 PM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries, SQL
Dec 3, 2010
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%');
Posted by Nilesh P at 11:42 AM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries, SQL
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:
Posted by Nilesh P at 11:31 AM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries, SQL
Dec 2, 2010
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:
Posted by Nilesh P at 6:19 PM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries, SQL
Nov 30, 2010
General Select Statments..
How to get Column Name:
select * from all_tab_columns where column_name like 'CURRENCY%' and data_type='VARCHAR2'
How to get Table Name:
select * from all_objects where object_type='TABLE' and object_name like 'CUSTOMER%'
select * from v$parameter where UPPER(name) like 'UTL_FILE_DIR'
How to Know column comments from Database?
----For knowing the column comments-----
select * from ALL_COL_COMMENTS
where table_name = 'CASHRECEIPTS_ALL'
and column_name ='CAREASONCODE'
How to Know the Proc and Packages from Database?
----for knowing the Proc and Packages------------
select * from all_objects
where object_type like 'PROCEDURE'
and object_name like '%BSCS%'
select * from all_objects
where object_type like 'PACKAGE'
and object_name like '%BSCS%'
copy the Proc or Pack and Press F4.
How to Know the columns and rows select stmt from Database?
----columns and rows select stmt---------------
SELECT --deptno,
SUBSTR(SYS_CONNECT_BY_PATH(ename,','),2) name
FROM
(SELECT ename
,deptno
,COUNT(*) over (PARTITION BY deptno) cnt
,row_number () over (PARTITION BY deptno ORDER BY ename) seq
FROM emp
WHERE deptno IS NOT NULL
)
WHERE cnt=seq
START WITH seq=1
CONNECT BY PRIOR seq+1=seq
AND PRIOR deptno=deptno
Posted by Nilesh P at 2:45 PM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries
Nov 17, 2010
What is Query for getting customers having multiple accounts?
What is Query for getting customers having multiple accounts?
and How to get customers having multiple accounts?
SELECT hca.account_number,rc.customer_name, hp.party_type,hp.party_name
FROM hz_parties hp, hz_Cust_Accounts hca,ar.ra_customers rc
WHERE hp.party_id = hca.party_id
and rc.customer_id=hca.cust_Account_id
AND hca.cust_account_id IN (
SELECT customer_id
FROM ap_bank_account_uses_all bauses,
ap_bank_accounts_all bacct
WHERE bauses.external_bank_account_id = bacct.bank_account_id
AND primary_flag = 'Y'
GROUP BY customer_id)
HAVING COUNT (DISTINCT (external_bank_account_id)) > 1)
Posted by Nilesh P at 6:23 PM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries
Apr 1, 2010
Advance SQL Queries....
1. How to Query for finding the nth maximum salary ?
Select distinct a.sal from emp a where (&n-1) = (select count (unique sal ) from emp b where b.sal > a.sal)
2. How to Query for finding the nth minimum salary ?
Select a.sal from emp1 a where (&n-1) = (select count (unique sal) from emp1 b where b.sal < a.sal)
3. What is Query for deleting the duplicate rows in table ?
Delete from emp where rowed not in (select max(rowid) from emp group by empno)
4. What is Query for finding the 2nd maximum ?
Select empno from emp where sal = (select max(sal) from emp where sal <> (select max(sal) from emp));
5. What is Query for finding the 2nd minimum ?
Select empno from emp where sal = (select min(sal) from emp where sal <> (select min(sal) from emp));
6. How to Query to find the cumulative total?
Select sum(x.sal) from emp1 x, emp1 y where y.rowid >= x.rowed group by y.row order by sum(x.sal)
7. How to Query to find the alternate rows ?
Select empno, ename from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);
8. How to Query to find the other alternate rows ?
Select * from emp where rowed not in (select empno, ename from emp where (empno,rownum) in (select empno,mod(rownum,2) from emp));
9. How to Query to delete alternate rows ?
Delete from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);
10. What is Query to print some text with the column values ?
Select empno,deptno, decode (mod(rownum,5),0,'*****') print from emp;
11. what is Query to get column without specifying the column name ?
Select &n, &q from emp where deptno = 10;
12. Query to delete duplicate rows by leaving one row deleted on specific condition ?
Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = 10);
13. Query to delete duplicate rows but leaving one row undeleted ?
Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = x.deptno);
14. What is Query to select all columns, rowid with out specifying the column name ?
Select rowid, &a from emp;
15. What is Query to print static text?
Select empno, sal, 'Maximum from emp where sal = (select max(sal) from emp)
Posted by Nilesh P at 9:07 PM 1 comment: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Apps Report, Queries, SQL
Mar 30, 2010
How to Set Organization? SET MO.
/*
select * from fnd_user where user_id=4080
select * from fnd_responsibility where responsibility_id=20707
select * from fnd_application where application_id=201
*/
begin
fnd_profile.put('USER_ID', 0);--SYSADMIN
fnd_profile.put('RESP_ID', 20432);--PA
fnd_profile.put('RESP_APPL_ID', 275);--PA
apps.fnd_client_info.set_org_context(to_char(183));
end;
/
begin
dbms_application_info.set_client_info('Org_id');
end;
To Retriew the records from Org dependent Views on Sql Developer/Toad/Sql * Plus
begin
fnd_client_info.set_org_context(Org_id);
end;
Posted by Nilesh P at 2:44 PM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries
Mar 26, 2010
Script To find Oracle API's for any module
Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PA_%API%'
order by
a.owner, a.name;
Oracle Database 11g The Complete Reference (Osborne ORACLE Press Series)
Posted by Nilesh P at 7:54 PM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Apps APIs, Queries
Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities
Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities...
SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'SAIRAM_GOUD' --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name
Posted by Nilesh P at 7:52 PM No comments: Links to this post
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Queries
How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus
How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus
select * from V$SESSION
where OSUSER like 'c_sgoud' -- User name of the terminal ( may be your windows login name )
Alter system kill session '146,46619'
select sid, serial# from v$session where username = 'USER';
alter system kill session 'SID,SERIAL#';
you need to find first lock
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
;
by this query you can find lock
then you can kill
col program for a25
col status for a10
col SER# for a10
col LOGON_TIME for a20
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program ,
b.status,
-- b.module,
b.LOGON_TIME
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
and b.status='ACTIVE'
--and b.module like '%blbn%'
--and b.username='ENBAPP23'
order by program ,OS_USER
Kindle Wireless Reading Device (6" Display, Global Wireless, Latest Generation)
How to Unlock the Objects
The following query could be useful :
Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select SESSION_ID from V$LOCKED_OBJECT where OBJECT_ID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))
Get the Process id from the query. Login as unix user and run the following command to kill the process.
Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)
