employee-sample-database/sqlite/dataset_small/object.sql

-- SQLite implementation of views and functions
-- This is simplified compared to the MySQL version

-- Drop views if they exist
DROP VIEW IF EXISTS v_full_employee;
DROP VIEW IF EXISTS v_full_department;
DROP VIEW IF EXISTS emp_dept_current;

-- Create helper view to get current department for employees
CREATE VIEW emp_dept_current AS
SELECT 
    e.emp_no,
    de.dept_no
FROM 
    employee e
JOIN 
    dept_emp de ON e.emp_no = de.emp_no
JOIN (
    SELECT 
        emp_no, 
        MAX(from_date) AS max_from_date
    FROM 
        dept_emp
    GROUP BY 
        emp_no
) latest ON de.emp_no = latest.emp_no AND de.from_date = latest.max_from_date;

-- View that shows employee with their current department name
CREATE VIEW v_full_employee AS
SELECT
    e.emp_no,
    e.first_name, 
    e.last_name,
    e.birth_date, 
    e.gender,
    e.hire_date,
    d.dept_name AS department
FROM
    employee e
LEFT JOIN
    emp_dept_current edc ON e.emp_no = edc.emp_no
LEFT JOIN
    department d ON edc.dept_no = d.dept_no;

-- View to get current managers for departments
CREATE VIEW current_managers AS
SELECT
    d.dept_no,
    d.dept_name,
    e.first_name || ' ' || e.last_name AS manager
FROM
    department d
LEFT JOIN
    dept_manager dm ON d.dept_no = dm.dept_no
JOIN (
    SELECT
        dept_no,
        MAX(from_date) AS max_from_date
    FROM
        dept_manager
    GROUP BY
        dept_no
) latest ON dm.dept_no = latest.dept_no AND dm.from_date = latest.max_from_date
LEFT JOIN
    employee e ON dm.emp_no = e.emp_no;

-- Create a view showing departments with their managers
CREATE VIEW v_full_department AS
SELECT
    dept_no,
    dept_name,
    manager
FROM
    current_managers;