1) Difference between union and union all in oracle ?
Answer –> Union all does not remove duplicates
— Union removes duplicate records
Both queries should have same columns.
Number of columns in each UNION query must match
Data types must match.
For large data set queries UNION might have performance issues. So use it very carefully.
Link –> http://sqlandplsql.com/2012/06/22/difference-between-union-and-union-all-clause-oracle/
***********************************************************************************************************
2) What is Subquery in Oracle?
Answer –> http://www.techonthenet.com/oracle/subqueries.php
Subquery can be used in 3 location –>
— Where Clause —
SELECT *
FROM all_tables tabs
WHERE tabs.table_name IN (SELECT cols.table_name
FROM all_tab_columns cols
WHERE cols.column_name = ‘SUPPLIER_ID’);
** Limitation: Oracle allows up to 255 levels of subqueries in the WHERE clause.
— From –it is known as Inline views
SELECT suppliers.name, subquery1.total_amt
FROM suppliers,
(SELECT supplier_id, SUM(orders.amount) AS total_amt
FROM orders
GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;
** Oracle allows an unlimited number of subqueries in the FROM clause.
—SELECT CLAUSE–
SELECT tbls.owner, tbls.table_name,
(SELECT COUNT(column_name) AS total_columns
FROM all_tab_columns cols
WHERE cols.owner = tbls.owner
AND cols.table_name = tbls.table_name) subquery2
FROM all_tables tbls;
The trick to placing a subquery in the select clause is that the subquery must return a single value.
This is why an aggregate function such as SUM function, COUNT function, MIN function, or MAX function is commonly used in the subquery.
**********************************************************************************************************************************************
3) Usage of INSTR and SUBSTR in oracle?
select INSTR(‘1234-abc’,’-‘) from dual; –will find postion, for eg. in this case it will give 5
select SUBSTR(‘1234-abc’,1,5-1) from dual ; — over here 5 -1 is the position of separator -1 ;
**********************************************************************************************************************************************
— Not answered Completely
4) USage of regexp_substr to split single row into multiple columns?
select regexp_substr(‘111*222*333’, ‘[^*]+’, 1, level) str
from dual
connect by level <= length(‘111*222*333’)-length(replace(‘111*222*333′,’*’))+1
— http://www.oracle-developer.net/display.php?id=508
**********************************************************************************************************************************************
5) Use of Execute Immediate Statement –>
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement .
DECLARE
sql_stmt VARCHAR2(200);
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’;
—–
In a PL/SQL program, you can invoke static SQL without any special notation. What is static SQL ? Static SQL is :
Data Manipulation Language (DML) Statements (except EXPLAIN PLAN) : INSERT, UPDATE, DELETE
Transaction Control Language (TCL) Statements : COMMIT, ROLLBACK…
SQL Functions
SQL Pseudocolumns : ROWID, ROWNUM…
SQL Operators
As you can see, CREATE statements are not static SQL. To invoke them, you have to use dynamic SQL, via EXECUTE IMMEDIATE.
———————————
**********************************************************************************************************************************************
6) what are the compound symbols?
–Answer:
:= assignment operator
.. range operator
|| concatenation operator
— single-line comment indicator
**********************************************************************************************************************************************
7) To DEbug and Test PL / SQL package –>
set serveroutput on
Begin
dbms_output.put_line(‘Hello Tester’);
End;
/
**********************************************************************************************************************************************
8) HOW TO SAVE YOUR QUERY AND RESULT IN THE TEXT FILE(working in SQLplus and SQL Developer):
SPOOL C:\TEST_FILE.TXT
—QUERY
SPOOL OFF
**********************************************************************************************************************************************
9) Global temporary tables –>
********************************************************************************************************************************************
10)-– COALESCE Function
The COALESCE function returns first not null expression among the arguments. Minimum 2 arguments required.
If all expressions are null then it returns null.
Syntax:-
COALESCE (expr_1,expr_2,…expr_n)
Examples :-
1. select COALESCE(null,null,null,10) from dual;
would return 10.
2. select COALESCE(null,null,null) from dual;
would return null.
**********************************************************************************************************************************************
—Why procedure can’t be used in SQL statement, whereas Function can be used ?
Answer ==
1) This is because Function must return a value, while procedure may or may not.
2) Also function can be used as an assignment statement, while Procedure can never be used as an assignment.
Function can be used as assignmnet statement.
— http://www.club-oracle.com/threads/why-procedure-can-not-be-used-in-select-query.3193/
Oracle Apps HRMS 