--1
CREATE TABLE employees AS SELECT  * FROM hr.employees;
CREATE TABLE department AS SELECT  * FROM hr.departments;
CREATE TABLE countries AS SELECT  * FROM hr.countries;
CREATE TABLE jobs AS SELECT  * FROM hr.jobs;
CREATE TABLE locations AS SELECT  * FROM hr.locations;
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

ALTER TABLE jobs
ADD PRIMARY KEY (job_id);

ALTER TABLE location
ADD PRIMARY KEY (location_id);

ALTER TABLE departments
ADD PRIMARY KEY (department_id);

ALTER TABLE employees
ADD FOREIGN KEY (department_id) REFERENCES departments (department_id);

ALTER TABLE employees
ADD FOREIGN KEY (job_id) REFERENCES jobs (job_id);

ALTER TABLE employees
ADD FOREIGN KEY (location_id) REFERENCES locations (location_id);

--2
CREATE VIEW dane60 AS SELECT * FROM employees
WHERE department_id = 60;

--3
SELECT * FROM dane60;

UPDATE dane60
SET salary = salary*1.1;

UPDATE dane60 SET salary = salary*1.1
WHERE department_id = 90;

--4
CREATE VIEW daneShiping AS SELECT first_name, last_name, salary
FROM employees JOIN departments USING (department_id)
WHERE department_name= 'Shipping';

--5
CREATE VIEW danePracow AS SELECT first_name, last_name,
nv12(commission_pct, (commission_pct+1)*salary*12, salary*12) AS wynagrodzenie
FROM employees;

--6
DROP VIEW danePracow;

--7
CREATE VIEW danePrac AS SELECT employee_id, first_name, last_name, job_title
FROM employees JOIN jobs USING (job_id);

SELECT * FROM danePrac;

UPDATE danePrac SET first_name = 'Jan' WHERE employee_id =206;
DROP VIEW DanePrac;

CREATE VIEW danePrac AS SELECT employee_id, first_name, last_name, job_title
FROM employees JOIN jobs USING(job_id)
WITH READ ONLY;

UPDATE danePrac SET first_name = 'Jan' WHERE employee_id = 200;

--8
CREATE VIEW stanowiska AS SELECT job_id, COUNT(employee_id) AS liczba
FROM employees
GROUP BY job_id;

--9
CREATE VIEW oddzial AS SELECT department_id,
COUNT(DISTINCT employee_id) AS pracownicyOddzialow, MIN(salary) AS minimalna, MAX(salary) AS maksymalna
FROM employees
GROUP BY department_id;

--10
CREATE VIEW LiczbaPracOddzialach AS SELECT country_name,
COUNT(department_id) AS liczba_oddzialow, COUNT(employee_id) AS liczbaPrac
FROM employees JOIN departments USING(location_id)
JOIN locations USING (location_id)
JOIN countries USING (country_id)
GROUP BY country_name;

SELECT * FROM LiczbaPracOddzialach;
DROP VIEW LiczbaPracOddzialach;

--11
CREATE OR REPLACE VIEW Pensje AS SELECT MIN(salary) AS minimalna, MAX(salary) AS maksymalna
avg(salary) AS srednia, department_name
FROM employees JOIN departments USING(department_id)
GROUP BY department_name;

SELECT * Pensje;
UPDATE Pensje SET department_name  = 'New IT'
WHERE department_name = 'IT';

--12
CREATE VIEW danePracow AS
SELECT first_name, last_name, job_title
FROM employees JOIN jobs USING(job_id);
INSERT INTO danePracow(FIRST_NAME, last_name, job_title)
VALUES ('Ada', 'Nowak', 'President');