Dział IV. Stosowanie Strukturalnego Języka Zapytań SQL: Technik Informatyk (Klasa 4)

17.1. Wyszukiwanie danych (SELECT, gwiazdka, aliasy)

Co robimy: Uczymy się podstawowego zapytania SELECT. Pokazujemy, jak wyświetlać wszystkie dane (SELECT *) oraz jak wybierać tylko potrzebne kolumny, nadając im czytelne aliasy (AS).

Zadanie: Wyświetl wszystkie produkty z magazynu. Następnie wyświetl tylko ich nazwy i ceny, zmieniając nagłówek kolumny Cena na Kwota_Netto.

 Wyświetlenie wszystkich produktów
SELECT * FROM Produkty;

 Wyświetlenie wybranych kolumn z aliasem
SELECT Nazwa, Cena AS Kwota_Netto FROM Produkty;
        

17.2. Dodawanie danych (INSERT INTO)

Co robimy: Polecenie INSERT INTO służy do wstawiania nowego rekordu do tabeli. Wartości muszą pasować do kolejności i typu danych kolumn.

Zadanie: Wstaw do tabeli Produkty nowy produkt o nazwie "Drukarka 3D" i cenie 1200.00. Ilość magazynową ustaw na 0.

 INSERT z jawnym określeniem kolumn
INSERT INTO Produkty (Nazwa, Cena, IloscMagazyn) 
VALUES ('Drukarka 3D', 1200.00, 0);

 Sprawdzenie, czy nowy produkt został dodany
SELECT * FROM Produkty WHERE Nazwa = 'Drukarka 3D';
        

17.3. Usuwanie danych (DELETE FROM)

Co robimy: Polecenie DELETE FROM służy do usuwania danych. Pamiętaj: zawsze używamy klauzuli WHERE, aby wskazać dokładnie, który rekord ma zostać usunięty.

Zadanie: Wstaw nowego klienta "Zając", a następnie go usuń. Użyj KlientID = 4 (jeśli jest to kolejny wolny identyfikator).

 Wstawienie klienta do usunięcia (potrzebne do ćwiczenia)
INSERT INTO Klienci (Nazwisko, AdresEmail) 
VALUES ('Zając', 'krzysztof.zajac@poczta.pl');

 Zawsze najpierw sprawdź, jakie ID ma rekord, który chcesz usunąć
SELECT KlientID, Nazwisko FROM Klienci WHERE Nazwisko = 'Zając';

 Usunięcie rekordu, używając ID jako bezpiecznego klucza (zakładamy ID=4)
DELETE FROM Klienci WHERE KlientID = 4;

 Sprawdzenie
SELECT * FROM Klienci;
        

17.4. Modyfikowanie kolumny (DDL: ALTER TABLE)

Co robimy: Uczymy się zmieniać strukturę tabeli za pomocą ALTER TABLE. Pokażemy, jak usunąć istniejącą kolumnę.

Zadanie: Usuń z tabeli Produkty kolumnę Opis.

 ALTER TABLE, aby usunąć kolumnę
ALTER TABLE Produkty
DROP COLUMN Opis;

 Sprawdź strukturę tabeli
DESCRIBE Produkty;
        

17.5. Aktualizacja danych (UPDATE)

Co robimy: Polecenie UPDATE służy do zmiany wartości w istniejących rekordach. Określamy, co zmienić (SET) i który rekord (WHERE).

Zadanie: Zmień status zamówienia o ZamowienieID = 2 z "Nowe" na "Wysłane".

 Sprawdzenie bieżącego statusu
SELECT ZamowienieID, Status FROM Zamowienia WHERE ZamowienieID = 2;

 Aktualizacja statusu
UPDATE Zamowienia
SET Status = 'Wysłane'
WHERE ZamowienieID = 2;

 Ponowne sprawdzenie
SELECT ZamowienieID, Status FROM Zamowienia WHERE ZamowienieID = 2;
        

18.1. Definiowanie ograniczeń (CHECK, NOT NULL)

Co robimy: Dodajemy ograniczenie CHECK, aby ilość magazynowa nigdy nie była ujemna.

Zadanie: Dodaj do tabeli Produkty ograniczenie CHECK, które zagwarantuje, że IloscMagazyn nie jest mniejsza niż 0.

 ALTER TABLE z ograniczeniem CHECK
ALTER TABLE Produkty
ADD CONSTRAINT chk_IloscMagazyn CHECK (IloscMagazyn >= 0);

 System zablokuje próbę wpisania ujemnej wartości.
        

18.2. Klucze główne (PRIMARY KEY) i indeksy

Co robimy: Tworzymy tabelę z kluczem głównym, który automatycznie się inkrementuje.

Zadanie: Utwórz nową tabelę Kategorie z kolumną KategoriaID jako kluczem głównym AUTO_INCREMENT.

 Tworzenie nowej tabeli z kluczem głównym
CREATE TABLE Kategorie (
    KategoriaID INT PRIMARY KEY AUTO_INCREMENT,
    NazwaKategorii VARCHAR(50) NOT NULL UNIQUE
);

 Sprawdzenie struktury
DESCRIBE Kategorie;
        

18.3. Tworzenie relacji (FOREIGN KEY)

Co robimy: Dodajemy do Produkty kolumnę KategoriaID i ustawiamy ją jako klucz obcy do tabeli Kategorie.

Zadanie: Zmodyfikuj tabelę Produkty, aby zawierała klucz obcy do Kategorie.

 DDL: Dodanie kolumny
ALTER TABLE Produkty
ADD KategoriaID INT;

 DDL: Dodanie klucza obcego
ALTER TABLE Produkty
ADD CONSTRAINT fk_Kategoria
FOREIGN KEY (KategoriaID) REFERENCES Kategorie(KategoriaID);
        

18.4. Unikalność danych (UNIQUE)

Co robimy: Wymuszamy unikalność wartości w kolumnie.

Zadanie: Dodaj do tabeli Klienci ograniczenie UNIQUE na kolumnie Nazwisko.

 Dodajemy ograniczenie UNIQUE na kolumnie Nazwisko
ALTER TABLE Klienci
ADD CONSTRAINT uq_Nazwisko UNIQUE (Nazwisko);

 Uwaga: jeśli istnieją duplikaty nazwisk, komenda się nie powiedzie.
        

18.5. Tabela logująca zmiany cen

Co robimy: Tworzymy tabelę Logi_Cen do zapisywania historii zmian cen produktów.

Zadanie: Stwórz tabelę Logi_Cen z kolumnami: LogID, ProduktID, DataZmiany, z kluczem obcym do Produkty.

 Skrypt tworzący tabelę logującą
DROP TABLE IF EXISTS Logi_Cen;

CREATE TABLE Logi_Cen (
    LogID INT PRIMARY KEY AUTO_INCREMENT,
    ProduktID INT NOT NULL,
    DataZmiany DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ProduktID) REFERENCES Produkty(ProduktID)
);
        

19.1. Filtrowanie złożone (WHERE, LIKE, BETWEEN, ORDER BY)

Co robimy: Uczymy się precyzyjnego filtrowania za pomocą LIKE, AND, OR oraz sortowania ORDER BY.

Zadanie: Znajdź klientów, których nazwisko zaczyna się na "K" lub "W" i którzy zarejestrowali się po dacie "2024-01-01". Wyniki posortuj alfabetycznie.

 Wyszukiwanie z LIKE, OR i sortowaniem
SELECT Nazwisko, AdresEmail, DataRejestracji
FROM Klienci 
WHERE (Nazwisko LIKE 'K%' OR Nazwisko LIKE 'W%')
  AND DataRejestracji > '2024-01-01'
ORDER BY Nazwisko ASC;
        

19.2. Agregacja i grupowanie (GROUP BY, MAX, MIN, AVG, COUNT)

Co robimy: Używamy funkcji agregujących do podsumowania danych oraz GROUP BY do grupowania wyników.

Zadanie: Oblicz najwyższą, najniższą i średnią cenę produktów. Następnie policz zamówienia w podziale na status.

 Zapytanie 1: Funkcje agregujące
SELECT 
    MAX(Cena) AS Cena_Max, 
    MIN(Cena) AS Cena_Min, 
    AVG(Cena) AS Cena_Srednia
FROM Produkty;

 Zapytanie 2: Grupowanie według statusu
SELECT 
    Status, 
    COUNT(ZamowienieID) AS Liczba_Zamowien
FROM Zamowienia
GROUP BY Status;
        

19.3. Klienci bez zamówień (LEFT JOIN)

Co robimy: Używamy LEFT JOIN, aby znaleźć klientów, którzy nie złożyli żadnego zamówienia.

Zadanie: Wyświetl nazwiska wszystkich klientów i ID ich zamówień, a następnie tylko tych, którzy nie mają zamówień.

 Znalezienie klientów bez zamówień
SELECT K.Nazwisko, Z.ZamowienieID
FROM Klienci K
LEFT JOIN Zamowienia Z ON K.KlientID = Z.KlientID
WHERE Z.ZamowienieID IS NULL;

 Wszyscy klienci z informacją o zamówieniach
SELECT K.Nazwisko, Z.ZamowienieID
FROM Klienci K
LEFT JOIN Zamowienia Z ON K.KlientID = Z.KlientID;
        

19.4. Złączenie wielu tabel i HAVING

Co robimy: Łączymy trzy tabele i liczymy łączne wydatki klienta, filtrując wyniki klauzulą HAVING.

Zadanie: Pokaż klientów, którzy wydali ponad 2000 zł.

 Łączna kwota wydana przez klienta
SELECT 
    K.Nazwisko, 
    SUM(SZ.Ilosc * SZ.CenaJednostkowa) AS Laczne_Wydatki
FROM Klienci K
JOIN Zamowienia Z ON K.KlientID = Z.KlientID
JOIN Szczegoly_Zamowienia SZ ON Z.ZamowienieID = SZ.ZamowienieID
GROUP BY K.Nazwisko
HAVING Laczne_Wydatki > 2000.00
ORDER BY Laczne_Wydatki DESC;
        

19.5. Podzapytania – znajdowanie ekstremalnych wartości

Co robimy: Używamy podzapytania w WHERE, aby znaleźć najdroższy produkt.

Zadanie: Znajdź nazwę i cenę najdroższego produktu.

 Podzapytanie do znalezienia najdroższego produktu
SELECT Nazwa, Cena
FROM Produkty
WHERE Cena = (SELECT MAX(Cena) FROM Produkty);
        

19.6. Złączenia, podzapytania i zaawansowane zapytania

Co robimy: Łączymy cztery tabele i używamy agregacji do analizy wartości zamówień.

Zadanie: Znajdź klientów, którzy kupili "Laptop Gamingowy" oraz zamówienia o wartości powyżej 1500 zł.

 Zapytanie 1: Złączenie czterech tabel
SELECT DISTINCT K.Nazwisko
FROM Klienci K
JOIN Zamowienia Z ON K.KlientID = Z.KlientID
JOIN Szczegoly_Zamowienia SZ ON Z.ZamowienieID = SZ.ZamowienieID
JOIN Produkty P ON SZ.ProduktID = P.ProduktID
WHERE P.Nazwa = 'Laptop Gamingowy';

 Zapytanie 2: Agregacja z HAVING
SELECT ZamowienieID, SUM(Ilosc * CenaJednostkowa) AS Wartosc
FROM Szczegoly_Zamowienia
GROUP BY ZamowienieID
HAVING Wartosc > 1500.00;
        

20.1. Aktualizacja rekordów (UPDATE z operacjami matematycznymi)

Co robimy: Modyfikujemy dane z użyciem działań matematycznych w klauzuli SET.

Zadanie: Zmień adres e-mail klienta o ID=2 i zastosuj 10% rabatu dla produktów droższych niż 1000 zł.

 UPDATE 1: Zmiana adresu email
UPDATE Klienci 
SET AdresEmail = 'nowy.email@poczta.pl' 
WHERE KlientID = 2;

 UPDATE 2: Rabaty (operacja matematyczna w SET)
UPDATE Produkty 
SET Cena = Cena * 0.90 
WHERE Cena > 1000.00;

 Sprawdzenie po rabacie
SELECT Nazwa, Cena FROM Produkty WHERE Cena > 900;
        

20.2. Usuwanie danych (DELETE z podzapytaniem)

Co robimy: Ćwiczymy usuwanie danych w powiązanych tabelach.

Zadanie: Usuń szczegóły zamówienia dla produktu o ID=3, a następnie usuń wszystkie zamówienia o statusie "Anulowane".

 DELETE 1: Usunięcie konkretnego szczegółu zamówienia
DELETE FROM Szczegoly_Zamowienia 
WHERE ProduktID = 3;

 DELETE 2: Usunięcie wszystkich zamówień o statusie 'Anulowane'
DELETE FROM Zamowienia 
WHERE Status = 'Anulowane';

 Sprawdzenie
SELECT * FROM Zamowienia;
        

20.3. INSERT INTO... SELECT

Co robimy: Kopiujemy dane klientów z zamówieniami o statusie "Wysłane" do tabeli archiwalnej.

Zadanie: Utwórz tabelę Klienci_Archiwum i przenieś do niej klientów z wysłanymi zamówieniami.

 Krok 1: Utworzenie tabeli archiwum (DDL)
DROP TABLE IF EXISTS Klienci_Archiwum;
CREATE TABLE Klienci_Archiwum LIKE Klienci;

 Krok 2: Wstawienie danych z zapytania (DML)
INSERT INTO Klienci_Archiwum (KlientID, Nazwisko, AdresEmail, DataRejestracji)
SELECT DISTINCT   Używamy DISTINCT, aby nie zarchiwizować tego samego klienta wielokrotnie
    K.KlientID, K.Nazwisko, K.AdresEmail, K.DataRejestracji
FROM Klienci K
JOIN Zamowienia Z ON K.KlientID = Z.KlientID
WHERE Z.Status = 'Wysłane';

 Sprawdzenie
SELECT * FROM Klienci_Archiwum;
        

20.4. Transakcje i COMMIT

Co robimy: Używamy transakcji, aby kilka operacji wykonało się jako całość.

Zadanie: Obniż cenę produktu ID=4 o 10 zł i dopisz log do Logi_Cen w ramach jednej transakcji.

 Transakcja zmiany ceny
START TRANSACTION;

 1. Zmiana ceny
UPDATE Produkty 
SET Cena = Cena - 10.00 
WHERE ProduktID = 4;

 2. Logowanie zmiany
INSERT INTO Logi_Cen (ProduktID) VALUES (4); 

 Zatwierdzenie transakcji
COMMIT;

 W przypadku błędu można użyć ROLLBACK (nie w tym przykładzie).
        

20.5. Transakcje i ROLLBACK

Co robimy: Pokazujemy, jak cofnąć zmiany wykonane w transakcji.

Zadanie: Zmień tymczasowo nazwisko klienta ID=1 i cofnij zmiany poleceniem ROLLBACK.

 Krok 1: Sprawdzenie stanu początkowego
SELECT Nazwisko FROM Klienci WHERE KlientID = 1;

 Krok 2: Rozpoczęcie transakcji
START TRANSACTION;

 Krok 3: Tymczasowa zmiana
UPDATE Klienci 
SET Nazwisko = 'Tymczasowy' 
WHERE KlientID = 1;

 Krok 4: Wycofanie transakcji
ROLLBACK; 

 Krok 5: Sprawdzenie stanu końcowego
SELECT Nazwisko FROM Klienci WHERE KlientID = 1;
        

21.1. Łączenie poleceń w skrypty (batch)

Co robimy: Wykonujemy sekwencję poleceń SQL razem, aby zautomatyzować operacje.

Zadanie: Zwiększ cenę wszystkich produktów o 5% i wyświetl zaktualizowaną listę.

 Skrypt aktualizujący i wyświetlający
UPDATE Produkty 
SET Cena = Cena * 1.05;

SELECT Nazwa, Cena 
FROM Produkty 
ORDER BY Cena DESC;
        

21.2. Widoki (VIEW)

Co robimy: Tworzymy widok łączący klientów z ich zamówieniami.

Zadanie: Stwórz widok Raport_Wydatki_Klienta z nazwiskiem klienta, ID zamówienia i datą zamówienia.

 Tworzenie widoku (wirtualna tabela)
CREATE VIEW Raport_Wydatki_Klienta AS
SELECT
    K.Nazwisko,
    Z.ZamowienieID,
    Z.DataZamowienia
FROM Klienci K
JOIN Zamowienia Z ON K.KlientID = Z.KlientID;

 Użycie widoku
SELECT * 
FROM Raport_Wydatki_Klienta 
WHERE Nazwisko = 'Kowalski';
        

21.3. Procedury składowane (stored procedures)

Co robimy: Tworzymy procedurę usuwającą klienta wraz z jego zamówieniami i szczegółami.

Zadanie: Stwórz procedurę UsunKlienta, która przyjmuje id_klienta.

DELIMITER //

CREATE PROCEDURE UsunKlienta (
    IN id_klienta INT
)
BEGIN
     1. Usuń szczegóły zamówień danego klienta
    DELETE FROM Szczegoly_Zamowienia 
    WHERE ZamowienieID IN (
        SELECT ZamowienieID 
        FROM Zamowienia 
        WHERE KlientID = id_klienta
    );
    
     2. Usuń zamówienia klienta
    DELETE FROM Zamowienia 
    WHERE KlientID = id_klienta;
    
     3. Usuń klienta
    DELETE FROM Klienci 
    WHERE KlientID = id_klienta;
END//

DELIMITER ;

 Użycie: CALL UsunKlienta(1);
        

21.4. Funkcje składowane (stored functions)

Co robimy: Tworzymy funkcję zwracającą status zamówienia.

Zadanie: Stwórz funkcję SprawdzStatus, która przyjmuje id_zamowienia i zwraca tekstowy status.

DELIMITER //

CREATE FUNCTION SprawdzStatus (
    id_zamowienia INT
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE status_zam VARCHAR(20);

    SELECT Status INTO status_zam
    FROM Zamowienia
    WHERE ZamowienieID = id_zamowienia;
    
    RETURN status_zam;
END//

DELIMITER ;

 Użycie funkcji:
 SELECT SprawdzStatus(1);
        

21.5. Wyzwalacze (triggers)

Co robimy: Tworzymy wyzwalacz logujący zmiany cen produktów.

Zadanie: Stwórz wyzwalacz after_product_update, który dopisze wpis do Logi_Cen po zmianie ceny.

DELIMITER //

CREATE TRIGGER after_product_update
AFTER UPDATE ON Produkty
FOR EACH ROW
BEGIN
     Sprawdź, czy nowa cena różni się od starej
    IF OLD.Cena != NEW.Cena THEN
        INSERT INTO Logi_Cen (ProduktID, DataZmiany)
        VALUES (NEW.ProduktID, NOW());
    END IF;
END//

DELIMITER ;

 Test wyzwalacza:
 UPDATE Produkty SET Cena = 500.00 WHERE ProduktID = 5;
 SELECT * FROM Logi_Cen;
        

22.1. Pełny cykl życia zamówienia

Zadanie: Wykonaj pełny cykl: od dodania klienta, przez złożenie zamówienia, po zmianę statusu na "Wysłane".

 1. Wstawienie nowego klienta (KlientID zostanie nadane automatycznie)
INSERT INTO Klienci (Nazwisko, AdresEmail) 
VALUES ('Testowy', 'test@example.com');

 Tu w praktyce sprawdzamy KlientID ręcznie (np. SELECT MAX(KlientID)...).
 Załóżmy, że KlientID = 5.

START TRANSACTION;

 2. Złożenie nowego zamówienia przez klienta (KlientID=5)
INSERT INTO Zamowienia (KlientID, Status) 
VALUES (5, 'Nowe');

 Załóżmy, że ZamowienieID = 4.

 3. Dodanie szczegółu zamówienia (ProduktID=1, Laptop Gamingowy)
INSERT INTO Szczegoly_Zamowienia (ZamowienieID, ProduktID, Ilosc, CenaJednostkowa) 
VALUES (4, 1, 1, 4500.00);

 4. Aktualizacja statusu zamówienia na 'Wysłane'
UPDATE Zamowienia
SET Status = 'Wysłane'
WHERE ZamowienieID = 4;

COMMIT;

 Weryfikacja
SELECT K.Nazwisko, Z.Status, SZ.Ilosc
FROM Klienci K
JOIN Zamowienia Z ON K.KlientID = Z.KlientID
JOIN Szczegoly_Zamowienia SZ ON Z.ZamowienieID = SZ.ZamowienieID
WHERE K.KlientID = 5;
        

22.2. Najlepiej sprzedający się produkt

Zadanie: Znajdź produkt sprzedany w największej łącznej ilości.

 Zapytanie o najlepiej sprzedający się produkt
SELECT 
    P.Nazwa, 
    SUM(SZ.Ilosc) AS Laczna_Sprzedaz
FROM Produkty P
JOIN Szczegoly_Zamowienia SZ ON P.ProduktID = SZ.ProduktID
GROUP BY P.Nazwa
ORDER BY Laczna_Sprzedaz DESC
LIMIT 1;   Ograniczenie do pierwszego rekordu
        

22.3. Raport finansowy (SUMA i VIEW)

Zadanie: Stwórz widok Wartosc_Zamowienia_Raport, a następnie oblicz łączną wartość zamówień o statusie "Wysłane".

 Krok 1: Widok obliczający wartość każdego zamówienia
CREATE OR REPLACE VIEW Wartosc_Zamowienia_Raport AS
SELECT 
    Z.ZamowienieID,
    Z.Status,
    SUM(SZ.Ilosc * SZ.CenaJednostkowa) AS Wartosc_Calkowita
FROM Zamowienia Z
JOIN Szczegoly_Zamowienia SZ ON Z.ZamowienieID = SZ.ZamowienieID
GROUP BY Z.ZamowienieID, Z.Status;

 Krok 2: Suma wszystkich 'Wysłanych' zamówień
SELECT 
    SUM(Wartosc_Calkowita) AS Laczna_Wartosc_Wyslanych
FROM Wartosc_Zamowienia_Raport
WHERE Status = 'Wysłane';