Ćwiczenia z baz danych

1. Tworzenie 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), nazwisko VARCHAR(100), email VARCHAR(100), telefon VARCHAR(15) );

2. Tworzenie formularza do wprowadzania danych

Stwórz formularz w HTML do wprowadzania danych kontaktowych, takich jak imię, nazwisko, email i telefon. Formularz powinien być powiązany z odpowiednim zapytaniem PHP do zapisania danych w tabeli stworzonej w poprzednim zadaniu.

3. Tworzenie 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;

4. Modyfikowanie danych w tabeli

Stwórz formularz do edycji danych kontaktowych. Formularz powinien umożliwiać zmianę imienia, nazwiska, emaila i telefonu, a następnie zaktualizowanie tych danych w bazie danych.

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

5. Tworzenie zapytań z podzapytaniami

Stwórz zapytanie SQL, które zaciągnie dane o kontaktach, których numer telefonu zaczyna się na określoną cyfrę, np. "5".

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

6. Tworzenie raportu z danych w bazie

Stwórz zapytanie, które generuje raport kontaktów, posortowanych alfabetycznie po nazwisku. Wyświetl dane na stronie, formatując je w formie czytelnego raportu.

SELECT * FROM kontakty ORDER BY nazwisko;

7. Modyfikowanie struktury bazy danych

Dodaj do tabeli "kontakty" nową kolumnę przechowującą datę dodania kontaktu. Użyj odpowiedniego zapytania ALTER TABLE, aby wprowadzić zmiany w strukturze tabeli.

ALTER TABLE kontakty ADD COLUMN data_dodania DATETIME DEFAULT CURRENT_TIMESTAMP;

8. Usuwanie elementów struktury bazy danych

Usuń z tabeli "kontakty" kolumnę, która przechowuje numer telefonu, ponieważ nie jest już potrzebna w projekcie.

ALTER TABLE kontakty DROP COLUMN telefon;

9. Udostępnianie bazy danych w chmurze

Zadbaj o odpowiednie zabezpieczenie bazy danych i udostępnienie jej w chmurze. Zapewnij dostęp tylko autoryzowanym użytkownikom, przy użyciu odpowiednich mechanizmów bezpieczeństwa, takich jak szyfrowanie.

10. Publikowanie bazy danych w sieci

Przygotuj bazę danych do opublikowania w sieci. Zadbaj o poprawną konfigurację serwera, w tym uprawnienia użytkowników oraz zabezpieczenia przed atakami typu SQL Injection.

11. Rozbudowa struktury bazy danych przez tworzenie nowych tabel

Stwórz nową tabelę „produkty”, która będzie zawierała dane o produktach w sklepie internetowym, takie jak nazwa, cena, ilość w magazynie oraz data dodania produktu.

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

12. Dodawanie danych do nowej tabeli

Dodaj do tabeli „produkty” dane o trzech różnych produktach, używając odpowiedniego zapytania INSERT INTO.

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

13. Tworzenie zapytań z agregatami

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

SELECT AVG(cena) AS srednia_cena FROM produkty;

14. Tworzenie zapytań z grupowaniem

Stwórz zapytanie, które zgrupuje produkty w tabeli „produkty” według ich ceny (np. „tanie”, „średnia cena” i „drogie”) i wyświetli ich liczbę 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;

15. Tworzenie zapytań z warunkiem AND i OR

Stwórz zapytanie, które zwróci wszystkie produkty w tabeli „produkty”, które mają cenę większą niż 50 i ilość w magazynie większą niż 5. Zastosuj warunki AND i OR.

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

16. Aktualizacja danych w tabeli

Stwórz zapytanie, które zaktualizuje cenę produktu o ID 2 na 59.99 oraz zmieni ilość w magazynie na 10.

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

17. Usuwanie 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

Stwórz zapytanie, które połączy tabelę „produkty” z tabelą „kontakty” (zakładając, że w każdej tabeli znajduje się pole „id” jako klucz główny) i wyświetli dane o produktach oraz ich właścicielach.

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

19. Tworzenie zapytań z wykorzystaniem podzapytań

Stwórz zapytanie, które znajdzie produkty, których cena jest wyższa niż średnia cena produktów w tabeli „produkty”.

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

20. Tworzenie zapytań z LIMIT

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

SELECT * FROM produkty LIMIT 5;

21. Tworzenie zapytań z użyciem DISTINCT

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

SELECT DISTINCT cena FROM produkty;

22. Tworzenie zapytań z użyciem IN

Stwórz zapytanie, które zwróci produkty, których cena należy do zestawu wartości (np. 50, 100, 150).

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

23. Tworzenie procedury w SQL

Stwórz prostą procedurę w SQL, która zwróci wszystkie produkty w tabeli „produkty” o cenie większej niż określona wartość (przekazywana 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 ;

24. Tworzenie funkcji w SQL

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

DELIMITER // CREATE FUNCTION calculate_total_sales() RETURNS DECIMAL(10,2) BEGIN DECLARE total_sales DECIMAL(10,2); SELECT SUM(cena * ilosc) INTO total_sales FROM produkty; RETURN total_sales; END // DELIMITER ;

Ćwiczenia z baz danych – SQL i formularze