How to Create and Use Views in SQL

-- Views in SQL (Note: SQLite has limited view support)
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees VALUES 
  (1, 'Alice', 'Engineering', 95000),
  (2, 'Bob', 'Marketing', 72000),
  (3, 'Charlie', 'Engineering', 105000),
  (4, 'Diana', 'Design', 88000);

-- Create a view (virtual table)
CREATE VIEW high_earners AS
SELECT name, department, salary
FROM employees
WHERE salary > 80000;

-- Query the view like a table
SELECT * FROM high_earners;

-- Views can simplify complex queries
CREATE VIEW dept_stats AS
SELECT department,
       COUNT(*) as emp_count,
       ROUND(AVG(salary), 0) as avg_salary,
       MAX(salary) as max_salary
FROM employees
GROUP BY department;

SELECT * FROM dept_stats;

-- Views are useful for:
-- 1. Simplifying complex queries
-- 2. Restricting access to sensitive data
-- 3. Providing consistent interfaces