Computer Engineering is coming soon

We're still writing and reviewing the notes for every subject, and we'll open it when every subject is ready. Meanwhile you can practise aptitude, ask Aria about any subject, or see the branches that are ready.

Views, indexes, DCL and TCL

Concise study notes on Views, Indexes, DCL and TCL explaining definitions, rules, SQL syntax, examples and exam tips for engineering students.

Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.

Why it matters

Two sentences: Views, indexes, DCL and TCL are everyday tools an engineer uses to present data safely, make queries fast, and control transactions and privileges. You will use views to give simplified or restricted table shapes, indexes to speed up lookups, DCL (Data Control Language) to manage user rights, and TCL (Transaction Control Language) to make multi-step changes atomic and recoverable.

Key ideas

  • Types: simple (single-table) view — SELECT from one table, no aggregates, may be updatable; complex view — uses joins, aggregates, DISTINCT, GROUP BY, set operations, or expressions; typically not updatable. Materialized view — stores the query result physically and must be refreshed.
  • Updatability rules (typical, but DBMS-dependent): A view is updatable if every column being modified maps directly to a single base table column, the view is defined on a single table, there are no GROUP BY/aggregates, and no DISTINCT. Many DBMSs allow INSTEAD OF triggers to make non-updatable views accept DML via trigger code. Use WITH CHECK OPTION on an updatable view to prevent updates/inserts that would make rows not satisfy the view predicate.
  • Index: an index is a data structure that lets the DBMS find rows faster for certain queries. Common index types: B+‑tree (ordered) and hash (for equality). Indexes can be clustered (data stored in index order—usually only one per table) or nonclustered (separate structure pointing to rows). A unique index enforces uniqueness.
    • Draw: one table with rows; beside it, a tree labeled B+‑tree with keys and pointers to row locations. For a hash index, draw buckets keyed by hash value pointing to rows.
    • When DBMS uses index: WHERE on indexed column(s), ORDER BY matching index order, some joins. An index does not change which rows are returned; it speeds up predicates and ordering, and can enable index-only scans when all selected columns are in the index. A plain SELECT * with no WHERE/ORDER BY usually won't benefit.
    • Costs: faster reads for matched queries, slower writes (INSERT/UPDATE/DELETE) because index must be updated, extra storage.
  • DCL (Data Control Language): statements to control privileges: GRANT and REVOKE are the main commands. They affect who can SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc., on schema objects. Many DBMSs have roles and schema-level or object-level privilege granularity.
  • TCL (Transaction Control Language): statements that manage transactions: BEGIN/START TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT.
    • Transaction: a sequence of one or more SQL statements treated as a single unit of work. Transactions follow ACID properties (Atomicity, Consistency, Isolation, Durability) — see the Transactions topic for proofs and schedules.
    • COMMIT makes all changes in the transaction permanent. ROLLBACK undoes them. SAVEPOINT name creates a point you can ROLLBACK TO without undoing the entire transaction.

Assumptions and DBMS differences to note: exact rules for view updatability, presence and behaviour of materialized views, and whether an index is clustered depend on the DBMS (Oracle, PostgreSQL, MySQL/InnoDB, SQL Server differ). Mention these differences in answers.

Formulas

(Syntax lines to remember) CREATE VIEW view_name AS SELECT ... [WITH CHECK OPTION]; — defines a view; view_name: identifier. DROP VIEW view_name; — removes a view. CREATE [UNIQUE] INDEX index_name ON table_name (col1 [, col2 ...]); — creates an index. DROP INDEX index_name; (or DROP INDEX index_name ON table_name in some DBMS like MySQL/SQL Server; or schema-qualified DROP INDEX schema.index_name) — removes an index. GRANT privilege_list ON object TO grantee [WITH GRANT OPTION]; — give privileges (privilege_list: SELECT, INSERT, UPDATE(col), DELETE, etc.). REVOKE privilege_list ON object FROM grantee; — remove privileges. BEGIN; or START TRANSACTION; — start a transaction. You can set isolation: SET TRANSACTION ISOLATION LEVEL READ COMMITTED | REPEATABLE READ | SERIALIZABLE; (names/availability vary by DBMS). COMMIT; — commit current transaction. ROLLBACK; — rollback current transaction. SAVEPOINT sp_name; — set savepoint in transaction. RELEASE SAVEPOINT sp_name; — optionally remove a savepoint. ROLLBACK TO SAVEPOINT sp_name; — rollback to savepoint.

Worked examples

All examples use standard SQL. Shown outputs are exact SELECT results in plain table text.

Example 1 — Views: create table, view, updatable vs not SQL:

CREATE TABLE EMP(emp_id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, salary INT);
CREATE TABLE DEPT(dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO DEPT VALUES (10, 'Sales'), (20, 'HR');
INSERT INTO EMP VALUES (1, 'Alice', 10, 50000), (2, 'Bob', 20, 40000);
-- Simple view on single table (updatable in most DBMS)
CREATE VIEW emp_basic AS SELECT emp_id, name, salary FROM EMP;
SELECT * FROM emp_basic;

Output of SELECT * FROM emp_basic:

emp_id | name  | salary
----------------------
1      | Alice | 50000
2      | Bob   | 40000

Now update via view (allowed because view is simple):

UPDATE emp_basic SET salary = 55000 WHERE emp_id = 1;
SELECT emp_id, name, salary FROM EMP ORDER BY emp_id;

Output after update:

emp_id | name  | salary
----------------------
1      | Alice | 55000
2      | Bob   | 40000

Now a join view (usually not updatable):

CREATE VIEW emp_dept AS SELECT e.emp_id, e.name, d.dept_name, e.salary
  FROM EMP e JOIN DEPT d ON e.dept_id = d.dept_id;
SELECT * FROM emp_dept;
-- Attempting an UPDATE on emp_dept will typically fail (non-updatable view)

Explain in answer: write that updating emp_dept will raise error in many DBMS until you create INSTEAD OF triggers.

Example 2 — Indexes: creation and effect SQL and sample data (continuing from above EMP table):

-- Create non-unique index on salary
CREATE INDEX idx_emp_salary ON EMP(salary);
-- Query that benefits from index
SELECT emp_id, name FROM EMP WHERE salary > 45000 ORDER BY salary DESC;

Expected output:

emp_id | name
--------------
1      | Alice

Note: Result is same whether index exists or not; index affects performance, not result. To test uniqueness:

CREATE UNIQUE INDEX idx_emp_id_unique ON EMP(emp_id);
-- Attempting to insert duplicate emp_id will fail (already enforced by PRIMARY KEY; a UNIQUE index would enforce this if no PK/UNIQUE existed)
INSERT INTO EMP VALUES (1, 'Charlie', 10, 30000); -- error: duplicate key

Example 3 — TCL (transaction) and DCL (privilege) examples SQL (accounts for TCL):

CREATE TABLE ACCOUNTS(account_id INT PRIMARY KEY, balance INT);
INSERT INTO ACCOUNTS VALUES (1, 1000), (2, 500);
-- Transfer 100 from account 1 to 2, atomic
BEGIN;
UPDATE ACCOUNTS SET balance = balance - 100 WHERE account_id = 1;
UPDATE ACCOUNTS SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
SELECT * FROM ACCOUNTS ORDER BY account_id;

Output after COMMIT:

account_id | balance
-------------------
1          | 900
2          | 600

Rollback example:

-- restore initial state for demonstration
UPDATE ACCOUNTS SET balance = 1000 WHERE account_id = 1;
UPDATE ACCOUNTS SET balance = 500 WHERE account_id = 2;
BEGIN;
UPDATE ACCOUNTS SET balance = balance - 200 WHERE account_id = 1;
UPDATE ACCOUNTS SET balance = balance + 200 WHERE account_id = 2;
ROLLBACK;
SELECT * FROM ACCOUNTS ORDER BY account_id;

Output after ROLLBACK (unchanged):

account_id | balance
-------------------
1          | 1000
2          | 500

DCL example (effect explained):

GRANT SELECT ON EMP TO some_user;
-- Now some_user can read EMP
REVOKE SELECT ON EMP FROM some_user;

Explain: after GRANT the user can run SELECT; after REVOKE attempts to SELECT will fail with permission error.

Common mistakes

  • Believing an index changes query results — it only changes execution plan and speed. Never use indexes to change semantics.
  • Creating many unneeded indexes: slows writes and wastes space.
  • Forgetting to COMMIT or checking autocommit setting before assuming changes are permanent.
  • Confusing DCL commands with role creation or OS-level permissions — GRANT/REVOKE are DB-level.

For your exam

Kinds of questions: definitions (what is a view/index/TCL/DCL), short notes (advantages/disadvantages, updatability rules), derivations (explain why indexes speed up search: O(log n) vs O(n); and hash indexes give O(1) average for equality), SQL programs (write CREATE VIEW, update via view, create index), and diagrams (draw index structure, show view dependency graph). Full-marks answers include:

  • Precise definitions with bold term usage (view, index, transaction).
  • Exact SQL syntax lines and a short worked example (at least one SELECT output or explanation).
  • Mention of DBMS differences where rules differ (updatability, clustered index behaviour, materialized view refresh options).
  • For transactions, state ACID properties and explain COMMIT vs ROLLBACK with an example showing before/after state.

Quick check

  1. What SQL command makes a transaction permanent?
  2. True/False: An index always makes SELECT queries return different rows.
  3. Name two reasons to use a view. Answers: COMMIT; False; To simplify complex queries for users, and to restrict columns/rows for security or convenience (also: logical abstraction, compatibility).

Finished this topic? Mark it so your progress, study plan and readiness keep up.

Stuck on something here?