Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --lab 3
- --ex1
- SELECT emp.last_name, to_char(emp.hire_date, 'MONTH'), to_char(emp.hire_date, 'YYYY')
- FROM employees emp
- JOIN employees gates ON emp.department_id = gates.department_id
- WHERE emp.last_name LIKE ('%a%') AND gates.last_name LIKE ('Gates')
- AND emp.last_name NOT LIKE ('Gates');
- --ex1 v2
- SELECT e.last_name, TO_CHAR(e.hire_date,'MON'), TO_CHAR(e.hire_date,'YYYY'), e.department_id, e.hire_date
- FROM employees e JOIN employees Gates
- ON e.department_id = gates.department_id
- WHERE LOWER(e.last_name) LIKE '%a%' AND Gates.last_name = 'Gates'
- AND e.employee_id != Gates.employee_id;
- --ex1 v2 metoda 2
- SELECT e.last_name, TO_CHAR(e.hire_date,'MON'), TO_CHAR(e.hire_date,'YYYY'), e.department_id, e.hire_date
- FROM employees e JOIN employees Gates
- ON e.department_id = gates.department_id
- WHERE instr(LOWER(e.last_name),'a') > 0 AND Gates.last_name = 'Gates'
- AND e.employee_id != Gates.employee_id;
- --ex2
- SELECT DISTINCT emp.employee_id, emp.last_name, dep.department_id, dep.department_name
- FROM employees emp JOIN departments dep
- ON emp.department_id = dep.department_id
- JOIN employees coleg
- ON coleg.department_id = emp.department_id
- WHERE coleg.last_name LIKE ('%t%')
- ORDER BY emp.last_name;
- --ex3
- SELECT last_name, salary, job_title, city, country_name
- FROM employees emp JOIN jobs
- ON emp.job_id = jobs.job_id
- JOIN departments dep
- ON emp.department_id = dep.department_id
- JOIN locations loc
- ON dep.location_id = loc.location_id
- JOIN countries cnt
- ON loc.country_id = cnt.country_id
- WHERE emp.manager_id = (
- SELECT emp.employee_id
- FROM employees emp
- WHERE emp.last_name LIKE ('King') AND emp.manager_id IS NULL
- )
- ORDER BY emp.last_name;
- --ex4
- SELECT dep.department_id, dep.department_name, emp.last_name, job_title, to_char(salary,'$99,999.99')
- FROM departments dep JOIN employees emp
- ON dep.department_id = emp.department_id
- JOIN jobs
- ON emp.job_id = jobs.job_id
- WHERE LOWER(dep.department_name) LIKE ('%ti%')
- ORDER BY department_name, last_name;
- --ex5
- SELECT emp.last_name, emp.department_id, dep.department_name, loc.city, jobs.job_title
- FROM employees emp
- JOIN departments dep ON emp.department_id = dep.department_id
- JOIN locations loc ON dep.location_id = loc.location_id
- JOIN jobs ON emp.job_id = jobs.job_id
- WHERE dep.location_id = (
- SELECT dep.location_id
- FROM departments dep
- JOIN locations loc
- ON dep.location_id = loc.location_id
- WHERE loc.city LIKE ('Oxford')
- );
- --ex6
- SELECT employee_id, last_name, salary
- FROM employees emp
- JOIN jobs ON emp.job_id = jobs.job_id
- WHERE salary > (min_salary + max_salary)/2 AND emp.department_id IN
- ( SELECT department_id
- FROM employees
- WHERE last_name LIKE ('%t')
- );
- --ex7
- SELECT emp.last_name, dep.department_name
- FROM departments dep
- RIGHT OUTER JOIN employees emp ON dep.department_id = emp.department_id;
- --ex7v2
- SELECT
- e.last_name,
- d.department_name
- FROM
- employees e
- RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
- --ex8
- SELECT dep.department_name, emp.last_name
- FROM departments dep
- LEFT OUTER JOIN employees emp ON dep.department_id = emp.department_id;
- --ex9
- SELECT dep.department_name, emp.last_name
- FROM departments dep
- FULL OUTER JOIN employees emp ON dep.department_id = emp.department_id;
- --ex10
- SELECT department_id, department_name
- FROM departments
- WHERE LOWER(department_name) LIKE ('%re%') OR department_id IN
- ( SELECT department_id
- FROM employees
- WHERE job_id LIKE ('SA_REP')
- );
- --ex10 v2
- SELECT department_id
- FROM departments
- WHERE LOWER(department_name) LIKE ('%re%')
- UNION
- SELECT department_id
- FROM employees
- WHERE LOWER(job_id) = 'sa_rep';
- --ex12
- SELECT department_id
- FROM departments
- minus
- SELECT department_id
- FROM employees;
- --ex12 v2
- SELECT department_id
- FROM departments
- WHERE manager_id IS NULL;
- --ex13
- SELECT department_id
- FROM departments
- WHERE LOWER(department_name) LIKE ('%re%')
- INTERSECT
- SELECT department_id
- FROM employees
- WHERE LOWER(job_id) = 'hr_rep';
- --ex13v2
- SELECT department_id
- FROM departments dep FULL OUTER JOIN employees emp
- WHERE dep.department_id;
- --ex14
- SELECT employee_id, job_id, last_name, salary
- FROM employees JOIN jobs USING (job_id)
- WHERE salary > 3000 OR salary = (min_salary + max_salary)/2;
- --ex14 v2
- SELECT employee_id, job_id, last_name
- FROM employees
- WHERE salary > 3000
- UNION
- SELECT employee_id, j.job_id, last_name
- FROM employees e
- JOIN jobs j ON e.job_id = j.job_id
- WHERE salary = (min_salary + max_salary )/2;
- --ex15
- SELECT last_name, hire_date
- FROM employees
- WHERE hire_date > ( SELECT hire_date
- FROM employees
- WHERE last_name = 'Gates' );
- --ex16
- SELECT last_name, salary
- FROM employees
- WHERE department_id = ( SELECT department_id
- FROM employees
- WHERE last_name LIKE ('Gates')
- )
- AND last_name != 'Gates';
- --ex17
- SELECT last_name, salary
- FROM employees
- WHERE manager_id = ( SELECT employee_id
- FROM employees
- WHERE manager_id IS NULL);
- --ex18
- SELECT last_name, department_id, salary
- FROM employees
- WHERE (department_id,salary) IN (SELECT department_id, salary
- FROM employees
- WHERE commission_pct IS NOT NULL);
- --ex19
- -- e gata facuta
- --ex20
- SELECT last_name, salary
- FROM employees
- WHERE salary >ALL (SELECT salary
- FROM employees
- WHERE LOWER(job_id) LIKE ('%clerk%')
- )
- ORDER BY salary DESC;
- --ex21
- SELECT last_name, department_name, salary,emp.manager_id
- FROM employees emp JOIN departments dep ON emp.department_id = dep.department_id
- WHERE emp.commission_pct IS NULL AND emp.manager_id IN ( SELECT manager_id
- FROM employees
- WHERE commission_pct IS NOT NULL);
- --ex22
- SELECT last_name, department_id, salary, job_id
- FROM employees
- WHERE (salary,commission_pct) IN ( SELECT salary, commission_pct
- FROM employees emp JOIN departments dep
- ON emp.department_id = dep.department_id
- JOIN locations loc
- ON dep.location_id = loc.location_id
- WHERE city LIKE ('Oxford')
- );
- --ex23
- SELECT last_name, department_id, job_id
- FROM employees
- WHERE department_id IN ( SELECT department_id
- FROM departments dep JOIN locations loc
- ON dep.location_id = loc.location_id
- WHERE loc.city LIKE ('Toronto')
- );
- --lab 4
- --ex2
- SELECT
- MIN(salary) AS "Minim",
- MAX(salary) AS "Maxim",
- SUM(salary) AS "Suma",
- round(AVG(salary)) AS "Media"
- FROM
- employees;
- --ex3
- SELECT job_id, MIN(salary), MAX(salary), SUM(salary), avg(salary)
- FROM employees
- GROUP BY job_id;
- --ex4
- SELECT job_id, COUNT(employee_id)
- FROM employees
- GROUP BY job_id;
- --ex5
- SELECT COUNT(DISTINCT manager_id) AS "Nr. manageri"
- FROM employees;
- --ex6
- SELECT MAX(salary) - MIN(salary) AS "Diferenta"
- FROM employees;
- --ex7
- SELECT department_name, city, COUNT(employee_id), avg(salary)
- FROM employees emp
- JOIN departments dep USING (department_id)
- JOIN locations loc USING (location_id)
- GROUP BY department_name, city;
- --ex8
- SELECT employee_id, last_name
- FROM employees
- WHERE salary > (
- SELECT avg(salary)
- FROM employees
- )
- ORDER BY salary DESC;
- --ex9
- SELECT manager_id, MIN(salary)
- FROM employees
- WHERE manager_id IS NOT NULL
- GROUP BY manager_id, salary
- HAVING MIN(salary) > 1000
- ORDER BY 2 DESC;
- --ex10
- SELECT department_id, department_name, MAX(salary)
- FROM departments dep JOIN employees emp USING (department_id)
- GROUP BY department_id, department_name
- HAVING MAX(salary) > 3000;
- --ex11
- SELECT round(avg(MIN(salary)))
- FROM employees
- GROUP BY job_id;
- --ex12
- SELECT department_id, department_name, SUM(salary)
- FROM departments dep JOIN employees emp USING (department_id)
- GROUP BY department_id, department_name;
- --ex13
- SELECT round(MAX(avg(salary)))
- FROM employees
- GROUP BY department_id;
- --ex14
- SELECT job_id, job_title, avg(salary)
- FROM jobs JOIN employees emp USING (job_id)
- GROUP BY job_id, job_title
- HAVING avg(salary) = (SELECT MIN(avg(salary))
- FROM employees
- GROUP BY job_id);
- --ex 16
- SELECT department_id, job_id, SUM(salary)
- FROM employees
- GROUP BY department_id, job_id
- ORDER BY department_id;
- --ex 18a
- SELECT department_id, department_name, COUNT(employee_id)
- FROM departments dep JOIN employees emp USING (department_id)
- GROUP BY department_id, department_name
- HAVING COUNT(employee_id) < 4;
- --ex 18b
- SELECT department_id, department_name, COUNT(employee_id)
- FROM departments dep JOIN employees emp USING (department_id)
- GROUP BY department_id, department_name
- HAVING COUNT(employee_id) = ( SELECT MAX(COUNT(employee_id))
- FROM employees
- GROUP BY department_id);
- --ex 20 v2
- SELECT COUNT(COUNT(department_id))
- FROM employees
- GROUP BY department_id
- HAVING COUNT(employee_id) > 15;
- --ex 21
- SELECT department_id, SUM(salary)
- FROM employees
- WHERE department_id != 30
- GROUP BY department_id
- HAVING COUNT(employee_id) > 10
- ORDER BY SUM(salary);
- --ex 22
- SELECT department_id, department_name, COUNT(employee_id), avg(salary), last_name, salary, job_id
- FROM departments dep JOIN employees emp USING (department_id)
- GROUP BY department_id, department_name;
- --ex23
- SELECT city, department_name, job_id, SUM(salary)
- FROM employees emp JOIN departments dep USING (department_id)
- JOIN locations loc USING (location_id)
- WHERE department_id > 80
- GROUP BY department_name, job_id, city;
- --ex24
- SELECT employee_id
- FROM job_history
- GROUP BY employee_id
- HAVING COUNT(employee_id) >= 2;
- --ex25
- SELECT avg(nvl(commission_pct,0))
- FROM employees;
- --ex28
- SELECT COUNT(employee_id),
- (SELECT COUNT(employee_id)
- FROM employees
- WHERE to_char(hire_date, 'yyyy') = 1997) AS "1997"
- ,
- (SELECT COUNT(employee_id)
- FROM employees
- WHERE to_char(hire_date, 'yyyy') = 1998) AS "1998"
- ,
- (SELECT COUNT(employee_id)
- FROM employees
- WHERE to_char(hire_date, 'yyyy') = 1999) AS "1999"
- FROM employees
- GROUP BY 'orice';
- --lab5
- --ex3
- SELECT department_name, job_id, emp.manager_id, MAX(salary), SUM(salary)
- FROM employees emp JOIN departments dep USING (department_id)
- GROUP BY GROUPING sets((department_name,job_id), (job_id,emp.manager_id), () );
- --ex4
- SELECT MAX(salary)
- FROM employees
- HAVING MAX(salary)>15000;
- --ex5
- SELECT last_name
- FROM employees emp
- WHERE salary >= (SELECT avg(salary)
- FROM employees col
- WHERE col.department_id = emp.department_id
- AND col.employee_id != emp.employee_id);
- --ex6
- SELECT last_name, salary
- FROM employees
- WHERE salary >ALL (SELECT (avg(salary))
- FROM employees
- GROUP BY department_id
- );
- --ex7 subcerere nesinc
- SELECT last_name, salary
- FROM employees
- WHERE (salary, department_id) IN (SELECT MIN(salary), department_id
- FROM employees
- GROUP BY department_id);
- --ex7 subcerere sinc
- SELECT last_name, salary
- FROM employees ext
- WHERE salary = (SELECT MIN(salary)
- FROM employees
- WHERE department_id = ext.department_id
- GROUP BY department_id);
- --ex8
- SELECT department_name, last_name
- FROM employees emp JOIN departments dep ON emp.department_id = dep.department_id
- WHERE hire_date = (SELECT MIN(hire_date)
- FROM employees xx
- WHERE xx.department_id = emp.department_id
- GROUP BY department_id);
- --ex10
- SELECT last_name, salary
- FROM (
- SELECT *
- FROM employees
- ORDER BY salary)
- WHERE rownum <=3
- ORDER BY salary DESC;
- --ex11
- SELECT employee_id, last_name, first_name
- FROM employees ext
- WHERE 2 <= (
- SELECT COUNT(employee_id)
- FROM employees
- WHERE ext.manager_id = manager_id
- GROUP BY manager_id
- );
- --ex12
- SELECT city, COUNT(department_name)
- FROM locations loc JOIN departments dep ON (loc.location_id=dep.location_id)
- WHERE loc.location_id IN (SELECT DISTINCT location_id
- FROM departments
- )
- GROUP BY city;
- --ex13
- SELECT department_id
- FROM departments
- WHERE department_id NOT IN (SELECT department_id
- FROM employees)
- GROUP BY department_id;
- --lab 7
- --ex1
- CREATE TABLE emp_cpi AS SELECT * FROM employees;
- CREATE TABLE dept_cpi AS SELECT * FROM departments;
- --ex2
- DESC emp_cpi;
- DESC dept_cpi;
- --ex3
- SELECT *
- FROM dept_cpi;
- --ex4
- ALTER TABLE emp_cpi
- ADD CONSTRAINT pk_emp_cpi PRIMARY KEY(employee_id);
- ALTER TABLE dept_cpi
- ADD CONSTRAINT pk_dept_cpi PRIMARY KEY(department_id);
- ALTER TABLE emp_cpi
- ADD CONSTRAINT fk_emp_dept_cpi
- FOREIGN KEY(department_id) REFERENCES dept_cpi(department_id);
- --ex5
- INSERT INTO dept_cpi
- VALUES (300,'Programare');
- INSERT INTO dept_cpi(department_id, department_name)
- VALUES (300,'Programare');
- INSERT INTO dept_cpi(department_name, department_id)
- VALUES (300,'Programare');
- INSERT INTO dept_cpi(department_id, department_name, location_id)
- VALUES(300,'Programare', NULL);
- INSERT INTO dept_cpi(department_name, location_id)
- VALUES('Programare',NULL);
- --ex6
- INSERT INTO emp_cpi
- VALUES(250,'Andrei','Ion','mailmail@gmail.com',NULL,to_date('24-12-2018','DD-MM-YYYY'),'SA_REP',1500,NULL,NULL,300);
- commit;
- --ex7
- INSERT INTO emp_cpi(employee_id,last_name,email,hire_date,job_id,department_id)
- VALUES(305,'Voinea','voineagabi@gmail.com',sysdate,'Aprozar',300);
- --ex9
- CREATE TABLE emp1_cpi AS
- SELECT *
- FROM employees;
- DELETE FROM emp1_cpi;
- INSERT INTO emp1_cpi
- SELECT *
- FROM employees
- WHERE commission_pct > 0.25;
- commit;
- --ex 10
- SELECT USER FROM dual;
- INSERT INTO emp_cpi(employee_id, last_name, email, hire_date, job_id, salary, commission_pct, department_id)
- SELECT 0, USER, 'total', sysdate, 'total', SUM(salary), avg(commission_pct),NULL
- FROM employees;
- --ex12
- CREATE TABLE emp2_cpi AS
- SELECT *
- FROM employees;
- CREATE TABLE emp3_cpi AS
- SELECT *
- FROM employees;
- DELETE FROM emp2_cpi;
- DELETE FROM emp3_cpi;
- INSERT INTO emp1_cpi
- SELECT *
- FROM employees
- WHERE salary < 5000;
- INSERT INTO emp2_cpi
- SELECT *
- FROM employees
- WHERE salary BETWEEN 5000 AND 10000;
- INSERT INTO emp3_cpi
- SELECT *
- FROM employees
- WHERE salary >10000;
- DELETE FROM emp1_cpi;
- DELETE FROM emp2_cpi;
- DELETE FROM emp3_cpi;
- --ex13
- CREATE TABLE emp0_cpi AS
- SELECT *
- FROM employees;
- DELETE FROM emp0_cpi;
- INSERT FIRST
- WHEN department_id = 80 THEN INTO emp0_cpi
- WHEN salary < 5000 THEN INTO emp1_cpi
- WHEN salary BETWEEN 5000 AND 10000 THEN INTO emp2_cpi
- WHEN salary > 10000 THEN INTO emp3_cpi
- SELECT *
- FROM employees;
- commit;
- --ex14
- UPDATE emp_cpi
- SET salary = salary * 1.05;
- ROLLBACK;
- --ex15
- UPDATE emp_cpi
- SET job_id = 'SA_REP'
- WHERE commission_pct > 0 AND department_id = 80;
- ROLLBACK;
- --LAB 8
- --ex1
- CREATE TABLE angajati_cpi ( cod_ang NUMBER(4),
- nume varchar2(20) CONSTRAINT nume_nn NOT NULL,
- prenume varchar2(20),
- email CHAR(15),
- data_ang DATE,
- job varchar2(10),
- cod_sef NUMBER(4),
- salariu NUMBER(8,2) CONSTRAINT salariu_nn NOT NULL,
- cod_dep NUMBER(2),
- CONSTRAINT cod_ang_pk PRIMARY KEY(cod_ang)
- );
- DROP TABLE angajati_cpi;
- --ex2
- INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
- VALUES(100, 'nume1', 'prenume1', NULL, NULL, 'Director', NULL, 20000, 10);
- INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
- VALUES (101, 'nume2', 'prenume2', 'nume2', to_date('02-02-2004', 'dd-mm-yyyy'), 'inginer', 100, 10000, 10);
- INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
- VALUES(102, 'nume3', 'prenume3', 'nume3', to_date('05-06-2000', 'dd-mm-yyyy'), 'analist', 101, 5000, 20);
- DELETE FROM angajati_cst
- WHERE cod_ang = 101;
- DELETE FROM angajati_cst
- WHERE cod_ang = 102;
- INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
- VALUES (101, 'nume2', 'prenume2', 'nume2', to_date('02-02-2004', 'dd-mm-yyyy'), 'inginer', 100, 10000, 10);
- INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
- VALUES(102, 'nume3', 'prenume3', 'nume3', to_date('05-06-2000', 'dd-mm-yyyy'), 'analist', 101, 5000, 20);
- INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
- VALUES (103, 'nume4', 'prenume4', NULL, NULL, 'inginer', 100, 90000, 20);
- INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
- VALUES(104, 'nume5', 'prenume5', 'nume5', NULL, 'analist', 101, 3000, 30);
- DELETE FROM angajati_cst
- WHERE cod_ang = 104;
- --ex3
- CREATE TABLE angajati10_cpi AS
- SELECT *
- FROM angajati_cpi
- WHERE cod_dep = 10;
- --ex4
- ALTER TABLE angajati_cpi
- ADD comision NUMBER(4,2);
- --ex5
- ALTER TABLE angajati_cpi
- MODIFY salariu NUMBER(6,2);
- --ex6
- ALTER TABLE angajati_cpi
- MODIFY salariu NUMBER DEFAULT 32121;
- --ex7
- ALTER TABLE angajati_cpi
- MODIFY (comision NUMBER(2,2), salariu NUMBER(10,2));
- --ex8
- UPDATE angajati_cpi
- SET comision = 0.1
- WHERE LOWER(job) LIKE 'a%';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement