BD Lab 3

SELECT
ROUND(TO_date('31-DEC-' || TO_CHAR(SYSDATE, 'YYYY')) - SYSDATE)
FROM DUAL;
--18
SELECT last_name, job_title
FROM employees e, jobs j
--ON e.job_id = j.job_id
WHERE department_id = 30;

--19
SELECT last_name, department_name, city
FROM employees JOIN departments
ON employees.department_id = departments.department_id
JOIN locations
ON departments.location_id=locations.location_id
WHERE commission_pct IS NOT NULL;
--20
SELECT last_name,department_name,e.department_id
FROM employees e JOIN departments d
ON e.department_id=d.department_id
WHERE LOWER(last_name) LIKE '%a%';

SELECT last_name, job_title, d.department_id, department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.city = 'Oxford';

--22
SELECT ang.employee_id AS "Ang#", ang.last_name AS "Angajat",
  mgr.employee_id AS "Mgr#", mgr.last_name AS "Manager"
FROM employees ang
JOIN employees mgr ON mgr.employee_id = ang.manager_id;
--23
SELECT ang.employee_id AS "Ang#", ang.last_name AS "Angajat",
  mgr.employee_id AS "Mgr#", mgr.last_name AS "Manager"
FROM employees ang
LEFT JOIN employees mgr ON mgr.employee_id = ang.manager_id;
--24
SELECT e.last_name "Angajat",e.department_id,ec.last_name "Coleg"
FROM employees e JOIN employees ec
ON e.department_id=ec.department_id
WHERE e.employee_id<ec.employee_id;
--25
DESC jobs;
SELECT e.last_name,e.job_id,j.job_title,d.department_name,e.salary
FROM employees e
JOIN jobs j ON j.job_id=e.job_id
LEFT JOIN departments d ON d.department_id=e.department_id;

--26
SELECT ang.last_name, ang.hire_date
FROM employees ang JOIN employees Gates
ON Gates.hire_date<ang.hire_date
WHERE Gates.Last_name='Gates';
--27
SELECT e.last_name AS "Angajat", e.hire_date AS "Data_ang",
m.last_name AS "Manager", m.hire_date AS "Data_mgr"
FROM employees e JOIN employees m
ON e.manager_id=m.employee_id
WHERE e.hire_date<m.hire_date;

--1
SELECT e.last_name,to_char(e.hire_date, 'MON'),to_char(e.hire_date, 'YYYY'),
e.hire_date, e.department_id
FROM employees e , employees gates
WHERE e.department_id=gates.department_id AND instr(LOWER(e.last_name),'a')>0
AND gates.last_name='Gates'
AND e.employee_id != gates.employee_id;

--2
SELECT DISTINCT e.employee_id, e.last_name, d.department_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN employees col
ON col.department_id = e.department_id
WHERE LOWER(col.last_name) LIKE '%t%'
ORDER BY e.last_name;