ćwiczenia w tworzeniu kwerend (sql)


Pobierz plik sql
Pobierz plik bazy w formacie sql
Pobierz bazę sql w formacie zip
Pobrany plik (skrypt sql) uruchom w środowisku bazy mysql. W wyniku tego powstanie 7 tabel, na których będą wykonywane kolejne ćwiczenia.

Funkcje agregujące
Funkcje agregujące w SQL Server w odróżnieniu od pozostałych funkcji cechuje przede wszytkim to, że zwracają wynik z obliczeń pionowych wykonanych na kolumnie. Za ich pomocą możemy obliczyć przede wszystkim sumę kolumny, średnią z wartości w kolumnie, zliczyć wartości w kolumnie, obliczyć najniższą i najwyższą wartość występującą w danej kolumnie Najważniejsze funkcje agregujące MsSQL:

AVG – średnia
SUM – suma
MIN – minimum
MAX – maximum
COUNT – licznik wartości, zwaraca wynik o typie danych int
COUNT_BIG – licznik wartości, zwraca wynik o typie danych bigint


Przykład:
select AVG(cena) AS srednia FROM filmy;
Grupowanie danych
Język SQL służy nie tylko do odczytywania i łączenia pojedynczych rekordów. Dzięki zastosowaniu operacji grupowania, nazywanego też agregacją (ang. grouping, aggregation), możliwe jest wyliczanie sum, średnich, odchyleń, minimów, maksimów itp. na podstawie wielu rekordów funkcje agregujące - powyżej).
Zapytania SQL dokonujące agregacji – tzw. zapytania agregujące – posługują się trzema ważnymi elementami składniowymi:

  • klauzulą GROUP BY wskazującą sposób podziału rekordów tabeli na tzw. grupy,
  • funkcjami grupowymi SQL, które umożliwiają wyliczanie sum, średnich, odchyleń, minimów, maksimów itp. dla każdej grupy,
  • klauzulą HAVING umożliwiającą odrzucenie grup niespełniających podanego warunku logicznego.

    Zadaniem klauzuli GROUP BY jest tymczasowy podział rekordów tabeli na tzw. grupy. Dla każdej z grup zostanie następnie wyznaczona jedna wartość funkcji grupowej. Podział rekordów na grupy odbywa się w oparciu o wartości wskazanej kolumny (lub kolumn). Rekordy posiadające jednakową wartość takiej kolumny stanowią jedną grupę.

    Składnia polecenia SQL dokonującego agregacji:

    select funkcja_grupowa(kolumna) from tabela group by kolumna_grupująca
    gdzie w miejscu słowa „funkcja_grupowa” należy umieścić nazwę funkcji grupowej SQL, w miejscu słowa „kolumna” należy umieścić nazwę kolumny, której wartości mają być przeliczone przez funkcję grupową, w miejscu słowa „tabela” należy umieścić nazwę tabeli, a w miejscu słowa „kolumna_grupująca” należy umieścić nazwę kolumny determinującej podział rekordów na grupy.

    • Ćwiczenia / zadania

    Ćw 1. Utwórz bazę "zaklad".

    Wykorzystaj polecenie CREATE DATABASE [nazwa]

    Ćw 2. Wybierz bazę zaklad i utwórz w niej tabelę pracownicy zawierającą informację o pracownikach (id, imie, nazwisko, miasto, staz, stanowisko, pensja ).

    W celu utworzenia tabeli pracownicy, zawierającą informację o pracownikach ( id, imie, nazwisko, miasto, staz, stanowisko, pensja) wpisz następującą komendę:
    CREATE TABLE pracownicy ( id INT, imie VARCHAR(20), nazwisko VARCHAR(20), miasto VARCHAR(20), staz INT, stanowisko VARCHAR(20), pensja DECIMAL(9,2));
    a następnie sprawdź czy tabela istnieje, komendą SHOW TABLES;

    Ćw.3. Dopisz do tabeli następujące rekordy:

    2,Jan,Nowak,Warszawa,6,Informatyk,6000,
    3,Waclaw,Jarzabek,Warszawa,15,Portier,1300,
    5,Anna,Nowak,Warszawa,7,Ksiegowy,6000,
    6,Justyna,Oliwa,Krakow,10,Asystent,2800,
    7,Kamil,Wieczorek,Poznan,3,Asystent,2250,
    8,Adam,Orzech,Poznan,6,Informatyk,5200,
    9,Zenon,Kowalski,Warszawa,10,Prezes,14800,
    10,Kamila,Ostrowska,Krakow,11,Informatyk,5900,
    i jeszcze dodatkowo 3 własne

    insert into pracownicy values ('2' ,'Jan', 'Nowak' ,'Warszawa', '6', 'Informatyk','6000');
    insert into pracownicy values ('3', 'Waclaw', 'Jarzabek', 'Warszawa', '15', 'Portier', '1300');
    insert into pracownicy values ('5', 'Anna', 'Nowak', 'Warszawa', '7', 'Ksiegowy', '6000');
    insert into pracownicy values ('6', 'Justyna', 'Oliwa', 'Krakow', '10', 'Asystent', '2800');
    insert into pracownicy values ('7', 'Kamil', 'Wieczorek', 'Poznan', '3', 'Asystent', '2250');
    insert into pracownicy values ('8', 'Adam', 'Orzech', 'Poznan', '6', 'Informatyk', '5200');
    insert into pracownicy values ('9', 'Zenon', 'Kowalski', 'Warszawa', '10', 'Prezes', '14800');
    insert into pracownicy values ('10', 'Kamila', 'Ostrowska', 'Krakow', '11', 'Informatyk', '5900');
    insert into pracownicy values ('11', 'Stanislaw', 'Zalas', 'Olsztyn', '6', 'Sprzedawca', '1900');
    insert into pracownicy values ('12', 'Roman', 'Strzelecki', 'Krakow', '3', 'Nauczyciel', '3000');
    insert into pracownicy values ('13', 'Wieslaw', 'Gomolka', 'Kielce', '6', 'Inzynier', '6000');
    insert into pracownicy values ('14', 'Joalnta', 'Kwasniewska', 'Warszawa', '10', 'Redaktor', '20000');

    Ćw.4. Wyświetl wszystkie rekordy z tabeli "pracownicy".

    Użyj polecenia SELECT * FROM pracownicy;

    Ćw.5. Wypisz zawartość tabeli "pracownicy" za pomocą następujących komend:
    SELECT * FROM pracownicy;
    SELECT id FROM pracownicy;
    SELECT id ,imie, nazwisko FROM pracownicy;
    SELECT id , pensja FROM pracownicy;


    Wykonaj operację złożoną:
    SELECT stanowisko, AVG(pensja) FROM pracownicy GROUP BY stanowisko;

    Użyj poleceń z ćwiczenia

    Ćw.6. Dokonaj aktualizacji w tabeli "pracownicy" poprawiając pole "pensja" na 6800 dla pracownika: Jan Nowak
    Następnie wypisz zawartość rekordu tylko dla Jana Nowaka

    Użyj poleceń:
    UPDATE pracownicy SET pensja =6800 WHERE imie = ‘Jan’ AND nazwisko = ‘Nowak’;
    SELECT * FROM pracownicy WHERE imie = ‘Jan’ AND nazwisko = ‘Nowak’;

    Ćw.7. Dokonaj usunięcia rekordu/rekordów dla których pensja='6000' lub pensja='6800' a następnie wyświetl wszystkie rekordy z tabeli.


    Użyj poleceń:
    DELETE FROM pracownicy WHERE pensja = ‘6800’ OR pensja= ‘6000’;
    SELECT * FROM pracownicy;

    Ćw.8. Wykonaj samodzielnie następujące operacje na bazie:
    1.Dodaj rekord ze swoim imieniem i nazwiskiem (pozostałe pola wypełnij dowolnie wg uznania). W tym celu przepisz wartość polom rekordu takim jak: Id, imie, nazwisko, miasto, staz, stanowisko, pensja stosując odpowiednie zapytanie SQL (jedno z podanych wcześniej).
    2.W celu sprawdzenia zawartości bazy wypisz zawartość wszystkich rekordów tabeli.
    3.Wypisz całą zawartość tylko tych rekordów, gdzie stanowisko = ‘Asystent’.
    4.Zmień pensjewszystkim asystentom na 3000.
    5.Wypisz tylko pensje dla stanowiska Asystent.
    6.Usuń rekord, dla którego nazwisko=’Zalas’. Sprawdź czy zmodyfikowano tablicę.

    Użyj poleceń:
    UPDATE pracownicy SET pensja =6800 WHERE imie = ‘Jan’ AND nazwisko = ‘Nowak’;
    SELECT * FROM pracownicy WHERE imie = ‘Jan’ AND nazwisko = ‘Nowak’;

    Poprzednia lekcja
    Wróć do spisu tematów
    Następna lekcja