Ćwiczenia z baz danych – SQL i formularze

1Tworzenie tabeli w bazie danych

Celem tego zadania jest nauczenie się tworzenia tabel w bazach danych. Stwórz tabelę zawierającą dane kontaktowe, np. imię, nazwisko, adres email, numer telefonu. Zdefiniuj odpowiednie typy danych oraz klucz główny.

CREATE TABLE kontakty (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    imie     VARCHAR(100)    NOT NULL,
    nazwisko VARCHAR(100)    NOT NULL,
    email    VARCHAR(100)    NOT NULL,
    telefon  VARCHAR(15)
);

Klucz główny id zapewnia unikalność rekordów; oznaczenie NOT NULL wymusza podanie kluczowych danych kontaktowych.

2Tworzenie formularza do wprowadzania danych

Stwórz formularz w HTML do wprowadzania danych kontaktowych, takich jak imię, nazwisko, email i telefon. Formularz powinien wysyłać dane do skryptu dodaj_kontakt.php, który zapisze je w tabeli kontakty.

<form action="dodaj_kontakt.php" method="post">
    Imię:
    <input type="text" name="imie" required><br>

    Nazwisko:
    <input type="text" name="nazwisko" required><br>

    Email:
    <input type="email" name="email" required><br>

    Telefon:
    <input type="text" name="telefon"><br>

    <input type="submit" value="Dodaj kontakt">
</form>

Atrybuty name są kluczowe – ich nazwy odpowiadają kluczom w tablicy $_POST po stronie PHP.

3Tworzenie zapytania do bazy danych

Użyj zapytania SQL do pobrania wszystkich kontaktów z tabeli kontakty. Wyświetl je na stronie, tworząc prostą tabelę HTML z wynikami zapytania.

SELECT *
FROM kontakty;

4Modyfikowanie danych w tabeli

Stwórz formularz do edycji danych kontaktowych, a następnie zapytanie UPDATE, które zmieni dane konkretnego rekordu na podstawie id.

UPDATE kontakty
SET imie     = 'Nowe Imię',
    nazwisko = 'Nowe Nazwisko',
    email    = 'nowyemail@example.com'
WHERE id = 1;

Zawsze dodawaj warunek WHERE; jego brak spowoduje zmianę wszystkich rekordów w tabeli.

5Zapytanie z filtrem po numerze telefonu

Stwórz zapytanie SQL, które pobierze kontakty, których numer telefonu zaczyna się od cyfry 5.

SELECT *
FROM kontakty
WHERE telefon LIKE '5%';

6Raport kontaktów posortowanych po nazwisku

Stwórz zapytanie, które generuje raport kontaktów posortowanych alfabetycznie po nazwisku.

SELECT *
FROM kontakty
ORDER BY nazwisko ASC;

7Dodanie kolumny z datą dodania kontaktu

Dodaj do tabeli kontakty kolumnę przechowującą datę dodania kontaktu.

ALTER TABLE kontakty
ADD COLUMN data_dodania DATETIME DEFAULT CURRENT_TIMESTAMP;

8Usuwanie kolumny z tabeli

Usuń z tabeli kontakty kolumnę telefon, jeśli nie jest już potrzebna.

ALTER TABLE kontakty
DROP COLUMN telefon;

11Rozbudowa struktury – tabela produktów

Stwórz tabelę produkty dla sklepu internetowego.

CREATE TABLE produkty (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    nazwa        VARCHAR(200)    NOT NULL,
    cena         DECIMAL(10,2)   NOT NULL,
    ilosc        INT             NOT NULL,
    data_dodania DATETIME        DEFAULT CURRENT_TIMESTAMP
);

12Dodawanie danych do nowej tabeli

Dodaj do tabeli produkty dane o trzech różnych produktach.

INSERT INTO produkty (nazwa, cena, ilosc)
VALUES
    ('Produkt A', 99.99, 10),
    ('Produkt B', 49.99, 5),
    ('Produkt C', 199.99, 2);

13Zapytanie z agregatami

Stwórz zapytanie, które obliczy średnią cenę produktów w tabeli produkty.

SELECT AVG(cena) AS srednia_cena
FROM produkty;

14Grupowanie produktów według ceny

Stwórz zapytanie, które zgrupuje produkty według zakresu cen i policzy liczbę produktów w każdej grupie.

SELECT 
    CASE 
        WHEN cena < 50 THEN 'Tanie'
        WHEN cena BETWEEN 50 AND 100 THEN 'Średnia cena'
        ELSE 'Drogie'
    END AS kategoria,
    COUNT(*) AS liczba_produktow
FROM produkty
GROUP BY kategoria;

15Zapytanie z warunkiem AND

Stwórz zapytanie, które zwróci produkty o cenie większej niż 50 i ilości w magazynie większej niż 5.

SELECT *
FROM produkty
WHERE cena > 50
  AND ilosc > 5;

16Aktualizacja danych w tabeli

Zaktualizuj cenę produktu o ID 2 na 59.99 oraz ilość w magazynie na 10.

UPDATE produkty
SET cena = 59.99,
    ilosc = 10
WHERE id = 2;

17Usuwanie danych z tabeli

Stwórz zapytanie, które usunie produkt o ID 3 z tabeli produkty.

DELETE FROM produkty
WHERE id = 3;

18Łączenie tabel za pomocą JOIN

Połącz tabelę produkty z tabelą kontakty (zakładając, że ID są powiązane) i wyświetl dane o produktach oraz „właścicielach”.

SELECT p.nazwa,
       p.cena,
       k.imie,
       k.nazwisko
FROM produkty p
JOIN kontakty k
  ON p.id = k.id;

19Zapytanie z podzapytaniem

Stwórz zapytanie, które znajdzie produkty o cenie wyższej niż średnia cena produktów.

SELECT *
FROM produkty
WHERE cena > (SELECT AVG(cena) FROM produkty);

20Zapytanie z LIMIT

Stwórz zapytanie, które zwróci tylko pierwsze 5 produktów z tabeli produkty.

SELECT *
FROM produkty
LIMIT 5;

21Zapytanie z DISTINCT

Stwórz zapytanie, które zwróci unikalne ceny produktów z tabeli produkty.

SELECT DISTINCT cena
FROM produkty;

22Zapytanie z IN

Stwórz zapytanie, które zwróci produkty, których cena jest równa 50, 100 lub 150.

SELECT *
FROM produkty
WHERE cena IN (50, 100, 150);

23Tworzenie procedury w SQL

Stwórz prostą procedurę, która zwróci produkty o cenie większej niż wartość przekazana jako parametr.

DELIMITER //

CREATE PROCEDURE get_products_above_price(
    IN min_price DECIMAL(10,2)
)
BEGIN
    SELECT *
    FROM produkty
    WHERE cena > min_price;
END //

DELIMITER ;

24Tworzenie funkcji w SQL

Stwórz funkcję, która obliczy całkowitą wartość sprzedaży, mnożąc cenę przez ilość w magazynie dla wszystkich produktów.

DELIMITER //

CREATE FUNCTION calculate_total_sales()
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total_sales DECIMAL(10,2);

    SELECT SUM(cena * ilosc)
    INTO total_sales
    FROM produkty;

    RETURN total_sales;
END //

DELIMITER ;