Advertisement
mali_cox

t5

Jan 24th, 2017
143
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 5.81 KB | None | 0 0
  1.  
  2. -- Zadatak_1
  3. SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", d.department_name "Odjel", j.job_title "Posao"
  4. FROM employees e, departments d, jobs j
  5. WHERE e.job_id = j.job_id AND e.department_id = d.department_id AND e.first_name NOT LIKE 'Susan' AND
  6.       e.department_id = ( SELECT e1.department_id
  7.                           FROM employees e1
  8.                           WHERE e1.first_name LIKE 'Susan' );
  9. -- Nema rezultata za dati upit, probati 'Donald' :)
  10.  
  11. -- Zadatak_2
  12. SELECT e.employee_id "Sifra", e.first_name||' '||e.last_name "Naziv zaposlenog", e.salary "Plata"
  13. FROM employees e
  14. WHERE e.salary > ( SELECT Avg(salary)
  15.                    FROM employees
  16.                    WHERE department_id IN (30, 90) );
  17.  
  18. -- Zadatak_3
  19. SELECT *
  20. FROM employees
  21. WHERE department_id IN ( SELECT department_id
  22.                          FROM employees
  23.                          WHERE first_name LIKE '%C%' );
  24.  
  25. -- Zadatak_4                                                          
  26. SELECT e.employee_id "Sifra", e.first_name||' '||e.last_name "Naziv zaposlenog", j.job_title "Posao"
  27. FROM employees e, jobs j
  28. WHERE e.job_id = j.job_id AND e.department_id IN ( SELECT d.department_id
  29.                                                    FROM departments d, locations l
  30.                                                    WHERE d.location_id = l.location_id AND l.city LIKE 'Toronto' );
  31.  
  32. -- Zadatak_5
  33. SELECT *
  34. FROM employees
  35. WHERE manager_id IN ( SELECT employee_id
  36.                       FROM employees
  37.                       WHERE last_name LIKE 'King' );
  38.  
  39. -- Zadatak_6 (modifikacija upita pod rednim brojem 2)
  40. SELECT e.employee_id "Sifra", e.first_name||' '||e.last_name "Naziv zaposlenog", e.salary "Plata"
  41. FROM employees e
  42. WHERE e.salary > ( SELECT Avg(e1.salary)
  43.                    FROM employees e1
  44.                    WHERE e.department_id = e1.department_id );
  45.  
  46. --  Zadatak_6 (modifikacija upita pod rednim brojem 3)
  47. SELECT *
  48. FROM employees
  49. WHERE salary > ( SELECT Avg(salary)
  50.                          FROM employees
  51.                          WHERE first_name LIKE '%C%' );
  52.  
  53. -- Zadatak_7
  54. SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", d.department_name "Odjel", e.salary "Plata"
  55. FROM employees e, departments d
  56. WHERE e.department_id = d.department_id AND e.department_id IN ( SELECT e1.department_id
  57.                                                                  FROM employees e1
  58.                                                                  WHERE e.department_id = e1.department_id AND e1.commission_pct IS NOT NULL );
  59.  
  60. -- Zadatak_8
  61. SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", d.department_name "Odjel", e.salary "Plata", l.city "Grad"
  62. FROM employees e, departments d, locations l
  63. WHERE e.department_id = d.department_id AND d.location_id = l.location_id
  64.       AND (e.salary, Nvl(e.commission_pct, 0)) IN ( SELECT e1.salary, Nvl(e1.commission_pct, 0)
  65.                                                     FROM employees e1, departments d1, locations l1
  66.                                                     WHERE e1.department_id = d1.department_id AND d1.location_id = l1.location_id AND l1.city LIKE 'Rome' );
  67. -- Nema rezultata za dati upit, probati 'Seattle' :)
  68.  
  69. -- Zadatak_9
  70. SELECT first_name||' '||last_name "Naziv zaposlenog", hire_date "Datum zaposlenja", salary "Plata"
  71. FROM employees
  72. WHERE (salary, Nvl(commission_pct, 0)) IN ( SELECT salary, Nvl(commission_pct, 0)
  73.                                             FROM employees
  74.                                             WHERE e.first_name LIKE 'Scott' );
  75. -- Nema rezultata za dati upit, probati 'Steven' :)
  76.  
  77. SELECT * FROM departments;
  78.  
  79. -- Zadatak_10
  80. SELECT first_name||' '||last_name "Naziv zaposlenog"
  81. FROM employees
  82. WHERE salary > ( SELECT Max(e.salary)
  83.                  FROM employees e, departments d
  84.                  WHERE e.department_id = d.department_id AND Lower(d.department_name) LIKE '%sale%' )
  85. ORDER BY salary DESC;
  86.  
  87. -- Zadatak_11
  88. SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", d.department_name "Odjel", j.job_title "Posao", l.city "Grad"
  89. FROM employees e, jobs j, departments d, locations l, ( SELECT Avg(b.salary) prosjecna_plata
  90.                                                         FROM employees a, employees b
  91.                                                         WHERE a.manager_id = b.employee_id AND b.commission_pct IS NOT NULL) sefovi
  92. WHERE e.job_id = j.job_id AND e.department_id = d.department_id AND d.location_id = l.location_id AND e.salary > sefovi.prosjecna_plata;
  93. /* Provjeriti: http://pastebin.com/1GRKdkg9 !! */
  94.  
  95. -- Zadatak_12
  96. SELECT e.employee_id "Sifra zaposlenog", e.first_name||' '||e.last_name "Naziv zaposlenog", e.department_id "Sifra odjela", d.department_name "Naziv odjela",
  97.        ( SELECT Ceil(Avg(e1.salary))
  98.          FROM employees e1
  99.          WHERE e.department_id = e1.department_id ) "Prosjecna plata odjela zap.",
  100.  
  101.        ( SELECT Min(e2.salary)
  102.          FROM employees e2
  103.          WHERE e.department_id = e2.department_id ) "Minimalna plata odjela zap.",
  104.  
  105.        ( SELECT Max(e3.salary)
  106.          FROM employees e3
  107.          WHERE e.department_id = e3.department_id ) "Maksimalna plata odjela zap.",
  108.  
  109.        ( SELECT Ceil(Avg(e4.salary))
  110.          FROM employees e4 ) "Prosjecna plata firme",
  111.  
  112.        ( SELECT Min(e5.salary)
  113.          FROM employees e5 ) "Minimalna plata firme",
  114.  
  115.        ( SELECT Max(e6.salary)
  116.          FROM employees e6 ) "Maksimalna plata firme"
  117.  
  118. FROM employees e, departments d
  119. WHERE e.department_id = d.department_id AND e.salary > ( SELECT Min(Avg(e7.salary))
  120.                                                          FROM employees e7
  121.                                                          WHERE e.manager_id = e7.employee_id AND e.department_id = d.department_id
  122.                                                          GROUP BY e7.department_id );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement