Untitled

distinct spz

 

set typ = 'O'

 

set nazov_sluzby='svetielka'

 

select first_name,last_name,job_title,department_name from employees join jobs using(job_id) join departments using(department_id) where job_title like '%Manager%';

 

select first_name, last_name, department_name, city from hr.employees e join hr.departments d on d.manager_id = e.employee_id join hr.locations using(location_id) where city like'%Seattle%' ;

 

select department_name, count(*) from hr.employees join hr.departments using(department_id) group by department_name having count(*) between 5 and 10;

 

select department_id, job_id, count(*) from employees where department_id in(50, 80) group by job_id, department_id having count(*) >= 20;

 

select department_name, city, country_name from departments join locations using(location_id) join countries using(county_id) order by 3, 2, 1;

 

select department_name, max(salary) from hr.employees join hr.departments using(department_id) where department_name like 'Finance' group by department_name;

 

select department_name, avg(salary) from hr.employees join hr.departments using(department_id) where department_name = 'Sales' group by department_name;

 

select avg(salary), count(employee_id) from hr.employees where job_id in ('SA_REP');

 

select count(*) from hr.employees where department_id = 50;

 

select min(last_name) from employees;

 

delete from auto where spz like 'KE%';

 

delete auto where typ in('o','O');

 

delete from sluzba where cena_sluzby > 15;

 

select min(cena_sluzby), max(cena_sluzby), avg(cena_sluzby) from sluzba;

 

select spz, count(*) from asz having count(*)>2 group by spz;

 

TEORETICKE:

 

REL.operacia join: kombinovanie suvisiacich zaznamov z dvoch relacii do jednoduchych zaznamov

 

Operacia relacnej algebry projekcia:

        -vekrikalny vyber

        -vyber urcitych atributov z relacie

 

Podmienka s rel. operatormi(<,>,=.....): -theta join

 

Medzi mnozinove operacie rel. algebry patria:

        -rozdiel

        -karteziansky sucin

        -zjednotenie

        -prienik

 

Relacia nad ktorymi sa aplikuje KARTEZIANSKY sucin: nemusia byt kompatibilne vzhladom k zjednoteniu

 

Sucastne tvrdenie pre operacie: zjednotenie, prienik, rozdiel:

        -operacie su kompatibilne vzhladom k zjednoteniu

 

Vysledok operacie zjednotenie (union) R u S je relacia, ktora obsahuje vsetky zaznamy ktore:

        -patria bud do R alebo do S alebo do R aj S. Duplicitne zaznamy su eliminovane

 

Pocet zaznamov vo vysledku projekcie ak zoznam atributov obsahuje kluc relacie:

        -je mensi alebo rovny poctu zaznamov v povodnej relacii. Duplicitne zaznamy su implicitne eliminovane

 

Podmienka spojenia v operacii spojenie je specifikovana:

        -nad atributmi z dvoch relacii R a S, ktore maju rovnaku domenu a maju rovnaky semanticky vyznam

 

Operacia R right outer join S zachovava:

        -kazdy zaznam v druhej relacii S vo vysledku R right outer join S. Ak ziadny zodpovedajuci zaznam       nie je najdeny v relacii R, potom atributy R vo vysledku su vyplnene hodnotami NULL

 

alter table uloha add check(end_date > start_date);

alter table uloha modify end_date date check (end_date > start_date);

 

KONSTRUKCIA databazy je: proces ukladania dat na nejake pamatove medium riadeny SRBD