SQL Views, Indexes and Advanced Query Patterns

SQL beginner course · Chapter 8 of 10

SQL Views, Indexes and Advanced Query Patterns explained in simple steps. Examples use practice tables such as students and scores; adapt names to your database.

Follow the concepts from left to right, then run the examples below.

In this lesson

1. CREATE and ALTER VIEW
2. Updating a view
3. Indexes
4. Create, change and drop indexes
5. DROP objects
6. Advanced output

CREATE and ALTER VIEW

A view stores a query definition, not usually a separate copy of its rows. Changing a view definition and updating through a view have product-specific restrictions.

Example

CREATE VIEW active_students AS SELECT student_id, student_name FROM students WHERE active = 1;

Expected result: A reusable named view of active students is available.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Updating a view

Simple single-table views can sometimes be updated. Joined, grouped or calculated views often cannot; check your database rules.

Example

UPDATE active_students SET student_name = 'Maya K' WHERE student_id = 11;

Expected result: If the view is updatable, the base row changes.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Indexes

An index helps locate rows on frequently searched or joined columns. It costs storage and can slow writes, so inspect real query plans before adding many.

Example

CREATE INDEX ix_scores_student ON scores(student_id);

Expected result: Lookups by student_id may become faster.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Create, change and drop indexes

CREATE INDEX adds a lookup structure; UNIQUE also rejects duplicate keys. To change an index, many systems require dropping and recreating it, while some support ALTER INDEX for maintenance. Do not guess a vendor-specific command.

Example

CREATE UNIQUE INDEX ux_students_email ON students(email);

Expected result: Duplicate email values are rejected if the column and index are supported.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

DROP objects

DROP VIEW and DROP INDEX remove those objects, while DROP TABLE removes data too. Exact index-drop syntax varies by database.

Example

DROP VIEW active_students;

Expected result: The view definition is removed.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Advanced output

CASE produces readable conditional labels in query output. Window functions can rank or total rows without collapsing detail rows.

Example

SELECT student_name, CASE WHEN grade >= 8 THEN 'Senior' ELSE 'Junior' END AS level_name FROM students;

Expected result: Each student gets a calculated label.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Quick review

Explain each concept in your own words. Recreate one example without looking at the code. Compare your result with the expected result above.

Browse the full SQL learning path. This is original teaching material; the syllabus was checked against the w3resource SQL tutorial index.