Views, Indexes and Window Functions

Views

A view is a saved query that behaves like a virtual table. Views simplify complex queries and can hide sensitive columns.

SQL

CREATE VIEW pune_staff AS
SELECT name, dept FROM employees WHERE city = 'Pune';

SELECT * FROM pune_staff;

+-------+------+ | name | dept | +-------+------+ | Asha | IT | | Meera | IT | | Neha | HR | +-------+------+

Indexes

SQL

CREATE INDEX idx_emp_dept ON employees(dept);
DROP INDEX idx_emp_dept ON employees;      -- MySQL syntax

Indexes speed up reads (WHERE, JOIN, ORDER BY) but slow down writes and use extra space.

Window Functions

SQL

SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

+-------+-------+--------+-----------+ | name | dept | salary | dept_rank | +-------+-------+--------+-----------+ | Neha | HR | 47000 | 1 | | Ravi | HR | 42000 | 2 | | Meera | IT | 78000 | 1 | | Asha | IT | 65000 | 2 | | Karan | Sales | 51000 | 1 | +-------+-------+--------+-----------+

Other window functions: ROW_NUMBER(), DENSE_RANK(), LAG(), LEAD() and SUM() OVER (...) for running totals.

Track your progress through the SQL course

← Prev