/*===============================================================
ADVANCED SQL FEATURES
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 09A β ORACLE ADVANCED SQL FEATURES
Topics Covered
--------------
β’ Oracle pseudocolumns
β’ ROWID
β’ ROWNUM
β’ SYSDATE / SYSTIMESTAMP
β’ USER / UID
β’ USERENV
β’ Oracle sequences
===============================================================*/
/*---------------------------------------------------------------
PSEUDOCOLUMN INTRODUCTION
---------------------------------------------------------------*/
/*
Pseudocolumns behave like table columns but are not
physically stored in the table.
Common pseudocolumns
ROWID
ROWNUM
LEVEL
SYSDATE
SYSTIMESTAMP
USER
UID
*/
/*---------------------------------------------------------------
QUESTION 1
Display ROWID of employees
---------------------------------------------------------------*/
SELECT
employee_id,
ROWID
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 2
Display employee ROWID and name
---------------------------------------------------------------*/
SELECT
ROWID,
employee_id,
first_name
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 3
Use ROWID to fetch specific row
---------------------------------------------------------------*/
SELECT *
FROM hr.employees
WHERE ROWID =
(
SELECT ROWID
FROM hr.employees
WHERE employee_id = 100
);
/*---------------------------------------------------------------
ROWNUM
---------------------------------------------------------------*/
/*
ROWNUM assigns sequential numbers to rows returned.
*/
/*---------------------------------------------------------------
QUESTION 4
Display first 5 employees
---------------------------------------------------------------*/
SELECT *
FROM hr.employees
WHERE ROWNUM <= 5;
/*---------------------------------------------------------------
QUESTION 5
Display first 10 employees
---------------------------------------------------------------*/
SELECT *
FROM hr.employees
WHERE ROWNUM <= 10;
/*---------------------------------------------------------------
QUESTION 6
Top salaries using ROWNUM
---------------------------------------------------------------*/
SELECT *
FROM
(
SELECT employee_id, salary
FROM hr.employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 5;
/*---------------------------------------------------------------
DATE PSEUDOCOLUMNS
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 7
Display current system date
---------------------------------------------------------------*/
SELECT SYSDATE
FROM dual;
/*---------------------------------------------------------------
QUESTION 8
Display system timestamp
---------------------------------------------------------------*/
SELECT SYSTIMESTAMP
FROM dual;
/*---------------------------------------------------------------
QUESTION 9
Display employees hired before today
---------------------------------------------------------------*/
SELECT employee_id, hire_date
FROM hr.employees
WHERE hire_date < SYSDATE;
/*---------------------------------------------------------------
QUESTION 10
Employees hired in last 1 year
---------------------------------------------------------------*/
SELECT employee_id, hire_date
FROM hr.employees
WHERE hire_date >= ADD_MONTHS(SYSDATE,-12);
/*---------------------------------------------------------------
USER INFORMATION
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 11
Display current database user
---------------------------------------------------------------*/
SELECT USER
FROM dual;
/*---------------------------------------------------------------
QUESTION 12
Display current user id
---------------------------------------------------------------*/
SELECT UID
FROM dual;
/*---------------------------------------------------------------
QUESTION 13
Display session id
---------------------------------------------------------------*/
SELECT USERENV('SESSIONID')
FROM dual;
/*---------------------------------------------------------------
QUESTION 14
Display session language
---------------------------------------------------------------*/
SELECT USERENV('LANGUAGE')
FROM dual;
/*---------------------------------------------------------------
QUESTION 15
Display terminal name
---------------------------------------------------------------*/
SELECT USERENV('TERMINAL')
FROM dual;
/*---------------------------------------------------------------
SEQUENCES
---------------------------------------------------------------*/
/*
Sequences generate unique numbers.
*/
/*---------------------------------------------------------------
QUESTION 16
Create employee sequence
---------------------------------------------------------------*/
CREATE SEQUENCE emp_seq
START WITH 1001
INCREMENT BY 1;
/*---------------------------------------------------------------
QUESTION 17
Generate next sequence value
---------------------------------------------------------------*/
SELECT emp_seq.NEXTVAL
FROM dual;
/*---------------------------------------------------------------
QUESTION 18
Generate multiple sequence values
---------------------------------------------------------------*/
SELECT emp_seq.NEXTVAL
FROM dual
CONNECT BY LEVEL <= 5;
/*---------------------------------------------------------------
QUESTION 19
Display current sequence value
---------------------------------------------------------------*/
SELECT emp_seq.CURRVAL
FROM dual;
/*---------------------------------------------------------------
QUESTION 20
Use sequence for insert example
---------------------------------------------------------------*/
CREATE TABLE employee_sequence_test
(
emp_id NUMBER,
emp_name VARCHAR2(50)
);
INSERT INTO employee_sequence_test
VALUES
(
emp_seq.NEXTVAL,
'Test Employee'
);
/*---------------------------------------------------------------
QUESTION 21
View inserted data
---------------------------------------------------------------*/
SELECT *
FROM employee_sequence_test;
/*---------------------------------------------------------------
QUESTION 22
Insert multiple employees using sequence
---------------------------------------------------------------*/
INSERT INTO employee_sequence_test
SELECT emp_seq.NEXTVAL, first_name
FROM hr.employees
WHERE ROWNUM <= 5;
/*---------------------------------------------------------------
QUESTION 23
Check inserted rows
---------------------------------------------------------------*/
SELECT *
FROM employee_sequence_test;
/*---------------------------------------------------------------
QUESTION 24
Drop test table
---------------------------------------------------------------*/
DROP TABLE employee_sequence_test;
/*---------------------------------------------------------------
QUESTION 25
Drop sequence
---------------------------------------------------------------*/
DROP SEQUENCE emp_seq;
/*---------------------------------------------------------------
END OF PART 09A
---------------------------------------------------------------*/
/*===============================================================
PART 09B β OBJECT TYPES AND TABLE OPERATORS
Continuation of PART 09A
Topics Covered
--------------
β’ Object Types
β’ Nested Tables
β’ Collections
β’ TABLE() operator
β’ Pipelined functions
β’ Advanced SQL usage
===============================================================*/
/*---------------------------------------------------------------
OBJECT TYPES
---------------------------------------------------------------*/
/*
Oracle object types allow structured data
similar to classes in object-oriented programming.
*/
/*---------------------------------------------------------------
QUESTION 26
Create employee object type
---------------------------------------------------------------*/
CREATE OR REPLACE TYPE emp_obj AS OBJECT
(
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department_id NUMBER,
salary NUMBER
);
/*---------------------------------------------------------------
QUESTION 27
Create nested table type
---------------------------------------------------------------*/
CREATE OR REPLACE TYPE emp_table AS TABLE OF emp_obj;
/*---------------------------------------------------------------
COLLECTION EXAMPLE
---------------------------------------------------------------*/
/*
Collections store multiple rows
as a single object.
*/
/*---------------------------------------------------------------
QUESTION 28
Create function returning collection
---------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION get_employees_by_dept
(
p_dept_id NUMBER
)
RETURN emp_table PIPELINED
IS
BEGIN
FOR rec IN
(
SELECT employee_id,
first_name,
last_name,
department_id,
salary
FROM hr.employees
WHERE department_id = p_dept_id
)
LOOP
PIPE ROW
(
emp_obj
(
rec.employee_id,
rec.first_name,
rec.last_name,
rec.department_id,
rec.salary
)
);
END LOOP;
RETURN;
END;
/
/*---------------------------------------------------------------
TABLE() OPERATOR
---------------------------------------------------------------*/
/*
TABLE() converts collection to relational rows.
*/
/*---------------------------------------------------------------
QUESTION 29
Query pipelined function
---------------------------------------------------------------*/
SELECT *
FROM TABLE(get_employees_by_dept(50));
/*---------------------------------------------------------------
QUESTION 30
Employees from department 60
---------------------------------------------------------------*/
SELECT *
FROM TABLE(get_employees_by_dept(60));
/*---------------------------------------------------------------
QUESTION 31
Employees from department 90
---------------------------------------------------------------*/
SELECT *
FROM TABLE(get_employees_by_dept(90));
/*---------------------------------------------------------------
JOIN WITH TABLE FUNCTION
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 32
Join pipelined function with departments
---------------------------------------------------------------*/
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM TABLE(get_employees_by_dept(50)) e
JOIN hr.departments d
ON e.department_id = d.department_id;
/*---------------------------------------------------------------
QUESTION 33
Join pipelined function with jobs
---------------------------------------------------------------*/
SELECT
e.employee_id,
e.first_name,
j.job_title
FROM TABLE(get_employees_by_dept(60)) e
JOIN hr.jobs j
ON e.employee_id =
(
SELECT employee_id
FROM hr.employees
WHERE job_id = j.job_id
AND ROWNUM = 1
);
/*---------------------------------------------------------------
ADVANCED COLLECTION USAGE
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 34
Create collection variable
---------------------------------------------------------------*/
DECLARE
emp_list emp_table;
BEGIN
SELECT emp_obj(employee_id,
first_name,
last_name,
department_id,
salary)
BULK COLLECT INTO emp_list
FROM hr.employees
WHERE department_id = 50;
END;
/
/*---------------------------------------------------------------
QUESTION 35
Bulk collect employees
---------------------------------------------------------------*/
DECLARE
emp_list emp_table;
BEGIN
SELECT emp_obj(employee_id,
first_name,
last_name,
department_id,
salary)
BULK COLLECT INTO emp_list
FROM hr.employees
WHERE salary > 10000;
END;
/
/*---------------------------------------------------------------
QUESTION 36
Create nested table variable
---------------------------------------------------------------*/
DECLARE
emp_list emp_table := emp_table();
BEGIN
emp_list.EXTEND;
emp_list(1) :=
emp_obj(1000,'Sample','Employee',10,5000);
END;
/
/*---------------------------------------------------------------
QUESTION 37
Query collection elements
---------------------------------------------------------------*/
SELECT *
FROM TABLE(get_employees_by_dept(70));
/*---------------------------------------------------------------
ADVANCED SQL EXAMPLES
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 38
Department employee summary
---------------------------------------------------------------*/
SELECT
department_id,
COUNT(*) employee_count,
AVG(salary) avg_salary
FROM TABLE(get_employees_by_dept(50))
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 39
Highest salary from pipelined function
---------------------------------------------------------------*/
SELECT MAX(salary)
FROM TABLE(get_employees_by_dept(90));
/*---------------------------------------------------------------
QUESTION 40
Employee ranking within function result
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
RANK() OVER (ORDER BY salary DESC)
FROM TABLE(get_employees_by_dept(60));
/*---------------------------------------------------------------
QUESTION 41
Employee salary statistics
---------------------------------------------------------------*/
SELECT
AVG(salary),
MAX(salary),
MIN(salary)
FROM TABLE(get_employees_by_dept(50));
/*---------------------------------------------------------------
QUESTION 42
Department salary totals
---------------------------------------------------------------*/
SELECT
department_id,
SUM(salary)
FROM TABLE(get_employees_by_dept(60))
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 43
Employee salary ranking
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
ROW_NUMBER() OVER
(
ORDER BY salary DESC
)
FROM TABLE(get_employees_by_dept(90));
/*---------------------------------------------------------------
QUESTION 44
List employees ordered by salary
---------------------------------------------------------------*/
SELECT *
FROM TABLE(get_employees_by_dept(50))
ORDER BY salary DESC;
/*---------------------------------------------------------------
QUESTION 45
Employees above average salary
---------------------------------------------------------------*/
SELECT *
FROM TABLE(get_employees_by_dept(60))
WHERE salary >
(
SELECT AVG(salary)
FROM TABLE(get_employees_by_dept(60))
);
/*---------------------------------------------------------------
QUESTION 46
Employees below average salary
---------------------------------------------------------------*/
SELECT *
FROM TABLE(get_employees_by_dept(60))
WHERE salary <
(
SELECT AVG(salary)
FROM TABLE(get_employees_by_dept(60))
);
/*---------------------------------------------------------------
OBJECT TYPE CLEANUP
---------------------------------------------------------------*/
/*
Optional cleanup commands
*/
/*
DROP FUNCTION get_employees_by_dept;
DROP TYPE emp_table;
DROP TYPE emp_obj;
*/
/*---------------------------------------------------------------
END OF PART 09
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express
or implied warranties, including but not limited to warranties of accuracy, completeness,
reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges
and agrees that they do so at their own risk. The author shall not be held liable for any
direct, indirect, incidental, consequential, special, or exemplary damages, including but
not limited to loss of data, loss of profits, system failures, security breaches, or any
other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries,
and configurations in a controlled, non-production (test) environment prior to deploying
them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials
complies with all applicable local, national, and international laws, regulations,
and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/