Views for security and abstraction in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a SQL view?

A SQL view is a virtual table based on the result of a SQL query. It does not store data itself but shows data from one or more tables.

beginner

How do views help with security in databases?

Views restrict user access by showing only specific columns or rows, hiding sensitive data from users who don't have full table access.

intermediate

Explain abstraction in the context of SQL views.

Abstraction means hiding complex table structures or joins behind a simple view, so users can work with easy-to-understand data without knowing the details.

intermediate

Can you update data through a SQL view?

Some views are updatable, meaning you can insert, update, or delete data through them if they meet certain conditions, like referencing a single table without aggregates.

beginner

Write a simple SQL statement to create a view that shows only employee names and departments.

CREATE VIEW EmployeeDept AS SELECT name, department FROM Employees;

What does a SQL view store?

A The actual data from tables

B Indexes for faster queries

C Only the query definition, not data

D User permissions

A view stores only the SQL query definition, not the actual data.

How can views improve database security?

A By encrypting the data

B By speeding up queries

C By backing up data automatically

D By hiding sensitive columns or rows

Views can hide sensitive columns or rows, limiting what users see.

Which of these is a benefit of using views for abstraction?

A Users must write complex joins

B Users see simplified data structures

C Views increase storage space

D Views prevent all data updates

Views simplify data by hiding complex joins or calculations.

When is a view updatable?

A When it references a single table without aggregates

B When it uses aggregate functions

C When it includes multiple tables with joins

D When it contains subqueries

Views are updatable if they reference a single table and avoid aggregates or complex joins.

Which SQL keyword is used to create a view?

A CREATE VIEW

B CREATE TABLE

C CREATE INDEX

D CREATE PROCEDURE

CREATE VIEW is used to define a new view.

Describe how views can be used to protect sensitive data in a database.

Think about showing only what users need to see.

Explain the concept of abstraction with SQL views and why it is helpful for users.

Imagine making a complicated table look simple.