Why views are needed in SQL - See It in Action

Understanding Why Views Are Needed in SQL

📖 Scenario: You are working as a database assistant for a small company. The company has a table that stores employee information, including sensitive data like salaries. You want to create a way for managers to see employee names and departments without exposing sensitive salary information.

🎯 Goal: Build a simple SQL view that shows only employee names and departments, hiding sensitive salary data. This will help managers access only the information they need.

📋 What You'll Learn

Create a table called employees with columns id, name, department, and salary.

Insert at least three employees with exact values.

Create a view called employee_overview that shows only name and department.

Use the view to select all employee names and departments.

💡 Why This Matters

🌍 Real World

Companies often need to share parts of their data with different teams without exposing everything. Views let them do this safely and easily.

💼 Career

Database administrators and developers use views to control data access and simplify complex queries for users.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), department (text), and salary (integer). Then insert these exact rows: (1, 'Alice', 'HR', 50000), (2, 'Bob', 'IT', 60000), (3, 'Charlie', 'Finance', 55000).

SQL

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create a view to show only names and departments

Create a view called employee_overview that selects only the name and department columns from the employees table.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT,
  salary INTEGER
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice', 'HR', 50000),
  (2, 'Bob', 'IT', 60000),
  (3, 'Charlie', 'Finance', 55000);

-- Create the employee_overview view here
-- Your code here

Need a hint?

Use CREATE VIEW view_name AS SELECT ... to create the view.

3

Query the view to see employee names and departments

Write a SQL query to select all columns from the employee_overview view.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT,
  salary INTEGER
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice', 'HR', 50000),
  (2, 'Bob', 'IT', 60000),
  (3, 'Charlie', 'Finance', 55000);

CREATE VIEW employee_overview AS
SELECT name, department FROM employees;

-- Select all from employee_overview
-- Your code here

Need a hint?

Use SELECT * FROM view_name to query the view.

4

Explain why views are useful

Add a comment explaining why creating the employee_overview view is helpful for the company.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT,
  salary INTEGER
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice', 'HR', 50000),
  (2, 'Bob', 'IT', 60000),
  (3, 'Charlie', 'Finance', 55000);

CREATE VIEW employee_overview AS
SELECT name, department FROM employees;

SELECT * FROM employee_overview;

-- Add a comment explaining why views are useful
-- Your code here

Need a hint?

Write a comment starting with -- explaining the benefit of views.