Spaces:

Aniruddha7
/

QueryLensV2b

Running on Zero

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

Full deploy of QueryLensV2 (code + demo db) to a fresh Space

19dfd32 verified 3 days ago

1.7 kB

-- 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;

	-- 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;