SELECT Employee_ID, Salary,
row_number() over (ORDER BY Salary DESC) AS DenseRank
FROM Employee;
SELECT Employee_ID, Salary,
rank() over (ORDER BY Salary DESC) AS Ranking
FROM Employees;
SELECT Employee_ID, Salary,
dense_rank() over (ORDER BY Salary DESC) AS DenseRank
FROM Employees;
--*. Grouping ROLLUP
--ROLLUP creates sub totals from most detailed to grand total. It moves from right to left in the list of columns given in ROLLUP.
--ROLLUP gives grand total for all column and subtotal for all columns excluding first column.
select ccode,fcode, count(*)
from batches
group by rollup(ccode,fcode);
----------------
--GROUPING function
--This will return a value of 1 if the column’s value is generated by ROLLUP. So we can use DECODE and GROUPING functions to get the same result as the above.
select decode(grouping(ccode),1,'ALL courses',ccode) ccode,
decode(grouping(fcode),1,'All faculty',fcode) fcode,
count(*) count
from batches
group by rollup(ccode,fcode);
--CUBE
--CUBE generate subtotals and grand total for all menstioned columns. That means it provides number of batches taken by each faculty
--also along with Roll Up functionality.
select decode(grouping(department_id), 1, 'All_Dept',department_id) dept_id ,
decode(grouping(e.MANAGER_ID), 1, 'All_mngr', manager_id) mngr_id,
round(avg(salary)), count(*)
from EMPLOYEES e
where e.MANAGER_ID is not null and e.DEPARTMENT_ID is not null
GROUP by CUBE( e.DEPARTMENT_ID, e.MANAGER_ID)
order by 1, 2;
select decode(grouping(ccode),1,'ALL courses',ccode) ccode,
decode(grouping(fcode),1,'All faculty',fcode) fcode,
count(*) count
from batches
group by cube(ccode,fcode);
--*. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
--*. Differnece between NVL and NVL2
SELECT nvl2('Col1', 'col2', 'col3') FROM dual;
-- Return Col2 if col1 is NOT NULL else return col3 (if col1 is NULL).
SELECT nvl('Col1', 'col2') FROM dual;
-- Return Col2 if col1 is null.
--*. What is COALESCE
SELECT COALESCE('Col1', 'col2', 'col3', 'col4', 'col5') FROM dual;
-- Return Col2 if col1 is null , if col2 is also NULL return col3.....
--SQL Interview Questions
--1. How do you check the current user name ?
sql show user;
--2. How do you change SQL prompt name?
set sqlprompt "test ".
--3. What is the output of SIGN function?
1 for posite value,
0 for Zero,
-1 for Negaative value.
--4. What are different type of Joins in oracle SQL?
Equi Join
NON Equi Join
Inner Join
Outer JOin Left outer, Right Outer,
Full Outer
Self Join
Cartesian Join
--5. How do you switch to DOS prompt?
SQL host
--6. How do you eliminate the duplicate rows from a table?
SQL: delete FROM emp
WHERE ROWID not in (select MAX (ROWID)
FROM emp
group by empno);
--7. How do you display row number with records
SQL select rownum, sal, ename FROM emp;
--8. How do you display the records between two ranges?
select rownum , ename, empno
FROM emp
WHERE rowid in (select rowid FROM emp WHERE rownum <= &upto-MAX
MINUS
select rowid FROM emp WHERE rownum <= &start-MIN);
--9. NVL function only allow the name data typw (ie. number or char or date NVL(comm, 0)), if commission is null
--then the text "Not Applicatible" want to display, instead of blank space. How do you write the query?
SQL select NVL(to_char(comm.),'NA') from table_name;
--10. How do you find out nth Highest salary from emp table
select distinct (a.sal)
FROM emp a
where &N = (select count(distinct (b.sal) FROM emp b WHERE a.sal >= b.sal));
--11. How do you view installed Oracle version information?
SQL select baner FROM v$version;
--12. How do you display the number value in Words?
SQL select (to_char(to_date(salary, 'j'),'Jsp')) FROM employees;
--13. How do you display Odd/ Even number of records?
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum, 2) FROM emp);
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum, 2) FROM emp);
--14. Which date function return number value?
MONTHS_BETWEEN -- return the number of months between two dates.
--15. How do you replace query result null value with a test?
SQL set NULL 'N/A'
select * from emp where (rowid,0) in (select rowid, mod(rownum, 2) FROM emp);
to reset
SQL SET NULL ''
--16. What are the more common pseudo-columns?
SYSDATE, USER, UID, CURVAL, NEXTVAL, ROWID, ROWNUM
--17. What is the difference between Inner Join and Outer Join?
Inner join: return the rows satisfying the criteria from both the tables.
Outer Join: return the rows satifying the criteria from both tables and the unmatched rows from left, right or both the tables.
--18. When do you use WHERE clause and when do you use HAVING clause?
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause.
WHERE clause is used when you want to specify a condition for columns.
---------------------------------------------------------------
-- Transpose of column to Row
-- Pivot
SELECT ...
FROM ...
PIVOT [XML]
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
/*
In addition to the new PIVOT keyword, we can see three new pivot clauses, described below.
pivot_clause: defines the columns to be aggregated (pivot is an aggregate operation);
pivot_for_clause: defines the columns to be grouped and pivoted;
pivot_in_clause: defines the filter for the column(s) in the pivot_for_clause (i.e. the range of values to limit the results to). The aggregations for each value in the pivot_in_clause will be transposed into a separate column (where appropriate).
*/
CREATE TABLE yourTable(color varchar(5), Paul int, John int, Tim int, Eric int);
INSERT INTO yourTable (color, Paul, John, Tim, Eric) VALUES ('Red', 1, 5, 1, 3);
INSERT INTO yourTable (color, Paul, John, Tim, Eric) VALUES ('Green', 8, 4, 3, 5);
INSERT INTO yourTable (color, Paul, John, Tim, Eric) VALUES ('Blue', 2, 2, 9, 1);
/*
insert into yourTable select
'Red' ,1 ,5 ,1 ,3 from dual union all select
'Green' ,8 ,4 ,3 ,5 from dual union all select
'Blue' ,2 ,2 ,9 ,1 from dual ;
*/
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name;
select * from YOURTABLE;
SELECT name, Red, Green, Blue
FROM (SELECT name, color, value --sum(value) value
FROM yourtable
UNPIVOT ( value FOR name IN (Paul, John, Tim, Eric) ) unpiv
-- group by color, name
) src
PIVOT ( SUM(value) FOR color IN ('Red' Red, 'Green' Green, 'Blue' Blue) ) piv;
--======================================
WITH pivot_data AS (
SELECT department_id, job, salary
FROM employees
where department_id in (10,20,30,40)
)
SELECT *
FROM pivot_data
PIVOT (
SUM(salary) --<-- pivot_clause
FOR department_id --<-- pivot_for_clause
IN (10,20,30,40)
--<-- pivot_in_clause
);
---==============================================
SQL PLSQL Fundamental Question
--Raise -- To raise Exception.
--RAISE used inside exception will ReRaise the same exception
DECLARE
v_msg VARCHAR2(100);
v_name VARCHAR2(100);
BEGIN
BEGIN
SELECT e.LAST_NAME INTO v_name FROM employees e WHERE first_name = 'Sandeep';
EXCEPTION
WHEN no_data_found THEN
SYS.DBMS_OUTPUT.put_line ('1st exception');
raise;
END;
EXCEPTION
WHEN too_many_rows THEN
SYS.DBMS_OUTPUT.put_line ('2st exception');
when others then
SYS.DBMS_OUTPUT.put_line ('Other exception');
END;
--============================================================================
--PROCEDURE / PACKAGE : IN mode
--IN parameters lets you pass a value to the subprogram being called. The value cannot be changed inside the subprogram. It is like a constant in the subprogram.
CREATE OR REPLACE PROCEDURE test_in_para_prod (test_para in varchar2)
IS
v_msg VARCHAR2(100);
BEGIN
v_msg := '1st Return Test start'||test_para;
test_para := v_msg;
DBMS_OUTPUT.PUT_LINE(test_para);
END;
9923894398 / 9011350481
Error(6,3): PLS-00363: expression 'TEST_PARA' cannot be used as an assignment target
--============================================================================
--Return in function
select test_func from dual;
--Function is end at first use of RETURN
CREATE OR REPLACE FUNCTION test_func
RETURN VARCHAR2
IS
v_msg VARCHAR2(100);
BEGIN
v_msg := '1st Return Test start';
RETURN v_msg;
V_msg:= '2nd Return after call Test Start';
RETURN v_msg;
END;
FND_PROFILE and FND_GLOBAL values
Posted by Aopu Mohsin
Following are the FND_PROFILE values that can be used in the PL/SQL code:
fnd_profile.value('PROFILEOPTION');
fnd_profile.value('MFG_ORGANIZATION_ID');
fnd_profile.value('ORG_ID');
fnd_profile.value('LOGIN_ID');
fnd_profile.value('USER_ID');
fnd_profile.value('USERNAME');
fnd_profile.value('CONCURRENT_REQUEST_ID');
fnd_profile.value('GL_SET_OF_BKS_ID');
fnd_profile.value('SO_ORGANIZATION_ID');
fnd_profile.value('APPL_SHRT_NAME');
fnd_profile.value('RESP_NAME');
fnd_profile.value('RESP_ID');
Following are the FND_GLOBAL values that can be used in the PL/SQL code:
FND_GLOBAL.USER_ID;
FND_GLOBAL.APPS_INTIALIZE;
FND_GLOBAL.LOGIN_ID;
FND_GLOBAL.CONC_LOGIN_ID;
FND_GLOBAL.PROG_APPL_ID;
FND_GLOBAL.CONC_PROGRAM_ID;
FND_GLOBAL.CONC_REQUEST_ID;
For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:
g_user_id PLS_INTEGER := fnd_global.user_id;
g_login_id PLS_INTEGER := fnd_global.login_id;
g_conc_req_id PLS_INTEGER := fnd_global.conc_request_id;
g_org_id PLS_INTEGER := fnd_profile.value('ORG_ID');
g_sob_id PLS_INTEGER := fnd_profile.value('GL_SET_OF_BKS_ID');
And initialize the application environment as follows:
v_resp_appl_id := fnd_global.resp_appl_id;
v_resp_id := fnd_global.resp_id;
v_user_id := fnd_global.user_id;
FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);
