Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- ZADANIE 1
- SELECT
- first_name, last_name, salary
- FROM employees
- WHERE
- (TO_CHAR(hire_date, 'yyyy') = 1987 AND salary < 1000) OR
- (TO_CHAR(hire_date, 'yyyy') > 1993)
- -- ZADANIE 2
- SELECT
- first_name, last_name,
- (CASE
- WHEN salary>6000 THEN 'TAK'
- ELSE 'NIE'
- END) AS salary_info
- FROM employees;
- -- ZADANIE 3
- SELECT
- d.department_id, round(avg(e.salary), 2) AS avg_salary, j.job_title
- FROM departments d
- LEFT JOIN employees e ON
- e.department_id = e.department_id
- LEFT JOIN jobs j ON
- j.job_id = e.job_id
- GROUP BY d.department_id, j.job_id, j.job_title
- ORDER BY d.department_id, j.job_id ASC
- -- ZADANIE 4
- SELECT
- d.department_id, j.job_title, ROUND(MIN(e.salary),2) AS min_salary, ROUND(MAX(e.salary),2) AS max_salary
- FROM departments d
- LEFT JOIN employees e ON
- e.department_id = d.department_id
- RIGHT JOIN jobs j ON
- j.job_id = e.job_id
- GROUP BY
- d.department_id, j.job_title
- ORDER BY
- j.job_title, d.department_id
- -- ZADANIE 5
- SELECT
- last_name
- FROM employees
- WHERE
- hire_date > (
- SELECT hire_date
- FROM employees
- WHERE
- first_name = 'Alexander' AND
- last_name = 'Hunold'
- )
- -- ZADANIE 6
- SELECT
- last_name
- FROM employees
- WHERE
- job_id = (
- SELECT job_id
- FROM employees
- WHERE
- first_name = 'David' AND
- last_name = 'Austin'
- )
- -- ZADANIE 7
- SELECT
- e.last_name, e1.last_name
- FROM employees e
- LEFT JOIN employees e1 ON
- e1.job_id = e.job_id
- WHERE
- e1.last_name != e.last_name
- GROUP BY
- e.last_name, e1.last_name
- ORDER BY
- e.last_name ASC
- -- ZADANIE 8
- SELECT e.first_name, e.last_name, e.hire_date, l.city
- FROM employees e
- LEFT JOIN departments d ON
- d.department_id = e.department_id
- LEFT JOIN locations l ON
- l.location_id = d.location_id
- WHERE
- d.department_id IN (
- SELECT department_id
- FROM employees
- WHERE
- first_name LIKE '%k%' OR
- last_name LIKE '%k%'
- )
- -- ZADANIE 9
- SELECT
- first_name, last_name, salary
- FROM employees
- WHERE
- salary < (
- SELECT salary
- FROM employees
- WHERE
- last_name = 'Davies'
- )
- -- ZADANIE 10
- SELECT e.first_name, e.last_name, e.salary, l.country_id
- FROM employees e
- LEFT JOIN departments d ON
- d.department_id = e.department_id
- LEFT JOIN locations l ON
- l.location_id = d.location_id
- WHERE
- e.salary = (
- SELECT MIN(e1.salary)
- FROM employees e1
- LEFT JOIN departments d1 ON
- d1.department_id = e1.department_id
- LEFT JOIN locations l1 ON
- l1.location_id = d1.location_id
- WHERE
- l1.country_id = l.country_id
- )
- -- ZADANIE 11
- UPDATE employees SET commission_pct = 0.15
- WHERE commission_pct IS NULL AND
- (employee_id IN (
- SELECT e.employee_id FROM employees e
- LEFT JOIN departments d ON d.department_id = e.department_id
- LEFT JOIN locations l ON l.location_id = d.location_id
- WHERE l.country_id = 'CA'
- ))
- -- ZADANIE 12
- UPDATE employees SET salary = NVL((
- SELECT MAX(e.salary)
- FROM employees e
- LEFT JOIN departments d ON
- d.department_id = e.department_id
- LEFT JOIN locations l ON
- l.location_id = d.location_id
- WHERE
- l.city = 'Seattle' AND e.job_id = 'IT_PROG'
- ),salary)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement