Complex Query (Interview Questions)
1. To find the Nth Maximum Salary.
SELECT DISTINCT SAL FROM EMP A WHERE &N=(SELECT
COUNT (DISTINCT [Link]) FROM EMP B WHERE [Link]<=[Link]);
2. To find the no. of columns for a particular table.
SELECT COUNT (COLUMN_NAME) FROM
USER_TAB_COLUMNS WHERE TABLE_NAME = 'DEPT'
3. To use the Exists Clause.
SELECT DNAME, DEPTNO FROM DEPT WHERE EXISTS (SELECT
* FROM EMP WHERE [Link] = [Link])
4. To Find The Non-Null Column Alone In A Table.
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE
NULLABLE = 'N' AND TABLE_NAME = 'COUNTRY'
5. To delete The Duplicate Rows Alone in A Table.
DELETE DEPT WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM DEPT GROUP BY DEPTNO HAVING COUNT (*) >=1)
6. To find The Max Salary without the MAX Function.
1. SELECT DISTINCT SAL FROM EMP1 WHERE SAL NOT IN
(SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL
FROM EMP1))
2. SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL
FROM EMP)
7. Alternative for DESC.
SELECT COLUMN_NAME NAME, DECODE (NULLABLE,'N','NOT
NULL','Y',' ')
"NULL", CONCAT (DATA_TYPE, DATA_LENGTH) TYPE FROM
USER_TAB_COLUMNSWHERE TABLE_NAME = 'DEPT'
8. SQL> Example for startwith, connect by and prior
SELECT ENAME, JOB, LEVEL, EMPNO, MGR FROM EMP111
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME = 'RAJA'
SELECT EMPNO, LPAD (‘ ‘, 6*(LEVEL – 1)) || ENAME “EMPLOYEE
NAME” FROM EMP START WITH ENAME=’KING’ CONNECT BY PRIOR
EMPNO = MGR
9. To find the database name
SELECT * FROM GLOBAL_NAME;
10. To convert the given no to word
SELECT TO_CHAR (TO_DATE (&NUM,'J'),'JSP') FROM DUAL;
11. To reverse
12. How can I eliminate duplicate values in a table?
Choose one of the following queries to identify or remove duplicate rows from
a table leaving one record:
Method 1:
DELETE FROM table_name A WHERE ROWID > (SELECT min (rowid)
FROM table_name B
WHERE A.key_values = B.key_values); Method 2:
SQL> create table table_name2 as select distinct * from table_name1; SQL>
drop table_name1;
SQL> rename table_name2 to table_name1; Method 3: (thanks to Kenneth R
Vanluvanee)
SQL> Delete from my_table where rowid not in (select max (rowid) from
my_table group by my_column_name);
Method 4: (thanks to Dennis Gurnick)
SQL> delete from my_table t1 where exists (select 'x' from my_table t2 where
t2.key_value1 = t1.key_value1
And t2.key_value2 = t1.key_value2and [Link] > [Link]);
Note: If you create an index on the joined fields in the inner loop, you for all
intensive purposes eliminate N^2 operations (no need to loop through the entire
table on each pass by a record).
13. How can I generate primary key values for my table?
Create your table with a NOT NULL column (say SEQNO). This column can
now be populated with unique values:
SQL> UPDATE table_name SET seqno = ROWNUM; Or use a sequence
generator:
SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT
BY 1;
SQL> UPDATE table_name SET seqno = sequence_name. NEXTVAL; Finally,
create a unique index on this column.
14. 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...
15. How does one count different data values in a column?
Select dept, sum (decode (sex,'M', 1,0)) MALE, sum (decode (sex,'F', 1,0))
FEMALE, count (decode (sex,'M', 1,'F', 1)) TOTAL from my_emp_table group
by dept;
16. 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).
Look at this example:
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;
For equal size ranges it might be easier to calculate it with DECODE (TRUNC
(value/range), 0, rate_0, 1, rate_1,).
E.g.
Select ename "Name", sal "Salary", decode (trunc (f2/1000, 0), 0, 0.0,1, 0.1, 2,
0.2, 3, 0.31) "Tax
rate"
From my_table;
17. Can one only retrieve the Nth row from a table?
Ravi Pachalla provided this solution:
SELECT f1 FROM t1 WHERE rowid = (SELECT rowid FROM t1 WHERE
rownum <= 10 MINUS
SELECT rowid FROM t1 WHERE rownum < 10);
18. Can one only retrieve rows X to Y from a table?
To display rows 5 to 7, construct a query like this:
SELECT * FROM tableX WHERE rowid in (SELECT rowid FROM tableX
WHERE rownum <= 7 MINUS
SELECT rowid FROM tableX WHERE rownum < 5);
19. How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries
like this: Method 1: Using a subquery
SELECT *FROM EMP WHERE (ROWID, 0) IN (SELECT ROWID, MOD
(ROWNUM, 4) FROM EMP);
Method 2: Use dynamic views (available from Oracle7.2):
SELECT * FROM (SELECT rownum rn, empno, ename FROM EMP) temp
WHERE MOD (temp. ROWNUM, 4) = 0;
20. How does one select the TOP N rows from a table?
SELECT * FROM my_table a WHERE 10 >= (SELECT COUNT (DISTINCT
maxcol) FROM
my_table b
WHERE [Link] >= [Link]) ORDER BY maxcol DESC;
21. How does one code a tree-structured query?
This is definitely non-relational (enough to kill Codd and then make him roll in
his grave) and is a feature I have not seen in the competition.
The definitive example is in the example SCOTT/TIGER database, when
looking at the EMP table (EMPNO and MGR columns). The MGR column
contains the employee number of the "current" employee's boss.
You have available an extra pseudo-column, LEVEL, that says how deep in the
tree you are. Oracle can handle queries with a depth up to 255.
Select LEVEL, EMPNO, ENAME, MGR from EMP connect by prior EMPNO
= MGR start with MGR is NULL;
You can get an "indented" report by using the level number to sub-string or lpad
a series of spaces and
Concatenate that to the string.
Select lpad (' ', LEVEL * 2) || ENAME...
You use the start with clause to specify the start of the tree(s). More than one
record can match the starting condition. One disadvantage of a "connect by
prior" is that you cannot perform a join to other tables. Still, I have not managed
to see anything else like the "connect by prior" in the other vendor offerings and
I like trees. Even trying to doing this programmatic ally in embedded SQL is
difficult as you have to do the top level query, for each of them open a cursor to
look for child nodes, for each of these open a cursor Pretty soon you blow the
cursor limit for your installation.
The way around this is to use PL/SQL, open the driving cursor with the
"connect by prior" statement, and the select matching records from other tables
on a row-by-row basis, inserting the results into a temporary table for later
retrieval.
22. How to implement if-then-else in a select statement?
The Oracle decode function acts like a procedural statement inside an SQL
statement to return different values or columns based on the values of other
columns in the select statement.
Some examples:
Select decode (sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees; Select
a, b, decode( abs (a-b), a-b, 'a > b',0, 'a = b','a < b') from tableX;
Select decode (GREATEST (A, B), A, 'A is greater than B', 'B is greater than
A')...
Note: The decode function is not ANSI SQL and are rarely implemented in
other RDBMS offerings. It is one of the good things about Oracle, but use it
sparingly if portability is required.
23. How can one dump/ examine the exact content of a database column?
SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1; DUMP (COL1)
Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the
column is 32, which is the ASCII code for a space. This tells us that this column
is blank-padded.
24. Can one drop a column from a table?
Oracle does not provide a way to DROP a column (reference: Enhancement
Request 51118). However, Joseph S. Testa wrote a DROP COLUMN package
that can be downloaded from
[Link] Apparently Oracle 8.1.X will have an
"ALTER TABLE table_name DROP COLUMN column_name" command.
Other workarounds:
[Link] t1 set column_to_drop = NULL; Rename t1 to t1_base;
Create view t1 as select <specific columns> from t1_base;
[Link] table t2 as select <specific columns> from t1; Drop table t1;
Rename t2 to t1;
25. Can one rename a column in a table?
No, this is listed as Enhancement Request 163519. Workarounds:
1. Rename t1 to t1_base;
Create view t1 <column list with new name> as select * from t1_base;
[Link] table t2 <column list with new name> as select * from t1; Drop table t1;
Rename t2 to t1;
26. How can I change my Oracle password?
Issue the following SQL command:
ALTER USER <username> IDENTIFIED BY <new_password>
27. Sending Messages to Different Session
Declare
Begin
a integer;
b integer;
a := dbms_pipe.create_pipe('kumaran'); dbms_pipe.pack_message('kumaran
software is a good company'); b := dbms_pipe.send_message('kumaran');
if b = 0 then
dbms_output.put_line('successfully send');
else
dbms_output.put_line('not send');
end if;
end;
28. Receiving Messages At Different Session
declare
begin
a integer;
b varchar2(30);
a := dbms_pipe.receive_message('kumaran'); dbms_pipe.unpack_message(b);
if a = 0 then
dbms_output.put_line('successfully received'); dbms_output.put_line(b);
else
dbms_output.put_line('not received');
end if;
end;
29. Overloading Concept
create or replace package pw1 as procedure pp1(a char);
procedure pp1(a char,b number); end pw1;
create or replace package body pw1 as procedure pp1(a char) is
begin
dbms_output.put_line(a);
end;
procedure pp1(a char,b number) is begin
dbms_output.put_line(a||to_char(b) );
end;
end pw1;
30. Restriction Concept
Only local or packaged subprograms can be overloaded. Therefore, you cannot
overload standalone subprograms. Also, you cannot overload two subprograms
if their formal parameters
differ only in name or parameter mode. For example, you cannot overload the
following
PROCEDURE reconcile (acctno IN INTEGER) IS BEGIN ... END;
PROCEDURE reconcile (acctn out INTEGER) IS BEGIN ...END;
Finally, you cannot overload two functions that differ only in return type (the
datatype of the result value) even if the types are in different families. For
example, you cannot overload the following functions:
FUNCTION acct_ok (acct_id INTEGER) RETURN BOOLEAN IS BEGIN ...
END;
FUNCTION acct_ok (acct_id INTEGER) RETURN INTEGER IS BEGIN ...
END;