Zadania z SZBD – Zaawansowane programowanie (MySQL / XAMPP)

Setup · DDL · Relacje

SPrzygotowanie bazy firma i tabel

Utwórz bazę danych firma oraz tabele Pracownicy, Dzialy i HistoriaWynagrodzen wraz z relacjami.

SQL · DDL

CREATE DATABASE IF NOT EXISTS firma
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_polish_ci;
USE firma;

-- Tabela Pracownicy
CREATE TABLE IF NOT EXISTS Pracownicy (
  ID               INT AUTO_INCREMENT PRIMARY KEY,
  Imie             VARCHAR(50),
  Nazwisko         VARCHAR(50),
  Stanowisko       VARCHAR(50),
  Wynagrodzenie    DECIMAL(10,2),
  DataZatrudnienia DATE,
  ID_Dzialu        INT
);

-- Tabela Dzialy
CREATE TABLE IF NOT EXISTS Dzialy (
  ID    INT AUTO_INCREMENT PRIMARY KEY,
  Nazwa VARCHAR(50)
);

-- Dane przykładowe do Dzialy
INSERT INTO Dzialy (Nazwa) VALUES ('IT'), ('Kadry'), ('Finanse');

-- Tabela HistoriaWynagrodzen
CREATE TABLE IF NOT EXISTS HistoriaWynagrodzen (
  ID                 INT AUTO_INCREMENT PRIMARY KEY,
  ID_Pracownika      INT,
  StareWynagrodzenie DECIMAL(10,2),
  NoweWynagrodzenie  DECIMAL(10,2),
  DataZmiany         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT FK_HW_Prac FOREIGN KEY (ID_Pracownika) REFERENCES Pracownicy(ID)
);

-- Przykładowa aktualizacja pracowników do testów
UPDATE Pracownicy SET ID_Dzialu = 1, DataZatrudnienia = '2023-01-15' WHERE Imie = 'Jan';
UPDATE Pracownicy SET ID_Dzialu = 1, DataZatrudnienia = '2024-05-20' WHERE Imie = 'Anna';
UPDATE Pracownicy SET ID_Dzialu = 2, DataZatrudnienia = '2022-03-01' WHERE Imie = 'Piotr';

-- Relacja Pracownicy → Dzialy
ALTER TABLE Pracownicy
  ADD CONSTRAINT FK_Dzialu
  FOREIGN KEY (ID_Dzialu) REFERENCES Dzialy(ID);

Procedura · masowa podwyżka

11Procedura podwyżki 10%

Napisz procedurę składowaną zwiększającą wynagrodzenie wszystkich pracowników o 10%.

SQL · PROCEDURE

DELIMITER $$

CREATE PROCEDURE ZwiekszWynagrodzenie()
BEGIN
  UPDATE Pracownicy
  SET Wynagrodzenie = Wynagrodzenie * 1.10;
END $$

DELIMITER ;

Trigger · historia zmian płac

12Wyzwalacz historii wynagrodzeń

Stwórz wyzwalacz zapisujący historię zmian wynagrodzenia do tabeli HistoriaWynagrodzen.

SQL · TRIGGER

DELIMITER $$

CREATE TRIGGER ZmianaWynagrodzenia
AFTER UPDATE ON Pracownicy
FOR EACH ROW
BEGIN
  IF OLD.Wynagrodzenie <> NEW.Wynagrodzenie THEN
    INSERT INTO HistoriaWynagrodzen (ID_Pracownika, StareWynagrodzenie, NoweWynagrodzenie)
    VALUES (OLD.ID, OLD.Wynagrodzenie, NEW.Wynagrodzenie);
  END IF;
END $$

DELIMITER ;

JOIN · nazwa działu

13JOIN z tabelą Dzialy

Pobierz dane pracowników wraz z nazwami ich działów.

SQL · JOIN

SELECT P.Imie, P.Nazwisko, D.Nazwa AS NazwaDzialu
FROM Pracownicy P
JOIN Dzialy D ON P.ID_Dzialu = D.ID;

AVG · Group by

14Średnie wynagrodzenie w dziale

Pokaż średnie wynagrodzenie w każdym dziale.

SQL · AGREGACJA

SELECT ID_Dzialu,
       AVG(Wynagrodzenie) AS SrednieWynagrodzenie
FROM Pracownicy
GROUP BY ID_Dzialu;

ORDER BY · LIMIT

15TOP 3 najlepiej zarabiających

Wyświetl trzech najlepiej zarabiających pracowników.

SQL · DQL

SELECT *
FROM Pracownicy
ORDER BY Wynagrodzenie DESC
LIMIT 3;

DDL · tabela historii

16Tabela HistoriaWynagrodzen (DDL)

Utwórz tabelę HistoriaWynagrodzen do przechowywania zmian wynagrodzenia.

SQL · DDL

CREATE TABLE HistoriaWynagrodzen (
  ID                 INT AUTO_INCREMENT PRIMARY KEY,
  ID_Pracownika      INT,
  StareWynagrodzenie DECIMAL(10,2),
  NoweWynagrodzenie  DECIMAL(10,2),
  DataZmiany         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT FK_HW_Prac FOREIGN KEY (ID_Pracownika) REFERENCES Pracownicy(ID)
);

INSERT · test historii

17Wstawianie danych do historii

Dodaj przykładowe rekordy do tabeli HistoriaWynagrodzen.

SQL · DML

INSERT INTO HistoriaWynagrodzen (ID_Pracownika, StareWynagrodzenie, NoweWynagrodzenie)
VALUES
  (1, 5000.00, 5500.00),
  (2, 6000.00, 6600.00);

ALTER TABLE · FK

18Dodanie klucza obcego ID_Dzialu

Jeśli kolumna ID_Dzialu istnieje, dodaj do niej klucz obcy do tabeli Dzialy.

SQL · DDL

ALTER TABLE Pracownicy
  ADD CONSTRAINT FK_Dzialu
  FOREIGN KEY (ID_Dzialu) REFERENCES Dzialy(ID);

Podzapytanie · średnia płaca

19Pracownicy powyżej średniej

Wyświetl pracowników, którzy zarabiają więcej niż średnie wynagrodzenie w firmie.

SQL · DQL

SELECT *
FROM Pracownicy
WHERE Wynagrodzenie > (
  SELECT AVG(Wynagrodzenie) FROM Pracownicy
);

ALTER TABLE · DROP FK

20Usunięcie relacji Pracownicy–Dzialy

Usuń klucz obcy łączący tabelę Pracownicy z tabelą Dzialy.

SQL · DDL

ALTER TABLE Pracownicy
  DROP FOREIGN KEY FK_Dzialu;

Nazwę ograniczenia (FK_Dzialu) można sprawdzić w INFORMATION_SCHEMA lub w phpMyAdmin.

Transakcje · COMMIT / ROLLBACK

21Transakcja z warunkiem

Podwyżka 500 zł dla pracownika o ID = 1 z decyzją o COMMIT/ROLLBACK na podstawie nowej płacy.

SQL · TRANSACTION

START TRANSACTION;

UPDATE Pracownicy
SET Wynagrodzenie = Wynagrodzenie + 500
WHERE ID = 1;

-- Sprawdź aktualne wynagrodzenie:
-- SELECT Wynagrodzenie FROM Pracownicy WHERE ID = 1;
-- Jeśli > 10000 wykonaj: ROLLBACK;
-- W przeciwnym razie wykonaj: COMMIT;

W phpMyAdmin najlepiej wykonać UPDATE, potem SELECT, a następnie ręcznie zdecydować: COMMIT lub ROLLBACK.

Trigger · walidacja

22Wyzwalacz walidujący minimalne wynagrodzenie

Blokuj dodanie pracownika z wynagrodzeniem mniejszym niż 3500.

SQL · TRIGGER

DELIMITER $$

CREATE TRIGGER SprawdzMinimalneWynagrodzenie
BEFORE INSERT ON Pracownicy
FOR EACH ROW
BEGIN
  IF NEW.Wynagrodzenie < 3500 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Wynagrodzenie nie może być niższe niż 3500.';
  END IF;
END $$

DELIMITER ;

Podzapytanie skorelowane

23Średnia płaca w dziale – skorelowane

Pracownicy zarabiający więcej niż średnia wynagrodzeń w ich dziale.

SQL · DQL

SELECT Imie, Nazwisko, Wynagrodzenie, ID_Dzialu
FROM Pracownicy P1
WHERE P1.Wynagrodzenie > (
  SELECT AVG(P2.Wynagrodzenie)
  FROM Pracownicy P2
  WHERE P2.ID_Dzialu = P1.ID_Dzialu
);

LEFT JOIN + COUNT

24Liczba pracowników w każdym dziale

Pokaż liczbę pracowników w każdym dziale (również tych bez pracowników).

SQL · AGREGACJA

SELECT D.Nazwa AS NazwaDzialu,
       COUNT(P.ID) AS LiczbaPracownikow
FROM Dzialy D
LEFT JOIN Pracownicy P ON D.ID = P.ID_Dzialu
GROUP BY D.Nazwa;

DATEDIFF · staż pracy

25Staż pracy w dniach

Oblicz staż pracy w dniach od daty zatrudnienia.

SQL · FUNKCJE DATY

SELECT Imie, Nazwisko,
       DATEDIFF(CURDATE(), DataZatrudnienia) AS StazWDniach
FROM Pracownicy;

CREATE TABLE AS · kopia

26INSERT ... SELECT (PracownicyIT)

Skopiuj pracowników z działu IT do tabeli PracownicyIT.

SQL · DDL/DML

CREATE TABLE PracownicyIT AS
SELECT *
FROM Pracownicy
WHERE ID_Dzialu = 1;

UNIQUE INDEX

27Unikalny indeks na Imie + Nazwisko

Zabezpiecz tabelę przed duplikatami pracowników o tych samych imieniu i nazwisku.

SQL · INDEX

CREATE UNIQUE INDEX idx_unikalny_pracownik
ON Pracownicy(Imie, Nazwisko);

GROUP BY · HAVING

28Działy z więcej niż 2 pracownikami

Wyświetl działy, w których liczba pracowników jest większa niż 2.

SQL · HAVING

SELECT D.Nazwa,
       COUNT(P.ID) AS LiczbaPracownikow
FROM Dzialy D
JOIN Pracownicy P ON D.ID = P.ID_Dzialu
GROUP BY D.Nazwa
HAVING COUNT(P.ID) > 2;

DELETE · powiązane rekordy

29Usunięcie pracownika i jego historii

Usuń pracownika o ID = 5 oraz jego historię płac.

SQL · DML

DELETE FROM HistoriaWynagrodzen
WHERE ID_Pracownika = 5;

DELETE FROM Pracownicy
WHERE ID = 5;

Jeśli klucz obcy zdefiniowano z ON DELETE CASCADE, wystarczy samo DELETE FROM Pracownicy.

Procedura z parametrem

30Podwyżka 5% dla wybranego stanowiska

Stwórz procedurę, która podnosi wynagrodzenie o 5% wszystkim pracownikom danego stanowiska.

SQL · PROCEDURE

DELIMITER $$

CREATE PROCEDURE PodwyzkaDlaStanowiska(IN stanowisko_param VARCHAR(50))
BEGIN
  UPDATE Pracownicy
  SET Wynagrodzenie = Wynagrodzenie * 1.05
  WHERE Stanowisko = stanowisko_param;
END $$

DELIMITER ;

-- Wywołanie:
-- CALL PodwyzkaDlaStanowiska('Programista');