Analizujemy bazy danych i język SQL. (Część 6 - Sprawdzenie zadania końcowego) - "Projekt Java od A do Z"

Artykuł z serii o tworzeniu projektu w Javie (linki do innych materiałów znajdują się na końcu). Jego celem jest analiza kluczowych technologii, efektem jest napisanie bota telegramowego. Ta część zawiera analizę końcowego zadania w bazie danych. Witam Was drodzy czytelnicy. Dziś przeanalizujemy zadanie z ostatniego artykułu w bazie. Jest to interesujące, ponieważ jest przeznaczone do rozmów kwalifikacyjnych na poziomie średnim. Oznacza to, że po tym zadaniu możesz już udać się na rozmowę kwalifikacyjną i pomyślnie zaliczyć przynajmniej część tego, co dotyczy relacyjnych baz danych. Wiem, jak potrzebny może być ten artykuł, dlatego włożę całe moje doświadczenie, aby był użyteczny i interesujący. A jeśli nie zaśniesz w połowie artykułu, będzie to oznaczać, że osiągnąłem swój cel. Nie będę powtarzał całego zadania: przytoczę je przed wykonaniem każdego zadania, podkreślając je kursywą. Spodziewam się, że każdy, kto przeczyta ten artykuł, wykona wszystkie zapytania w swojej bazie danych i otrzyma to samo. Przyniesie to maksymalne korzyści dla firmy. I będę trochę szczęśliwszy na myśl, że pomogłem komuś w naszym trudnym zadaniu)

Ćwiczenie 1

Napisz skrypt SQL tworzący tabelę „Student” zawierającą następujące pola: id (klucz podstawowy), imię i nazwisko, nazwisko, e_mail (unikalny). Już to zrobiliśmy, więc nie powinno być żadnych problemów. W skrypcie należy podać klucz podstawowy oraz unikalne pole, różniące się od podstawowego. Najpierw utwórzmy nową bazę danych dla tego zadania: $ CREATE DATABASE final_task; I skorzystajmy z tej bazy danych: $ USE final_task; Gdy środowisko jest skonfigurowane i gotowe do uruchomienia zadania, możemy napisać następujący skrypt: $ CREATE TABLE student ( id INT AUTO_INCREMENT, imię VARCHAR(40), nazwisko VARCHAR(50), e-mail VARCHAR(100), KLUCZ PODSTAWOWY ( id), UNIKALNY (e-mail) ); Jak dotąd nie ma nic nowego w porównaniu z tym, przez co już przeszliśmy. Wszelkie komentarze są niepotrzebne, przejdźmy dalej.

Zadanie 2-3

Napisz skrypt SQL tworzący tabelę „Książka” zawierającą następujące pola: id, tytuł (id + tytuł = klucz podstawowy). Połącz „Studenta” i „Książkę” relacją „Student” jeden do wielu „Książka”. Połączmy dwa zadania w jedno, żeby było szybciej i wygodniej. O tym, jak dodać osobny klucz obcy, mówiłem już w poprzednich artykułach. Co więcej, musimy pamiętać, w jaki sposób nawiązujemy połączenia i przez co. Pomoże ci poprzedni artykuł, a oto skrypt: $ CREATE TABLE book ( id INT, tytuł VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, tytuł), KLUCZ OBCY (student_id) REFERENCJE student (id ) ); W ten prosty sposób dodaliśmy klucz złożony do naszej tabeli PRIMARY KEY (id, title) , teraz klucz będzie dokładnie parą. Oznacza to, że w tabeli może znajdować się więcej niż jedna identyczna wartość pola id. I dokładnie to samo w przypadku tytułu.

Zadanie 4

Napisz skrypt SQL tworzący tabelę „Nauczyciel” zawierającą następujące pola: id (klucz podstawowy), imię i nazwisko, nazwisko, e_mail (unikalny), temat. Kontynuujemy przygotowywanie naszej bazy danych na zapytania, tworzymy tabelę nauczycieli: $ CREATE TABLE nauczyciel (id INT AUTO_INCREMENT, imię VARCHAR(30), nazwisko VARCHAR(30), e-mail VARCHAR(100), temat VARCHAR(40), KLUCZ PODSTAWOWY ( id), UNIKALNY (e-mail) ); Póki co nie jest to trudne, prawda? Trzy zadania już za nami!

Zadanie 5

Połącz „Uczeń” i „Nauczyciel” relacją „Uczeń” wiele do wielu Nauczyciel. Teraz jest ciekawiej! Właśnie rozmawialiśmy o tym ostatnim razem. Przypomnę, co jest potrzebne do osiągnięcia tego celu: musisz utworzyć tabelę pośrednią, w której będą przechowywane pary uczeń-nauczyciel. Za jego pomocą możliwe będzie utworzenie relacji wiele do wielu. Dlatego utwórzmy tabelę studenci_x_techers . Podejście do nazewnictwa jest otwarte i może również wyglądać następująco: student_teacher . $ UTWÓRZ TABELĘ studenci_x_teachers ( student_id INT NIE NULL, nauczyciel_id INT NIE NULL, KLUCZ PODSTAWOWY (student_id, nauczyciel_id), KLUCZ OBCY (student_id) REFERENCJE student(id), KLUCZ OBCY (teacher_id) REFERENCJE nauczyciel(id) ); Jak widać wszystko jest zrobione przejrzyście i spójnie. Mamy klucz złożony dla dwóch kluczy obcych: student_id i nauczyciel_id. Dlaczego także klucz obcy? Abyśmy mieli pewność, że w tabelach uczniów i nauczycieli istnieją zapisy dotyczące rejestrowanych par.

Zadanie 6

Wybierz „Student”, który ma „oro” w nazwisku, na przykład „Sidorov”, „Woronovsky”. Aby było to dla nas interesujące i wizualne, sugeruję najpierw dodać kilku uczniów, aby niektórzy z nich nadawali się do tej prośby, a niektórzy nie. Dlatego wypiszmy tych, którzy powinni zostać uwzględnieni w wyniku żądania: $ WSTAW DO studenta (imię, nazwisko, e-mail) WARTOŚCI („Ivan”, „Sidorov”, „ivan.sidorov@gmail.com”); $ WSTAW DO studenta (imię, nazwisko, e-mail) WARTOŚCI („Nikołaj”, „Woronowski”, „nikolaj.voronovsky@gmail.com”); A ci, którzy nie powinni wejść: $ WPISAĆ DO studenta (imię, nazwisko, e-mail) WARTOŚCI („Roman”, „Fortny”, „roman.fortny@gmail.com”); $ WSTAW DO studenta (imię, nazwisko, e-mail) WARTOŚCI('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Sprawdźmy wynik, spójrzmy na listę danych w tabeli uczniów: $ SELECT * FROM student; i otrzymujemy: W sumie są cztery rekordy, dwa z nich powinny się zmieścić, a dwa nie. Po przygotowaniu wszystkich danych do zapytania możemy złożyć zlecenie na samo zadanie: $ SELECT * FROM student WHERE nazwisko LIKE '%oro%'; W rezultacie Iwan i Mikołaj przejrzeli listę.

Zadanie 7

Kolejne zadanie, które czytamy: Wybierz z tabeli „Student” wszystkie nazwiska („nazwisko”) i liczbę ich powtórzeń. Weź pod uwagę, że w bazie danych znajdują się imienniki. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

nazwisko	ilość
Pietrow	15
Iwanow	12
Sidorow	3

Dla jasności musisz dodać więcej danych. Bez zbędnych ceregieli dodajmy jeszcze Pietrowów, Iwanowów i Sidorowów, którzy nie znają swojego pokrewieństwa ;) Nie będę wymyślać adresu email, po prostu wykluczę go z nowych wpisów. Uruchommy 12 razy następującą komendę: $ INSERT INTO student (imię, nazwisko) VALUES („Ivan”, „Ivanov”); Dodajmy 15 Pietrowów: $ WSTAW DO studenta (imię, nazwisko) WARTOŚCI („Petr”, „Petrov”); I dwóch Sidorovów (już mamy))): $ INSERT INTO student (imię, nazwisko) WARTOŚCI („Sidor”, „Sidorov”); Teraz dane są gotowe. Aby uzyskać takie dane należy dokonać grupowania, aby dokonać grupowania należy skorzystać z operatora Group By i to przy pomocy pola nazwisko. Można też zauważyć, że liczba powtórzeń jest oznaczona jako ilość i tutaj też trzeba pamiętać jak tworzyć aliasy w SQL: $ SELECT nazwisko, COUNT(*) jako ilość FROM student GROUP BY nazwisko ORDER BY COUNT(*) DESC ; Więc przesadziłem z Pietrowami - okazało się, że jest 16))

Zadanie 8

Warunek: Wybierz 3 najczęściej powtarzające się imiona z listy „Student”. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

nazwa	ilość
Aleksander	27
Siergiej	10
Piotr	7

Och, w tym celu mamy już Ivanów, Petersów i Sidorów. Dlatego nie ma potrzeby ich dodawania. Wiemy już, jak sortować. Jedyną rzeczą, o której dzisiaj nie rozmawialiśmy, jest wybór określonej liczby rekordów. Pojawiło się to już w poprzednich rozwiązaniach problemów z bazami danych. Kto nie czytał niech przeczyta. Resztę przejdźmy od razu do rzeczy: $ WYBIERZ nazwę, COUNT(*) jako ilość OD ucznia GROUP WEDŁUG imienia ZAMÓW PRZEZ LICZBĘ(*) OPIS LIMIT 3; Jak widać z zapytania, jeśli znasz kolejność operatorów w zapytaniu SELECT, nie będzie problemów z wykonaniem takiego zapytania. I to zadanie wciąż przed nami stoi. A wiedza, która została przedstawiona wcześniej, jest w zupełności wystarczająca, aby rozwiązać ten problem.

Zadanie 9

Warunek zadania: Wybierz „Ucznia”, który ma największą liczbę „Książek” i powiązanego „Nauczyciela”. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

Nazwisko nauczyciela	Nazwisko ucznia	ilość książki
Pietrow	Sidorow	7
Iwanow	Kowal	5
Pietrow	Kankava	2

Zatem to zadanie jest wyraźnie trudniejsze niż poprzednie, prawda? Nic dziwnego: to pachnie połączeniem... i więcej niż jednym) Najpierw musimy zrozumieć, co robić... Oczywiste jest, że ilość Książki wymaga pogrupowania. Ale co? I dlaczego powinniśmy je grupować? Zapytanie obejmuje trzy tabele, grupowanie i sortowanie. Sądząc po tym, że rekordy, w których nie ma ksiąg, nie są wyświetlane, oznacza to, że musisz skorzystać z INNER JOIN. Złożymy również prośbę o LEFT JOIN, aby nie było z tym problemów. Istnieje kilka opcji. Pierwszą rzeczą, którą robimy, jest połączenie trzech tabel w jeden rekord. Następnie grupujemy według uczniów i dodajemy do tego nazwisko nauczyciela. Co wybierzemy? Imię i nazwisko nauczyciela, ucznia i numer książek. Dodajmy dane do żądania:

• trzech nauczycieli;
• dziesięć książek;
• połącz dwóch uczniów z trzema nauczycielami.

Trzej nauczyciele

$ WSTAWIĆ WARTOŚCI nauczyciela (nazwisko) („Matvienko”); $ WSTAWIĆ WARTOŚCI nauczyciela (nazwiska) („Szewczenko”); $ WSTAWIĆ WARTOŚCI nauczyciela (nazwisko) („Vasilenko”);

10 książek

Przyjmę legitymacje uczniów klas I i II. Dołączę do nich książki. Ponieważ nie ustawiono AUTO_INCREMENT, aby nie zapisywać za każdym razem nowego identyfikatora, należy wykonać następujące czynności: $ ALTER TABLE książka MODIFY id INT NOT NULL AUTO_INCREMENT; Następnie dodaj książki dla pierwszego ucznia: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ WSTAW DO książki (tytuł, identyfikator_studenta) WARTOŚCI('książka2', 1); $ WSTAW DO książki (tytuł, identyfikator_studenta) WARTOŚCI('książka3', 1); $ WSTAW DO książki (tytuł, identyfikator_studenta) WARTOŚCI('książka4', 1); $ WSTAW DO książki (tytuł, identyfikator_studenta) WARTOŚCI('książka5', 1); $ WSTAW DO książki (tytuł, identyfikator_studenta) WARTOŚCI('książka6', 1); Oraz książki dla drugiego ucznia: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ WSTAW DO książki (tytuł, identyfikator_studenta) WARTOŚCI('książka8', 2); $ WSTAW DO książki (tytuł, identyfikator_studenta) WARTOŚCI('książka9', 2); $ WSTAW DO książki (tytuł, identyfikator_studenta) WARTOŚCI('książka10', 2);

Połączenia nauczyciel-uczeń

W tym celu do tabeli dodaj studentów_x_nauczycieli: $ WSTAWIĆ WARTOŚCI studentów_x_nauczycieli (1,1); $INSERT INTO studenci_x_nauczyciele WARTOŚCI(1,2); $INSERT INTO studenci_x_nauczyciele WARTOŚCI(2,3);

Zrealizujmy prośbę

Wykonujemy pierwszy etap - łączymy trzy tabele w jeden rekord: $ SELECT * FROM nauczyciel tch INNER JOIN studenci_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.id_studenta; Oczywiście nie mamy jeszcze żadnych rekordów, jednak widzimy, że pomyślnie połączyliśmy trzy tabele. Teraz dodajemy grupowanie, sortowanie książek i potrzebne nam pola: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) jako książki FROM student st INNER JOIN book b ON st.id = b.student_id INNER DOŁĄCZ studenci_x_teachers st_x_tch ON st.id = st_x_tch.student_id DOŁĄCZ DO WEWNĘTRZNEGO nauczyciel tch ON tch.id = st_x_tch.teacher_id GRUPA WG st.id ZAMÓW WG książek DESC; Ale pojawia się błąd w SQL i następująca odpowiedź: Kod błędu: 1055. Wyrażenie nr 1 listy SELECT nie znajduje się w klauzuli GROUP BY i zawiera niezagregowaną kolumnę „final_task.tch.last_name”, która nie jest funkcjonalnie zależna od kolumn w GROUP BY klauzula Nie działa uwzględnienie tych elementów, ponieważ istnieje relacja „wiele do wielu” pomiędzy nauczycielem a uczniem. I to prawda: nie możemy mieć tylko jednego nauczyciela na jednego ucznia. Przejdźmy więc w drugą stronę. Użyjmy czegoś o nazwie View SQL. Jaki jest pomysł: tworzymy osobny widok, czyli nową tabelę, już z potrzebnym nam grupowaniem. Do tej tabeli dodamy niezbędne nazwiska nauczycieli. Bierzemy jednak pod uwagę fakt, że nauczycieli może być więcej niż jeden, dlatego wpisy będą powtarzane. Utwórz widok: $ UTWÓRZ WIDOK studentBooks jako SELECT st.last_name,st.id,COUNT(*) jako książki OD student st.JOIN WEWNĘTRZNA książka b ON st.id=b.student_id GROUP BY st.id ZAMÓW WG książek DESC; Następnie pracujemy z tym widokiem jako prostą tabelą zawierającą trzy pola: nazwisko studenta, identyfikator_studenta i liczbę książek. Zgodnie z identyfikatorem ucznia, nauczyciela możemy dodać także poprzez dwa złączenia: $ SELECT tch.last_name as 'Nauczyciel', sbw.last_name 'Student', sbw.books jako 'Books' z księgi uczniów sbw INNER JOIN studenci_x_teachers stch ON sbw. id = stch.student_id nauczyciel DOŁĄCZENIE WEWNĘTRZNE tch ON tch.id = stch.teacher_id; A teraz wynik będzie następujący: Huh! To jest prośba, prawda?) Okazało się, jak się spodziewano: uczeń z id=1 ma sześć książek i dwóch nauczycieli, a uczeń z id=2 ma cztery książki i jednego nauczyciela.

Zadanie 10

Warunek: Wybierz „Nauczyciela”, który ma największą liczbę „Książek” spośród wszystkich swoich „Uczniów”. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

Nazwisko nauczyciela	ilość książki
Pietrow	9
Iwanow	5

Tutaj możemy skorzystać z gotowego żądania z poprzedniego zadania. Co musimy w nim zmienić? Mamy już te dane, pozostaje nam tylko dodać kolejne grupowanie i usunąć nazwisko ucznia z danych wyjściowych. Ale najpierw dodajmy jeszcze jednego ucznia do nauczyciela, aby wynik był bardziej interesujący. W tym celu piszemy: $ WSTAWIĆ WARTOŚCI studentów_x_nauczycieli (2, 1); A samo zapytanie: $ SELECT tch.last_name jako „Nauczyciel”, SUM(sbw.books) jako „Książki” z księgi uczniów sbw INNER JOIN studenci_x_teachers stch ON sbw.id = stch.student_id INNER JOIN nauczyciel tch ON tch.id = stch .teacher_id GRUPA WEDŁUG tch.id; W rezultacie otrzymujemy: ten nauczyciel Wasilenko ma 10 książek, a Szewczenko 6...)

Zadanie 11

Warunek: Wybierz „Nauczyciela”, którego liczba „Książek” dla wszystkich jego „Uczniów” wynosi od 7 do 11. Sortuj według ilości w kolejności malejącej. To powinno wyglądać tak:

Nazwisko nauczyciela	ilość książki
Pietrow	jedenaście
Sidorow	9
Iwanow	7

W tym miejscu użyjemy HAVING. Rozmawialiśmy o nim. Żądanie będzie dokładnie takie samo jak poprzednio, wystarczy tylko dodać warunek, że liczba książek będzie się mieścić w określonym przedziale. Jak już mówiłem w poprzednich artykułach, gdy musimy wykonać filtrowanie podczas grupowania i/lub funkcji agregacji, musimy użyć HAVING : $ SELECT tch.last_name jako „Nauczyciel”, SUM(sbw.books) jako „Książki” z studentbook sbw DOŁĄCZENIE WEWNĘTRZNE studenci_x_teachers stch ON sbw.id = stch.student_id POŁĄCZENIE WEWNĘTRZNE nauczyciel tch ON tch.id = stch.teacher_id GRUPA WG tch.id MAJĄCA SUM(sbw.books) > 6 ORAZ SUM(sbw.books) < 12; Zaznaczyłem fragment, który dodałem. I właściwie oczekiwany wynik: tylko Wasilenko przeszedł tę rundę))

Zadanie 12

Warunek: Wydrukuj wszystkie „nazwisko” i „imię” wszystkich „Nauczyciela” i „Ucznia” z polem „typ” (uczeń lub nauczyciel). Sortuj alfabetycznie według „nazwiska”. To powinno wyglądać tak:

nazwisko	typ
Iwanow	student
Kankava	nauczyciel
Kowal	student
Sidorow	nauczyciel
Pietrow	nauczyciel

Oznacza to, że musimy połączyć dwa wyniki i właśnie po to jest UNION. Innymi słowy, pobierzemy dane od uczniów i nauczycieli i wydrukujemy razem: $ WYBIERZ nazwisko, „nauczyciel” jako typ od nauczyciela UNION ALL wybierz nazwisko, „uczeń” jako typ od ucznia ZAMÓW PRZEZ nazwisko; I będą zarówno nauczyciele, jak i uczniowie. Wydawałoby się, że wszystko jest proste, ale wtedy już patrzymy na wynik. Musisz więc zgadnąć dwie rzeczy.

Zadanie 13

Warunek: Dodaj kolumnę „stawka” do istniejącej tabeli „Student”, w której będzie przechowywany kurs, w którym aktualnie uczestniczy student (wartość liczbowa od 1 do 6). ZMIEŃ TABELĘ student DODAJ OGRANICZENIE check_rate CHECK (stopa > 0 ORAZ stawka < 7); Tutaj dodajemy pole poprzez ALTER TABLE i CHECK, aby ustawić limit dla tego pola od 1 do 6.

Zadanie 14

Warunek: ten element nie jest wymagany, ale będzie plusem. Napisz funkcję, która przejrzy wszystkie „Książki” i wydrukuje wszystkie „tytuły” oddzielone przecinkami. Tutaj wystarczy zwrócić linię w wyniku żądania, która będzie zawierać wszystkie tytuły książek. Tutaj znowu musiałem googlować. Istnieje taka funkcja - GROUP_CONCAT , za pomocą której można to zrobić w bardzo prosty sposób: $ SELECT GROUP_CONCAT(tytuł) z książki; I tyle...)) Wszystkie 14 zadań jest gotowych.

wnioski

Uuu... To nie było łatwe. To było ciekawe. Zadania były tego warte, jestem więcej niż pewien. Wykonując te zadania, omówiliśmy wiele rzeczy, które nie były wcześniej znane:

• WIDOK SQL
• GRUPA_CONCAT
• UNIA

i tak dalej. Dziękuję wszystkim, którzy mieli siłę przeczytać i powtórzyć to co zrobiłem. Kto wie, jak lepiej formułować prośby - napisz w komentarzach, na pewno je przeczytam)

Lista wszystkich materiałów wchodzących w skład serii znajduje się na początku artykułu.