146
SQL
QUERY BANK
1.
Query for retrieving N highest paid employees FROM
each Department. I
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. II
3.
Query for listing Deptno, ename, sal, SUM(sal in that
dept). III
4.
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. IV
5.
Nth Top Salary of all the employees. V
6.
Retrieving the Nth row FROM a table. VI
7.
Tree Query. VII
8.
Eliminate duplicates rows in a table. VIII
9.
Displaying EVERY Nth row in a table. IX
10.
Top N rows FROM a table. X
11.
COUNT/SUM RANGES of data values in a column. XI
12.
For equal size ranges it might be easier to calculate
it with DECODE(TRUNC(value/range),
0,
rate_0, 1, rate_1, ...). XII
13.
Count different data values in a column. XIII
14.
Query to get the product of all the values of a
column. XIV
15.
Query to display only the duplicate records in a
table. XV
16.
Query for getting the following output as many number
of rows in the table. XVI
17.
Function for getting the Balance Value. XVII
18.
Function for getting the Element Value. XVIII
19.
SELECT Query for counting No of words. XIX
20.
Function to check for a leap year. XX
21.
Query for removing all non-numeric. XXI
22.
Query for translating a column values to INITCAP. XXII
23.
Function for displaying Rupees in Words. XXIII
24.
Query for deleting alternate even rows FROM a table. XXIV
25.
Query for deleting alternate odd rows FROM a table. XXV
26.
Procedure for sending Email. XXVI
27.
Alternate Query for DECODE function. XXVII
28.
Create table adding Constraint to a date field to
SYSDATE or 3 months later. XXVIII
29.
To list all the suppliers who r supplying all the
parts supplied by supplier 'S2'. XXIX
30.
Query to get the last Sunday of any month. XXX
31.
Query to get all those who have no children
themselves. XXXI
32.
Query to SELECT last N rows FROM a table. XXXII
33.
SELECT with variables. XXXIII
147
34.
Query to get the DB Name. XXXIV
35.
Getting the current default schema. XXXV
36.
Query to get all the column names of a particular
table. XXXVI
37.
Spool only the query result to a file in SQLPLUS. XXXVII
38.
Query for getting the current SessionID. XXXVIII
39.
Query to display rows FROM m to n. XXXIX
40.
Query to count no. Of columns in a table. XXXX
41.
Procedure to increase the buffer length. XXXXI
42.
Inserting an & symbol in a Varchar2 column. XXXXII
43.
Create Query to restrict the user to a single row. XXXXIII
44.
Query to get the first inserted record FROM a table. XXXXIV
45.
Concatenate a column value with multiple rows. XXXXV
46.
Query to delete all the tables at once. XXXXVI
47.
SQL Query for getting Orphan Records. XXXXVII
48.
Removing Trailing blanks in a spooled file. XXXXVIII
49.
Samples for executing Dynamic SQL Statements. XXXXIX
50.
Differences between SQL and MS-Access. XXXXX
51.
Query to display all the children, sub children of a
parent. XXXXXI
52.
Procedure to read/write data from/to a text file. XXXXXII
53.
Query to display random number between any two given
numbers. XXXXXIII
54.
Time difference between two date columns. XXXXXIV
55.
Display the Nth Max Sal of the Employee where Salary
must be Distinct. XXXXXV
56.
Display the Current username, which you have logged
on. XXXXXVI
57.
Will this query work if it works then what will be
the output? XXXXXVII
58.
Display the Count that how many columns existing in
the Table? XXXXXVIII
148
I.
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;
II.
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;
III.
Query for listing Deptno, ename, sal, SUM(sal in that dept) :
SELECT
adaptor, 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
149
IV.
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 ;
V.
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;
VI.
Retrieving the 5th row FROM a table :
1.
SELECT DEPTNO, ENAME, SAL
FROM
EMP
WHERE
ROWID = (SELECT ROWID FROM EMP WHERE ROWNUM <= 5
MINUS
SELECT
ROWID FROM EMP WHERE ROWNUM < 5)
2.
SELECT * FROM EMP A
WHERE
&N = (SELECT COUNT(ROWID)
FROM
EMP B
WHERE
A.ROWID >= B.ROWID);
VII.
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';
150
VIII.
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);
IX.
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);
X.
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;
XI.
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;
XII.
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;
XIII.
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;
XIV.
Query to get the product of all the values of a column :
SELECT
EXP(SUM(LN(col1))) FROM bias_table;
151
XV.
Query to display only the duplicate records in a table:
SELECT
num
FROM
satyam
GROUP
BY num
HAVING
COUNT(*) > 1;
XVI.
Query for getting the following output as many number of rows in the
table
:
*
**
***
****
*****
SELECT
RPAD(DECODE(temp,temp,'*'),ROWNUM,'*')
FROM
bias_table1;
XVII.
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);
152
exception
WHEN
no_data_found THEN
RETURN
0;
END;
XVIII.
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;
XIX.
SELECT Query for counting No of words :
SELECT
ename,
153
NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTU
VWXYZ''
',' @'),' ',''))+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)),'ABCDEFGHIJKLMNOPQRSTUVWX
YZ''
',' @'),' ',''))+1 -- This will give u the count of @ characters in the
above
result.
XX.
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';
XXI.
Query for removing all non-numeric :
SELECT
TRANSLATE(LOWER(ssn),'abcdefghijklmnopqrstuvwxyz-
','')
FROM
DUAL;
XXII.
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
bias_table1;
XXIII.
Function for displaying Rupees in Words :
CREATE
OR REPLACE FUNCTION to_word_i
(amount
IN NUMBER)
RETURN
VARCHAR2
AS
v_length
INTEGER := 0;
v_num2
VARCHAR2 (50) := NULL;
v_amount
VARCHAR2 (50) := TO_CHAR (TRUNC (amount));
154
v_word
VARCHAR2 (4000) := NULL;
v_word1
VARCHAR2 (4000) := NULL;
TYPE
myarray IS TABLE OF VARCHAR2 (255);
v_str
myarray := myarray (' Thousand ',
'
Lakh ',
'
Crore ');
BEGIN
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 ( TO_CHAR ( amount, '999999999') , 'J'),
'JSP');
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
to_word_i;
XXIV.
Query for deleting alternate even rows FROM a table :
DELETE
FROM
bias_table
WHERE
(ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2)
FROM
bias_table);
XXV.
Query for deleting alternate odd rows FROM a table :
DELETE
FROM
bias_table
WHERE
(ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2)
FROM
bias_table);
155
XXVI.
Procedure for sending Email :
CREATE
OR REPLACE PROCEDURE Send_Mail
IS
sender
VARCHAR2(50) := 'sender@something.com';
recipient
VARCHAR2(50) := 'recipient@something.com';
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;
XXVII.
Alternate Query for DECODE function :
SELECT
case
WHEN
sex = 'm' THEN 'male'
WHEN
sex = 'f' THEN 'female'
ELSE
'unknown'
END
FROM
mytable;
XXVIII.
Create table adding Constraint to a date field to SYSDATE or 3 months
later:
CREATE
TABLE bias_table(dt1 date DEFAULT SYSDATE, dt2 date,
CONSTRAINT
check_dt2 CHECK ((dt2 >= dt1) AND (dt2 <=
ADD_MONTHS(SYSDATE,3)));
XXIX.
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
156
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
XXX.
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;
XXXI.
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;
XXXII.
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));
XXXIII.
SELECT with variables:
CREATE
OR REPLACE PROCEDURE disp
AS
xTableName
varchar2(25):='emp';
xFieldName
varchar2(25):='ename';
157
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;
XXXIV.
Query to get the DB Name:
SELECT
name FROM v$database;
XXXV.
Getting the current default schema :
SELECT
SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
XXXVI.
Query to get all the column names of a particular table :
SELECT
column_name
FROM
all_tab_columns
WHERE
TABLE_NAME = 'ORDERS';
XXXVII.
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:\chaitu.txt
SELECT
empno,ename FROM emp; /* Write your Query here */
spool
off
/
XXXVIII.
Query for getting the current SessionID :
SELECT
SYS_CONTEXT('USERENV','SESSIONID') Session_ID FROM DUAL;
XXXIX.
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);
158
OR
SELECT
ename
FROM
emp
GROUP
BY ROWNUM, ename
HAVING
ROWNUM > 1 and ROWNUM < 3;
XXXX.
Query to count no. Of columns in a table:
SELECT
COUNT(column_name)
FROM
user_tab_columns
WHERE
table_name = 'MYTABLE';
XXXXI.
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;
/
XXXXII.
Inserting an & symbol in a Varchar2 column :
Set
the following to some other character. By default it is &.
set
define '~'
XXXXIII.
Create Query to restrict the user to a single row :
CREATE
OR REPLACE TRIGGER TRIG_N
BEFORE
UPDATE OR DELETE ON EMP
FOR
EACH ROW
WHEN(OLD.EMPNO=7788)
BEGIN
RAISE_APPLICATION_ERROR('-20001'
,'CANT DO ANY OPERATION ON THIS ROW');
END;
SQL>
UPDATE EMP SET SAL=SAL+1 WHERE EMPNO=7788;
XXXXIV.
Query to get the first inserted record FROM a table :
SELECT
* FROM T_N WHERE ROWNUM=1;
XXXXV.
How to concatenate a column value with multiple rows :
159
XXXXVI.
Query to delete all the tables at once :
XXXXVII.
SQL Query for getting Orphan Records :
XXXXVIII.
How do you remove Trailing blanks in a spooled file :
Change
the Environment Options Like this :
set
trimspool on
set
trimout on
XXXXIX.
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;
160
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;
XXXXX.
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.
XXXXXI.
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'));
XXXXXII.
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;
161
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;
XXXXXIII.
Query to display random number between any two given numbers :
SELECT
DBMS_RANDOM.VALUE (1,2) FROM DUAL;
XXXXXIV.
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;
XXXXXV.
Display Nth MAX Sal of Employee where Salary must be Distinct:
SELECT
* FROM EMP WHERE SAL=(SELECT SAL FROM
(SELECT
DISTINCT(SAL) FROM
EMP
ORDER BY SAL DESC) HAVING ROWNUM=4
GROUP
BY ROWNUM, SAL)
XXXXXVI.
Display Current Username which you have logged in:
1.
SELECT USER FROM DUAL;
2.
SELECT S.USERNAME
FROM
V$PROCESS P,
V$SESSION
S
WHERE
P.ADDR = S.PADDR AND
S.AUDSID
= USERENV('SESSIONID')
XXXXXVII.
Will This Query Works and What will be the output:
1.
SELECT 'A ''WRONG'' word.' FROM DUAL;
2.
SELECT DISTINCT * FROM EMP;
3.
SELECT ROWID ROWNUM, ENAME, SAL FROM EMP;
XXXXXVIII.
Count the Columns in the table name:
SELECT
COUNT(*) FROM USER_TAB_COLUMNS
WHERE
TABLE_NAME LIKE 'EMP'
No comments:
Post a Comment