SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Department(id integer, name text); CREATE TABLE Personal(id integer, id_head integer, id_dep integer, name text, sal integer); INSERT INTO Department VALUES(1, 'Финансы'); INSERT INTO Department VALUES(2, 'Риски'); INSERT INTO Department VALUES(3, 'Розница'); INSERT INTO Department VALUES(4, 'Безопасность'); INSERT INTO Department VALUES(1000, 'ДКК'); INSERT INTO Personal VALUES(1, 1, 2, 'Бегинс', 45000); INSERT INTO Personal VALUES(2, 1, 2, 'Поттер', 80000); INSERT INTO Personal VALUES(3, 2, 2, 'Чапаева', 100000); INSERT INTO Personal VALUES(4, 4, 4, 'Шилов', 65000); INSERT INTO Personal VALUES(10000, 5, 3, 'Наумов', 64500); CREATE FUNCTION getSex (name varchar(50)) RETURNS char(4) DETERMINISTIC BEGIN DECLARE sex char(4); IF right(name,1)='а' then set sex= 'г-жа'; ELSE set sex = 'г-н'; END IF; return sex; END; // DELIMITER ; select D.name, CONCAT(getSex(p.name) ,' ', P.name )as name_with_prefix from personal p LEFT JOIN department d ON p.ID_DEP=d.id