task10.sql

CREATE DATABASE task10;
USE task10;

--Create tables (Employees & Departments)

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary DECIMAL(10,2),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

--Insert sample data
INSERT INTO departments VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');

INSERT INTO employees VALUES
(101, 'Alice', 50000, 2),
(102, 'Bob', 40000, 1),
(103, 'Charlie', 60000, 2),
(104, 'Diana', 55000, 3);

--Create a complex JOIN query
SELECT 
    e.emp_id,
    e.emp_name,
    e.salary,
    d.dept_name
FROM employees e
JOIN departments d
ON e.dept_id = d.dept_id;

--Convert the query into a SQL VIEW
CREATE VIEW emp_department_view AS
SELECT 
    e.emp_id,
    e.emp_name,
    e.salary,
    d.dept_name
FROM employees e
JOIN departments d
ON e.dept_id = d.dept_id;

-- View created successfully.

--Query data from the VIEW
SELECT * FROM emp_department_view;

--Apply filtering & sorting on views
-- Employees from IT department
SELECT * 
FROM emp_department_view
WHERE dept_name = 'IT';

-- Sort by salary (highest first)
SELECT * 
FROM emp_department_view
ORDER BY salary DESC;

--INSERT data through a VIEW (limitations)
INSERT INTO emp_department_view (emp_id, emp_name, salary, dept_name)
VALUES (105, 'Evan', 45000, 'HR');


--This will fail because:

View contains a JOIN

Views with joins are not updatable

--Insert must be done on base tables instead.

--Drop and recreate VIEW safely
DROP VIEW IF EXISTS emp_department_view;

CREATE VIEW emp_department_view AS
SELECT 
    e.emp_id,
    e.emp_name,
    e.salary,
    d.dept_name
FROM employees e
JOIN departments d
ON e.dept_id = d.dept_id;