rezolvate problema 3, day2
select ang.last_name, ang.employee_id,ang.hire_date, dav.hire_date
from employees ang join employees Dav
on (ang.hire_date>dav.hire_date)
where dav.last_name="davies";
where trim(lower( dav.last_name))="davies";
problema 4
select ang.last_name,ang.hire_date,man.last_name, man.hire_date
from employees ang join employees man on (ang.manager_id=man.employee_id)
where ang.hire_date<man.hire_date;
media sal din companie(nesincronizat)
select *
from employees
where salary >(select avg(salary)
from employees);
din propriul departament(sincronizat)
select *
from employees e
where salary >(select avg(salary)
from employees
where department_id=e.department_id)
select count(commission_pct)
from employees;
select count(*)
from test;
select sum(commission_pct)/count(employee_id)
from employees;
create table test (id integer primary key, valoare char(28));
insert into test values(1,NULL), (2,NULL), (3,NULL);
alter table test drop column id;
select*
from test;
set @nr=0;
select *
from employees
where salary in
(select salary
from
(
select salary,(@nr:=@nr+1) crt
from (select distinct salary from employees) sal_distinct
order by salary desc) sal_ordonat
where crt<11);
pdf4
select substr(job_id,4), job_id
from jobs;
ziua 3
exercitiul 1 - 8
select department_id
from departments
where department_id not in
(select department_id
from employees
where upper(job_id)='ST_CLERK')
order by department_id;
exercitiul 2
select country_id, country_name
from countries
where country_id not in
(select country_id
from locations join departments using(location_id));
ex 3
a )select job_id,department_id
from employees
where department_id=10
union
select job_id,department_id
from employees
where department_id=50
union
select job_id,department_id
from employees
where department_id=20
order by department_id;
b) select distinct job_id,department_id
from employees
where department_id in (10,50,20)
order by if (department_id=20,100,department_id);
c) select distinct job_id,department_id
from employees
where department_id in (10,50,20)
order by mod(department_id,14) desc;
d)select distinct job_id,department_id
from employees
where department_id in (10,50,20)
order by case when department_id=10 then 1
when department_id=20 then 3
when department_id=50 then 2 end;
ex4
a) select employee_id,job_id
from employees
where concat(employee_id,job_id)
in
(select concat(employee_id,job_id)
from job_history);
b) select employee_id,job_id
from employees
where concat(employee_id,job_id)
in
(select concat(employee_id,job_id)
from job_history);
ex5
select last_name nume,department_id cod_dep, null nume_dep
from employees
union all
select null, department_id, department_name
from departments;
unit 9
Retrieving Data
SELECT department_name, city
FROM departments
NATURAL JOIN (SELECT l.location_id, l.city, l.country_id
FROM locations l JOIN countries c ON(l.country_id=c.country_id)
JOIN regions USING( region_id)
WHERE region_name ='Europe') as loc;
PRACTICE
(EX7)
problema 7
cerere necorelata
SELECT employee_id
from employees
where employee_id not in
(select manager_id
from employees
where manager_id is not null) ;
PROBLEMA 8
cerere corelata
SELECT *
from employees e
where salary<
(select avg(salary)
from employees
where department_id=e.department_id);
A)select avg(salary),department_id
from employees
where department_id in (50,60,70)
group by department_id;
B)select avg(salary),department_id, COUNT(employee_id)
from employees
where department_id in (50,60,70)
group by department_id
HAVING COUNT(employee_id)>6;
"NOT EXIST"
SELECT employee_id,last_name, job_id,department_id
FROM employees E
WHERE not EXISTS (SELECT 'x'
FROM employees
WHERE manager_id=E.employee_id);
clauza "with"
with depsum as (select sum(salary) sumsal,department_id,department_name
from employees join departments using(department_id)
where department_id is not null
group by department_id),
avgsumsal as (select avg(sumsal) medie
from depsum)
select department_name,sumsal
from depsum,avgsumsal
where sumsal>medie;
Data Manipulation
INSERT Statement
insert into departments (department_id, department_name)
values(400,'dep400'),(410,'dep410');
Copying Rows from Another Table
create table sales_reps as
select employee_id cod, last_name name,salary,commission_pct
from employees
where job_id like '%REP&';
