SQL

🧠 Day 8 – Views, Analytical Functions & Set Operators in SQL

Photo of Mathimaa Mathimaa3 weeks ago
0 0 2 minutes read

🧠 Day 8 – Views, Analytical Functions & Set Operators in SQL

🪟 Views in SQL

A view is a virtual table that stores only the SQL query, not the actual data.
When the base (master) table data changes — the view reflects it automatically (except in Materialized Views).

🧩 Types of Views

1️⃣ Simple View

  • Created from a simple SQL query (single table, no joins).

  • Example:

    CREATE VIEW v_100 AS
    SELECT * FROM employees;

2️⃣ Complex View

  • Created using joins, group functions, or multiple tables.

  • Example:

    CREATE VIEW v_200 AS
    SELECT e.employee_id, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id;

3️⃣ Materialized View

  • Stores a physical copy (snapshot) of data.

  • Changes in master table won’t appear automatically.

  • Must refresh to sync.

CREATE MATERIALIZED VIEW mv_100 AS
SELECT * FROM employees;

🔄 Refreshing Materialized Views

  1. Complete Refresh – Reloads entire data.

  2. Fast Refresh – Applies only incremental changes.

  3. Force Refresh – Chooses between fast or complete depending on availability.

BEGIN
DBMS_MVIEW.REFRESH('mv_100');
END;

Alternate syntax:

BEGIN
DBMS_SNAPSHOT.REFRESH('mv_100');
END;

🧮 Analytical / Window Functions

These allow you to perform rankings, comparisons, or running totals across rows without grouping.

Function Description
LEAD() Shows value ahead of current row
LAG() Shows value behind the current row
RANK() Gives rank but skips next number for ties
DENSE_RANK() Gives rank without skipping numbers for ties
ROW_NUMBER() Assigns a unique sequential number

💡 Examples

-- LEAD
SELECT employee_id,
LEAD(employee_id) OVER (ORDER BY employee_id DESC) AS next_emp
FROM employees;

-- LAG
SELECT employee_id,
LAG(employee_id) OVER (ORDER BY employee_id) AS prev_emp
FROM employees;

-- RANK
SELECT first_name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- DENSE_RANK
SELECT first_name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- ROW_NUMBER
SELECT first_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rownum
FROM employees;

🧠 Example (Ranking difference):

Salary RANK DENSE_RANK
500 1 1
200 2 2
200 2 2
100 5 3
100 5 3
50 9 4

🔢 Sorting (ORDER BY)

Keyword Meaning
ASC Ascending order (default)
DESC Descending order
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary ASC;

🧩 Pseudo Columns

Pseudo columns act like table columns but aren’t actually stored in the table.

Pseudo Column Description
ROWNUM Sequence number of returned rows
ROWID Physical address of row in DB
SYSDATE Current date
SYSTIMESTAMP Current date + time
DISTINCT / UNIQUE Remove duplicates

Examples:

SELECT DISTINCT department_id FROM employees;
SELECT ROWNUM, first_name, salary FROM employees WHERE ROWNUM <= 3;
SELECT ROWID, first_name, salary FROM employees;
SELECT SYSDATE, SYSTIMESTAMP FROM dual;

⚖️ SET Operators

Used to combine results from multiple queries.

📌 Rules:

  1. Number of columns must match.

  2. Data types must be same.

Operator Description
UNION Combines results, removes duplicates
UNION ALL Combines all, keeps duplicates
INTERSECT Returns only common rows
MINUS Returns rows in first query not in second

💻 Examples

-- UNION
SELECT employee_id, first_name, department_id
FROM employees WHERE department_id IN (90,100)
UNION
SELECT employee_id, first_name, department_id
FROM employees WHERE department_id IN (100,110);

-- UNION ALL
SELECT employee_id, first_name, department_id
FROM employees WHERE department_id IN (90,100)
UNION ALL
SELECT employee_id, first_name, department_id
FROM employees WHERE department_id IN (100,110);

-- INTERSECT
SELECT employee_id, first_name, department_id
FROM employees WHERE department_id IN (90,100)
INTERSECT
SELECT employee_id, first_name, department_id
FROM employees WHERE department_id IN (100,110);

-- MINUS
SELECT employee_id, first_name, department_id
FROM employees WHERE department_id IN (90,100)
MINUS
SELECT employee_id, first_name, department_id
FROM employees WHERE department_id IN (100,110);

✅ Summary

Concept Key Point
View Virtual table that stores SQL logic
Materialized View Physical snapshot that needs refresh
Analytical Functions Compare or rank rows without grouping
Sorting ORDER BY sorts results
Pseudo Columns Special system-generated columns
Set Operators Combine query results
Photo of Mathimaa Mathimaa3 weeks ago
0 0 2 minutes read

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button