What is SQL Injection? How can it be handled?
SQL Injection - what's it?
It's an attack where some malicious piece of code is added to a SQL statement which is later passed to a DBMS to be parsed and executed. If the overall SQL statement string is syntactically correct (and of course if the user has rights to fire those SQL commands) then the SQL gets executed by the SQL Engine and consequently makes the database inconsistent (to say the least otherwise such an effort may completely spoil the database as well).
Example: suppose you have a SQL statement string as specified below:
..
String parm = request.getParamater("parameterName");
String queryString = "SELECT * FROM table_name WHERE column_name = '" + parm + "'";
...
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(queryString);
...
Now, if the end user enters say "value'; DROP TABLE table_name --" as the parameter then the SQL query string will ultimately become "SELECT * FROM table_name WHERE column_name = 'value'; DROP TABLE table_name --'" which is valid SQL statement and it'll first fetch the results of the query and immediately after it it'll drop the table until you actually realize it. Notice the usage of '--' at the end of the parameter which mischievously makes the last "'" of the queryString ineffective. Anything following '--' is considered as comment in SQL ... right? Needless to mention here that ';' is an SQL statement terminator and it gives the attacker a luxury of terminating the expected SQL statement and fire his/her own malicious SQL statement.
Thus we see how easily one can add malicious but valid code to SQL statements and how terrible results can that produce for your application.
How can SQL Injection be handled?
Next obvious questions which comes to mind is - how can we effectively handle it? Can we really restrict it to happen altogether? I really doubt that we can claim 100% avoidance, but we can follow several measures to catch it beforehand and then accordingly we can deal with the situation.
As we can easily understand that as long as a SQL query is valid (and the user has right set of privileges) the execution of SQL queries can hardly be stopped without programmatic intervention both at the middleware level as well as the DBMS level. So how can we actually deal with such a situation? By following the same old rule of not relying on what comes as an input and by ensuring that all the user inputs go through a strict set of validations before they reach the DBMS only in the case they pass all the stages. Now deciding on how many validations the input should go through depends upon the foresight of the DBA/Developer and the success of SQL Injection will then depend upon how far can the DBA/Developer think ahead of the attacker.
Some Typical Validations for SQL Injection avoidance
All Inputs should be strictly validated without making any assumption about their
• datatype
• length
• format
• range
Strictly checking and rejecting inputs having avoidable characters like
• ; : the SQL query delimeter (we saw the impact above)
• -- : the SQL comment
• ' : data string specifier (it should be appended by the code)
• /*..*/ : comment delimeters can be used to fool the app to a great extent
Checking the SQL Parameters - as we know that Parameters collection inherently checks the type and length checks so it's better to use them while passing the parameters to the parametrized SQL statements whenever possible.
Checking Injection caused by Truncation - pay attention to the maximum length a variable can hold as rest of the characters in the assigned value will be truncated silently and that may cause severe damage in certain cases.
These are just few basic validations. Depending upon the actual DBMS you're using you can have various other validations before the SQL statement is permitted to be parsed and executed.
SQL Injection - what's it?
It's an attack where some malicious piece of code is added to a SQL statement which is later passed to a DBMS to be parsed and executed. If the overall SQL statement string is syntactically correct (and of course if the user has rights to fire those SQL commands) then the SQL gets executed by the SQL Engine and consequently makes the database inconsistent (to say the least otherwise such an effort may completely spoil the database as well).
Example: suppose you have a SQL statement string as specified below:
..
String parm = request.getParamater("parameterName");
String queryString = "SELECT * FROM table_name WHERE column_name = '" + parm + "'";
...
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(queryString);
...
Now, if the end user enters say "value'; DROP TABLE table_name --" as the parameter then the SQL query string will ultimately become "SELECT * FROM table_name WHERE column_name = 'value'; DROP TABLE table_name --'" which is valid SQL statement and it'll first fetch the results of the query and immediately after it it'll drop the table until you actually realize it. Notice the usage of '--' at the end of the parameter which mischievously makes the last "'" of the queryString ineffective. Anything following '--' is considered as comment in SQL ... right? Needless to mention here that ';' is an SQL statement terminator and it gives the attacker a luxury of terminating the expected SQL statement and fire his/her own malicious SQL statement.
Thus we see how easily one can add malicious but valid code to SQL statements and how terrible results can that produce for your application.
How can SQL Injection be handled?
Next obvious questions which comes to mind is - how can we effectively handle it? Can we really restrict it to happen altogether? I really doubt that we can claim 100% avoidance, but we can follow several measures to catch it beforehand and then accordingly we can deal with the situation.
As we can easily understand that as long as a SQL query is valid (and the user has right set of privileges) the execution of SQL queries can hardly be stopped without programmatic intervention both at the middleware level as well as the DBMS level. So how can we actually deal with such a situation? By following the same old rule of not relying on what comes as an input and by ensuring that all the user inputs go through a strict set of validations before they reach the DBMS only in the case they pass all the stages. Now deciding on how many validations the input should go through depends upon the foresight of the DBA/Developer and the success of SQL Injection will then depend upon how far can the DBA/Developer think ahead of the attacker.
Some Typical Validations for SQL Injection avoidance
All Inputs should be strictly validated without making any assumption about their
• datatype
• length
• format
• range
Strictly checking and rejecting inputs having avoidable characters like
• ; : the SQL query delimeter (we saw the impact above)
• -- : the SQL comment
• ' : data string specifier (it should be appended by the code)
• /*..*/ : comment delimeters can be used to fool the app to a great extent
Checking the SQL Parameters - as we know that Parameters collection inherently checks the type and length checks so it's better to use them while passing the parameters to the parametrized SQL statements whenever possible.
Checking Injection caused by Truncation - pay attention to the maximum length a variable can hold as rest of the characters in the assigned value will be truncated silently and that may cause severe damage in certain cases.
These are just few basic validations. Depending upon the actual DBMS you're using you can have various other validations before the SQL statement is permitted to be parsed and executed.
Difference between Static Cursors and Dynamic Cursors
A Static Cursor doesn't reflect data changes made to the DB once the ResultSet has been created whereas a Dynamic Cursor reflects the changes as and when they happen.
Dynamic Cursor: A Dynamic Cursor is the one which reflects all the data changes made into the database as and when they happen. This may require continuous re-ordering of data in the ResultSet due to the database changes and hence it's much more expensive than a Static Cursor. The possible re-ordering of records in the ResultSet doesn't allow a Dynamic Cursor to support absolute positioning as it can't be sure of the absolute position of a record/row in the ResultSet. Hence a Dynamic Cursor only supports relative positioning. Furthermore a Dynamic Cursor doesn't support Bookmarks for the obvious reasons.
A Static Cursor doesn't reflect data changes made to the DB once the ResultSet has been created whereas a Dynamic Cursor reflects the changes as and when they happen.
- A Static Cursor is much more performant than a Dynamic Cursor as it doesn't require further interaction with the DB server.
- A static cursor supports both Relative and Absolute Positioning whereas a Dynamic Cursor supports only Relative Positioning.
- A Static Cursor can be used for Bookmarking purposes as the data returned is static whereas a Dynamic Cursor can't be used for the same.
Dynamic Cursor: A Dynamic Cursor is the one which reflects all the data changes made into the database as and when they happen. This may require continuous re-ordering of data in the ResultSet due to the database changes and hence it's much more expensive than a Static Cursor. The possible re-ordering of records in the ResultSet doesn't allow a Dynamic Cursor to support absolute positioning as it can't be sure of the absolute position of a record/row in the ResultSet. Hence a Dynamic Cursor only supports relative positioning. Furthermore a Dynamic Cursor doesn't support Bookmarks for the obvious reasons.
Q1.Table Structure: EMPLOYEE(EmpName, DeptName, Salary)
1. Write a SQL query to get highest salary in each Department.
2. Write a sql to get the number of employees in each Department.
3. Write a sql to split employee in two columns:
a) Employee above 5000(employee getting salary more than 5000)
b) Employee below 5000(employee getting salary below 5000)
No need to show the employee getting salary = 5000
1. SELECT DeptName, MAX(salary) as "Highest salary"
FROM employee
GROUP BY DeptName
2. select DeptName, count(EmpName)
from Employee
group by DeptName
3a. select Salary, EmpName,
case when salary < 5000 then
'Less than 5000'
else
'Greater than 5000'
end
from employee
OR, alternative way,
3a. select case when salary < 5000 then
EmpName+' '+CAST(salary As Varchar(100))
else
NULL
end 'Less than 5000',
case when salary > 5000 then
EmpName+' '+CAST(salary As Varchar(100))
else
NULL
end 'Greater than 5000'
from employee
3b. select EMPNAME = EmpName,
EMPSAL =
case
when salary < 5000
then CAST(Salary as varchar(100))+ ' '+'Less than 5000'
when salary > 5000
then CAST(salary as varchar(100))+' ' +'Greater than 5000'
when salary = 5000
then CAST(salary as varchar(100))+' ' +'Equal to 5000'
end
from employee1
Write a sql to get the department name having highest number of employees.
Select DeptName From Employee1
Group by DeptName
Having count(EmpName) in(
Select Max(A.COUNTEMP)
From (select DeptName, count(EmpName)as COUNTEMP
from Employee1
group by DeptName) A)
The recruiter asked "The database is a bug tracking database. There is really only one table that is relevent for this question. The name of the table is bugs. It has several columns: open_date, close_date, bugID, and severity.A bug is considered open on a given day if it's open date is on or before that day, and it's close date is on or after that day.So the goal is to generate a histogram to show the number of open bugs for a range of dates. The ideal result set would have two columns: date and open bug count on that date.so the question is, can you do that in one query, and if so, what does that query look like?"
SELECT DISTINCT BugDate = OpenDate,
BugCount = (SELECT Count(*)
FROM Bugs X
WHERE X.OpenDate <= B1.OpenDate
AND CloseDate >= B1.OpenDate )
FROM Bugs B1
UNION
SELECT DISTINCT BugDate = CloseDate,
BugCount = (SELECT Count(*)
FROM Bugs X
WHERE X.OpenDate <= B2.CloseDate
AND CloseDate >= B2.CloseDate )
FROM Bugs B2
ORDER BY 1
Write a query to update column maxmarks such that maxmarks column contains
Whatever be the greater value among marks1 and marks2 columns ( as shown in table ).
create table aa(name varchar(10), marks1 int, marks2 int, maxmarks int)
insert into aa values ('a',10,20,null)
insert into aa values ('b',20,25,null)
update a
set maxmarks= d.marks
from aa a, (select name, max(marks) as marks
from (select name,marks1 as marks from aa
union all
select name,marks2 as marks from aa) as c
group by name ) as d
where a.name=d.name
Question is been asked in the interview., I have table Department with details like
DeptId Dname Dlocation
10 Finance Del
20 Sales Mum
30 Marketing Blore
The output should be in this format
10 20 30
Finance Sales Marketing
Del Mum Blore
The query I need in SQL not using any transformation.
Can anyone help me out in this ?
select deptno,
max(decode(rn,1,ename))||
max(decode(rn,2,','||ename))||
max(decode(rn,3,','||ename))||
max(decode(rn,4,','||ename))||
max(decode(rn,5,','||ename))||
max(decode(rn,6,','||ename)) ename, loc
from (select emp.deptno, ename, loc,
row_number () over (partition by emp.deptno order by ename) rn
from emp, dept where emp.deptno = dept.deptno)
group by deptno, loc
What is the Hirerachy of SQL?
The hierarchy of SQL is: Parse - Execute - Bind – Fetch
Display the emploee records who joins the department before their manager?
select t.empno,t.hiredate,t.mgr,t1.empno,t1.hiredate
from emp t,emp t1
where t.mgr=t1.empno
and t.hiredate < t1.hiredate
Write a query to display alternate records from the employee table?
A table has following layout ; CITY, NAME , SEX
How do you write a query to get the count of Male, count of female in a given city XXX.Query result should be in a single row with count of male , count of female as columns?
SELECT CITY, COUNT(MALE), COUNT(FEMALE)
FROM
( SELECT CITY,
case WHEN SEX='M' THEN 'M' ELSE NULL END MALE,
case WHEN SEX='F' THEN 'F' ELSE NULL END FEMALE
FROM TAB_PT
)
GROUP BY CITY
select count(decode(sex 'M' 1)) Males count(decode(sex 'F' 1)) Females from emp where city 'NGP';
Display the records between two range I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query
select ename,decode(nvl(comm,0),0,'Not Applicable',comm) from scott.emp;
How can we order the column of day (mon to sun)such that monday comes first then tues and so no.Suppose we have retrieved day out of hiredate column
select TRUNC(SYSDATE) date, to_char(SYSDATE,'day') day from DUAL order by to_char(TRUNC(SYSDATE)-1,'d')
To display the all employee name with end of ‘S’ without using like operator ?
select ename from emp where lower(substr(ename,length(ename),1))='s';
HOw to get /select the nth row from the table ?
how to select first n rows ,last n rows from a table
1. select empno a from (select empno rownum a from emp order by empno) where a &temp;
2.select empno a from (select empno rownum a from emp order by empno) where a < &temp
How to execute the following query without using temp
select * from temp;
SELECT * FROM (SELECT TABLENAME FROM USER_TABLES WHERE TABLENAME LIKE 'TEM_')
Display Odd/ Even number of records
Even:
select * from table_name where (rowid,0) in
(select rowid,mod(rownum,2) from table_name);
Odd:
select * from table_name where (rowid,1) in
(select rowid,mod(rownum,2) from table_name);
Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?
SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)
How to get first 5 Records then next 5 records till end of row count in SQL –Oracle
select * from emp where not in (select rownum betwen 5 and count(rownum)-5 from emp)
How to find Nth largest or Nth smallest data from oracle table, for ex..5th highest salary from employees
To find the nth higest salary:
select min(sal) from (select distinct sal from emp order by sal desc) where rownum<=&n;
To find the nth smallest salary:
select max(sal) from (select distinct sal from emp order by sal) where rownum<=&n;
Dont get confused... its min(sal) for highest and max(sal) for lowest. Becos:
Ex: Top 3 salaries - 5000,3000,2975 for 3rd higest min(sal) i.e 2975...
How do I eliminate the duplicate rows ?
1. delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
2. delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
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);
Number in Text
Select sal "Salary ",(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
Display Odd/ Even number of records
Select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
SQL
1. List the employees who are getting less salary than it’s department average salary?
ANS: SELECT * FROM EMP E1 WHERE SAL <(SELECT AVG(SAL) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO);
2. Display last word (Means Last Letter of that WORD) for every name?
ANS: SELECT SUBSTR(ENAME, -1) FROM EMP;
3. Query for Nth highest salary?
ANS: SELECT * FROM EMP E1 WHERE N = (SELECT COUNT(DISTINCT(SAL)) FROM EMP E2 WHERE E1.SAL <= E2.SAL)
4. Query For max salary – department wise?
ANS: SELECT * FROM EMP E1 WHERE N = (SELECT COUNT(DISTINCT(SAL)) FROM EMP E2 WHERE E1.SAL <= E2.SAL AND E1.DEPTNO = E2.DEPTNO)
5. Display the duplicate records?
ANS: SELECT * FROM EMP WHERE EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(*) >1)
6. Display the selected records?
ANS: SELECT * FROM (SELECT ROWNUM R, ENAME, EMPNO, SAL, DEPTNO FROM EMP) WHERE R = 4 OR R = 9
7. Find how many comma’s in a string? For Example – ‘K,R,I,S,H,N,A’?
ANS: SELECT LENGTH('K,R,I,S,H,N,A')-LENGTH(REPLACE('K,R,I,S,H,N,A',',',NULL)) FROM DUAL
8. How to delete duplicate records?
ANS: DELETE FROM EMP WHERE ROWID NOT IN ( SELECT MAX(ROWID) FROM EMP GROUP BY EMPNO, ENAME, SAL, JOB, DEPTNO)
9. Select updated records?
ANS: SELECT S.ENAME, S.EMPNO, S.SAL FROM EMP S, EMP T WHERE S.ENAME != T.ENAME OR S.EMPNO != T.EMPNO OR S.SAL != T.SAL
10. How to find the day in given date?
ANS: SELECT TO_CHAR(TO_DATE('15-AUG-1947','DD-MM-YY'),'DAY') FROM DUAL;
11. Find experience of employees in Employee table?
ANS: SELECT ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) AS EXPERIENCE FROM EMP
12. Find the last record of a Employee table?
ANS: SELECT * FROM EMP WHERE ROWID = (SELECT MAX(ROWID) FROM EMP)
-----------------------------------------------------------------------------------------------------------------------------------------------1. The following query retrieves "2" highest paid employees FROM each Department :
SELECT deptno, empno, sal
FROM emp e
WHERE
2 > ( SELECT COUNT(e1.sal)
FROM emp e1
WHERE e.deptno = e1.deptno AND e.sal < e1.sal )
ORDER BY 1,3 DESC;
Index
2. Query that will display the total no. of employees, and of that total the number who were hired in 1980, 1981, 1982, and 1983. Give appropriate column headings.
I am looking at the following output. We need to stick to this format.
Total 1980 1981 1982 1983
----------- ------------ ------------ ------------- -----------
14 1 10 2 1
SELECT COUNT (*), COUNT(DECODE(TO_CHAR (hiredate, 'YYYY'),'1980', empno)) "1980",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1981', empno)) "1981",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1982', empno)) "1982",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1983', empno)) "1983"
FROM emp;
Index
3. Query for listing Deptno, ename, sal, SUM(sal in that dept) :
SELECT a.deptno, ename, sal, (SELECT SUM(sal) FROM emp b WHERE a.deptno = b.deptno)
FROM emp a
ORDER BY a.deptno;
OUTPUT :
=======
DEPTNO ENAME SAL SUM (SAL)
========= ======= ==== =========
10 KING 5000 11725
30 BLAKE 2850 10900
10 CLARK 2450 11725
10 JONES 2975 11725
30 MARTIN 1250 10900
30 ALLEN 1600 10900
30 TURNER 1500 10900
30 JAMES 950 10900
30 WARD 2750 10900
20 SMITH 8000 33000
20 SCOTT 3000 33000
20 MILLER 20000 33000
Index
4. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job for all departments, giving each column an appropriate heading.
The output is as follows - we need to stick to this format :
Job Dept 10 Dept 20 Dept 30 Total
---------- --------------- ------------- ------------- ---------
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
SELECT job "Job", SUM (DECODE (deptno, 10, sal)) "Dept 10",
SUM (DECODE (deptno, 20, sal)) "Dept 20",
SUM (DECODE (deptno, 30, sal)) "Dept 30",
SUM (sal) "Total"
FROM emp
GROUP BY job ;
Index
5. 4th Top Salary of all the employees :
SELECT DEPTNO, ENAME, SAL
FROM EMP A
WHERE
3 = (SELECT COUNT(B.SAL) FROM EMP B
WHERE A.SAL < B.SAL) ORDER BY SAL DESC;
Index
6. Retrieving the 5th row FROM a table :
SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID = (SELECT ROWID FROM EMP WHERE ROWNUM <= 5
MINUS
SELECT ROWID FROM EMP WHERE ROWNUM < 5)
Index
7. Tree Query :
Name Null? Type
-------------------------------------------------------------------
SUB NOT NULL VARCHAR2(4)
SUPER VARCHAR2(4)
PRICE NUMBER(6,2)
SELECT sub, super
FROM parts
CONNECT BY PRIOR sub = super
START WITH sub = 'p1';
Index
8. Eliminate duplicates rows in a table :
DELETE FROM table_name A
WHERE ROWID > ( SELECT min(ROWID) FROM table_name B WHERE A.col = B.col);
Index
9. Displaying EVERY 4th row in a table : (If a table has 14 rows, 4,8,12 rows will be selected)
SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);
Index
10. Top N rows FROM a table : (Displays top 9 salaried people)
SELECT ename, deptno, sal
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM < 10;
Index
11. How does one count/sum RANGES of data values in a column? A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z).
SELECT
f2,
COUNT(DECODE(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
COUNT(DECODE(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
COUNT(DECODE(greatest(f1,29), least(f1, 0), 1, 0)) "Range 00-29"
FROM my_table
GROUP BY f2;
Index
12. For equal size ranges it migth be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...).
SELECT ename "Name", sal "Salary",
DECODE( TRUNC(sal/1000, 0), 0, 0.0,
1, 0.1,
2, 0.2,
3, 0.3) "Tax rate"
FROM emp;
13. How does one count different data values in a column?
COL NAME DATATYPE
----------------------------------------
DNO NUMBER
SEX CHAR
SELECT dno, SUM(DECODE(sex,'M',1,0)) MALE,
SUM(DECODE(sex,'F',1,0)) FEMALE,
COUNT(DECODE(sex,'M',1,'F',1)) TOTAL
FROM t1
GROUP BY dno;
Index
14. Query to get the product of all the values of a column :
SELECT EXP(SUM(LN(col1))) FROM srinu;
Index
15. Query to display only the duplicate records in a table:
SELECT num
FROM satyam
GROUP BY num
HAVING COUNT(*) > 1;
Index
16. Query for getting the following output as many number of rows in the table :
*
**
***
****
*****
SELECT RPAD(DECODE(temp,temp,'*'),ROWNUM,'*')
FROM srinu1;
Index
17. Function for getting the Balance Value :
FUNCTION F_BALANCE_VALUE
(p_business_group_id number, p_payroll_action_id number,
p_balance_name varchar2, p_dimension_name varchar2) RETURN NUMBER
IS
l_bal number;
l_defined_bal_id number;
l_assignment_action_id number;
BEGIN
SELECT assignment_action_id
INTO l_assignment_action_id
FROM
pay_assignment_actions
WHERE
assignment_id = :p_assignment_id
AND payroll_action_id = p_payroll_action_id;
SELECT
defined_balance_id
INTO
l_defined_bal_id
FROM
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE
pbt.business_group_id = p_business_group_id
AND UPPER(pbt.balance_name) = UPPER(p_balance_name)
AND pbt.business_group_id = pdb.business_group_id
AND pbt.balance_type_id = pdb.balance_type_id
AND UPPER(pbd.dimension_name) = UPPER(p_dimension_name)
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
l_bal := pay_balance_pkg.get_value(l_defined_bal_id,l_assignment_action_id);
RETURN (l_bal);
exception
WHEN no_data_found THEN
RETURN 0;
END;
Index
18. Function for getting the Element Value :
FUNCTION f_element_value(
p_classification_name in varchar2,
p_element_name in varchar2,
p_business_group_id in number,
p_input_value_name in varchar2,
p_payroll_action_id in number,
p_assignment_id in number
)
RETURN number
IS
l_element_value number(14,2) default 0;
l_input_value_id pay_input_values_f.input_value_id%type;
l_element_type_id pay_element_types_f.element_type_id%type;
BEGIN
SELECT DISTINCT element_type_id
INTO l_element_type_id
FROM pay_element_types_f pet,
pay_element_classifications pec
WHERE pet.classification_id = pec.classification_id
AND upper(classification_name) = upper(p_classification_name)
AND upper(element_name) = upper(p_element_name)
AND pet.business_group_id = p_business_group_id;
SELECT input_value_id
INTO l_input_value_id
FROM pay_input_values_f
WHERE upper(name) = upper(p_input_value_name)
AND element_type_id = l_element_type_id;
SELECT NVL(prrv.result_value,0)
INTO l_element_value
FROM pay_run_result_values prrv,
pay_run_results prr,
pay_assignment_actions paa
WHERE prrv.run_result_id = prr.run_result_id
AND prr.assignment_ACTION_ID = paa.assignment_action_id
AND paa.assignment_id = p_assignment_id
AND input_value_id = l_input_value_id
AND paa.payroll_action_id = p_payroll_action_id;
RETURN (l_element_value);
exception
WHEN no_data_found THEN
RETURN 0;
END;
Index
19. SELECT Query for counting No of words :
SELECT ename,
NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ',''))+1,1) word_length
FROM emp;
Explanation :
TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @') -- This will translate all the characters FROM A-Z including a single quote to a space. It will also translate a space to a @.
REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ','') -- This will replace every space with nothing in the above result.
LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ',''))+1 -- This will give u the count of @ characters in the above result.
Index
20. Function to check for a leap year :
CREATE OR REPLACE FUNCTION is_leap_year (p_date IN DATE) RETURN VARCHAR2
AS
v_test DATE;
BEGIN
v_test := TO_DATE ('29-Feb-' || TO_CHAR (p_date,'YYYY'),'DD-Mon-YYYY');
RETURN 'Y';
EXCEPTION
WHEN OTHERS THEN
RETURN 'N';
END is_leap_year;
SQL> SELECT hiredate, TO_CHAR (hiredate, 'Day') weekday
FROM emp
WHERE is_leap_year (hiredate) = 'Y';
Index
21. Query for removing all non-numeric :
SELECT
TRANSLATE(LOWER(ssn),'abcdefghijklmnopqrstuvwxyz- ','')
FROM DUAL;
Index
22. Query for translating a column values to INITCAP :
SELECT
TRANSLATE(INITCAP(temp),
SUBSTR(temp, INSTR(temp,'''')+1,1), LOWER(SUBSTR(temp, INSTR(temp,'''')+1)))
FROM srinu1;
Index
23. Function for displaying Rupees in Words :
CREATE OR REPLACE FUNCTION RUPEES_IN_WORDS(amt IN NUMBER) RETURN CHAR
IS
amount NUMBER(10,2);
v_length INTEGER := 0;
v_num2 VARCHAR2 (50) := NULL;
v_amount VARCHAR2 (50);
v_word VARCHAR2 (4000) := NULL;
v_word1 VARCHAR2 (4000) := NULL;
TYPE myarray IS TABLE OF VARCHAR2 (255);
v_str myarray := myarray (' thousand ',
' lakh ',
' crore ',
' arab ',
' kharab ',
' shankh ');
BEGIN
amount := amt;
IF ((amount = 0) OR (amount IS NULL)) THEN
v_word := 'zero';
ELSIF (TO_CHAR (amount) LIKE '%.%') THEN
IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0) THEN
v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);
IF (LENGTH (v_num2) < 2) THEN
v_num2 := v_num2 * 10;
END IF;
v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1,2), 'J'),
'JSP' ))|| ' paise ';
v_amount := SUBSTR(amount,1,INSTR (amount, '.')-1);
v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2,3), 'J'), 'Jsp' ) || v_word;
v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);
FOR i in 1 .. v_str.COUNT
LOOP
EXIT WHEN (v_amount IS NULL);
v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,2), 'J'), 'Jsp' ) || v_str (i) || v_word;
v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
END LOOP;
END IF;
ELSE
v_word := TO_CHAR (TO_DATE (SUBSTR (amount, LENGTH (amount) - 2,3), 'J'), 'Jsp' );
amount := SUBSTR (amount, 1, LENGTH (amount) - 3);
FOR i in 1 .. v_str.COUNT
LOOP
EXIT WHEN (amount IS NULL);
v_word := TO_CHAR (TO_DATE (SUBSTR (amount, LENGTH (amount) - 1,2), 'J'), 'Jsp' ) || v_str (i) || v_word;
amount := SUBSTR (amount, 1, LENGTH (amount) - 2);
END LOOP;
END IF;
v_word := v_word || ' ' || v_word1 || ' only ';
v_word := REPLACE (RTRIM (v_word), ' ', ' ');
v_word := REPLACE (RTRIM (v_word), '-', ' ');
RETURN INITCAP (v_word);
END;
Index
24. Function for displaying Numbers in Words:
SELECT TO_CHAR( TO_DATE( SUBSTR( TO_CHAR(5373484),1),'j'),'Jsp') FROM DUAL;
Only up to integers from 1 to 5373484
Index
25. Query for deleting alternate even rows FROM a table :
DELETE
FROM srinu
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2)
FROM srinu);
Index
26. Query for deleting alternate odd rows FROM a table :
DELETE
FROM srinu
WHERE (ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2)
FROM srinu);
Index
27. Procedure for sending Email :
CREATE OR REPLACE PROCEDURE Send_Mail
IS
sender VARCHAR2(50) := '[email protected]';
recipient VARCHAR2(50) := '[email protected]';
subject VARCHAR2(100) := 'Test Message';
message VARCHAR2(1000) := 'This is a sample mail ....';
lv_mailhost VARCHAR2(30) := 'HOTNT002';
l_mail_conn utl_smtp.connection;
lv_crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
BEGIN
l_mail_conn := utl_smtp.open_connection (lv_mailhost, 80);
utl_smtp.helo ( l_mail_conn, lv_mailhost);
utl_smtp.mail ( l_mail_conn, sender);
utl_smtp.rcpt ( l_mail_conn, recipient);
utl_smtp.open_data (l_mail_conn);
utl_smtp.write_data ( l_mail_conn, 'FROM: ' || sender || lv_crlf);
utl_smtp.write_data ( l_mail_conn, 'To: ' || recipient || lv_crlf);
utl_smtp.write_data ( l_mail_conn, 'Subject:' || subject || lv_crlf);
utl_smtp.write_data ( l_mail_conn, lv_crlf || message);
utl_smtp.close_data(l_mail_conn);
utl_smtp.quit(l_mail_conn);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error');
END;
/
Index
28. Alternate Query for DECODE function :
SELECT case
WHEN sex = 'm' THEN 'male'
WHEN sex = 'f' THEN 'female'
ELSE 'unknown'
END
FROM mytable;
Index
29. Create table adding Constraint to a date field to SYSDATE or 3 months later:
CREATE TABLE srinu(dt1 date DEFAULT SYSDATE, dt2 date,
CONSTRAINT check_dt2 CHECK ((dt2 >= dt1) AND (dt2 <= ADD_MONTHS(SYSDATE,3)));
Index
30. Query to list all the suppliers who supply all the parts supplied by supplier 'S2' :
SELECT DISTINCT a.SUPP
FROM ORDERS a
WHERE a.supp != 'S2'
AND a.parts IN
(SELECT DISTINCT PARTS FROM ORDERS WHERE supp = 'S2')
GROUP BY a.SUPP
HAVING
COUNT(DISTINCT a.PARTS) >=
(SELECT COUNT(DISTINCT PARTS) FROM ORDERS WHERE supp = 'S2');
Table : orders
SUPP PARTS
-------------------- -------
S1 P1
S1 P2
S1 P3
S1 P4
S1 P5
S1 P6
S2 P1
S2 P2
S3 P2
S4 P2
S4 P4
S4 P5
Index
31. Query to get the last Sunday of any month :
SELECT NEXT_DAY(LAST_DAY(TO_DATE('26-10-2001','DD-MM-YYYY')) - 7,'sunday')
FROM DUAL;
Index
32. Query to get all those who have no children themselves :
table data :
id name parent_id
-------------------------------
1 a NULL - the top level entry
2 b 1 - a child of 1
3 c 1
4 d 2 - a child of 2
5 e 2
6 f 3
7 g 3
8 h 4
9 i 8
10 j 9
SELECT ID
FROM MY_TABlE
WHERE PARENT_ID IS NOT NULL
MINUS
SELECT PARENT_ID
FROM MY_TABlE;
Index
33. Query to SELECT last N rows FROM a table :
SELECT empno FROM emp WHERE ROWID in
(SELECT ROWID FROM emp
MINUS
SELECT ROWID FROM emp WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM emp));
Index
34. SELECT with variables:
CREATE OR REPLACE PROCEDURE disp
AS
xTableName varchar2(25):='emp';
xFieldName varchar2(25):='ename';
xValue NUMBER;
xQuery varchar2(100);
name varchar2(10) := 'CLARK';
BEGIN
xQuery := 'SELECT SAL FROM ' || xTableName || ' WHERE ' || xFieldName ||
' = ''' || name || '''';
DBMS_OUTPUT.PUT_LINE(xQuery);
EXECUTE IMMEDIATE xQuery INTO xValue;
DBMS_OUTPUT.PUT_LINE(xValue);
END;
Index
35. Query to get the DB Name:
SELECT name FROM v$database;
Index
36. Getting the current default schema :
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
Index
37. Query to get all the column names of a particular table :
SELECT column_name
FROM all_tab_columns
WHERE TABLE_NAME = 'ORDERS';
Index
38. How do I spool only the query result to a file in SQLPLUS :
Place the following lines of code in a file and execute the file in SQLPLUS :
set heading off
set feedback off
set colsep ' '
set termout off
set verify off
spool c:\srini.txt
SELECT empno,ename FROM emp; /* Write your Query here */
spool off
/
Index
39. Query for getting the current SessionID :
SELECT SYS_CONTEXT('USERENV','SESSIONID') Session_ID FROM DUAL;
Index
40. Query to display rows FROM m to n :
To display rows 5 to 7 :
SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID IN
(SELECT ROWID FROM EMP
WHERE ROWNUM <= 7
MINUS
SELECT ROWID FROM EMP
WHERE ROWNUM < 5);
OR
SELECT ename
FROM emp
GROUP BY ROWNUM, ename
HAVING ROWNUM > 1 and ROWNUM < 3;
Index
41. Query to count no. Of columns in a table:
SELECT COUNT(column_name)
FROM user_tab_columns
WHERE table_name = 'MYTABLE';
Index
42. Procedure to increase the buffer length :
dbms_output.enable(4000); /*allows the output buffer to be increased to the specified number of bytes */
DECLARE
BEGIN
dbms_output.enable(4000);
FOR i IN 1..400
LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/
Index
43. Inserting an & symbol in a Varchar2 column :
Set the following to some other character. By default it is &.
set define '~'
Index
44. How do you remove Trailing blanks in a spooled file :
Change the Environment Options Like this :
set trimspool on
set trimout on
Index
45. Samples for executing Dynamic SQL Statements :
Sample :1
CREATE OR REPLACE PROCEDURE CNT(P_TABLE_NAME IN VARCHAR2)
AS
SqlString VARCHAR2(200);
tot number;
BEGIN
SqlString:='SELECT COUNT(*) FROM '|| P_TABLE_NAME;
EXECUTE IMMEDIATE SqlString INTO tot;
DBMS_OUTPUT.PUT_LINE('Total No.Of Records In ' || P_TABLE_NAME || ' ARE=' || tot);
END;
Sample :2
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := ’PERSONNEL’;
location VARCHAR2(13) := ’DALLAS’;
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id;
EXECUTE IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’;
END;
Sample 3
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
Index
46.Differences between SQL and MS-Access :
Difference 1:
Oracle : select name from table1 where name like 'k%';
Access: select name from table1 where name like 'k*';
Difference 2:
Access: SELECT TOP 2 name FROM Table1;
Oracle : will not work there is no such TOP key word.
Index
47. Query to display all the children, sub children of a parent :
SELECT organization_id,name
FROM hr_all_organization_units
WHERE organization_id in
(
SELECT ORGANIZATION_ID_CHILD FROM PER_ORG_STRUCTURE_ELEMENTS
CONNECT BY PRIOR
ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT
START WITH
ORGANIZATION_ID_CHILD = (SELECT organization_id
FROM hr_all_organization_units
WHERE name = 'EBG Corporate Group'));
Index
48. Procedure to read/write data from a text file :
CREATE OR REPLACE PROCEDURE read_data
AS
c_path varchar2(100) := '/usr/tmp';
c_file_name varchar2(20) := 'EKGSEP01.CSV';
v_file_id utl_file.file_type;
v_buffer varchar2(1022) := This is a sample text’;
BEGIN
v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'w');
UTL_FILE.PUT_LINE(v_file_id, v_buffer);
UTL_FILE.FCLOSE(v_file_id);
v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'r');
UTL_FILE.GET_LINE(v_file_id, v_buffer);
DBMS_OUTPUT.PUT_LINE(v_buffer);
UTL_FILE.FCLOSE(v_file_id);
END;
/
Index
49. Query to display random number between any two given numbers :
SELECT DBMS_RANDOM.VALUE (1,2) FROM DUAL;
Index
50. How can I get the time difference between two date columns :
SELECT
FLOOR((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
FLOOR((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
ROUND((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600 -
(FLOOR((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference
FROM my_table;
Index
51. Using INSTR and SUBSTR
I have this string in a column named location
LOT 8 CONC3 RR
Using instr and substr, I want to take whatever value follows LOT and put
it into a different column and whatever value follows CONC and put it into
a different column
select substr('LOT 8 CONC3 RR',4,instr('LOT 8 CONC3 RR','CONC')-4) from
dual;
select substr('LOT 8 CONC3 RR',-(length('LOT 8 CONC3 RR')-(instr('LOT 8
CONC3 RR','CONC')+3)))
from dual
Index
52. View procedure code
select text from all_source where name = 'X'
order by line;
select text from user_source where name = 'X'
select text from user_source where type = 'procedure' and
name='procedure_name';
select name,text from dba_source where name='ur_procedure'
and owner='scott';
Index
53. To convert signed number to number in oracle
select to_number('-999,999.99', 's999,999.99') from dual; -999,999.99
select to_number('+0,123.45', 's999,999,999.99') from dual; 123.45
select to_number('+999,999.99', 's999,999.99') from dual; 999,999.99
Index
54. Columns of a table
select column_name from user_tab_columns where TABLE_NAME = 'EMP'
select column_name from all_tab_columns where TABLE_NAME = 'EMP'
select column_name from dba_tab_columns where TABLE_NAME = 'EMP'
select column_name from cols where TABLE_NAME = 'EMP'
Index
55. Delete rows conditionally
I have a table have
a,b,c field,
a,b should be unique, and leave max(c) row in.
How can I delete other rows?
delete from 'table'
where (a,b,c) not in (select a,b,max(c) from 'table' group by a,b);
Index
56. CLOB to Char
1) This function helps if your clob column value not exceed 4000 bytes
(varchar2 limit).if clob column's data exceeds 4000 limit, you have to
follow different approach.
create or replace function lob_to_char(clob_col clob) return varchar2 IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
bfils bfile;
l_var varchar2(4000):='';
begin
LOOP
if dbms_lob.getlength(clob_col)<=4000 THEN
dbms_lob.read (clob_col, amt, pos, buffer);
l_var := l_var||buffer;
pos:=pos+amt;
ELSE
l_var:= 'Cannot convert to varchar2..Exceeding varchar2 field
limit';
exit;
END IF;
END LOOP;
return l_var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return l_var;
END;
2) CREATE GLOBAL TEMPORARY TABLE temp_tab(id number,varchar_col
varchar2(4000));
SQL> var r refcursor
SQL> exec lobpkg.lob_to_char(:r);
SQL> print r
create or replace package lobpkg is
type ref1 is ref cursor;
n number:=0;
PROCEDURE lob_to_char(rvar IN OUT lobpkg.ref1) ;
end;
/
create or replace package body lobpkg is
PROCEDURE lob_to_char(rvar IN OUT lobpkg.ref1) IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
r lobpkg.ref1;
bfils bfile;
l_var varchar2(4000):='';
CURSOR C1 IS SELECT * FROM clob_tab;
-- change clob_tab to your_table_name
begin
n:=n+1;
FOR crec IN c1 LOOP
amt:=4000;
pos:=1;
BEGIN
LOOP
--change crec.clob_col to crec.your_column_name
dbms_lob.read (crec.clob_col, amt, pos, buffer);
--change next line if you create temporary table with different name
insert into temp_tab values (n,buffer);
pos:=pos+amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
--change next line if you create temporary table with different name
open rvar for select vchar from temp_tab where id=n;
END;
END;
Index
57. Change Settings
Open file oracle_home\plus32\glogin.sql and
add this
set linesize 100
set pagewidth 20
and save the file
and exit from sql and reload then it will set it.
Index
58. Double quoting a Single quoted String
declare
-- we need one here to get a single quote into the variable
v_str varchar2 (20) := 'O''reilly''s';
begin
DBMS_OUTPUT.PUT_LINE ( 'original single quoted v_str= ' || v_str );
v_str := replace(v_str, '''', '''''');
DBMS_OUTPUT.PUT_LINE ( 'after double quoted v_str= ' || v_str );
end;
SQL> /
original single quoted v_str= O'reilly's
after double quoted v_str= O''reilly''s
Index
59. Time Conversion
CREATE OR REPLACE FUNCTION to_hms (i_days IN number)
RETURN varchar2
IS
BEGIN
RETURN TO_CHAR (TRUNC (i_days)) || ' days ' ||
TO_CHAR (TRUNC (SYSDATE) + MOD (i_days, 1), 'HH24:MI:SS');
END to_hms;
select to_hms(to_date('17-Jan-2002 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('11-Jan-2002 11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from
dual;
Index
60. Table comparison
The table in both the schemas should have exactly the same structure. The data in
it could be same or different
a-b and b-a
select * from a.a minus select * from b.a and select * from b.a minus select * from a.a
Index
61. Running Jobs
select * from user_jobs;
exec dbms_job.remove(job_no);
Index
62. Switching Columns
Update tblname
Set column1 = column2,
Column2 = column1;
Index
63. Replace and Round
I have the number e.g. 63,9823874012983 and I want to round it to 63,98 and at the same time change the , to a .
select round(replace('63,9823874012983',',','.'),2) from dual;
Index
64. First date of the year
select trunc(sysdate, 'y') from dual;
01-jan-2002
last year this month through a select statement
select add_months(sysdate, -12) from dual;
05-APR-01
Index
65. Create Sequence
create sequence sh increment by 1 start with 0;
Index
66. Cursors
cursor is someting like pointers in C language.
u fetch the data using cursor.( wiz...store it somewhere temporarily). u
can do any manipulation to the data that is fetched by the cursor. like
trim, padd, concat or validate. all this are done in temporary areas called
as context area or the cursor area. u can insert this data again in some
other table or do anything u want!!...like setting up some flags etc.
U can display the contents of cursor using the dbms_output only. U can
create an anonymous plsql block or a stored procedure. the major advantage
of cursors is that you can fetch more thatn one row and u can loop through
the resultset and do the manupulations in a secure manner.
set serveroutput on;
declare
cursor c1 is select * from emp;
begin
for var in c1 loop
exit when c1%notfound;
dbms_output.put_line('the employee' || var.ename ||'draws a
salary of '|| var.sal);
end loop;
end;
Index
67. Current Week
select next_day(sysdate-7,'SUNDAY'), next_day(sysdate,'SATURDAY') from dual;
NEXT_DAY( NEXT_DAY(
--------- ---------
07-APR-02 13-APR-02
Index
68. Create Query to restrict the user to a single row
69. Query to get the first inserted record FROM a table
70. How to concatenate a column value with multiple rows
71. Query to delete all the tables at once
72. SQL Query for getting Orphan Records :
Index-----------------------------------------------------------------------------------------------------------------------------------------------
What does the EXPLAIN PLAN statement do in Oracle?
EXPLAIN PLAN statement when executed for a query displays the execution plan chosen by the Cost Based Oracle Otimizer. The execution plan of a SQL statement shows the sequence of instrcutions the Oracle Server will perform while executing that statement. This works for SELECT, INSERT, UPDATE, and DELETE all four statements. The beauty of EXPLAIN PLAN statement lies in the fact that it doesn't require the query statement to be run and this reduces the time taken for displaying the execution plan to a great extent, especially for long-running queries. This was a limitation with the AUTOTRACE statement and probably this is the reason why they have come up with the EXPLAIN PLAN statement as AUTOTRACE always requires the query to be run to completion (Remember TRACEONLY option of the AUTOTRACEcommand only suppresses the display of the result of the query, but the query is required to run to completion in that case as well).
DML or DDL?
EXPLAIN PLAN is a DML statement and hence you need to COMMIT the transaction explicitly as Oracle does not implicitly commit the changes made by DML statements.
What all you require to use it?
EXPLAIN PLAN statement requires you to have sufficient privileges like INSERT-privilege on the output table (in case you're specifying an output table to hold the execution plan... the default output table is PLAN_TABLE), EXECUTE-privilege to execute the SQL statement being used with the EXPLAIN PLAN, and if that SQL statement accesses any other Views/Tables (and if those Views/Tables subsequently access any other Vieews/Tables then for them as well... and so on) then you should have privileges to access all those Views/Tables involved directly or indirectly involved in the execution of the SQL statement for which EXPLAIN PLAN is being used.
Example: how do we use EXPLAIN PLAN?
EXPLAIN PLAN
FOR
SELECT * FROM TABLE_NAME1 T1, TABLE_NAME2 T2
WHERE T1.PK = T2.PK AND ... ;
The immediate output your SQL* Plus console will show is 'Explained.' and for viewing the execution plan generated by the EXPLAIN PLAN command, you can useutlxpls.sql (for parallel queries you should use utlxplp.sql).
What if the same plan table is used by many?
If the same plan table is being shared by many or if you are interested in maintaining the history of execution plans generated then you should use the STATEMENT_ID clause and this will attach the user specified ID with the particular execution plan. Now, you can easily retrieve all the plans by just mentioning the particular STATEMENT_IDs.
EXPLAIN PLAN
SET STATEMENT_ID = 'Statement Id #1'
INTO PLAN_TABLE
FOR
...SQL statement...;
EXPLAIN PLAN works fine for partitioned tables as well and in that case you can simply use the PARTITION_START, PARTITION_STOP, and PARTITION_IDcolumns of the PLAN_TABLE (if the default plan table is being used to capture the execution plan) to get the partitio-related information contained in the execution plan. Needless to mention that you always use pre-defined bind variables with theEXECUTION PLAN.
-----------------------------------------------------------------------------------------------------------------------------------------------1) Display the details of all employees
SQL>Select * from emp;
2) Display the depart information from department table
SQL>select * from dept;
3) Display the name and job for all the employees
SQL>select ename,job from emp;
4) Display the name and salary for all the employees
SQL>select ename,sal from emp;
5) Display the employee no and totalsalary for all the employees
SQL>select empno,ename,sal,comm, sal+nvl(comm,0) as"total salary" from
emp
6) Display the employee name and annual salary for all employees.
SQL>select ename, 12*(sal+nvl(comm,0)) as "annual Sal" from emp
7) Display the names of all the employees who are working in depart number 10.
SQL>select emame from emp where deptno=10;
8) Display the names of all the employees who are working as clerks and
drawing a salary more than 3000.
SQL>select ename from emp where job='CLERK' and sal>3000;
9) Display the employee number and name who are earning comm.
SQL>select empno,ename from emp where comm is not null;
10) Display the employee number and name who do not earn any comm.
SQL>select empno,ename from emp where comm is null;
11) Display the names of employees who are working as clerks,salesman or
analyst and drawing a salary more than 3000.
SQL>select ename from emp where job='CLERK' OR JOB='SALESMAN'
OR JOB='ANALYST' AND SAL>3000;
12) Display the names of the employees who are working in the company for
the past 5 years;
SQL>select ename from emp where to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;
13) Display the list of employees who have joined the company before
30-JUN-90 or after 31-DEC-90.
a)select ename from emp where hiredate < '30-JUN-1990' or hiredate >
'31-DEC-90';
14) Display current Date.
SQL>select sysdate from dual;
15) Display the list of all users in your database(use catalog table).
SQL>select username from all_users;
16) Display the names of all tables from current user;
SQL>select tname from tab;
17) Display the name of the current user.
SQL>show user
18) Display the names of employees working in depart number 10 or 20 or 40
or employees working as
CLERKS,SALESMAN or ANALYST.
SQL>select ename from emp where deptno in(10,20,40) or job
in('CLERKS','SALESMAN','ANALYST');
19) Display the names of employees whose name starts with alaphabet S.
SQL>select ename from emp where ename like 'S%';
20) Display the Employee names for employees whose name ends with alaphabet S.
SQL>select ename from emp where ename like '%S';
21) Display the names of employees whose names have second alphabet A in
their names.
SQL>select ename from emp where ename like '_A%';
22) select the names of the employee whose names is exactly five characters
in length.
SQL>select ename from emp where length(ename)=5;
23) Display the names of the employee who are not working as MANAGERS.
SQL>select ename from emp where job not in('MANAGER');
24) Display the names of the employee who are not working as SALESMAN OR
CLERK OR ANALYST.
SQL>select ename from emp where job not
in('SALESMAN','CLERK','ANALYST');
25) Display all rows from emp table.The system should wait after every
screen full of informaction.
SQL>set pause on
26) Display the total number of employee working in the company.
SQL>select count(*) from emp;
27) Display the total salary beiging paid to all employees.
SQL>select sum(sal) from emp;
28) Display the maximum salary from emp table.
SQL>select max(sal) from emp;
29) Display the minimum salary from emp table.
SQL>select min(sal) from emp;
30) Display the average salary from emp table.
SQL>select avg(sal) from emp;
31) Display the maximum salary being paid to CLERK.
SQL>select max(sal) from emp where job='CLERK';
32) Display the maximum salary being paid to depart number 20.
SQL>select max(sal) from emp where deptno=20;
33) Display the minimum salary being paid to any SALESMAN.
SQL>select min(sal) from emp where job='SALESMAN';
34) Display the average salary drawn by MANAGERS.
SQL>select avg(sal) from emp where job='MANAGER';
35) Display the total salary drawn by ANALYST working in depart number 40.
SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;
36) Display the names of the employee in order of salary i.e the name of
the employee earning lowest salary should salary appear first.
SQL>select ename from emp order by sal;
37) Display the names of the employee in descending order of salary.
a)select ename from emp order by sal desc;
38) Display the names of the employee in order of employee name.
a)select ename from emp order by ename;
39) Display empno,ename,deptno,sal sort the output first base on name and
within name by deptno and with in deptno by sal.
SQL>select empno,ename,deptno,sal from emp order by
40) Display the name of the employee along with their annual salary(sal*12).The name of the employee earning highest annual salary should apper first.
SQL>select ename,sal*12 from emp order by sal desc;
41) Display name,salary,hra,pf,da,total salary for each employee. The
output 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.
SQL>select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as
da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;
42) Display depart numbers and total number of employees working in each
department.
SQL>select deptno,count(deptno)from emp group by deptno;
43) Display the various jobs and total number of employees within each job
group.
SQL>select job,count(job)from emp group by job;
44) Display the depart numbers and total salary for each department.
SQL>select deptno,sum(sal) from emp group by deptno;
45) Display the depart numbers and max salary for each department.
SQL>select deptno,max(sal) from emp group by deptno;
46) Display the various jobs and total salary for each job
SQL>select job,sum(sal) from emp group by job;
47) Display the various jobs and total salary for each job
SQL>select job,min(sal) from emp group by job;
48) Display the depart numbers with more than three employees in each dept.
SQL>select deptno,count(deptno) from emp group by deptno having
count(*)>3;
49) Display the various jobs along with total salary for each of the jobs
where total salary is greater than 40000.
SQL>select job,sum(sal) from emp group by job having sum(sal)>40000;
50) 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.
SQL>select job,count(empno) from emp group by job having count(job)>3
51) Display the name of the empployee who earns highest salary.
SQL>select ename from emp where sal=(select max(sal) from emp);
52) Display the employee number and name for employee working as clerk and
earning highest salary among clerks.
SQL>select empno,ename from emp where where job='CLERK'
and sal=(select max(sal) from emp where job='CLERK');
53) Display the names of salesman who earns a salary more than the highest
salary of any clerk.
SQL>select ename,sal from emp where job='SALESMAN' and sal>(select
max(sal) from emp
where job='CLERK');
54) Display the names of clerks who earn a salary more than the lowest
salary of any salesman.
SQL>select ename from emp where job='CLERK' and sal>(select min(sal)
from emp
where job='SALESMAN');
Display the names of employees who earn a salary more than that of
Jones or that of salary grether than that of scott.
SQL>select ename,sal from emp where sal>
(select sal from emp where ename='JONES')and sal> (select sal from emp
where ename='SCOTT');
55) Display the names of the employees who earn highest salary in their
respective departments.
SQL>select ename,sal,deptno from emp where sal in(select max(sal) from
emp group by deptno);
56) Display the names of the employees who earn highest salaries in their
respective job groups.
SQL>select ename,sal,job from emp where sal in(select max(sal) from emp
group by job)
57) Display the employee names who are working in accounting department.
SQL>select ename from emp where deptno=(select deptno from dept where
dname='ACCOUNTING')
58) Display the employee names who are working in Chicago.
SQL>select ename from emp where deptno=(select deptno from dept where
LOC='CHICAGO')
59) Display the Job groups having total salary greater than the maximum
salary for managers.
SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT
MAX(SAL) FROM EMP WHERE JOB='MANAGER');
60) Display the names of employees from department number 10 with salary
grether than that of any employee working in other department.
SQL>select ename from emp where deptno=10 and sal>any(select sal from
emp where deptno not in 10).
61) Display the names of the employees from department number 10 with
salary greater than that of all employee working in other departments.
SQL>select ename from emp where deptno=10 and sal>all(select sal from
emp where deptno not in 10).
62) Display the names of the employees in Uppercase.
SQL>select upper(ename)from emp
63) Display the names of the employees in Lowecase.
SQL>select lower(ename)from emp
64) Display the names of the employees in Propercase.
SQL>select initcap(ename)from emp;
65) Display the length of Your name using appropriate function.
SQL>select length('name') from dual
66) Display the length of all the employee names.
SQL>select length(ename) from emp;
67) select name of the employee concatenate with employee number.
SQL>select ename||empno from emp;
68) User appropriate function and extract 3 characters starting from 2
characters from the following string 'Oracle'. i.e the out put should be 'ac'.
SQL>select substr('oracle',3,2) from dual
69) Find the First occurance of character 'a' from the following string i.e
'Computer Maintenance Corporation'.
SQL>SELECT INSTR('Computer Maintenance Corporation','a',1) FROM DUAL
70) Replace every occurance of alphabhet A with B in the string Allens(use
translate function)
SQL>select translate('Allens','A','B') from dual
71) Display the informaction from emp table.Where job manager is found it
should be displayed as boos(Use replace function).
SQL>select replace(JOB,'MANAGER','BOSS') FROM EMP;
72) Display empno,ename,deptno from emp table.Instead of display department
numbers display the related department name(Use decode function).
SQL>select empno,ename,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS') from emp;
73) Display your age in days.
SQL>select to_date(sysdate)-to_date('10-sep-77')from dual
74) Display your age in months.
SQL>select months_between(sysdate,'10-sep-77') from dual
75) Display the current date as 15th Augest Friday Nineteen Ninety Saven.
SQL>select to_char(sysdate,'ddth Month day year') from dual
76) Display the following output for each row from emp table.
scott has joined the company on wednesday 13th August ninten nintey.
SQL>select ENAME||' HAS JOINED THE COMPANY ON '||to_char(HIREDATE,'day
ddth Month year') from EMP;
77) Find the date for nearest saturday after current date.
SQL>SELECT NEXT_DAY(SYSDATE,'SATURDAY')FROM DUAL;
78) Display current time.
SQL>select to_char(sysdate,'hh:MM:ss') from dual.
79) Display the date three months Before the current date.
SQL>select add_months(sysdate,3) from dual;
80) Display the common jobs from department number 10 and 20.
SQL>select job from emp where deptno=10 and job in(select job from emp
where deptno=20);
81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.
SQL>select distinct(job) from emp where deptno=10 or deptno=20
(or)
SQL>select distinct(job) from emp where deptno in(10,20);
82) Display the jobs which are unique to department 10.
SQL>select distinct(job) from emp where deptno=10
83) Display the details of those who do not have any person working under them.
SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by
e.ename having count(*)=1;
84) Display the details of those employees who are in sales department and
grade is 3.
SQL>select * from emp where deptno=(select deptno from dept where
dname='SALES')and sal between(select losal from salgrade where grade=3)and
(select hisal from salgrade where grade=3);
85) Display those who are not managers and who are managers any one.
i)display the managers names
SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;
ii)display the who are not managers
SQL>select ename from emp where ename not in(select distinct(m.ename)
from emp e,emp m where m.empno=e.mgr);
86) Display those employee whose name contains not less than 4 characters.
SQL>select ename from emp where length(ename)>4;
87) Display those department whose name start with "S" while the location
name ends with "K".
SQL>select dname from dept where dname like 'S%' and loc like '%K';
88) Display those employees whose manager name is JONES.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and
e.ename='JONES';
89) Display those employees whose salary is more than 3000 after giving 20%
increment.
SQL>select ename,sal from emp where (sal+sal*.2)>3000;
90) Display all employees while their dept names;
SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno
91) Display ename who are working in sales dept.
SQL>select ename from emp where deptno=(select deptno from dept where
dname='SALES');
92) Display employee name,deptname,salary and comm for those sal in between
2000 to 5000 while location is chicago.
SQL>select ename,dname,sal,comm from emp,dept where sal between 2000
and 5000
and loc='CHICAGO' and emp.deptno=dept.deptno;
93)Display those employees whose salary greter than his manager salary.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal
94) Display those employees who are working in the same dept where his
manager is work.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and
p.deptno=e.deptno;
95) Display those employees who are not working under any manager.
SQL>select ename from emp where mgr is null
96) Display grade and employees name for the dept no 10 or 30 but grade is
not 4 while joined the company before 31-dec-82.
SQL>select ename,grade from emp,salgrade where sal between losal and
hisal and deptno in(10,30) and grade<>4 and hiredate<'31-DEC-82';
97) Update the salary of each employee by 10% increment who are not
eligiblw for commission.
SQL>update emp set sal=sal+sal*10/100 where comm is null;
98) SELECT those employee who joined the company before 31-dec-82 while
their dept location is newyork or Chicago.
SQL>SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPT
WHERE (EMP.DEPTNO=DEPT.DEPTNO)AND
HIREDATE <'31-DEC-82' AND DEPT.LOC IN('CHICAGO','NEW YORK');
99) DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO ARE WORKING
AS MANAGER?
SQL>select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not
null;
100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES? --
[AND ALSO DISPLAY THEIR MANAGER NAME]?
SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND
E.ENAME='JONES';
101) Display name and salary of ford if his salary is equal to hisal of his
grade
a)select ename,sal,grade from emp,salgrade where sal between losal and
hisal
and ename ='FORD' AND HISAL=SAL;
102) Display employee name,job,depart name ,manager name,his grade and make
out an under department wise?
SQL>SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP
E,SALGRADE,DEPT
WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR
AND EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME
103) List out all employees name,job,salary,grade and depart name for every
one in the company except 'CLERK'.Sort on salary display the highest salary?
SQL>SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE
SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB
NOT IN('CLERK')ORDER BY SAL ASC;
104) Display the employee name,job and his manager.Display also employee who
are without manager?
SQL>select e.ename,e.job,eMP.ename AS Manager from emp,emp e where
emp.empno(+)=e.mgr
105) Find out the top 5 earners of company?
SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL)
FROM
EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;
106) Display name of those employee who are getting the highest salary?
SQL>select ename from emp where sal=(select max(sal) from emp);
107) Display those employee whose salary is equal to average of maximum and
minimum?
SQL>select ename from emp where sal=(select max(sal)+min(sal)/2 from
emp);
108) Select count of employee in each department where count greater than 3?
SQL>select count(*) from emp group by deptno having count(deptno)>3
109) Display dname where at least 3 are working and display only department
name?
SQL>select distinct d.dname from dept d,emp e where d.deptno=e.deptno
and 3>any
(select count(deptno) from emp group by deptno)
110) Display name of those managers name whose salary is more than average
salary of his company?
SQL>SELECT E.ENAME,EMP.ENAME FROM EMP,EMP E
WHERE EMP.EMPNO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMP);
111)Display those managers name whose salary is more than average salary of
his employee?
SQL>SELECT DISTINCT EMP.ENAME FROM EMP,EMP E WHERE
E.SAL <(SELECT AVG(EMP.SAL) FROM EMP
WHERE EMP.EMPNO=E.MGR GROUP BY EMP.ENAME) AND
EMP.EMPNO=E.MGR;
112) Display employee name,sal,comm and net pay for those employee
whose net pay is greter than or equal to any other employee salary of
the company?
SQL>select ename,sal,comm,sal+nvl(comm,0) as NetPay from emp
where sal+nvl(comm,0) >any (select sal from emp)
113) Display all employees names with total sal of company with each
employee name?
SQL>SELECT ENAME,(SELECT SUM(SAL) FROM EMP) FROM EMP;
114) Find out last 5(least)earners of the company.?
SQL>SELECT DISTINCT SAL FROM EMP E WHERE
5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE
A.SAL<=E.SAL)
ORDER BY SAL DESC;
115) Find out the number of employees whose salary is greater than their
manager salary?
SQL>SELECT E.ENAME FROM EMP ,EMP E WHERE EMP.EMPNO=E.MGR
AND EMP.SAL<E.SAL;
116) Display those department where no employee working?
SQL>select dname from emp,dept where emp.deptno not in(emp.deptno)
117) Display those employee whose salary is ODD value?
SQL>select * from emp where sal<0;
118) Display those employee whose salary contains alleast 3 digits?
SQL>select * from emp where length(sal)>=3;
119) Display those employee who joined in the company in the month of Dec?
SQL>select ename from emp where to_char(hiredate,'MON')='DEC';
120) Display those employees whose name contains "A"?
SQL>select ename from emp where instr(ename,'A')>0;
or
SQL>select ename from emp where ename like('%A%');
121) Display those employee whose deptno is available in salary?
SQL>select emp.ename from emp, emp e where emp.sal=e.deptno;
122) Display those employee whose first 2 characters from hiredate -last 2
characters of salary?
SQL>select ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp
123) Display those employee whose 10% of salary is equal to the year of
joining?
SQL>select ename from emp where to_char(hiredate,'YY')=sal*0.1;
124) Display those employee who are working in sales or research?
SQL>SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE
DNAME IN('SALES','RESEARCH'));
125) Display the grade of jones?
SQL>SELECT ENAME,GRADE FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL AND Ename='JONES';
126) Display those employees who joined the company before 15 of the month?
a)select ename from emp where to_char(hiredate,'DD')<15;
127) Display those employee who has joined before 15th of the month.
a)select ename from emp where to_char(hiredate,'DD')<15;
128) Delete those records where no of employees in a particular department
is less than 3.
SQL>delete from emp where deptno=(select deptno from emp
group by deptno having count(deptno)<3);
129) Display the name of the department where no employee working.
SQL> SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO
130) Display those employees who are working as manager.
SQL>SELECT M.ENAME MANAGER FROM EMP M ,EMP E
WHERE E.MGR=M.EMPNO GROUP BY M.ENAME
131) Display those employees whose grade is equal to any number of sal but
not equal to first number of sal?
SQL> SELECT ENAME,GRADE FROM EMP,SALGRADE
WHERE GRADE NOT IN(SELECT SUBSTR(SAL,0,1)FROM EMP)
132) Print the details of all the employees who are Sub-ordinate to BLAKE?
SQL>select emp.ename from emp, emp e where emp.mgr=e.empno and
e.ename='BLAKE';
133) Display employee name and his salary whose salary is greater than
highest average of department number?
SQL>SELECT SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP
GROUP BY DEPTNO);
134) Display the 10th record of emp table(without using rowid)
SQL>SELECT * FROM EMP WHERE ROWNUM<11
MINUS
SELECT * FROM EMP WHERE ROWNUM<10
135) Display the half of the ename's in upper case and remaining lowercase?
SQL>SELECT
SUBSTR(LOWER(ENAME),1,3)||SUBSTR(UPPER(ENAME),3,LENGTH(ENAME))
FROM EMP;
136) Display the 10th record of emp table without using group by and rowid?
SQL>SELECT * FROM EMP WHERE ROWNUM<11
MINUS
SELECT * FROM EMP WHERE ROWNUM<10
Delete the 10th record of emp table.
SQL>DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11
MINUS
SELECT EMPNO FROM EMP WHERE ROWNUM<10)
137) Create a copy of emp table;
SQL>create table new_table as select * from emp where 1=2;
138) Select ename if ename exists more than once.
SQL>select ename from emp e group by ename having count(*)>1;
139) Display all enames in reverse order?(SMITH:HTIMS).
SQL>SELECT REVERSE(ENAME)FROM EMP;
140) Display those employee whose joining of month and grade is equal.
SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN
(SELECT LOSAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,'MM')) AND
(SELECT HISAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,'MM'));
141) Display those employee whose joining DATE is available in deptno.
SQL>SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE,'DD')=DEPTNO
142) Display those employees name as follows
A ALLEN
B BLAKE
SQL> SELECT SUBSTR(ENAME,1,1),ENAME FROM EMP;
143) List out the employees ename,sal,PF(20% OF SAL) from emp;
SQL>SELECT ENAME,SAL,SAL*.2 AS PF FROM EMP;
144) Create table emp with only one column empno;
SQL>Create table emp as select empno from emp where 1=2;
145) Add this column to emp table ename vrachar2(20).
SQL>alter table emp add(ename varchar2(20));
146) Oops I forgot give the primary key constraint. Add in now.
SQL>alter table emp add primary key(empno);
147) Now increase the length of ename column to 30 characters.
SQL>alter table emp modify(ename varchar2(30));
148) Add salary column to emp table.
SQL>alter table emp add(sal number(10));
149) I want to give a validation saying that salary cannot be greater 10,000
(note give a name to this constraint)
SQL>alter table emp add constraint chk_001 check(sal<=10000)
150) For the time being I have decided that I will not impose this validation.My boss has agreed to pay more than 10,000.
SQL>again alter the table or drop constraint with alter table emp drop constraint chk_001 (or)Disable the constraint by using alter table emp modify constraint chk_001 disable;
151) My boss has changed his mind. Now he doesn't want to pay more than
10,000.so revoke that salary constraint.
SQL>alter table emp modify constraint chk_001 enable;
152) Add column called as mgr to your emp table;
SQL>alter table emp add(mgr number(5));
153) Oh! This column should be related to empno. Give a command to add this
constraint.
SQL>ALTER TABLE EMP ADD CONSTRAINT MGR_DEPT FOREIGN KEY(MGR) REFERENCES
EMP(EMPNO)
154) Add deptno column to your emp table;
SQL>alter table emp add(deptno number(5));
155) This deptno column should be related to deptno column of dept table;
SQL>alter table emp add constraint dept_001 foreign key(deptno)
reference dept(deptno)
[deptno should be primary key]
156) Give the command to add the constraint.
SQL>alter table <table_name) add constraint <constraint_name>
<constraint type>
157) Create table called as newemp. Using single command create this table
as well as get data into this table(use create table as);
SQL>create table newemp as select * from emp;
SQL>Create table called as newemp. This table should contain only
empno,ename,dname.
SQL>create table newemp as select empno,ename,dname from emp,dept where
1=2;
158) Delete the rows of employees who are working in the company for more
than 2 years.
SQL>delete from emp where (sysdate-hiredate)/365>2;
159) Provide a commission(10% Comm Of Sal) to employees who are not earning
any commission.
SQL>select sal*0.1 from emp where comm is null
160) If any employee has commission his commission should be incremented by
10% of his salary.
SQL>update emp set comm=sal*.1 where comm is not null;
161) Display employee name and department name for each employee.
SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno
162)Display employee number,name and location of the department in which he
is working.
SQL>select empno,ename,loc,dname from emp,dept where
emp.deptno=dept.deptno;
163) Display ename,dname even if there are no employees working in a
particular department(use outer join).
SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno(+)
164) Display employee name and his manager name.
SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;
165) Display the department name and total number of employees in each
department.
SQL>select dname,count(ename) from emp,dept where
emp.deptno=dept.deptno group by dname;
166)Display the department name along with total salary in each department.
SQL>select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno
group by dname;
167) Display itemname and total sales amount for each item.
SQL>select itemname,sum(amount) from item group by itemname;
168) Write a Query To Delete The Repeted Rows from emp table;
SQL>Delete from emp where rowid not in(select min(rowid)from emp group
by ename)
169) TO DISPLAY 5 TO 7 ROWS FROM A TABLE
SQL>select ename from emp
where rowid in(select rowid from emp where rownum<=7
minus
select rowid from empi where rownum<5)
170) DISPLAY TOP N ROWS FROM TABLE?
SQL>SELECT * FROM
(SELECT * FROM EMP ORDER BY ENAME DESC)
WHERE ROWNUM <10;
171) DISPLAY TOP 3 SALARIES FROM EMP;
SQL>SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC )
WHERE ROWNUM <4
172) DISPLAY 9th FROM THE EMP TABLE?
SQL>SELECT ENAME FROM EMP
WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10
MINUS
SELECT ROWID FROM EMP WHERE ROWNUM <10)
select second max salary from emp;
select max(sal) fromemp where sal<(select max(sal) from emp);
ANS: SELECT * FROM EMP E1 WHERE SAL <(SELECT AVG(SAL) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO);
2. Display last word (Means Last Letter of that WORD) for every name?
ANS: SELECT SUBSTR(ENAME, -1) FROM EMP;
3. Query for Nth highest salary?
ANS: SELECT * FROM EMP E1 WHERE N = (SELECT COUNT(DISTINCT(SAL)) FROM EMP E2 WHERE E1.SAL <= E2.SAL)
4. Query For max salary – department wise?
ANS: SELECT * FROM EMP E1 WHERE N = (SELECT COUNT(DISTINCT(SAL)) FROM EMP E2 WHERE E1.SAL <= E2.SAL AND E1.DEPTNO = E2.DEPTNO)
5. Display the duplicate records?
ANS: SELECT * FROM EMP WHERE EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(*) >1)
6. Display the selected records?
ANS: SELECT * FROM (SELECT ROWNUM R, ENAME, EMPNO, SAL, DEPTNO FROM EMP) WHERE R = 4 OR R = 9
7. Find how many comma’s in a string? For Example – ‘K,R,I,S,H,N,A’?
ANS: SELECT LENGTH('K,R,I,S,H,N,A')-LENGTH(REPLACE('K,R,I,S,H,N,A',',',NULL)) FROM DUAL
8. How to delete duplicate records?
ANS: DELETE FROM EMP WHERE ROWID NOT IN ( SELECT MAX(ROWID) FROM EMP GROUP BY EMPNO, ENAME, SAL, JOB, DEPTNO)
9. Select updated records?
ANS: SELECT S.ENAME, S.EMPNO, S.SAL FROM EMP S, EMP T WHERE S.ENAME != T.ENAME OR S.EMPNO != T.EMPNO OR S.SAL != T.SAL
10. How to find the day in given date?
ANS: SELECT TO_CHAR(TO_DATE('15-AUG-1947','DD-MM-YY'),'DAY') FROM DUAL;
11. Find experience of employees in Employee table?
ANS: SELECT ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) AS EXPERIENCE FROM EMP
12. Find the last record of a Employee table?
ANS: SELECT * FROM EMP WHERE ROWID = (SELECT MAX(ROWID) FROM EMP)
-----------------------------------------------------------------------------------------------------------------------------------------------1. The following query retrieves "2" highest paid employees FROM each Department :
SELECT deptno, empno, sal
FROM emp e
WHERE
2 > ( SELECT COUNT(e1.sal)
FROM emp e1
WHERE e.deptno = e1.deptno AND e.sal < e1.sal )
ORDER BY 1,3 DESC;
Index
2. Query that will display the total no. of employees, and of that total the number who were hired in 1980, 1981, 1982, and 1983. Give appropriate column headings.
I am looking at the following output. We need to stick to this format.
Total 1980 1981 1982 1983
----------- ------------ ------------ ------------- -----------
14 1 10 2 1
SELECT COUNT (*), COUNT(DECODE(TO_CHAR (hiredate, 'YYYY'),'1980', empno)) "1980",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1981', empno)) "1981",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1982', empno)) "1982",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1983', empno)) "1983"
FROM emp;
Index
3. Query for listing Deptno, ename, sal, SUM(sal in that dept) :
SELECT a.deptno, ename, sal, (SELECT SUM(sal) FROM emp b WHERE a.deptno = b.deptno)
FROM emp a
ORDER BY a.deptno;
OUTPUT :
=======
DEPTNO ENAME SAL SUM (SAL)
========= ======= ==== =========
10 KING 5000 11725
30 BLAKE 2850 10900
10 CLARK 2450 11725
10 JONES 2975 11725
30 MARTIN 1250 10900
30 ALLEN 1600 10900
30 TURNER 1500 10900
30 JAMES 950 10900
30 WARD 2750 10900
20 SMITH 8000 33000
20 SCOTT 3000 33000
20 MILLER 20000 33000
Index
4. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job for all departments, giving each column an appropriate heading.
The output is as follows - we need to stick to this format :
Job Dept 10 Dept 20 Dept 30 Total
---------- --------------- ------------- ------------- ---------
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
SELECT job "Job", SUM (DECODE (deptno, 10, sal)) "Dept 10",
SUM (DECODE (deptno, 20, sal)) "Dept 20",
SUM (DECODE (deptno, 30, sal)) "Dept 30",
SUM (sal) "Total"
FROM emp
GROUP BY job ;
Index
5. 4th Top Salary of all the employees :
SELECT DEPTNO, ENAME, SAL
FROM EMP A
WHERE
3 = (SELECT COUNT(B.SAL) FROM EMP B
WHERE A.SAL < B.SAL) ORDER BY SAL DESC;
Index
6. Retrieving the 5th row FROM a table :
SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID = (SELECT ROWID FROM EMP WHERE ROWNUM <= 5
MINUS
SELECT ROWID FROM EMP WHERE ROWNUM < 5)
Index
7. Tree Query :
Name Null? Type
-------------------------------------------------------------------
SUB NOT NULL VARCHAR2(4)
SUPER VARCHAR2(4)
PRICE NUMBER(6,2)
SELECT sub, super
FROM parts
CONNECT BY PRIOR sub = super
START WITH sub = 'p1';
Index
8. Eliminate duplicates rows in a table :
DELETE FROM table_name A
WHERE ROWID > ( SELECT min(ROWID) FROM table_name B WHERE A.col = B.col);
Index
9. Displaying EVERY 4th row in a table : (If a table has 14 rows, 4,8,12 rows will be selected)
SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);
Index
10. Top N rows FROM a table : (Displays top 9 salaried people)
SELECT ename, deptno, sal
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM < 10;
Index
11. How does one count/sum RANGES of data values in a column? A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z).
SELECT
f2,
COUNT(DECODE(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
COUNT(DECODE(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
COUNT(DECODE(greatest(f1,29), least(f1, 0), 1, 0)) "Range 00-29"
FROM my_table
GROUP BY f2;
Index
12. For equal size ranges it migth be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...).
SELECT ename "Name", sal "Salary",
DECODE( TRUNC(sal/1000, 0), 0, 0.0,
1, 0.1,
2, 0.2,
3, 0.3) "Tax rate"
FROM emp;
13. How does one count different data values in a column?
COL NAME DATATYPE
----------------------------------------
DNO NUMBER
SEX CHAR
SELECT dno, SUM(DECODE(sex,'M',1,0)) MALE,
SUM(DECODE(sex,'F',1,0)) FEMALE,
COUNT(DECODE(sex,'M',1,'F',1)) TOTAL
FROM t1
GROUP BY dno;
Index
14. Query to get the product of all the values of a column :
SELECT EXP(SUM(LN(col1))) FROM srinu;
Index
15. Query to display only the duplicate records in a table:
SELECT num
FROM satyam
GROUP BY num
HAVING COUNT(*) > 1;
Index
16. Query for getting the following output as many number of rows in the table :
*
**
***
****
*****
SELECT RPAD(DECODE(temp,temp,'*'),ROWNUM,'*')
FROM srinu1;
Index
17. Function for getting the Balance Value :
FUNCTION F_BALANCE_VALUE
(p_business_group_id number, p_payroll_action_id number,
p_balance_name varchar2, p_dimension_name varchar2) RETURN NUMBER
IS
l_bal number;
l_defined_bal_id number;
l_assignment_action_id number;
BEGIN
SELECT assignment_action_id
INTO l_assignment_action_id
FROM
pay_assignment_actions
WHERE
assignment_id = :p_assignment_id
AND payroll_action_id = p_payroll_action_id;
SELECT
defined_balance_id
INTO
l_defined_bal_id
FROM
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE
pbt.business_group_id = p_business_group_id
AND UPPER(pbt.balance_name) = UPPER(p_balance_name)
AND pbt.business_group_id = pdb.business_group_id
AND pbt.balance_type_id = pdb.balance_type_id
AND UPPER(pbd.dimension_name) = UPPER(p_dimension_name)
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
l_bal := pay_balance_pkg.get_value(l_defined_bal_id,l_assignment_action_id);
RETURN (l_bal);
exception
WHEN no_data_found THEN
RETURN 0;
END;
Index
18. Function for getting the Element Value :
FUNCTION f_element_value(
p_classification_name in varchar2,
p_element_name in varchar2,
p_business_group_id in number,
p_input_value_name in varchar2,
p_payroll_action_id in number,
p_assignment_id in number
)
RETURN number
IS
l_element_value number(14,2) default 0;
l_input_value_id pay_input_values_f.input_value_id%type;
l_element_type_id pay_element_types_f.element_type_id%type;
BEGIN
SELECT DISTINCT element_type_id
INTO l_element_type_id
FROM pay_element_types_f pet,
pay_element_classifications pec
WHERE pet.classification_id = pec.classification_id
AND upper(classification_name) = upper(p_classification_name)
AND upper(element_name) = upper(p_element_name)
AND pet.business_group_id = p_business_group_id;
SELECT input_value_id
INTO l_input_value_id
FROM pay_input_values_f
WHERE upper(name) = upper(p_input_value_name)
AND element_type_id = l_element_type_id;
SELECT NVL(prrv.result_value,0)
INTO l_element_value
FROM pay_run_result_values prrv,
pay_run_results prr,
pay_assignment_actions paa
WHERE prrv.run_result_id = prr.run_result_id
AND prr.assignment_ACTION_ID = paa.assignment_action_id
AND paa.assignment_id = p_assignment_id
AND input_value_id = l_input_value_id
AND paa.payroll_action_id = p_payroll_action_id;
RETURN (l_element_value);
exception
WHEN no_data_found THEN
RETURN 0;
END;
Index
19. SELECT Query for counting No of words :
SELECT ename,
NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ',''))+1,1) word_length
FROM emp;
Explanation :
TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @') -- This will translate all the characters FROM A-Z including a single quote to a space. It will also translate a space to a @.
REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ','') -- This will replace every space with nothing in the above result.
LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ',''))+1 -- This will give u the count of @ characters in the above result.
Index
20. Function to check for a leap year :
CREATE OR REPLACE FUNCTION is_leap_year (p_date IN DATE) RETURN VARCHAR2
AS
v_test DATE;
BEGIN
v_test := TO_DATE ('29-Feb-' || TO_CHAR (p_date,'YYYY'),'DD-Mon-YYYY');
RETURN 'Y';
EXCEPTION
WHEN OTHERS THEN
RETURN 'N';
END is_leap_year;
SQL> SELECT hiredate, TO_CHAR (hiredate, 'Day') weekday
FROM emp
WHERE is_leap_year (hiredate) = 'Y';
Index
21. Query for removing all non-numeric :
SELECT
TRANSLATE(LOWER(ssn),'abcdefghijklmnopqrstuvwxyz- ','')
FROM DUAL;
Index
22. Query for translating a column values to INITCAP :
SELECT
TRANSLATE(INITCAP(temp),
SUBSTR(temp, INSTR(temp,'''')+1,1), LOWER(SUBSTR(temp, INSTR(temp,'''')+1)))
FROM srinu1;
Index
23. Function for displaying Rupees in Words :
CREATE OR REPLACE FUNCTION RUPEES_IN_WORDS(amt IN NUMBER) RETURN CHAR
IS
amount NUMBER(10,2);
v_length INTEGER := 0;
v_num2 VARCHAR2 (50) := NULL;
v_amount VARCHAR2 (50);
v_word VARCHAR2 (4000) := NULL;
v_word1 VARCHAR2 (4000) := NULL;
TYPE myarray IS TABLE OF VARCHAR2 (255);
v_str myarray := myarray (' thousand ',
' lakh ',
' crore ',
' arab ',
' kharab ',
' shankh ');
BEGIN
amount := amt;
IF ((amount = 0) OR (amount IS NULL)) THEN
v_word := 'zero';
ELSIF (TO_CHAR (amount) LIKE '%.%') THEN
IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0) THEN
v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);
IF (LENGTH (v_num2) < 2) THEN
v_num2 := v_num2 * 10;
END IF;
v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1,2), 'J'),
'JSP' ))|| ' paise ';
v_amount := SUBSTR(amount,1,INSTR (amount, '.')-1);
v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2,3), 'J'), 'Jsp' ) || v_word;
v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);
FOR i in 1 .. v_str.COUNT
LOOP
EXIT WHEN (v_amount IS NULL);
v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,2), 'J'), 'Jsp' ) || v_str (i) || v_word;
v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
END LOOP;
END IF;
ELSE
v_word := TO_CHAR (TO_DATE (SUBSTR (amount, LENGTH (amount) - 2,3), 'J'), 'Jsp' );
amount := SUBSTR (amount, 1, LENGTH (amount) - 3);
FOR i in 1 .. v_str.COUNT
LOOP
EXIT WHEN (amount IS NULL);
v_word := TO_CHAR (TO_DATE (SUBSTR (amount, LENGTH (amount) - 1,2), 'J'), 'Jsp' ) || v_str (i) || v_word;
amount := SUBSTR (amount, 1, LENGTH (amount) - 2);
END LOOP;
END IF;
v_word := v_word || ' ' || v_word1 || ' only ';
v_word := REPLACE (RTRIM (v_word), ' ', ' ');
v_word := REPLACE (RTRIM (v_word), '-', ' ');
RETURN INITCAP (v_word);
END;
Index
24. Function for displaying Numbers in Words:
SELECT TO_CHAR( TO_DATE( SUBSTR( TO_CHAR(5373484),1),'j'),'Jsp') FROM DUAL;
Only up to integers from 1 to 5373484
Index
25. Query for deleting alternate even rows FROM a table :
DELETE
FROM srinu
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2)
FROM srinu);
Index
26. Query for deleting alternate odd rows FROM a table :
DELETE
FROM srinu
WHERE (ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2)
FROM srinu);
Index
27. Procedure for sending Email :
CREATE OR REPLACE PROCEDURE Send_Mail
IS
sender VARCHAR2(50) := '[email protected]';
recipient VARCHAR2(50) := '[email protected]';
subject VARCHAR2(100) := 'Test Message';
message VARCHAR2(1000) := 'This is a sample mail ....';
lv_mailhost VARCHAR2(30) := 'HOTNT002';
l_mail_conn utl_smtp.connection;
lv_crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
BEGIN
l_mail_conn := utl_smtp.open_connection (lv_mailhost, 80);
utl_smtp.helo ( l_mail_conn, lv_mailhost);
utl_smtp.mail ( l_mail_conn, sender);
utl_smtp.rcpt ( l_mail_conn, recipient);
utl_smtp.open_data (l_mail_conn);
utl_smtp.write_data ( l_mail_conn, 'FROM: ' || sender || lv_crlf);
utl_smtp.write_data ( l_mail_conn, 'To: ' || recipient || lv_crlf);
utl_smtp.write_data ( l_mail_conn, 'Subject:' || subject || lv_crlf);
utl_smtp.write_data ( l_mail_conn, lv_crlf || message);
utl_smtp.close_data(l_mail_conn);
utl_smtp.quit(l_mail_conn);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error');
END;
/
Index
28. Alternate Query for DECODE function :
SELECT case
WHEN sex = 'm' THEN 'male'
WHEN sex = 'f' THEN 'female'
ELSE 'unknown'
END
FROM mytable;
Index
29. Create table adding Constraint to a date field to SYSDATE or 3 months later:
CREATE TABLE srinu(dt1 date DEFAULT SYSDATE, dt2 date,
CONSTRAINT check_dt2 CHECK ((dt2 >= dt1) AND (dt2 <= ADD_MONTHS(SYSDATE,3)));
Index
30. Query to list all the suppliers who supply all the parts supplied by supplier 'S2' :
SELECT DISTINCT a.SUPP
FROM ORDERS a
WHERE a.supp != 'S2'
AND a.parts IN
(SELECT DISTINCT PARTS FROM ORDERS WHERE supp = 'S2')
GROUP BY a.SUPP
HAVING
COUNT(DISTINCT a.PARTS) >=
(SELECT COUNT(DISTINCT PARTS) FROM ORDERS WHERE supp = 'S2');
Table : orders
SUPP PARTS
-------------------- -------
S1 P1
S1 P2
S1 P3
S1 P4
S1 P5
S1 P6
S2 P1
S2 P2
S3 P2
S4 P2
S4 P4
S4 P5
Index
31. Query to get the last Sunday of any month :
SELECT NEXT_DAY(LAST_DAY(TO_DATE('26-10-2001','DD-MM-YYYY')) - 7,'sunday')
FROM DUAL;
Index
32. Query to get all those who have no children themselves :
table data :
id name parent_id
-------------------------------
1 a NULL - the top level entry
2 b 1 - a child of 1
3 c 1
4 d 2 - a child of 2
5 e 2
6 f 3
7 g 3
8 h 4
9 i 8
10 j 9
SELECT ID
FROM MY_TABlE
WHERE PARENT_ID IS NOT NULL
MINUS
SELECT PARENT_ID
FROM MY_TABlE;
Index
33. Query to SELECT last N rows FROM a table :
SELECT empno FROM emp WHERE ROWID in
(SELECT ROWID FROM emp
MINUS
SELECT ROWID FROM emp WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM emp));
Index
34. SELECT with variables:
CREATE OR REPLACE PROCEDURE disp
AS
xTableName varchar2(25):='emp';
xFieldName varchar2(25):='ename';
xValue NUMBER;
xQuery varchar2(100);
name varchar2(10) := 'CLARK';
BEGIN
xQuery := 'SELECT SAL FROM ' || xTableName || ' WHERE ' || xFieldName ||
' = ''' || name || '''';
DBMS_OUTPUT.PUT_LINE(xQuery);
EXECUTE IMMEDIATE xQuery INTO xValue;
DBMS_OUTPUT.PUT_LINE(xValue);
END;
Index
35. Query to get the DB Name:
SELECT name FROM v$database;
Index
36. Getting the current default schema :
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
Index
37. Query to get all the column names of a particular table :
SELECT column_name
FROM all_tab_columns
WHERE TABLE_NAME = 'ORDERS';
Index
38. How do I spool only the query result to a file in SQLPLUS :
Place the following lines of code in a file and execute the file in SQLPLUS :
set heading off
set feedback off
set colsep ' '
set termout off
set verify off
spool c:\srini.txt
SELECT empno,ename FROM emp; /* Write your Query here */
spool off
/
Index
39. Query for getting the current SessionID :
SELECT SYS_CONTEXT('USERENV','SESSIONID') Session_ID FROM DUAL;
Index
40. Query to display rows FROM m to n :
To display rows 5 to 7 :
SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID IN
(SELECT ROWID FROM EMP
WHERE ROWNUM <= 7
MINUS
SELECT ROWID FROM EMP
WHERE ROWNUM < 5);
OR
SELECT ename
FROM emp
GROUP BY ROWNUM, ename
HAVING ROWNUM > 1 and ROWNUM < 3;
Index
41. Query to count no. Of columns in a table:
SELECT COUNT(column_name)
FROM user_tab_columns
WHERE table_name = 'MYTABLE';
Index
42. Procedure to increase the buffer length :
dbms_output.enable(4000); /*allows the output buffer to be increased to the specified number of bytes */
DECLARE
BEGIN
dbms_output.enable(4000);
FOR i IN 1..400
LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/
Index
43. Inserting an & symbol in a Varchar2 column :
Set the following to some other character. By default it is &.
set define '~'
Index
44. How do you remove Trailing blanks in a spooled file :
Change the Environment Options Like this :
set trimspool on
set trimout on
Index
45. Samples for executing Dynamic SQL Statements :
Sample :1
CREATE OR REPLACE PROCEDURE CNT(P_TABLE_NAME IN VARCHAR2)
AS
SqlString VARCHAR2(200);
tot number;
BEGIN
SqlString:='SELECT COUNT(*) FROM '|| P_TABLE_NAME;
EXECUTE IMMEDIATE SqlString INTO tot;
DBMS_OUTPUT.PUT_LINE('Total No.Of Records In ' || P_TABLE_NAME || ' ARE=' || tot);
END;
Sample :2
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := ’PERSONNEL’;
location VARCHAR2(13) := ’DALLAS’;
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id;
EXECUTE IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’;
END;
Sample 3
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
Index
46.Differences between SQL and MS-Access :
Difference 1:
Oracle : select name from table1 where name like 'k%';
Access: select name from table1 where name like 'k*';
Difference 2:
Access: SELECT TOP 2 name FROM Table1;
Oracle : will not work there is no such TOP key word.
Index
47. Query to display all the children, sub children of a parent :
SELECT organization_id,name
FROM hr_all_organization_units
WHERE organization_id in
(
SELECT ORGANIZATION_ID_CHILD FROM PER_ORG_STRUCTURE_ELEMENTS
CONNECT BY PRIOR
ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT
START WITH
ORGANIZATION_ID_CHILD = (SELECT organization_id
FROM hr_all_organization_units
WHERE name = 'EBG Corporate Group'));
Index
48. Procedure to read/write data from a text file :
CREATE OR REPLACE PROCEDURE read_data
AS
c_path varchar2(100) := '/usr/tmp';
c_file_name varchar2(20) := 'EKGSEP01.CSV';
v_file_id utl_file.file_type;
v_buffer varchar2(1022) := This is a sample text’;
BEGIN
v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'w');
UTL_FILE.PUT_LINE(v_file_id, v_buffer);
UTL_FILE.FCLOSE(v_file_id);
v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'r');
UTL_FILE.GET_LINE(v_file_id, v_buffer);
DBMS_OUTPUT.PUT_LINE(v_buffer);
UTL_FILE.FCLOSE(v_file_id);
END;
/
Index
49. Query to display random number between any two given numbers :
SELECT DBMS_RANDOM.VALUE (1,2) FROM DUAL;
Index
50. How can I get the time difference between two date columns :
SELECT
FLOOR((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
FLOOR((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
ROUND((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600 -
(FLOOR((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference
FROM my_table;
Index
51. Using INSTR and SUBSTR
I have this string in a column named location
LOT 8 CONC3 RR
Using instr and substr, I want to take whatever value follows LOT and put
it into a different column and whatever value follows CONC and put it into
a different column
select substr('LOT 8 CONC3 RR',4,instr('LOT 8 CONC3 RR','CONC')-4) from
dual;
select substr('LOT 8 CONC3 RR',-(length('LOT 8 CONC3 RR')-(instr('LOT 8
CONC3 RR','CONC')+3)))
from dual
Index
52. View procedure code
select text from all_source where name = 'X'
order by line;
select text from user_source where name = 'X'
select text from user_source where type = 'procedure' and
name='procedure_name';
select name,text from dba_source where name='ur_procedure'
and owner='scott';
Index
53. To convert signed number to number in oracle
select to_number('-999,999.99', 's999,999.99') from dual; -999,999.99
select to_number('+0,123.45', 's999,999,999.99') from dual; 123.45
select to_number('+999,999.99', 's999,999.99') from dual; 999,999.99
Index
54. Columns of a table
select column_name from user_tab_columns where TABLE_NAME = 'EMP'
select column_name from all_tab_columns where TABLE_NAME = 'EMP'
select column_name from dba_tab_columns where TABLE_NAME = 'EMP'
select column_name from cols where TABLE_NAME = 'EMP'
Index
55. Delete rows conditionally
I have a table have
a,b,c field,
a,b should be unique, and leave max(c) row in.
How can I delete other rows?
delete from 'table'
where (a,b,c) not in (select a,b,max(c) from 'table' group by a,b);
Index
56. CLOB to Char
1) This function helps if your clob column value not exceed 4000 bytes
(varchar2 limit).if clob column's data exceeds 4000 limit, you have to
follow different approach.
create or replace function lob_to_char(clob_col clob) return varchar2 IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
bfils bfile;
l_var varchar2(4000):='';
begin
LOOP
if dbms_lob.getlength(clob_col)<=4000 THEN
dbms_lob.read (clob_col, amt, pos, buffer);
l_var := l_var||buffer;
pos:=pos+amt;
ELSE
l_var:= 'Cannot convert to varchar2..Exceeding varchar2 field
limit';
exit;
END IF;
END LOOP;
return l_var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return l_var;
END;
2) CREATE GLOBAL TEMPORARY TABLE temp_tab(id number,varchar_col
varchar2(4000));
SQL> var r refcursor
SQL> exec lobpkg.lob_to_char(:r);
SQL> print r
create or replace package lobpkg is
type ref1 is ref cursor;
n number:=0;
PROCEDURE lob_to_char(rvar IN OUT lobpkg.ref1) ;
end;
/
create or replace package body lobpkg is
PROCEDURE lob_to_char(rvar IN OUT lobpkg.ref1) IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
r lobpkg.ref1;
bfils bfile;
l_var varchar2(4000):='';
CURSOR C1 IS SELECT * FROM clob_tab;
-- change clob_tab to your_table_name
begin
n:=n+1;
FOR crec IN c1 LOOP
amt:=4000;
pos:=1;
BEGIN
LOOP
--change crec.clob_col to crec.your_column_name
dbms_lob.read (crec.clob_col, amt, pos, buffer);
--change next line if you create temporary table with different name
insert into temp_tab values (n,buffer);
pos:=pos+amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
--change next line if you create temporary table with different name
open rvar for select vchar from temp_tab where id=n;
END;
END;
Index
57. Change Settings
Open file oracle_home\plus32\glogin.sql and
add this
set linesize 100
set pagewidth 20
and save the file
and exit from sql and reload then it will set it.
Index
58. Double quoting a Single quoted String
declare
-- we need one here to get a single quote into the variable
v_str varchar2 (20) := 'O''reilly''s';
begin
DBMS_OUTPUT.PUT_LINE ( 'original single quoted v_str= ' || v_str );
v_str := replace(v_str, '''', '''''');
DBMS_OUTPUT.PUT_LINE ( 'after double quoted v_str= ' || v_str );
end;
SQL> /
original single quoted v_str= O'reilly's
after double quoted v_str= O''reilly''s
Index
59. Time Conversion
CREATE OR REPLACE FUNCTION to_hms (i_days IN number)
RETURN varchar2
IS
BEGIN
RETURN TO_CHAR (TRUNC (i_days)) || ' days ' ||
TO_CHAR (TRUNC (SYSDATE) + MOD (i_days, 1), 'HH24:MI:SS');
END to_hms;
select to_hms(to_date('17-Jan-2002 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('11-Jan-2002 11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from
dual;
Index
60. Table comparison
The table in both the schemas should have exactly the same structure. The data in
it could be same or different
a-b and b-a
select * from a.a minus select * from b.a and select * from b.a minus select * from a.a
Index
61. Running Jobs
select * from user_jobs;
exec dbms_job.remove(job_no);
Index
62. Switching Columns
Update tblname
Set column1 = column2,
Column2 = column1;
Index
63. Replace and Round
I have the number e.g. 63,9823874012983 and I want to round it to 63,98 and at the same time change the , to a .
select round(replace('63,9823874012983',',','.'),2) from dual;
Index
64. First date of the year
select trunc(sysdate, 'y') from dual;
01-jan-2002
last year this month through a select statement
select add_months(sysdate, -12) from dual;
05-APR-01
Index
65. Create Sequence
create sequence sh increment by 1 start with 0;
Index
66. Cursors
cursor is someting like pointers in C language.
u fetch the data using cursor.( wiz...store it somewhere temporarily). u
can do any manipulation to the data that is fetched by the cursor. like
trim, padd, concat or validate. all this are done in temporary areas called
as context area or the cursor area. u can insert this data again in some
other table or do anything u want!!...like setting up some flags etc.
U can display the contents of cursor using the dbms_output only. U can
create an anonymous plsql block or a stored procedure. the major advantage
of cursors is that you can fetch more thatn one row and u can loop through
the resultset and do the manupulations in a secure manner.
set serveroutput on;
declare
cursor c1 is select * from emp;
begin
for var in c1 loop
exit when c1%notfound;
dbms_output.put_line('the employee' || var.ename ||'draws a
salary of '|| var.sal);
end loop;
end;
Index
67. Current Week
select next_day(sysdate-7,'SUNDAY'), next_day(sysdate,'SATURDAY') from dual;
NEXT_DAY( NEXT_DAY(
--------- ---------
07-APR-02 13-APR-02
Index
68. Create Query to restrict the user to a single row
69. Query to get the first inserted record FROM a table
70. How to concatenate a column value with multiple rows
71. Query to delete all the tables at once
72. SQL Query for getting Orphan Records :
Index-----------------------------------------------------------------------------------------------------------------------------------------------
What does the EXPLAIN PLAN statement do in Oracle?
EXPLAIN PLAN statement when executed for a query displays the execution plan chosen by the Cost Based Oracle Otimizer. The execution plan of a SQL statement shows the sequence of instrcutions the Oracle Server will perform while executing that statement. This works for SELECT, INSERT, UPDATE, and DELETE all four statements. The beauty of EXPLAIN PLAN statement lies in the fact that it doesn't require the query statement to be run and this reduces the time taken for displaying the execution plan to a great extent, especially for long-running queries. This was a limitation with the AUTOTRACE statement and probably this is the reason why they have come up with the EXPLAIN PLAN statement as AUTOTRACE always requires the query to be run to completion (Remember TRACEONLY option of the AUTOTRACEcommand only suppresses the display of the result of the query, but the query is required to run to completion in that case as well).
DML or DDL?
EXPLAIN PLAN is a DML statement and hence you need to COMMIT the transaction explicitly as Oracle does not implicitly commit the changes made by DML statements.
What all you require to use it?
EXPLAIN PLAN statement requires you to have sufficient privileges like INSERT-privilege on the output table (in case you're specifying an output table to hold the execution plan... the default output table is PLAN_TABLE), EXECUTE-privilege to execute the SQL statement being used with the EXPLAIN PLAN, and if that SQL statement accesses any other Views/Tables (and if those Views/Tables subsequently access any other Vieews/Tables then for them as well... and so on) then you should have privileges to access all those Views/Tables involved directly or indirectly involved in the execution of the SQL statement for which EXPLAIN PLAN is being used.
Example: how do we use EXPLAIN PLAN?
EXPLAIN PLAN
FOR
SELECT * FROM TABLE_NAME1 T1, TABLE_NAME2 T2
WHERE T1.PK = T2.PK AND ... ;
The immediate output your SQL* Plus console will show is 'Explained.' and for viewing the execution plan generated by the EXPLAIN PLAN command, you can useutlxpls.sql (for parallel queries you should use utlxplp.sql).
What if the same plan table is used by many?
If the same plan table is being shared by many or if you are interested in maintaining the history of execution plans generated then you should use the STATEMENT_ID clause and this will attach the user specified ID with the particular execution plan. Now, you can easily retrieve all the plans by just mentioning the particular STATEMENT_IDs.
EXPLAIN PLAN
SET STATEMENT_ID = 'Statement Id #1'
INTO PLAN_TABLE
FOR
...SQL statement...;
EXPLAIN PLAN works fine for partitioned tables as well and in that case you can simply use the PARTITION_START, PARTITION_STOP, and PARTITION_IDcolumns of the PLAN_TABLE (if the default plan table is being used to capture the execution plan) to get the partitio-related information contained in the execution plan. Needless to mention that you always use pre-defined bind variables with theEXECUTION PLAN.
-----------------------------------------------------------------------------------------------------------------------------------------------1) Display the details of all employees
SQL>Select * from emp;
2) Display the depart information from department table
SQL>select * from dept;
3) Display the name and job for all the employees
SQL>select ename,job from emp;
4) Display the name and salary for all the employees
SQL>select ename,sal from emp;
5) Display the employee no and totalsalary for all the employees
SQL>select empno,ename,sal,comm, sal+nvl(comm,0) as"total salary" from
emp
6) Display the employee name and annual salary for all employees.
SQL>select ename, 12*(sal+nvl(comm,0)) as "annual Sal" from emp
7) Display the names of all the employees who are working in depart number 10.
SQL>select emame from emp where deptno=10;
8) Display the names of all the employees who are working as clerks and
drawing a salary more than 3000.
SQL>select ename from emp where job='CLERK' and sal>3000;
9) Display the employee number and name who are earning comm.
SQL>select empno,ename from emp where comm is not null;
10) Display the employee number and name who do not earn any comm.
SQL>select empno,ename from emp where comm is null;
11) Display the names of employees who are working as clerks,salesman or
analyst and drawing a salary more than 3000.
SQL>select ename from emp where job='CLERK' OR JOB='SALESMAN'
OR JOB='ANALYST' AND SAL>3000;
12) Display the names of the employees who are working in the company for
the past 5 years;
SQL>select ename from emp where to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;
13) Display the list of employees who have joined the company before
30-JUN-90 or after 31-DEC-90.
a)select ename from emp where hiredate < '30-JUN-1990' or hiredate >
'31-DEC-90';
14) Display current Date.
SQL>select sysdate from dual;
15) Display the list of all users in your database(use catalog table).
SQL>select username from all_users;
16) Display the names of all tables from current user;
SQL>select tname from tab;
17) Display the name of the current user.
SQL>show user
18) Display the names of employees working in depart number 10 or 20 or 40
or employees working as
CLERKS,SALESMAN or ANALYST.
SQL>select ename from emp where deptno in(10,20,40) or job
in('CLERKS','SALESMAN','ANALYST');
19) Display the names of employees whose name starts with alaphabet S.
SQL>select ename from emp where ename like 'S%';
20) Display the Employee names for employees whose name ends with alaphabet S.
SQL>select ename from emp where ename like '%S';
21) Display the names of employees whose names have second alphabet A in
their names.
SQL>select ename from emp where ename like '_A%';
22) select the names of the employee whose names is exactly five characters
in length.
SQL>select ename from emp where length(ename)=5;
23) Display the names of the employee who are not working as MANAGERS.
SQL>select ename from emp where job not in('MANAGER');
24) Display the names of the employee who are not working as SALESMAN OR
CLERK OR ANALYST.
SQL>select ename from emp where job not
in('SALESMAN','CLERK','ANALYST');
25) Display all rows from emp table.The system should wait after every
screen full of informaction.
SQL>set pause on
26) Display the total number of employee working in the company.
SQL>select count(*) from emp;
27) Display the total salary beiging paid to all employees.
SQL>select sum(sal) from emp;
28) Display the maximum salary from emp table.
SQL>select max(sal) from emp;
29) Display the minimum salary from emp table.
SQL>select min(sal) from emp;
30) Display the average salary from emp table.
SQL>select avg(sal) from emp;
31) Display the maximum salary being paid to CLERK.
SQL>select max(sal) from emp where job='CLERK';
32) Display the maximum salary being paid to depart number 20.
SQL>select max(sal) from emp where deptno=20;
33) Display the minimum salary being paid to any SALESMAN.
SQL>select min(sal) from emp where job='SALESMAN';
34) Display the average salary drawn by MANAGERS.
SQL>select avg(sal) from emp where job='MANAGER';
35) Display the total salary drawn by ANALYST working in depart number 40.
SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;
36) Display the names of the employee in order of salary i.e the name of
the employee earning lowest salary should salary appear first.
SQL>select ename from emp order by sal;
37) Display the names of the employee in descending order of salary.
a)select ename from emp order by sal desc;
38) Display the names of the employee in order of employee name.
a)select ename from emp order by ename;
39) Display empno,ename,deptno,sal sort the output first base on name and
within name by deptno and with in deptno by sal.
SQL>select empno,ename,deptno,sal from emp order by
40) Display the name of the employee along with their annual salary(sal*12).The name of the employee earning highest annual salary should apper first.
SQL>select ename,sal*12 from emp order by sal desc;
41) Display name,salary,hra,pf,da,total salary for each employee. The
output 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.
SQL>select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as
da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;
42) Display depart numbers and total number of employees working in each
department.
SQL>select deptno,count(deptno)from emp group by deptno;
43) Display the various jobs and total number of employees within each job
group.
SQL>select job,count(job)from emp group by job;
44) Display the depart numbers and total salary for each department.
SQL>select deptno,sum(sal) from emp group by deptno;
45) Display the depart numbers and max salary for each department.
SQL>select deptno,max(sal) from emp group by deptno;
46) Display the various jobs and total salary for each job
SQL>select job,sum(sal) from emp group by job;
47) Display the various jobs and total salary for each job
SQL>select job,min(sal) from emp group by job;
48) Display the depart numbers with more than three employees in each dept.
SQL>select deptno,count(deptno) from emp group by deptno having
count(*)>3;
49) Display the various jobs along with total salary for each of the jobs
where total salary is greater than 40000.
SQL>select job,sum(sal) from emp group by job having sum(sal)>40000;
50) 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.
SQL>select job,count(empno) from emp group by job having count(job)>3
51) Display the name of the empployee who earns highest salary.
SQL>select ename from emp where sal=(select max(sal) from emp);
52) Display the employee number and name for employee working as clerk and
earning highest salary among clerks.
SQL>select empno,ename from emp where where job='CLERK'
and sal=(select max(sal) from emp where job='CLERK');
53) Display the names of salesman who earns a salary more than the highest
salary of any clerk.
SQL>select ename,sal from emp where job='SALESMAN' and sal>(select
max(sal) from emp
where job='CLERK');
54) Display the names of clerks who earn a salary more than the lowest
salary of any salesman.
SQL>select ename from emp where job='CLERK' and sal>(select min(sal)
from emp
where job='SALESMAN');
Display the names of employees who earn a salary more than that of
Jones or that of salary grether than that of scott.
SQL>select ename,sal from emp where sal>
(select sal from emp where ename='JONES')and sal> (select sal from emp
where ename='SCOTT');
55) Display the names of the employees who earn highest salary in their
respective departments.
SQL>select ename,sal,deptno from emp where sal in(select max(sal) from
emp group by deptno);
56) Display the names of the employees who earn highest salaries in their
respective job groups.
SQL>select ename,sal,job from emp where sal in(select max(sal) from emp
group by job)
57) Display the employee names who are working in accounting department.
SQL>select ename from emp where deptno=(select deptno from dept where
dname='ACCOUNTING')
58) Display the employee names who are working in Chicago.
SQL>select ename from emp where deptno=(select deptno from dept where
LOC='CHICAGO')
59) Display the Job groups having total salary greater than the maximum
salary for managers.
SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT
MAX(SAL) FROM EMP WHERE JOB='MANAGER');
60) Display the names of employees from department number 10 with salary
grether than that of any employee working in other department.
SQL>select ename from emp where deptno=10 and sal>any(select sal from
emp where deptno not in 10).
61) Display the names of the employees from department number 10 with
salary greater than that of all employee working in other departments.
SQL>select ename from emp where deptno=10 and sal>all(select sal from
emp where deptno not in 10).
62) Display the names of the employees in Uppercase.
SQL>select upper(ename)from emp
63) Display the names of the employees in Lowecase.
SQL>select lower(ename)from emp
64) Display the names of the employees in Propercase.
SQL>select initcap(ename)from emp;
65) Display the length of Your name using appropriate function.
SQL>select length('name') from dual
66) Display the length of all the employee names.
SQL>select length(ename) from emp;
67) select name of the employee concatenate with employee number.
SQL>select ename||empno from emp;
68) User appropriate function and extract 3 characters starting from 2
characters from the following string 'Oracle'. i.e the out put should be 'ac'.
SQL>select substr('oracle',3,2) from dual
69) Find the First occurance of character 'a' from the following string i.e
'Computer Maintenance Corporation'.
SQL>SELECT INSTR('Computer Maintenance Corporation','a',1) FROM DUAL
70) Replace every occurance of alphabhet A with B in the string Allens(use
translate function)
SQL>select translate('Allens','A','B') from dual
71) Display the informaction from emp table.Where job manager is found it
should be displayed as boos(Use replace function).
SQL>select replace(JOB,'MANAGER','BOSS') FROM EMP;
72) Display empno,ename,deptno from emp table.Instead of display department
numbers display the related department name(Use decode function).
SQL>select empno,ename,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS') from emp;
73) Display your age in days.
SQL>select to_date(sysdate)-to_date('10-sep-77')from dual
74) Display your age in months.
SQL>select months_between(sysdate,'10-sep-77') from dual
75) Display the current date as 15th Augest Friday Nineteen Ninety Saven.
SQL>select to_char(sysdate,'ddth Month day year') from dual
76) Display the following output for each row from emp table.
scott has joined the company on wednesday 13th August ninten nintey.
SQL>select ENAME||' HAS JOINED THE COMPANY ON '||to_char(HIREDATE,'day
ddth Month year') from EMP;
77) Find the date for nearest saturday after current date.
SQL>SELECT NEXT_DAY(SYSDATE,'SATURDAY')FROM DUAL;
78) Display current time.
SQL>select to_char(sysdate,'hh:MM:ss') from dual.
79) Display the date three months Before the current date.
SQL>select add_months(sysdate,3) from dual;
80) Display the common jobs from department number 10 and 20.
SQL>select job from emp where deptno=10 and job in(select job from emp
where deptno=20);
81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.
SQL>select distinct(job) from emp where deptno=10 or deptno=20
(or)
SQL>select distinct(job) from emp where deptno in(10,20);
82) Display the jobs which are unique to department 10.
SQL>select distinct(job) from emp where deptno=10
83) Display the details of those who do not have any person working under them.
SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by
e.ename having count(*)=1;
84) Display the details of those employees who are in sales department and
grade is 3.
SQL>select * from emp where deptno=(select deptno from dept where
dname='SALES')and sal between(select losal from salgrade where grade=3)and
(select hisal from salgrade where grade=3);
85) Display those who are not managers and who are managers any one.
i)display the managers names
SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;
ii)display the who are not managers
SQL>select ename from emp where ename not in(select distinct(m.ename)
from emp e,emp m where m.empno=e.mgr);
86) Display those employee whose name contains not less than 4 characters.
SQL>select ename from emp where length(ename)>4;
87) Display those department whose name start with "S" while the location
name ends with "K".
SQL>select dname from dept where dname like 'S%' and loc like '%K';
88) Display those employees whose manager name is JONES.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and
e.ename='JONES';
89) Display those employees whose salary is more than 3000 after giving 20%
increment.
SQL>select ename,sal from emp where (sal+sal*.2)>3000;
90) Display all employees while their dept names;
SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno
91) Display ename who are working in sales dept.
SQL>select ename from emp where deptno=(select deptno from dept where
dname='SALES');
92) Display employee name,deptname,salary and comm for those sal in between
2000 to 5000 while location is chicago.
SQL>select ename,dname,sal,comm from emp,dept where sal between 2000
and 5000
and loc='CHICAGO' and emp.deptno=dept.deptno;
93)Display those employees whose salary greter than his manager salary.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal
94) Display those employees who are working in the same dept where his
manager is work.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and
p.deptno=e.deptno;
95) Display those employees who are not working under any manager.
SQL>select ename from emp where mgr is null
96) Display grade and employees name for the dept no 10 or 30 but grade is
not 4 while joined the company before 31-dec-82.
SQL>select ename,grade from emp,salgrade where sal between losal and
hisal and deptno in(10,30) and grade<>4 and hiredate<'31-DEC-82';
97) Update the salary of each employee by 10% increment who are not
eligiblw for commission.
SQL>update emp set sal=sal+sal*10/100 where comm is null;
98) SELECT those employee who joined the company before 31-dec-82 while
their dept location is newyork or Chicago.
SQL>SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPT
WHERE (EMP.DEPTNO=DEPT.DEPTNO)AND
HIREDATE <'31-DEC-82' AND DEPT.LOC IN('CHICAGO','NEW YORK');
99) DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO ARE WORKING
AS MANAGER?
SQL>select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not
null;
100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES? --
[AND ALSO DISPLAY THEIR MANAGER NAME]?
SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND
E.ENAME='JONES';
101) Display name and salary of ford if his salary is equal to hisal of his
grade
a)select ename,sal,grade from emp,salgrade where sal between losal and
hisal
and ename ='FORD' AND HISAL=SAL;
102) Display employee name,job,depart name ,manager name,his grade and make
out an under department wise?
SQL>SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP
E,SALGRADE,DEPT
WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR
AND EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME
103) List out all employees name,job,salary,grade and depart name for every
one in the company except 'CLERK'.Sort on salary display the highest salary?
SQL>SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE
SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB
NOT IN('CLERK')ORDER BY SAL ASC;
104) Display the employee name,job and his manager.Display also employee who
are without manager?
SQL>select e.ename,e.job,eMP.ename AS Manager from emp,emp e where
emp.empno(+)=e.mgr
105) Find out the top 5 earners of company?
SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL)
FROM
EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;
106) Display name of those employee who are getting the highest salary?
SQL>select ename from emp where sal=(select max(sal) from emp);
107) Display those employee whose salary is equal to average of maximum and
minimum?
SQL>select ename from emp where sal=(select max(sal)+min(sal)/2 from
emp);
108) Select count of employee in each department where count greater than 3?
SQL>select count(*) from emp group by deptno having count(deptno)>3
109) Display dname where at least 3 are working and display only department
name?
SQL>select distinct d.dname from dept d,emp e where d.deptno=e.deptno
and 3>any
(select count(deptno) from emp group by deptno)
110) Display name of those managers name whose salary is more than average
salary of his company?
SQL>SELECT E.ENAME,EMP.ENAME FROM EMP,EMP E
WHERE EMP.EMPNO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMP);
111)Display those managers name whose salary is more than average salary of
his employee?
SQL>SELECT DISTINCT EMP.ENAME FROM EMP,EMP E WHERE
E.SAL <(SELECT AVG(EMP.SAL) FROM EMP
WHERE EMP.EMPNO=E.MGR GROUP BY EMP.ENAME) AND
EMP.EMPNO=E.MGR;
112) Display employee name,sal,comm and net pay for those employee
whose net pay is greter than or equal to any other employee salary of
the company?
SQL>select ename,sal,comm,sal+nvl(comm,0) as NetPay from emp
where sal+nvl(comm,0) >any (select sal from emp)
113) Display all employees names with total sal of company with each
employee name?
SQL>SELECT ENAME,(SELECT SUM(SAL) FROM EMP) FROM EMP;
114) Find out last 5(least)earners of the company.?
SQL>SELECT DISTINCT SAL FROM EMP E WHERE
5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE
A.SAL<=E.SAL)
ORDER BY SAL DESC;
115) Find out the number of employees whose salary is greater than their
manager salary?
SQL>SELECT E.ENAME FROM EMP ,EMP E WHERE EMP.EMPNO=E.MGR
AND EMP.SAL<E.SAL;
116) Display those department where no employee working?
SQL>select dname from emp,dept where emp.deptno not in(emp.deptno)
117) Display those employee whose salary is ODD value?
SQL>select * from emp where sal<0;
118) Display those employee whose salary contains alleast 3 digits?
SQL>select * from emp where length(sal)>=3;
119) Display those employee who joined in the company in the month of Dec?
SQL>select ename from emp where to_char(hiredate,'MON')='DEC';
120) Display those employees whose name contains "A"?
SQL>select ename from emp where instr(ename,'A')>0;
or
SQL>select ename from emp where ename like('%A%');
121) Display those employee whose deptno is available in salary?
SQL>select emp.ename from emp, emp e where emp.sal=e.deptno;
122) Display those employee whose first 2 characters from hiredate -last 2
characters of salary?
SQL>select ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp
123) Display those employee whose 10% of salary is equal to the year of
joining?
SQL>select ename from emp where to_char(hiredate,'YY')=sal*0.1;
124) Display those employee who are working in sales or research?
SQL>SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE
DNAME IN('SALES','RESEARCH'));
125) Display the grade of jones?
SQL>SELECT ENAME,GRADE FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL AND Ename='JONES';
126) Display those employees who joined the company before 15 of the month?
a)select ename from emp where to_char(hiredate,'DD')<15;
127) Display those employee who has joined before 15th of the month.
a)select ename from emp where to_char(hiredate,'DD')<15;
128) Delete those records where no of employees in a particular department
is less than 3.
SQL>delete from emp where deptno=(select deptno from emp
group by deptno having count(deptno)<3);
129) Display the name of the department where no employee working.
SQL> SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO
130) Display those employees who are working as manager.
SQL>SELECT M.ENAME MANAGER FROM EMP M ,EMP E
WHERE E.MGR=M.EMPNO GROUP BY M.ENAME
131) Display those employees whose grade is equal to any number of sal but
not equal to first number of sal?
SQL> SELECT ENAME,GRADE FROM EMP,SALGRADE
WHERE GRADE NOT IN(SELECT SUBSTR(SAL,0,1)FROM EMP)
132) Print the details of all the employees who are Sub-ordinate to BLAKE?
SQL>select emp.ename from emp, emp e where emp.mgr=e.empno and
e.ename='BLAKE';
133) Display employee name and his salary whose salary is greater than
highest average of department number?
SQL>SELECT SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP
GROUP BY DEPTNO);
134) Display the 10th record of emp table(without using rowid)
SQL>SELECT * FROM EMP WHERE ROWNUM<11
MINUS
SELECT * FROM EMP WHERE ROWNUM<10
135) Display the half of the ename's in upper case and remaining lowercase?
SQL>SELECT
SUBSTR(LOWER(ENAME),1,3)||SUBSTR(UPPER(ENAME),3,LENGTH(ENAME))
FROM EMP;
136) Display the 10th record of emp table without using group by and rowid?
SQL>SELECT * FROM EMP WHERE ROWNUM<11
MINUS
SELECT * FROM EMP WHERE ROWNUM<10
Delete the 10th record of emp table.
SQL>DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11
MINUS
SELECT EMPNO FROM EMP WHERE ROWNUM<10)
137) Create a copy of emp table;
SQL>create table new_table as select * from emp where 1=2;
138) Select ename if ename exists more than once.
SQL>select ename from emp e group by ename having count(*)>1;
139) Display all enames in reverse order?(SMITH:HTIMS).
SQL>SELECT REVERSE(ENAME)FROM EMP;
140) Display those employee whose joining of month and grade is equal.
SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN
(SELECT LOSAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,'MM')) AND
(SELECT HISAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,'MM'));
141) Display those employee whose joining DATE is available in deptno.
SQL>SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE,'DD')=DEPTNO
142) Display those employees name as follows
A ALLEN
B BLAKE
SQL> SELECT SUBSTR(ENAME,1,1),ENAME FROM EMP;
143) List out the employees ename,sal,PF(20% OF SAL) from emp;
SQL>SELECT ENAME,SAL,SAL*.2 AS PF FROM EMP;
144) Create table emp with only one column empno;
SQL>Create table emp as select empno from emp where 1=2;
145) Add this column to emp table ename vrachar2(20).
SQL>alter table emp add(ename varchar2(20));
146) Oops I forgot give the primary key constraint. Add in now.
SQL>alter table emp add primary key(empno);
147) Now increase the length of ename column to 30 characters.
SQL>alter table emp modify(ename varchar2(30));
148) Add salary column to emp table.
SQL>alter table emp add(sal number(10));
149) I want to give a validation saying that salary cannot be greater 10,000
(note give a name to this constraint)
SQL>alter table emp add constraint chk_001 check(sal<=10000)
150) For the time being I have decided that I will not impose this validation.My boss has agreed to pay more than 10,000.
SQL>again alter the table or drop constraint with alter table emp drop constraint chk_001 (or)Disable the constraint by using alter table emp modify constraint chk_001 disable;
151) My boss has changed his mind. Now he doesn't want to pay more than
10,000.so revoke that salary constraint.
SQL>alter table emp modify constraint chk_001 enable;
152) Add column called as mgr to your emp table;
SQL>alter table emp add(mgr number(5));
153) Oh! This column should be related to empno. Give a command to add this
constraint.
SQL>ALTER TABLE EMP ADD CONSTRAINT MGR_DEPT FOREIGN KEY(MGR) REFERENCES
EMP(EMPNO)
154) Add deptno column to your emp table;
SQL>alter table emp add(deptno number(5));
155) This deptno column should be related to deptno column of dept table;
SQL>alter table emp add constraint dept_001 foreign key(deptno)
reference dept(deptno)
[deptno should be primary key]
156) Give the command to add the constraint.
SQL>alter table <table_name) add constraint <constraint_name>
<constraint type>
157) Create table called as newemp. Using single command create this table
as well as get data into this table(use create table as);
SQL>create table newemp as select * from emp;
SQL>Create table called as newemp. This table should contain only
empno,ename,dname.
SQL>create table newemp as select empno,ename,dname from emp,dept where
1=2;
158) Delete the rows of employees who are working in the company for more
than 2 years.
SQL>delete from emp where (sysdate-hiredate)/365>2;
159) Provide a commission(10% Comm Of Sal) to employees who are not earning
any commission.
SQL>select sal*0.1 from emp where comm is null
160) If any employee has commission his commission should be incremented by
10% of his salary.
SQL>update emp set comm=sal*.1 where comm is not null;
161) Display employee name and department name for each employee.
SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno
162)Display employee number,name and location of the department in which he
is working.
SQL>select empno,ename,loc,dname from emp,dept where
emp.deptno=dept.deptno;
163) Display ename,dname even if there are no employees working in a
particular department(use outer join).
SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno(+)
164) Display employee name and his manager name.
SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;
165) Display the department name and total number of employees in each
department.
SQL>select dname,count(ename) from emp,dept where
emp.deptno=dept.deptno group by dname;
166)Display the department name along with total salary in each department.
SQL>select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno
group by dname;
167) Display itemname and total sales amount for each item.
SQL>select itemname,sum(amount) from item group by itemname;
168) Write a Query To Delete The Repeted Rows from emp table;
SQL>Delete from emp where rowid not in(select min(rowid)from emp group
by ename)
169) TO DISPLAY 5 TO 7 ROWS FROM A TABLE
SQL>select ename from emp
where rowid in(select rowid from emp where rownum<=7
minus
select rowid from empi where rownum<5)
170) DISPLAY TOP N ROWS FROM TABLE?
SQL>SELECT * FROM
(SELECT * FROM EMP ORDER BY ENAME DESC)
WHERE ROWNUM <10;
171) DISPLAY TOP 3 SALARIES FROM EMP;
SQL>SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC )
WHERE ROWNUM <4
172) DISPLAY 9th FROM THE EMP TABLE?
SQL>SELECT ENAME FROM EMP
WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10
MINUS
SELECT ROWID FROM EMP WHERE ROWNUM <10)
select second max salary from emp;
select max(sal) fromemp where sal<(select max(sal) from emp);
PL-SQL
What are the datatypes a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are : I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
Explain the two type of Cursors ?
There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.
What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;
What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor.
How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned boolean value.
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.
What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.
What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.
Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integrity Constraints.
What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined exceptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.
Where the Pre_defined_exceptions are stored ?
In the standard package.
What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
What are advantages fo Stored Procedures /
Extensibility, Modularity, Reusability, Maintainability and one time compilation.
Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;
Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');
What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
What is a database link ?
Database Link is a named path through which a remote database can be accessed.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any
out/in-out parameters. A function can not be called.
Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.
Difference between SUBSTR and INSTR ?
INSTR (String1,String2(n,(m)),
INSTR returns the position of the mth occurrence of the string 2 in
string1. The search begins from nth position of string1.
SUBSTR (String1 n,m)
SUBSTR returns a character string of size m in string1, starting from nth position of string1.
Explain Connect by Prior ?
Retrieves rows in hierarchical order.
e.g. select empno, ename from emp where.
What is correlated sub-query ?
Correlated sub_query is a sub_query which has reference to the main query.
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are : I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
Explain the two type of Cursors ?
There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.
What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;
What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor.
How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned boolean value.
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.
What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.
What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.
Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integrity Constraints.
What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined exceptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.
Where the Pre_defined_exceptions are stored ?
In the standard package.
What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
What are advantages fo Stored Procedures /
Extensibility, Modularity, Reusability, Maintainability and one time compilation.
Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;
Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');
What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
What is a database link ?
Database Link is a named path through which a remote database can be accessed.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any
out/in-out parameters. A function can not be called.
Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.
Difference between SUBSTR and INSTR ?
INSTR (String1,String2(n,(m)),
INSTR returns the position of the mth occurrence of the string 2 in
string1. The search begins from nth position of string1.
SUBSTR (String1 n,m)
SUBSTR returns a character string of size m in string1, starting from nth position of string1.
Explain Connect by Prior ?
Retrieves rows in hierarchical order.
e.g. select empno, ename from emp where.
What is correlated sub-query ?
Correlated sub_query is a sub_query which has reference to the main query.