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:

  1. Pobierz i zainstaluj DB Browser for SQLite http://sqlitebrowser.org/sqlite_1-e1541772301103.png
  2. 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.sqlite_2.png
  3. 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!sqlite_3.png
  4. 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.

sqlite_4.png

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ć.