SHARE
TWEET

Untitled

a guest Aug 7th, 2018 82 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DISTINCT spz
  2.  
  3.  
  4. SET typ = 'O'
  5.  
  6.  
  7.  
  8. SET nazov_sluzby='svetielka'
  9.  
  10.  
  11.  
  12. 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%';
  13.  
  14.  
  15.  
  16. 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%' ;
  17.  
  18.  
  19.  
  20. SELECT department_name, COUNT(*) FROM hr.employees join hr.departments USING(department_id) GROUP BY department_name HAVING COUNT(*) BETWEEN 5 AND 10;
  21.  
  22.  
  23.  
  24. SELECT department_id, job_id, COUNT(*) FROM employees WHERE department_id IN(50, 80) GROUP BY job_id, department_id HAVING COUNT(*) >= 20;
  25.  
  26.  
  27.  
  28. SELECT department_name, city, country_name FROM departments join locations USING(location_id) join countries USING(county_id) ORDER BY 3, 2, 1;
  29.  
  30.  
  31.  
  32. SELECT department_name, MAX(salary) FROM hr.employees join hr.departments USING(department_id) WHERE department_name LIKE 'Finance' GROUP BY department_name;
  33.  
  34.  
  35.  
  36. SELECT department_name, AVG(salary) FROM hr.employees join hr.departments USING(department_id) WHERE department_name = 'Sales' GROUP BY department_name;
  37.  
  38.  
  39.  
  40. SELECT AVG(salary), COUNT(employee_id) FROM hr.employees WHERE job_id IN ('SA_REP');
  41.  
  42.  
  43.  
  44. SELECT COUNT(*) FROM hr.employees WHERE department_id = 50;
  45.  
  46.  
  47.  
  48. SELECT MIN(last_name) FROM employees;
  49.  
  50.  
  51.  
  52. DELETE FROM auto WHERE spz LIKE 'KE%';
  53.  
  54.  
  55.  
  56. DELETE auto WHERE typ IN('o','O');
  57.  
  58.  
  59.  
  60. DELETE FROM sluzba WHERE cena_sluzby > 15;
  61.  
  62.  
  63.  
  64. SELECT MIN(cena_sluzby), MAX(cena_sluzby), AVG(cena_sluzby) FROM sluzba;
  65.  
  66.  
  67.  
  68. SELECT spz, COUNT(*) FROM asz HAVING COUNT(*)>2 GROUP BY spz;
  69.  
  70.  SELECT COUNT(*) FROM auto WHERE spz IN (SELECT DISTINCT spz FROM asz);
  71.  
  72. 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;
  73.  
  74. SELECT spz, nazov_sluzby, priezvisko
  75. FROM auto left join asz USING(spz)
  76. left join sluzba USING(kod_sluzby) left join zam USING(evc) ORDER BY 1;
  77.  
  78. SELECT COUNT (DISTINCT job_id) FROM hr.jobs;
  79.  
  80.  
  81.  
  82. TEORETICKE:
  83.  
  84.  
  85.  
  86. REL.operacia join: kombinovanie suvisiacich zaznamov z dvoch relacii DO jednoduchych zaznamov
  87.  
  88.  
  89.  
  90. Operacia relacnej algebry projekcia:
  91.  
  92.         -vekrikalny vyber
  93.  
  94.         -vyber urcitych atributov z relacie
  95.  
  96.  
  97.  
  98. Podmienka s rel. operatormi(<,>,=.....): -theta join
  99.  
  100.  
  101.  
  102. Medzi mnozinove operacie rel. algebry patria:
  103.  
  104.         -rozdiel
  105.  
  106.         -karteziansky sucin
  107.  
  108.         -zjednotenie
  109.  
  110.         -prienik
  111.  
  112.  
  113.  
  114. Relacia nad ktorymi sa aplikuje KARTEZIANSKY sucin: nemusia byt kompatibilne vzhladom k zjednoteniu
  115.  
  116.  
  117.  
  118. Sucastne tvrdenie pre operacie: zjednotenie, prienik, rozdiel:
  119.  
  120.         -operacie su kompatibilne vzhladom k zjednoteniu
  121.  
  122.  
  123.  
  124. Vysledok operacie zjednotenie (UNION) R u S je relacia, ktora obsahuje vsetky zaznamy ktore:
  125.  
  126.         -patria bud DO R alebo DO S alebo DO R aj S. Duplicitne zaznamy su eliminovane
  127.  
  128.  
  129.  
  130. Pocet zaznamov vo vysledku projekcie ak zoznam atributov obsahuje kluc relacie:
  131.  
  132.         -je mensi alebo rovny poctu zaznamov v povodnej relacii. Duplicitne zaznamy su implicitne eliminovane
  133.  
  134.  
  135.  
  136. Podmienka spojenia v operacii spojenie je specifikovana:
  137.  
  138.         -nad atributmi z dvoch relacii R a S, ktore maju rovnaku domenu a maju rovnaky semanticky vyznam
  139.  
  140.  
  141.  
  142. Operacia R right outer join S zachovava:
  143.  
  144.         -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
  145.  
  146.  
  147.  
  148. ALTER TABLE uloha ADD CHECK(end_date > start_date);
  149.  
  150. ALTER TABLE uloha MODIFY end_date DATE CHECK (end_date > start_date);
  151.  
  152.  
  153.  
  154. KONSTRUKCIA databazy je: proces ukladania dat na nejake pamatove medium riadeny SRBD
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top