BD Lab 2

--1
SELECT last_name || ' ' || first_name || ' ' || 'casatiga ' || salary || ' lunar, dar doreste ' || salary*3
FROM employees;
--2
SELECT initcap(last_name), UPPER(first_name), LENGTH(first_name)
FROM employees
WHERE substr(LOWER(TRIM(last_name)),1,1) = 'j'
  OR substr(LOWER(TRIM(last_name)),1,1) = 'm'
  OR substr(LOWER(TRIM(last_name)),3,1) = 'a'
ORDER BY LENGTH (first_name) DESC;

--3

SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(TRIM(first_name)) = 'steven';

--4

SELECT employee_id, last_name, LENGTH(last_name), instr(last_name,'a')
FROM employees
WHERE last_name LIKE '%e';

--5
SELECT employee_id, last_name, round(sysdate - hire_date)
FROM employees
WHERE MOD(round(sysdate - hire_date), 7) = 0;

--8
SELECT sysdate+30, to_char(sysdate+30, 'dd-mon-yyyy hh24:mi:ss')
FROM dual;

--10 a)
SELECT to_char(sysdate+0.5, 'dd-mon-yyyy hh24:mi:ss')
FROM dual;

--10 b)
SELECT to_char(sysdate+ (1/24/60), 'dd-mon-yyyy hh24:mi:ss')
FROM dual;

--14
SELECT first_name,last_name, nvl(to_char(commission_pct), 'fara comision') AS "Comision"
FROM employees;

--15
SELECT last_name, salary, commission_pct
FROM employees
WHERE salary*(1 + nvl(commission_pct, 0)) > 10000;

--16
SELECT last_name, job_id, salary,
CASE job_id
WHEN 'it_prog' THEN salary + salary*0.02
WHEN 'sa_rep' THEN salary + salary*0.25
WHEN 'sa_man' THEN salary + salary*0.35
ELSE salary
END "salariu negociat"
FROM employees;

--17 JOIN - nonstandard
SELECT last_name, emp.department_id,department_name
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id;

--17 JOIN - nonstandard
SELECT last_name, emp.department_id,department_name
FROM employees emp JOIN departments dep
ON emp.department_id = dep.department_id;