Spaces:

Aniruddha7
/

QueryLensV2b

Running on Zero

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

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

19dfd32 verified 12 days ago

4.55 kB

use employee;

delimiter //
drop function if exists emp_dept_id //
drop function if exists emp_dept_name //
drop function if exists emp_name //
drop function if exists current_manager //
drop procedure if exists show_department //

--
-- returns the department id of a given employee
--
create function emp_dept_id( employee_id int )
returns char(4)
reads sql data
begin
    declare max_date date;
    set max_date = (
        select
            max(from_date)
        from
            dept_emp
        where
            emp_no = employee_id
    );
    set @max_date=max_date;
    return (
        select
            dept_no
        from
            dept_emp
        where
            emp_no = employee_id
            and
            from_date = max_date
            limit 1
    );
end //

--
-- returns the department name of a given employee
--

create function emp_dept_name( employee_id int )
returns varchar(40)
reads sql data
begin
    return (
        select
            dept_name
        from
            department
        where
            dept_no = emp_dept_id(employee_id)
    );
end//

--
-- returns the employee name of a given employee id
--
create function emp_name (employee_id int)
returns varchar(32)
reads SQL data
begin
    return (
        select
            concat(first_name, ' ', last_name) as name
        from
            employee
        where
            emp_no = employee_id
    );
end//

--
-- returns the manager of a department
-- choosing the most recent one
-- from the manager list
--
create function current_manager( dept_id char(4) )
returns varchar(32)
reads sql data
begin
    declare max_date date;
    set max_date = (
        select
            max(from_date)
        from
            dept_manager
        where
            dept_no = dept_id
    );
    set @max_date=max_date;
    return (
        select
            emp_name(emp_no)
        from
            dept_manager
        where
            dept_no = dept_id
            and
            from_date = max_date
            limit 1
    );
end //

delimiter ;

--
--  selects the employee records with the
--  latest department
--

CREATE OR REPLACE VIEW  v_full_employee
AS
SELECT
    emp_no,
    first_name , last_name ,
    birth_date , gender,
    hire_date,
    emp_dept_name(emp_no) as department
from
    employee;

--
-- selects the department list with manager names
--

CREATE OR REPLACE VIEW v_full_department
AS
SELECT
    dept_no, dept_name, current_manager(dept_no) as manager
FROM
    department;

delimiter //

--
-- shows the department with the number of employee
-- per department
--
create procedure show_department()
modifies sql data
begin
    DROP TABLE IF EXISTS department_max_date;
    DROP TABLE IF EXISTS department_people;
    CREATE TEMPORARY TABLE department_max_date
    (
        emp_no int not null primary key,
        dept_from_date date not null,
        dept_to_date  date not null, # bug#320513
        KEY (dept_from_date, dept_to_date)
    );
    INSERT INTO department_max_date
    SELECT
        emp_no, max(from_date), max(to_date)
    FROM
        dept_emp
    GROUP BY
        emp_no;

    CREATE TEMPORARY TABLE department_people
    (
        emp_no int not null,
        dept_no char(4) not null,
        primary key (emp_no, dept_no)
    );

    insert into department_people
    select dmd.emp_no, dept_no
    from
        department_max_date dmd
        inner join dept_emp de
            on dmd.dept_from_date=de.from_date
            and dmd.dept_to_date=de.to_date
            and dmd.emp_no=de.emp_no;
    SELECT
        dept_no,dept_name,manager, count(*)
        from v_full_department
            inner join department_people using (dept_no)
        group by dept_no;
        # with rollup;
    DROP TABLE department_max_date;
    DROP TABLE department_people;
end //

drop function if exists employee_usage //
drop procedure if exists employee_help //

CREATE FUNCTION employee_usage ()
RETURNS TEXT
DETERMINISTIC
BEGIN
    RETURN
'
    == USAGE ==
    ====================
    PROCEDURE show_department()
        shows the department with the manager and
        number of employee per department
    FUNCTION current_manager (dept_id)
        Shows who is the manager of a given departmennt
    FUNCTION emp_name (emp_id)
        Shows name and surname of a given employee
    FUNCTION emp_dept_id (emp_id)
        Shows the current department of given employee
';
END //

create procedure employee_help()
deterministic
begin
    select employee_usage() as info;
end//

delimiter ;

	use employee;

	delimiter //
	drop function if exists emp_dept_id //
	drop function if exists emp_dept_name //
	drop function if exists emp_name //
	drop function if exists current_manager //
	drop procedure if exists show_department //

	--
	-- returns the department id of a given employee
	--
	create function emp_dept_id( employee_id int )
	returns char(4)
	reads sql data
	begin
	declare max_date date;
	set max_date = (
	select
	max(from_date)
	from
	dept_emp
	where
	emp_no = employee_id
	);
	set @max_date=max_date;
	return (
	select
	dept_no
	from
	dept_emp
	where
	emp_no = employee_id
	and
	from_date = max_date
	limit 1
	);
	end //

	--
	-- returns the department name of a given employee
	--

	create function emp_dept_name( employee_id int )
	returns varchar(40)
	reads sql data
	begin
	return (
	select
	dept_name
	from
	department
	where
	dept_no = emp_dept_id(employee_id)
	);
	end//

	--
	-- returns the employee name of a given employee id
	--
	create function emp_name (employee_id int)
	returns varchar(32)
	reads SQL data
	begin
	return (
	select
	concat(first_name, ' ', last_name) as name
	from
	employee
	where
	emp_no = employee_id
	);
	end//

	--
	-- returns the manager of a department
	-- choosing the most recent one
	-- from the manager list
	--
	create function current_manager( dept_id char(4) )
	returns varchar(32)
	reads sql data
	begin
	declare max_date date;
	set max_date = (
	select
	max(from_date)
	from
	dept_manager
	where
	dept_no = dept_id
	);
	set @max_date=max_date;
	return (
	select
	emp_name(emp_no)
	from
	dept_manager
	where
	dept_no = dept_id
	and
	from_date = max_date
	limit 1
	);
	end //

	delimiter ;

	--
	-- selects the employee records with the
	-- latest department
	--

	CREATE OR REPLACE VIEW v_full_employee
	AS
	SELECT
	emp_no,
	first_name , last_name ,
	birth_date , gender,
	hire_date,
	emp_dept_name(emp_no) as department
	from
	employee;

	--
	-- selects the department list with manager names
	--

	CREATE OR REPLACE VIEW v_full_department
	AS
	SELECT
	dept_no, dept_name, current_manager(dept_no) as manager
	FROM
	department;

	delimiter //

	--
	-- shows the department with the number of employee
	-- per department
	--
	create procedure show_department()
	modifies sql data
	begin
	DROP TABLE IF EXISTS department_max_date;
	DROP TABLE IF EXISTS department_people;
	CREATE TEMPORARY TABLE department_max_date
	(
	emp_no int not null primary key,
	dept_from_date date not null,
	dept_to_date date not null, # bug#320513
	KEY (dept_from_date, dept_to_date)
	);
	INSERT INTO department_max_date
	SELECT
	emp_no, max(from_date), max(to_date)
	FROM
	dept_emp
	GROUP BY
	emp_no;

	CREATE TEMPORARY TABLE department_people
	(
	emp_no int not null,
	dept_no char(4) not null,
	primary key (emp_no, dept_no)
	);

	insert into department_people
	select dmd.emp_no, dept_no
	from
	department_max_date dmd
	inner join dept_emp de
	on dmd.dept_from_date=de.from_date
	and dmd.dept_to_date=de.to_date
	and dmd.emp_no=de.emp_no;
	SELECT
	dept_no,dept_name,manager, count(*)
	from v_full_department
	inner join department_people using (dept_no)
	group by dept_no;
	# with rollup;
	DROP TABLE department_max_date;
	DROP TABLE department_people;
	end //

	drop function if exists employee_usage //
	drop procedure if exists employee_help //

	CREATE FUNCTION employee_usage ()
	RETURNS TEXT
	DETERMINISTIC
	BEGIN
	RETURN
	'
	== USAGE ==
	====================

	PROCEDURE show_department()

	shows the department with the manager and
	number of employee per department

	FUNCTION current_manager (dept_id)

	Shows who is the manager of a given departmennt

	FUNCTION emp_name (emp_id)

	Shows name and surname of a given employee

	FUNCTION emp_dept_id (emp_id)

	Shows the current department of given employee
	';
	END //

	create procedure employee_help()
	deterministic
	begin
	select employee_usage() as info;
	end//

	delimiter ;