BD LAB 1 rezolvat

http://193.226.51.37/down/SQL/Laborator1_SQL_an2.pdf

--lab 1
--ex 3
DESC departments;

--ex4
SELECT *
FROM countries;

--ex5
SELECT employee_id, last_name, job_id, hire_date
FROM employees;

--ex6a
SELECT  job_id
FROM employees;

--ex6b
SELECT DISTINCT job_id
FROM employees;

--ex7
SELECT last_name|| ', ' || job_id AS "Angajat si titlu"
FROM employees;

--ex9
SELECT last_name, salary
FROM employees
WHERE salary > 2850;

--ex10
SELECT last_name, department_id
FROM employees
WHERE employee_id = 104;

--ex11
SELECT last_name, salary
FROM employees
--where salary >2850 or salary <1500;
WHERE salary NOT BETWEEN 1500 AND 2850;

--ex12
SELECT last_name, job_id, hire_date
FROM employees
WHERE hire_date BETWEEN '20-FEB-1987' AND '1-MAY-1989'
ORDER BY hire_date;

--ex13
SELECT last_name, department_id
FROM employees
WHERE department_id IN (10,30)
ORDER BY last_name;

--ex14
SELECT last_name AS "Angajat", salary AS "Salariu lunar"
FROM employees
WHERE department_id IN (10,30) AND salary > 3500;

--ex15
SELECT sysdate
FROM dual;

--ex16a
SELECT last_name, hire_date
FROM employees
WHERE hire_date LIKE ('%87%');

--ex16b
SELECT last_name, hire_date
FROM employees
WHERE to_char(hire_date,'YYYY') = 1987;

--ex 17
SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;

--ex18
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC, commission_pct DESC;

--ex19
SELECT last_name, salary, commission_pct
FROM employees
ORDER BY salary DESC, commission_pct DESC;

--ex20
SELECT last_name
FROM employees
WHERE last_name LIKE ('___a%');

--ex21
SELECT first_name
FROM employees
WHERE first_name LIKE ('%l%l%') AND (department_id = 30 OR manager_id = 101);

--ex22
SELECT first_name, job_id, salary
FROM employees
WHERE (job_id LIKE '%CLERK%' OR job_id LIKE '%REP%') AND salary NOT IN (1000,2000,3000) ;

--ex23
SELECT first_name, job_id, commission_pct*salary
FROM employees
WHERE salary > (salary*commission_pct)*5;