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;

 SELECT COUNT(*) FROM auto WHERE spz IN (SELECT DISTINCT spz FROM asz);

SELECT city,AVG(salary) FROM hr.employees join hr.departments USING(department_id) join hr.locations USING(location_id) GROUP BY city ORDER BY 1;

SELECT spz, nazov_sluzby, priezvisko
FROM auto left join asz USING(spz)
left join sluzba USING(kod_sluzby) left join zam USING(evc) ORDER BY 1;

SELECT COUNT (DISTINCT job_id) FROM hr.jobs;



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