Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Zadatak_1
- SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", d.department_name "Odjel", j.job_title "Posao"
- FROM employees e, departments d, jobs j
- WHERE e.job_id = j.job_id AND e.department_id = d.department_id AND e.first_name NOT LIKE 'Susan' AND
- e.department_id = ( SELECT e1.department_id
- FROM employees e1
- WHERE e1.first_name LIKE 'Susan' );
- -- Nema rezultata za dati upit, probati 'Donald' :)
- -- Zadatak_2
- SELECT e.employee_id "Sifra", e.first_name||' '||e.last_name "Naziv zaposlenog", e.salary "Plata"
- FROM employees e
- WHERE e.salary > ( SELECT Avg(salary)
- FROM employees
- WHERE department_id IN (30, 90) );
- -- Zadatak_3
- SELECT *
- FROM employees
- WHERE department_id IN ( SELECT department_id
- FROM employees
- WHERE first_name LIKE '%C%' );
- -- Zadatak_4
- SELECT e.employee_id "Sifra", e.first_name||' '||e.last_name "Naziv zaposlenog", j.job_title "Posao"
- FROM employees e, jobs j
- WHERE e.job_id = j.job_id AND e.department_id IN ( SELECT d.department_id
- FROM departments d, locations l
- WHERE d.location_id = l.location_id AND l.city LIKE 'Toronto' );
- -- Zadatak_5
- SELECT *
- FROM employees
- WHERE manager_id IN ( SELECT employee_id
- FROM employees
- WHERE last_name LIKE 'King' );
- -- Zadatak_6 (modifikacija upita pod rednim brojem 2)
- SELECT e.employee_id "Sifra", e.first_name||' '||e.last_name "Naziv zaposlenog", e.salary "Plata"
- FROM employees e
- WHERE e.salary > ( SELECT Avg(e1.salary)
- FROM employees e1
- WHERE e.department_id = e1.department_id );
- -- Zadatak_6 (modifikacija upita pod rednim brojem 3)
- SELECT *
- FROM employees
- WHERE salary > ( SELECT Avg(salary)
- FROM employees
- WHERE first_name LIKE '%C%' );
- -- Zadatak_7
- SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", d.department_name "Odjel", e.salary "Plata"
- FROM employees e, departments d
- WHERE e.department_id = d.department_id AND e.department_id IN ( SELECT e1.department_id
- FROM employees e1
- WHERE e.department_id = e1.department_id AND e1.commission_pct IS NOT NULL );
- -- Zadatak_8
- SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", d.department_name "Odjel", e.salary "Plata", l.city "Grad"
- FROM employees e, departments d, locations l
- WHERE e.department_id = d.department_id AND d.location_id = l.location_id
- AND (e.salary, Nvl(e.commission_pct, 0)) IN ( SELECT e1.salary, Nvl(e1.commission_pct, 0)
- FROM employees e1, departments d1, locations l1
- WHERE e1.department_id = d1.department_id AND d1.location_id = l1.location_id AND l1.city LIKE 'Rome' );
- -- Nema rezultata za dati upit, probati 'Seattle' :)
- -- Zadatak_9
- SELECT first_name||' '||last_name "Naziv zaposlenog", hire_date "Datum zaposlenja", salary "Plata"
- FROM employees
- WHERE (salary, Nvl(commission_pct, 0)) IN ( SELECT salary, Nvl(commission_pct, 0)
- FROM employees
- WHERE e.first_name LIKE 'Scott' );
- -- Nema rezultata za dati upit, probati 'Steven' :)
- SELECT * FROM departments;
- -- Zadatak_10
- SELECT first_name||' '||last_name "Naziv zaposlenog"
- FROM employees
- WHERE salary > ( SELECT Max(e.salary)
- FROM employees e, departments d
- WHERE e.department_id = d.department_id AND Lower(d.department_name) LIKE '%sale%' )
- ORDER BY salary DESC;
- -- Zadatak_11
- SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", d.department_name "Odjel", j.job_title "Posao", l.city "Grad"
- FROM employees e, jobs j, departments d, locations l, ( SELECT Avg(b.salary) prosjecna_plata
- FROM employees a, employees b
- WHERE a.manager_id = b.employee_id AND b.commission_pct IS NOT NULL) sefovi
- 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;
- /* Provjeriti: http://pastebin.com/1GRKdkg9 !! */
- -- Zadatak_12
- SELECT e.employee_id "Sifra zaposlenog", e.first_name||' '||e.last_name "Naziv zaposlenog", e.department_id "Sifra odjela", d.department_name "Naziv odjela",
- ( SELECT Ceil(Avg(e1.salary))
- FROM employees e1
- WHERE e.department_id = e1.department_id ) "Prosjecna plata odjela zap.",
- ( SELECT Min(e2.salary)
- FROM employees e2
- WHERE e.department_id = e2.department_id ) "Minimalna plata odjela zap.",
- ( SELECT Max(e3.salary)
- FROM employees e3
- WHERE e.department_id = e3.department_id ) "Maksimalna plata odjela zap.",
- ( SELECT Ceil(Avg(e4.salary))
- FROM employees e4 ) "Prosjecna plata firme",
- ( SELECT Min(e5.salary)
- FROM employees e5 ) "Minimalna plata firme",
- ( SELECT Max(e6.salary)
- FROM employees e6 ) "Maksimalna plata firme"
- FROM employees e, departments d
- WHERE e.department_id = d.department_id AND e.salary > ( SELECT Min(Avg(e7.salary))
- FROM employees e7
- WHERE e.manager_id = e7.employee_id AND e.department_id = d.department_id
- GROUP BY e7.department_id );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement