Weź udział w akcji i poznaj podstawy języka SQL
Witajcie żołnierze! Rozpoczynamy misję: Halo! Tu baza SQL. Naszym celem jest podniesienie własnych kwalifikacji i opanowanie podstaw tajnych baz danych.
1. Dzień pierwszy: Rekonesans i ćwiczenia bojowe!
Twoim pierwszym zadaniem jest instalacja i uruchomienie bazy SQL dla SQLite. Aby poprawnie wykonać zadanie zastosuj się do poniższych instrukcji:
- Pobierz i zainstaluj DB Browser for SQLite http://sqlitebrowser.org/
- Natychmiast po zainstalowaniu uruchom aplikację DB Browser for SQLite. Kliknij przycisk „New Database” i zapisz nowy plik. Zamknij okno „Edit table definitione”, które się pojawiło.
- Wybierz zakładkę „Execute SQL”Pobierz skrypt SQLite -> pobierz tutaj!
Wklej cały skrypt do okienka SQL 1 i kliknij „Execute SQL” (play). Baza gotowa! - Poniżej szybkie przeszkolenie z obsługi
- Dostępne tabele znajdziesz w zakładce „Database Structure”
- Zawartość tabel pokazana jest w zakładce „Browse Data”. Przeglądaną tabelę możesz zmienić na liście rozwijanej nad widokiem tabeli.
- Zapytania piszesz w miejscu wklejenia skyptu. Klikając „Execute SQL” (play) uruchomisz swoje zapytanie.
- UWAGA! Zapytania nie są zapisywane w pliku z bazą danych. Musisz trzymać je w osobnym pliku.
Udało się?
Przed Tobą ćwiczenia bojowe!
Ćwiczenie A:
Podaj imiona, nazwiska i stopnie wszystkich kaprali.
Ćwiczenie B:
Znajdź ile misji odbyło się w kwietniu?
PS Jeśli do wykonania zadań chcesz wykorzystać bazę Oracle lub MSSQL podajemy adekwatne skrypty.
Skrypt MSSQL-> pobierz tutaj!
Skrypt Oracle- pobierz tutaj!
Pamiętaj jednak, że rozwiązanie zadań krok po kroku podamy tylko dla skryptów SQLite! Tyle na dziś! Odmaszerować!
2. Dzień drugi - rozwiązanie ćwiczenia 1. i pierwsze starcie na froncie (ćwiczenie 2.)
Podajemy rozwiązanie ćwiczenia 1 A dla skryptów SQLite - krok po kroku:
/* Krok 1: Biorę wszystkie imiona, nazwiska i stopnie komandosów */ SELECT Imie, Nazwisko, Stopien FROM Komandosi; /* Krok 2: Wybieram tylko kaprali */ SELECT Imie, Nazwisko, Stopien FROM Komandosi WHERE Stopien = ‘Kapral’; /* Krok 3: Muszę wziąć pod uwagę również starszych kaprali, w dodatku potrzebuję pominąć wielkość znaków, bo słowo kapral pojawia się pisane wielką i małą literą */ SELECT Imie, Nazwisko, Stopien FROM Komandosi WHERE LOWER(Stopien) LIKE ‘%kapral’;
Podajemy rozwiązanie ćwiczenia 1 B dla skryptów SQLite - krok po kroku:
/* Krok 1: Wybieram tylko misje z kwietnia korzystając z funkcji strftime: */ SELECT * FROM Misje WHERE strftime(‘%m’, DataMisji) = ‘04’; /* Krok 2: zliczam ilość misji */ SELECT COUNT(*) AS IloscMisji FROM Misje WHERE strftime('%m', DataMisji) = '04';
Podajemy rozwiązanie ćwiczenia 1 A i 1B dla skryptów MSSQL:
/* Podaj imiona, nazwiska i stopnie wszystkich kaprali */ SELECT Imie, Nazwisko, Stopien FROM Komandosi WHERE LOWER(Stopien) LIKE '%kapral' /* Ile misji odbyło się w kwietniu? */ SELECT COUNT(*) AS IloscMisji FROM Misje WHERE MONTH(DataMisji) = 4
Podajemy rozwiązanie ćwiczenia 1 A i 1B dla skryptów Oracle:
/* Podaj imiona, nazwiska i stopnie wszystkich kaprali */ SELECT Imie, Nazwisko, Stopien FROM Komandosi WHERE LOWER(Stopien) LIKE '%kapral'; /* Ile misji odbyło się w kwietniu? */ SELECT COUNT(*) AS IloscMisji FROM Misje WHERE EXTRACT(month FROM DataMisji) = 4;
Żołnierze! Biegiem marsz! Do drugiego ćwiczenia NAPRZÓD!
Ćwiczenie 2 A:
Podaj, ilu komandosów potrafi prowadzić opancerzone wozy?
Ćwiczenie 2 B:
Znajdź dowódców misji. Podaj ich imiona, nazwiska i ilość prowadzonych misji.
3. Dzień trzeci - rozwiązanie ćwiczenia 2. i narada wojenna (ćwiczenie 3.)
Podajemy rozwiązanie ćwiczenia 2 A dla skryptów SQLite - krok po kroku:
/* Krok 1:Potrzebuję informacji o komandosach i ich umiejętnościach, zatem muszę połączyć tabele: */ SELECT * FROM Komandosi k INNER JOIN UmiejetnosciKomandosow uk ON uk.NumerKomandosa = k.Id INNER JOIN UmiejetnosciSpecjalne us ON us.Id = uk.NumerUmiejetnosci; /* Krok 2: Wybieram tylko umiejętność prowadzenia wozów opancerzonych */ SELECT * FROM Komandosi k INNER JOIN UmiejetnosciKomandosow uk ON uk.NumerKomandosa = k.Id INNER JOIN UmiejetnosciSpecjalne us ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Prowadzenie wozów opancerzonych'; /* Krok 3: Zliczam ilość elementów */ SELECT COUNT(*) AS IloscKomandosow FROM Komandosi k INNER JOIN UmiejetnosciKomandosow uk ON uk.NumerKomandosa = k.Id INNER JOIN UmiejetnosciSpecjalne us ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Prowadzenie wozów opancerzonych'; /* Krok 4: Optymalizacja. Tabela z komandosami nie jest potrzebna do wyniku, ponieważ w tabeli UmiejetnosciKomandosow mam informację o komandosie i jego umiejętnościach. */ SELECT COUNT(*) AS IloscKomandosow FROM UmiejetnosciSpecjalne us INNER JOIN UmiejetnosciKomandosow uk ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Prowadzenie wozów opancerzonych';
Podajemy rozwiązanie ćwiczenia 2 B dla skryptów SQLite - krok po kroku:
/* Krok 1: Do uzyskania wyniku potrzebuję tabel Misje i Komandosi */ SELECT Imie, Nazwisko FROM Misje m INNER JOIN Komandosi k ON m.NumerDowodcy = k.Id; /* Krok 2 :Muszę dla każdego komandosa policzyć ilość elementów. Aby tego dokonać muszę policzyć dla każdego komandosa ilość wystąpień. Zidentyfikować komandosa mogę po jego imieniu i nazwisku, zatem pogrupuję po tych informacjach,a na koniec policzę ilość elementów dla każdej grupy */ SELECT Imie, Nazwisko, COUNT(*) AS IloscProwadzonychMisji FROM Misje m INNER JOIN Komandosi k ON m.NumerDowodcy = k.Id GROUP BY Imie, Nazwisko;
Podajemy rozwiązanie ćwiczenia 2 A i 2B dla skryptów MSSQL:
/* Ilu komandosów potrafi prowadzić opancerzone wozy? */ SELECT COUNT(*) AS IloscKomandosow FROM UmiejetnosciSpecjalne us INNER JOIN UmiejetnosciKomandosow uk ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Prowadzenie wozów opancerzonych' /* Znajdź dowódców misji. Podaj ich imiona, nazwiska i ilość prowadzonych misji */ SELECT Imie, Nazwisko, COUNT(*) AS IloscProwadzonychMisji FROM Misje m INNER JOIN Komandosi k ON m.NumerDowodcy = k.Id GROUP BY Imie, Nazwisko
Podajemy rozwiązanie ćwiczenia 2 A i 2B dla skryptów Oracle:
/* Ilu komandosów potrfi prowadzić opancerzone wozy? */ SELECT COUNT(*) AS IloscKomandosow FROM UmiejetnosciSpecjalne us INNER JOIN UmiejetnosciKomandosow uk ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Prowadzenie wozów opancerzonych'; /* Znajdź dowódców misji. Podaj ich imiona, nazwiska i ilość prowadzonych misji */ SELECT Imie, Nazwisko, COUNT(*) AS IloscProwadzonychMisji FROM Misje m INNER JOIN Komandosi k ON m.NumerDowodcy = k.Id GROUP BY Imie, Nazwisko;
Żołnierze! Do boju! Przed Wami 3. ćwiczenie!
Ćwiczenie 3 A:
Który komandos nie brał udziału w misji?
Ćwiczenie 3 B:
Ilu komandosów brało udział w misjach?
Do dzieła! Jeśli rozwiązanie zadania sprawia Wam trudności - nie poddawajcie się! We wtorek (5 czerwca) opublikujemy rozwiązanie KROK PO KROKU.
4. Dzień czwarty - rozwiązanie ćwiczenia 3. i czas próby (ćwiczenie 4.)
Podajemy rozwiązanie ćwiczenia 3 A dla skryptów SQLite - krok po kroku:
/* Krok 1: Wyciągam wszystkich komandosów */ SELECT Imie, Nazwisko, Stopien FROM Komandosi; /* Krok 2: Łączę komandosów z misjami tak, aby mieć wszystkich komandosów. Nie potrzebuję informacji szczegółowych o misji, więc wystarczy tabela KomandosiNaMisji */ SELECT Imie, Nazwisko, Stopien FROM Komandosi k LEFT JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa; /* Krok 3: Wybieram tylko tych komandosów, którym nie przyczepił się żaden wiersz z misją */ SELECT Imie, Nazwisko, Stopien FROM Komandosi k LEFT JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa WHERE knm.Id IS NULL;
Podajemy rozwiązanie ćwiczenia 3 A dla skryptów SQLite - krok po kroku:
/* Krok 1: Potrzebuję informacji o komandosach i misjach, dlatego łączę tabelę z komandosami i misjami przez tabelę komandosi na misji */ SELECT * FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji; /* Krok 2: Muszę w ramach konkretnej misji policzyć ilość wystąpień komandosów, dlatego grupuję po nazwie i dacie misji(będę w stanie zidentyfikować później po tych informacjach jaka to jest misja) */ SELECT m.Nazwa, m.DataMisji, COUNT(*) AS IloscKomandosow FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji GROUP BY m.Nazwa, m.DataMisji;
Podajemy rozwiązanie ćwiczenia 3 A i 3B dla skryptów MSSQL:
/* Który komandos nie brał udziału w misji? */ SELECT Imie, Nazwisko, Stopien FROM Komandosi k LEFT JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa WHERE knm.Id IS NULL /* Ilu komandosów brało udział w misjach? */ SELECT m.Nazwa, m.DataMisji, COUNT(*) AS IloscKomandosow FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji GROUP BY m.Nazwa, m.DataMisji
Podajemy rozwiązanie ćwiczenia 3 A i 3B dla skryptów Oracle:
/* Który komandos nie brał udziału w misji? */ SELECT Imie, Nazwisko, Stopien FROM Komandosi k LEFT JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa WHERE knm.Id IS NULL; /* Ilu komandosów brało udział w misjach? */ SELECT m.Nazwa, m.DataMisji, COUNT(*) AS IloscKomandosow FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji GROUP BY m.Nazwa, m.DataMisji;
Komandosi! Oto Wasz czas próby - najtrudniejsze z najtrudniejszych zadań w naszej operacji.
Ćwiczenie 4 A:
Podaj imiona, nazwiska i ilość misji snajperów biorących udział w majowych misjach
Ćwiczenie 4 B:
Podaj imiona i nazwiska komandosów dowodzonych przez dwóch dowódców
Do dzieła! Jeśli rozwiązanie zadania sprawia Wam trudności - nie poddawajcie się! W środę (6 czerwca) opublikujemy rozwiązanie KROK PO KROKU.
5. Dzień piąty - rozwiązanie ćwiczenia 4 i zakończenie misji!
Podajemy rozwiązanie ćwiczenia 4 A dla skryptów SQLite - krok po kroku:
/* Krok 1: potrzebuję informacji o misjach (misja w maju), komandosach (imię i nazwisko) oraz umiejętnościach (tylko snajperzy). Łączę zatem ze sobą wszystkie 5 tabel.*/ SELECT * FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN UmiejetnosciKomandosow uk ON k.Id = uk.NumerKomandosa INNER JOIN UmiejetnosciSpecjalne us ON us.Id = uk.NumerUmiejetnosci; /* Krok 2: Wybieram tylko snajperów i misje majowe.*/ SELECT * FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN UmiejetnosciKomandosow uk ON k.Id = uk.NumerKomandosa INNER JOIN UmiejetnosciSpecjalne us ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Snajper' AND strftime('%m', m.DataMisji) = '05'; /* Krok 3: Liczę ilość misji każdego snajpera */ SELECT Imie, Nazwisko, COUNT(*) AS IloscMisji FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN UmiejetnosciKomandosow uk ON k.Id = uk.NumerKomandosa INNER JOIN UmiejetnosciSpecjalne us ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Snajper' AND strftime('%m', m.DataMisji) = '05' GROUP BY Imie, Nazwisko;
Podajemy rozwiązanie ćwiczenia 4 B dla skryptów SQLite - krok po kroku:
/* Krok 1: Łączę komandosa z jego dowódcą dzięki informacją zawartym w tabeli z misjami: */ SELECT * FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN Komandosi d ON d.Id = m.NumerDowodcy; /* Krok 2: Grupuję dane po imieniu, nazwisku i identyfikatorze dowódcy, aby znaleźć informacje o dowódcach każdego komandosa. */ SELECT k.Imie, k.Nazwisko FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN Komandosi d ON d.Id = m.NumerDowodcy GROUP BY k.Imie, k.Nazwisko, d.Id; /* Krok 3: Muszę policzyć ile razy każdy komandos się pojawia, ponieważ pojawia się raz dla każdego dowódcy. Korzystam tu z zapytania zagnieżdżonego, ponieważ nie jestem w stanie w pojedynczym zapytaniu dwukrotnie grupować danych. Na koniec wyciągam tylko te grupy, które mają dwa elementy.*/ SELECT Imie, Nazwisko FROM ( SELECT k.Imie, k.Nazwisko FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN Komandosi d ON d.Id = m.NumerDowodcy GROUP BY k.Imie, k.Nazwisko, d.Id) x GROUP BY Imie, Nazwisko HAVING COUNT(*) = 2;
Podajemy rozwiązanie ćwiczenia 4 A i 4B dla skryptów MSSQL:
/* Podaj imiona, nazwiska i ilość misji snajperów biorących udział w majowych misjach */ SELECT Imie, Nazwisko, COUNT(*) AS IloscMisji FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN UmiejetnosciKomandosow uk ON k.Id = uk.NumerKomandosa INNER JOIN UmiejetnosciSpecjalne us ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Snajper' AND MONTH(m.DataMisji) = 5 GROUP BY Imie, Nazwisko /* Podaj imiona i nazwiska komandosów dowodzonych przez dwóch dowódców */ SELECT Imie, Nazwisko FROM ( SELECT k.Imie, k.Nazwisko FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN Komandosi d ON d.Id = m.NumerDowodcy GROUP BY k.Imie, k.Nazwisko, d.Id) x GROUP BY Imie, Nazwisko HAVING COUNT(*) = 2
Podajemy rozwiązanie ćwiczenia 4 A i 4 B dla skryptów Oracle:
/* Podaj imiona, nazwiska i ilość misji snajperów biorących udział w majowych misjach */ SELECT Imie, Nazwisko, COUNT(*) AS IloscMisji FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN UmiejetnosciKomandosow uk ON k.Id = uk.NumerKomandosa INNER JOIN UmiejetnosciSpecjalne us ON us.Id = uk.NumerUmiejetnosci WHERE us.Nazwa = 'Snajper' AND EXTRACT(month FROM m.DataMisji) = 5 GROUP BY Imie, Nazwisko; /* Podaj imiona i nazwiska komandosów dowodzonych przez dwóch dowódców */ SELECT Imie, Nazwisko FROM ( SELECT k.Imie, k.Nazwisko FROM Komandosi k INNER JOIN KomandosiNaMisji knm ON k.Id = knm.NumerKomandosa INNER JOIN Misje m ON m.Id = knm.NumerMisji INNER JOIN Komandosi d ON d.Id = m.NumerDowodcy GROUP BY k.Imie, k.Nazwisko, d.Id) x GROUP BY Imie, Nazwisko HAVING COUNT(*) = 2; Komandosi! Informujemy, że misja została zakończona! Odmaszerować.