1-20: Basic View Creation and Usage
-- 1. Create a simple view
CREATE OR REPLACE VIEW emp_view AS
SELECT emp_id, emp_name, salary FROM employees;
-- 2. Select from a view
SELECT * FROM emp_view;
-- 3. Create a view with join
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 4. View with WHERE condition
CREATE OR REPLACE VIEW high_salary_emp AS
SELECT emp_name, salary FROM employees WHERE salary > 100000;
-- 5. Create a view with aggregation
CREATE OR REPLACE VIEW dept_salary_summary AS
SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;
-- 6. Create a view with DISTINCT
CREATE OR REPLACE VIEW unique_departments AS
SELECT DISTINCT department_id FROM employees;
-- 7. View using ORDER BY (allowed only in subquery, not in direct view)
CREATE OR REPLACE VIEW emp_ordered_view AS
SELECT * FROM employees ORDER BY salary DESC;
-- 8. Create a view on a subset of columns
CREATE OR REPLACE VIEW emp_basic_info AS
SELECT emp_id, emp_name, hire_date FROM employees;
-- 9. Create a view with calculation
CREATE OR REPLACE VIEW emp_bonus_view AS
SELECT emp_id, salary, salary * 0.10 AS bonus FROM employees;
-- 10. Create a view with case statement
CREATE OR REPLACE VIEW emp_grade_view AS
SELECT emp_name,
CASE
WHEN salary > 100000 THEN 'A'
WHEN salary > 70000 THEN 'B'
ELSE 'C'
END AS grade
FROM employees;
-- 11. Drop a view
DROP VIEW emp_view;
-- 12. Create a view with aliasing columns
CREATE OR REPLACE VIEW emp_alias_view AS
SELECT emp_name AS employee_name, salary AS employee_salary FROM employees;
-- 13. Create a view with concatenation
CREATE OR REPLACE VIEW emp_fullname_view AS
SELECT emp_id, first_name || ' ' || last_name AS full_name FROM employees;
-- 14. Create a view using subquery
CREATE OR REPLACE VIEW emp_dept_count_view AS
SELECT e.emp_name,
(SELECT COUNT(*) FROM employees WHERE department_id = e.department_id) AS dept_emp_count
FROM employees e;
-- 15. Create a view with NVL for null values
CREATE OR REPLACE VIEW emp_nvl_view AS
SELECT emp_name, NVL(commission_pct, 0) AS commission FROM employees;
-- 16. Create a view with ROWNUM filter
CREATE OR REPLACE VIEW top_10_emps AS
SELECT * FROM employees WHERE ROWNUM <= 10;
-- 17. Create a view with date functions
CREATE OR REPLACE VIEW emp_tenure_view AS
SELECT emp_name, SYSDATE - hire_date AS days_worked FROM employees;
-- 18. Create a view with UNION inside
CREATE OR REPLACE VIEW all_emps AS
SELECT emp_id, emp_name FROM employees
UNION
SELECT contractor_id, contractor_name FROM contractors;
-- 19. Create a view with DISTINCT and join
CREATE OR REPLACE VIEW distinct_dept_emps AS
SELECT DISTINCT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 20. Create a view with inline comments
CREATE OR REPLACE VIEW emp_view_comments AS
SELECT emp_id, /* employee id */ emp_name, /* employee name */ salary FROM employees;
________________________________________
21-40: Updatable Views and DML on Views
-- 21. Create a simple updatable view
CREATE OR REPLACE VIEW updatable_emp_view AS
SELECT emp_id, emp_name, salary FROM employees;
-- 22. Insert via view
INSERT INTO updatable_emp_view (emp_id, emp_name, salary) VALUES (101, 'John Doe', 90000);
-- 23. Update via view
UPDATE updatable_emp_view SET salary = salary * 1.1 WHERE emp_id = 101;
-- 24. Delete via view
DELETE FROM updatable_emp_view WHERE emp_id = 101;
-- 25. Create a non-updatable view (using aggregation)
CREATE OR REPLACE VIEW agg_salary_view AS
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
-- 26. Attempt update on non-updatable view (will error)
UPDATE agg_salary_view SET avg_salary = 50000 WHERE department_id = 10;
-- 27. Create an updatable view with INSTEAD OF trigger (for complex view)
CREATE OR REPLACE VIEW complex_emp_view AS
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e JOIN departments d ON e.department_id = d.dept_id;
-- 28. Create INSTEAD OF trigger for the view to enable DML
CREATE OR REPLACE TRIGGER complex_emp_view_io
INSTEAD OF INSERT ON complex_emp_view
FOR EACH ROW
BEGIN
INSERT INTO employees(emp_id, emp_name, department_id)
VALUES (:NEW.emp_id, :NEW.emp_name,
(SELECT dept_id FROM departments WHERE dept_name = :NEW.dept_name));
END;
/
-- 29. Create a view with check option (to enforce WHERE clause)
CREATE OR REPLACE VIEW high_salary_view AS
SELECT * FROM employees WHERE salary > 100000
WITH CHECK OPTION;
-- 30. Attempt to insert a row violating the check option (error)
INSERT INTO high_salary_view (emp_id, emp_name, salary) VALUES (105, 'Low Paid', 50000);
-- 31. Create a view that hides some columns
CREATE OR REPLACE VIEW emp_public_view AS
SELECT emp_id, emp_name FROM employees;
-- 32. Grant select on view but not underlying table
GRANT SELECT ON emp_public_view TO hr_user;
-- 33. Create a view with synonyms for easier access
CREATE OR REPLACE SYNONYM emp_view_syn FOR emp_public_view;
-- 34. Create a view with explicit column names different from base table
CREATE OR REPLACE VIEW emp_custom_view(emp_no, name, pay) AS
SELECT emp_id, emp_name, salary FROM employees;
-- 35. Create a view with CASE statement and DML support
CREATE OR REPLACE VIEW emp_status_view AS
SELECT emp_id, emp_name,
CASE WHEN salary > 100000 THEN 'High' ELSE 'Low' END AS salary_status
FROM employees;
-- 36. Create a view with a function call
CREATE OR REPLACE VIEW emp_bonus_calc AS
SELECT emp_id, emp_name, calculate_bonus(salary) AS bonus FROM employees;
-- 37. Use WITH CHECK OPTION on join view (non-updatable)
CREATE OR REPLACE VIEW emp_dept_check AS
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e JOIN departments d ON e.department_id = d.dept_id
WHERE d.dept_name = 'IT'
WITH CHECK OPTION;
-- 38. Create a view to mask sensitive data
CREATE OR REPLACE VIEW emp_mask_view AS
SELECT emp_id, emp_name, '****' AS salary_masked FROM employees;
-- 39. Create a read-only view to hide salary column
CREATE OR REPLACE VIEW emp_readonly_view AS
SELECT emp_id, emp_name FROM employees;
-- 40. Create a view that restricts rows using RLS-like filter
CREATE OR REPLACE VIEW emp_region_view AS
SELECT * FROM employees WHERE region_id = USER_REGION;
________________________________________
41-60: Materialized Views and Performance
-- 41. Create a basic materialized view
CREATE MATERIALIZED VIEW mv_emp_dept AS
SELECT e.emp_name, d.dept_name
FROM employees e JOIN departments d ON e.department_id = d.dept_id;
-- 42. Refresh materialized view on demand
EXEC DBMS_MVIEW.REFRESH('mv_emp_dept');
-- 43. Create materialized view with fast refresh
CREATE MATERIALIZED VIEW mv_dept_summary
REFRESH FAST ON COMMIT AS
SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY department_id;
-- 44. Create materialized view log (needed for fast refresh)
CREATE MATERIALIZED VIEW LOG ON employees WITH ROWID, SEQUENCE (department_id);
-- 45. Create materialized view with complete refresh
CREATE MATERIALIZED VIEW mv_emp_complete
REFRESH COMPLETE
AS SELECT * FROM employees;
-- 46. Create materialized view with query rewrite enabled
CREATE MATERIALIZED VIEW mv_emp_rewrite
ENABLE QUERY REWRITE AS
SELECT emp_name, salary FROM employees WHERE salary > 50000;
-- 47. Query that uses materialized view rewrite (if enabled)
SELECT emp_name, salary FROM employees WHERE salary > 50000;
-- 48. Drop materialized view
DROP MATERIALIZED VIEW mv_emp_dept;
-- 49. Create a materialized view with partitioning
CREATE MATERIALIZED VIEW mv_partitioned_emp
PARTITION BY HASH(emp_id)
AS SELECT emp_id, emp_name FROM employees;
-- 50. Create a materialized view with aggregation and join
CREATE MATERIALIZED VIEW mv_dept_salary
REFRESH FAST ON DEMAND AS
SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM employees e JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
-- 51. Create a materialized view with query rewrite and join
CREATE MATERIALIZED VIEW mv_join_query
ENABLE QUERY REWRITE AS
SELECT e.emp_name, d.dept_name
FROM employees e JOIN departments d ON e.department_id = d.dept_id;
-- 52. Create a materialized view log with primary key for fast refresh
CREATE MATERIALIZED VIEW LOG ON employees WITH PRIMARY KEY, SEQUENCE;
-- 53. Create a materialized view with rowid and including columns
CREATE MATERIALIZED VIEW LOG ON employees WITH ROWID, INCLUDING NEW VALUES;
-- 54. Check materialized view refresh status
SELECT mview_name, last_refresh_type, last_refresh_date FROM user_mviews;
-- 55. Create a materialized view with parallel refresh
CREATE MATERIALIZED VIEW mv_parallel_refresh
REFRESH FAST ON DEMAND
PARALLEL 4
AS SELECT * FROM employees;
-- 56. Alter materialized view to enable query rewrite
ALTER MATERIALIZED VIEW mv_emp_dept ENABLE QUERY REWRITE;
-- 57. Disable query rewrite on materialized view
ALTER MATERIALIZED VIEW mv_emp_dept DISABLE QUERY REWRITE;
-- 58. Force complete refresh of materialized view
EXEC DBMS_MVIEW.REFRESH('mv_emp_dept','C');
-- 59. Schedule automatic refresh using DBMS_JOB or DBMS_SCHEDULER
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'mv_refresh_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH(''mv_emp_dept''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=1',
enabled => TRUE
);
END;
/
-- 60. Create a materialized view with aggregate join group
CREATE MATERIALIZED VIEW mv_agg_join
REFRESH FAST ON DEMAND
AS SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM employees e JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
________________________________________
61-80: Advanced View Features (Complex Queries, Security, Partitioned Views)
-- 61. Create a view with analytic functions
CREATE OR REPLACE VIEW emp_rank_view AS
SELECT emp_name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
-- 62. Create a view with hierarchical query
CREATE OR REPLACE VIEW emp_hierarchy_view AS
SELECT emp_id, manager_id, LEVEL AS hierarchy_level
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;
-- 63. Create a view with recursive CTE (WITH clause)
CREATE OR REPLACE VIEW emp_recursive_view AS
WITH emp_cte (emp_id, manager_id, level) AS (
SELECT emp_id, manager_id, 1 FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.manager_id, c.level + 1
FROM employees e JOIN emp_cte c ON e.manager_id = c.emp_id
)
SELECT * FROM emp_cte;
-- 64. Create a view with pivot clause
CREATE OR REPLACE VIEW emp_pivot_view AS
SELECT * FROM
(SELECT emp_name, department_id, salary FROM employees)
PIVOT
(SUM(salary) FOR department_id IN (10 AS dept_10, 20 AS dept_20));
-- 65. Create a view with unpivot clause
CREATE OR REPLACE VIEW emp_unpivot_view AS
SELECT * FROM
(SELECT emp_name, dept_10, dept_20 FROM emp_pivot_view)
UNPIVOT
(salary FOR dept IN (dept_10, dept_20));
-- 66. Create a view with JSON extraction
CREATE OR REPLACE VIEW emp_json_view AS
SELECT emp_id, json_value(json_data, '$.address.city') AS city FROM employees;
-- 67. Create a view with XMLTABLE to parse XML column
CREATE OR REPLACE VIEW emp_xml_view AS
SELECT emp_id, x.city
FROM employees,
XMLTABLE('/employee/address' PASSING xml_data
COLUMNS city VARCHAR2(50) PATH 'city') x;
-- 68. Create a view using SYS_CONTEXT to get session info
CREATE OR REPLACE VIEW user_session_view AS
SELECT emp_id, SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user FROM employees;
-- 69. Create a secure view with column masking (using VPD policies)
CREATE OR REPLACE VIEW emp_secure_view AS
SELECT emp_id, emp_name, salary FROM employees;
-- 70. Create a view with encryption functions (DBMS_CRYPTO)
CREATE OR REPLACE VIEW emp_enc_view AS
SELECT emp_id, emp_name, DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(salary || '', 'AL32UTF8'),
DBMS_CRYPTO.DES_CBC_PKCS5, UTL_RAW.CAST_TO_RAW('key12345')) AS encrypted_salary
FROM employees;
-- 71. Create a partitioned view with UNION ALL (manual partitioning)
CREATE OR REPLACE VIEW emp_partition_view AS
SELECT * FROM employees_2019
UNION ALL
SELECT * FROM employees_2020;
-- 72. Create a view with lateral join (CROSS APPLY)
CREATE OR REPLACE VIEW emp_lateral_view AS
SELECT e.emp_name, d.dept_name
FROM employees e CROSS JOIN LATERAL
(SELECT dept_name FROM departments d WHERE d.dept_id = e.department_id) d;
-- 73. Create a view with cross join
CREATE OR REPLACE VIEW emp_cross_dept_view AS
SELECT e.emp_name, d.dept_name FROM employees e CROSS JOIN departments d;
-- 74. Create a view with window function and filtering
CREATE OR REPLACE VIEW emp_salary_rank_filtered AS
SELECT emp_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
WHERE salary > 50000;
-- 75. Create a view to hide salary using virtual column
CREATE OR REPLACE VIEW emp_virtual_mask AS
SELECT emp_id, emp_name, salary * 0 AS masked_salary FROM employees;
-- 76. Create a view to display current date/time for each row
CREATE OR REPLACE VIEW emp_current_time AS
SELECT emp_id, emp_name, SYSDATE AS query_time FROM employees;
-- 77. Create a view with DECODE statement
CREATE OR REPLACE VIEW emp_dept_code AS
SELECT emp_name,
DECODE(department_id, 10, 'HR', 20, 'Finance', 'Other') AS dept_name
FROM employees;
-- 78. Create a view with ROW_NUMBER and partition
CREATE OR REPLACE VIEW emp_rownum_view AS
SELECT emp_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees;
-- 79. Create a view with analytic aggregates
CREATE OR REPLACE VIEW emp_avg_dept_salary AS
SELECT emp_name, department_id,
AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary
FROM employees;
-- 80. Create a view with CASE inside aggregation
CREATE OR REPLACE VIEW emp_salary_group AS
SELECT department_id,
SUM(CASE WHEN salary > 70000 THEN 1 ELSE 0 END) AS high_salary_count
FROM employees GROUP BY department_id;
________________________________________
81-100: View Maintenance, Metadata, Security, and Miscellaneous
-- 81. Query USER_VIEWS metadata to see view text
SELECT view_name, text_length FROM user_views WHERE view_name = 'EMP_VIEW';
-- 82. Query DBA_VIEWS for all views in DB
SELECT owner, view_name FROM dba_views WHERE owner = 'HR';
-- 83. Query USER_OBJECTS to list views
SELECT object_name, status FROM user_objects WHERE object_type = 'VIEW';
-- 84. Query USER_DEPENDENCIES to check view dependencies
SELECT name, referenced_name FROM user_dependencies WHERE type = 'VIEW';
-- 85. Rename a view
ALTER VIEW emp_view RENAME TO employee_view;
-- 86. Compile a view if invalid
ALTER VIEW emp_view COMPILE;
-- 87. Grant select on a view to a user
GRANT SELECT ON emp_view TO hr_user;
-- 88. Revoke select on a view
REVOKE SELECT ON emp_view FROM hr_user;
-- 89. Create a view with WITH CHECK OPTION and WITH READ ONLY
CREATE OR REPLACE VIEW emp_readonly_check AS
SELECT emp_id, emp_name FROM employees WHERE salary > 50000
WITH CHECK OPTION
WITH READ ONLY;
-- 90. Create a view with aliases for multiple columns
CREATE OR REPLACE VIEW emp_alias_multi (id, name, sal) AS
SELECT emp_id, emp_name, salary FROM employees;
-- 91. Create a view with complex joins and filters
CREATE OR REPLACE VIEW emp_complex_view AS
SELECT e.emp_name, d.dept_name, j.job_title
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN jobs j ON e.job_id = j.job_id
WHERE e.salary > 50000;
-- 92. Create a view with UNION ALL to combine tables
CREATE OR REPLACE VIEW all_staff AS
SELECT emp_id, emp_name, 'Employee' AS role FROM employees
UNION ALL
SELECT contractor_id, contractor_name, 'Contractor' FROM
contractors;
-- 93. Create a view with inline table function call
CREATE OR REPLACE VIEW emp_table_func_view AS
SELECT * FROM TABLE(get_employees());
-- 94. Create a view that hides salaries below a threshold
CREATE OR REPLACE VIEW emp_filtered_salary AS
SELECT emp_id, emp_name, CASE WHEN salary > 50000 THEN salary ELSE NULL END AS salary
FROM employees;
-- 95. Create a view with DISTINCT and row_number to remove duplicates
CREATE OR REPLACE VIEW emp_unique_view AS
SELECT emp_id, emp_name, salary FROM
(SELECT emp_id, emp_name, salary,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY salary DESC) rn
FROM employees)
WHERE rn = 1;
-- 96. Create a view that uses a sequence in SELECT (not allowed directly, but via function)
CREATE OR REPLACE VIEW emp_seq_view AS
SELECT emp_id, emp_name, get_next_seq() AS seq_num FROM employees;
-- 97. Create a view using inline PL/SQL table function
CREATE OR REPLACE VIEW emp_inline_func_view AS
SELECT * FROM TABLE(employees_function());
-- 98. Create a view with inline comments in SELECT list
CREATE OR REPLACE VIEW emp_comments AS
SELECT emp_id, emp_name, salary /* employee salary */ FROM employees;
-- 99. Create a view with multiple joins and subqueries
CREATE OR REPLACE VIEW emp_multi_join_view AS
SELECT e.emp_name, d.dept_name,
(SELECT MAX(salary) FROM employees WHERE department_id = e.department_id) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 100. Drop a view safely (check existence first)
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW emp_view';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
No comments:
Post a Comment