querries.txt

	-po przyjściu do pracy „odbija się”, czyli przekazuje kiedy rozpoczął pracę
		INSERT INTO Worktime_evidence (worktime_id, employee_id, start_date, finish_date)
		VALUES ('14', '21', SYSDATE(), NULL);
	-przed wyjściem z pracy „odbija się”, tym razem przekazuje kiedy zakończył pracę
		use mydb;
UPDATE worktime_evidence SET finish_date = NOW() 
WHERE employee_id = "13";
	-może wygenerować rozliczenie w danym okresie

		SELECT Employees.employee_id, SUM((worktime_evidence.finish_date - 	Worktime_evidence.start_date) * 24 * Job_history.salary)
		FROM (Employees INNER JOIN Worktime_evidence ON Employees.employee_id = Worktime_evidence.employee_id) INNER JOIN Job_history ON Employees.employee_id = 					Job_history.employees_employee_id
		WHERE Worktime_evidence.start_date >= '2000-02-10' AND Worktime_evidence.finish_date <= '2022-06-13' AND Worktime_evidence.start_date >= Job_history.start_date AND 				(Worktime_evidence.finish_date <= Job_history.finish_date OR Job_history.finish_date IS NULL)
		GROUP BY Employees.employee_id;
	
	-może sprawdzić ile w danym dniu znajduje się pracowników w pracy
		SELECT COUNT(*) FROM Worktime_evidence
		WHERE start_date < NOW() AND finish_date IS NULL;

	-może przygotować rozliczenie za dany okres dla danego działu
		SELECT Employees.department_id, SUM((Worktime_evidence.finish_date - Worktime_evidence.start_date) * 24 * Job_history.salary)
		FROM (Employees INNER JOIN Worktime_evidence ON Employees.employee_id = Worktime_evidence.employee_id) INNER JOIN Job_history ON Employees.employee_id = 					Job_history.employees_employee_id
		WHERE Employees.department_id = 1 AND Worktime_evidence.start_date >= '2000-02-10' AND Worktime_evidence.finish_date <= '2022-06-13' AND Worktime_evidence.start_date >= 			Job_history.start_date AND (Worktime_evidence.finish_date <= Job_history.finish_date OR Job_history.finish_date IS NULL)
		GROUP BY Employees.employee_id;

	-może zatrudnić nowego pracownika
		use mydb;
INSERT INTO Employees(employee_id, first_name, last_name, job_id, department_id)
		VALUES(28, "Piotr", "Kowalski", 954772, 1);
		
INSERT INTO Job_history(employees_employee_id, start_date, finish_date, job_id, salary)
VALUES(28,
	CURDATE(), 
	NULL, 
	954772,
	5000);


	-może zwolnić pracownika
		UPDATE Job_history 
		SET finish_date = CURDATE()
		WHERE employees_employee_id = 28 AND job_id = 954772;
	
	-może zmienić pracownikowi pensję
	//zwolnić pracownika w job_history i dodać nowy wpis z poprawioną pensją, inaczej będą przekłamania
		UPDATE Job_history 
		SET finish_date = CURDATE()
		WHERE employees_employee_id = 28 AND job_id = 954772;

		INSERT INTO Job_history(employees_employee_id, start_date, finish_date, job_id, salary)
		VALUES(28, CURDATE(), NULL, 954772, 5000);

	-może zmienić pracownikowi stanowisko
	//zwolnić pracownika w job_history i dodać nowy wpis z poprawionym stanowiskiem, inaczej będą przekłamania
		UPDATE Job_history 
		SET finish_date = CURDATE()
		WHERE employees_employee_id = 28 AND job_id = 954772;

		INSERT INTO Job_history(employees_employee_id, start_date, finish_date, job_id, salary)
		VALUES(28, CURDATE(), NULL, 4522034, 5000);

	-może dać pracownikowi premię
		INSERT INTO Bonuses(employee_id, bonus_date, bonus_value)
		VALUES("28", CURDATE(), "200");