Oracle SQL – Wprowadzenie
- Zadania nie są ułożone od najłatwiejszego do najtrudniejszego; są wymieszane.
- Poziomy trudności są orientacyjne; poziom trudności każdego zadania to tak naprawdę sprawa indywidualna (co kto potrafi).
- Niektóre zadania mogą być podobne do innych. Czasem faktycznie tak jest, a czasami to podobieństwo jest tylko pozorne.
- Modyfikuj zapytania! Dopisuj swoje elementy żeby zobaczyć, co się stanie. Ostatecznie nic przecież nie wybuchnie – więc nie bój się eksperymentować.
- Niektóre zadania mają więcej niż jedno poprawne rozwiązanie.
- Aby ułatwić zadanie – byście mogli skupić się na pisaniu kwerendy – przy każdym ćwiczeniu jest zaznaczone, z jakich tabel i kolumn korzystać.
- Warto mieć pod ręką schemat bazy, z której się korzysta.
Poziomy trudności zadań
Zadania zostały podzielone na kilka kategorii (poziomów trudności / złożoności):
- Zaczynamy od zera – jak sama nazwa wskazuje, ćwiczenia przeznaczone dla stawiających pierwsze kroki w świecie SQL; proste zapytania do jednej tabeli, z różnymi warunkami filtrowania wierszy.
- Podstawowy – operuje na podstawowych składnikach zapytań: łączenie tabel, grupowanie oraz filtracja grup, funkcje agregujące (MIN, MAX, SUM, AVG), proste użycie funkcji okna OVER(), funkcje znakowe (np. SUBSTR, REPLACE), praca z NULLami (NVL, IS [NOT] NULL), funkcja CASE, operacje na datach, konwersje oraz formatowanie wyniku funkcjami to_char(), to_date().
- Średniozaawansowany – bardziej złożone zapytania: podkwerendy (zwykłe oraz skorelowane), przygotowywanie podzapytań (klauzula WITH), operatory zbiorowe (UNION, MINUS, INTERSECT), widoki (perspektywy), dalsze zastosowanie funkcji okna OVER; dodatkowo znajdowanie więcej niż jednego rozwiązania danego problemu.
- Zaawansowany – tworzenie i modyfikowanie tabel (CREATE, ALTER, DROP), modyfikacja danych (INSERT, UPDATE, DELETE), transakcje, sekwencje i identyfikatory, tworzenie więzów integralności (klucze główne i obce), indeksy.
Instalacja bazy danych i SQL Developer’a
Instalacja i konfiguracja bazy danych Oracle 12c
- Przechodzimy do strony http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
- Akceptujemy warunki licencyjne, wybieramy wersję odpowiadającą naszemu systemowi operacyjnemu (u mnie – Win7x64bit). Zanim jednak będziemy mogli pobrać pliki, strona poprosi nas o zalogowanie się. Jeśli nie mamy konta na oracle.com, trzeba będzie je założyć.
- Oba pobrane pliki *.zip rozpakowujemy (do tej samej lokalizacji – to jest jeden folder rozdzielony na dwa pliki *.zip); w efekcie otrzymamy folder nazwany database. W środku znajdziemy plik setup.exe – uruchamiamy go.
- Ukaże nam się okno instalatora. W pierwszym kroku możemy odhaczyć kwadracik i przejść do dalszego etapu.
- Następnie mamy wybór – co chcemy instalować? Wybieramy Create and configure a database. Next.
- Trzeci krok to kolejny wybór – Desktop class albo Server class. Wybieramy Desktop class, Next.
- Krok czwarty. Oracle pyta, jakiego konta Windows chcemy użyć (chodzi o kwestie bezpieczeństwa). Ja stworzyłam sobie nowe konto tylko do tego celu; można też użyć opcji trzeciej (Built-In Account).
- Teraz następuje konfiguracja instalacji. Określamy, gdzie baza ma się zainstalować, jak ma się nazywać; ustalamy też hasło administratora (warto je zapamiętać – przyda nam się niedługo). Ja zostawiłam opcje domyślne, wpisałam hasło admina i odznaczyłam Create as Container Database. Gdy już wszystko mamy – Next.
- Nastąpi podsumowanie i, jeśli będzie wszystko ok, można instalować. Zajmie to chwilę.
- Pod koniec instalacji wyskoczy dodatkowe okno, a potem Database Configuration Assistant.
- Jest to ważne okno z dwóch powodów: po pierwsze, widzimy tam adres (podświetlony na niebiesko); prowadzi on do Enterprise Management (centrum zarządzania bazą). Po drugie, jest przycisk Password Management. Kliknijmy w niego – otworzy się okno z domyślnie zainstalowanymi kontami użytkowników. Będziemy potrzebowali korzystać z „zawartości” użytkownika HR, który domyślnie jest zablokowany (jak wszystkie konta poza administracyjnymi). Znajdźmy więc tego użytkownika na liście i ustalmy mu hasło („ptaszek” w pierwszej kolumnie powinniśmy odhaczyć).
- Klikamy OK. Instalacja powinna się zaraz zakończyć.
Instalacja SQL Developer’a
- Program można pobrać stąd: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html (podobnie jak poprzednio, wymagane będzie zalogowanie się).
- UWAGA: potrzebne będzie również środowisko Javy w wersji 8 lub wyższej; jeśli go nie masz, musisz pobrać (http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html) i zainstalować przed SQL Developerem.
- Pobrany *.zip zawierający program należy rozpakować. Otrzymany w ten sposób folder należy przekopiować w jakieś bezpieczne miejsce (np tam gdzie mamy zainstalowaną bazę). W folderze jest plik sqldeveloper.exe – uruchomi program. SQL Developer nie wymaga on instalacji! Działa od razu po rozpakowaniu. Warto zrobić skrót do programu i umieścić go na pulpicie (PPM > Wyślij do > Pulpit (utwórz skrót)).
- Może się zdarzyć, że przy pierwszym uruchomieniu zostaniemy zapytani o miejsce instalacji środowiska JDK. Wskazujemy wtedy lokalizację (np C:\Program Files\Java\jdk1.8.0_45).
SQL Developer – łączenie z bazą danych
- Gdy mamy już uruchomiony program, trzeba skonfigurować połączenie z bazą. W tym celu klikamy w zielony znak plus po lewej stronie w oknie Connections (lub odrobinę poniżej PPM w Connections > New Connection). Otworzy nam się okno do konfiguracji połączeń.
- Wpisujemy nazwę połączenia (dowolną), nazwę użytkownika (np sys albo hr) oraz ustalone hasło. Dla użytkownika sys należy wybrać rolę SYSDBA, dla hr będzie to DEFAULT. Możemy przetestować czy połączenie działa (Test u dołu okna). Jeśli działa, Save.
- Na koniec zrobiłabym jeszcze jedną rzecz – drobne usprawnienie środowiska zanim zaczniemy pisać zapytania. Z górnego menu wybierzmy Tools > Preferences, następnie z listy rozwiń pole Code Editor i przejdź do Fonts. Domyślna czcionka jest mała i męczy wzrok; warto ustawić większą (ostatecznie spędzimy przy tym środowisku trochę czasu). Następnie niżej (pod Fonts) mamy Line Gutters – tu warto zaznaczyć Show Line Numbers – łatwiej nam będzie nawigować w kodzie np w przypadku wystąpienia błędów.
Odblokowanie usera HR
Odblokowanie użytkownika HR
Dlaczego ten użytkownik jest taki ważny? Jest to, ściślej rzecz ujmując, schemat bazy danych. Zawiera prostą przykładową bazę, na której można ćwiczyć pisanie zapytań – rzecz zdecydowanie przydatna 😉
W zadaniach na tej stronie będziemy korzystać z użytkowników HR oraz OE – odblokuj od razu obu.
Jak wspominałam wcześniej, jest on domyślnie instalowany, ale zablokowany, więc nie można z niego korzystać bez uprzedniego odblokowania. Uczynić to można na kilka sposobów.
- Podczas instalacji bazy, pod koniec, gdy wyskoczy okno Database Configuration Assistant (Password Management). Jeśli ominęliśmy ten krok, nic straconego.
- Poprzez Enterprise Management (link podany pod koniec instalacji bazy – https://localhost:5500/em/login). Logujemy się do panelu użytkownikiem sys i hasłem administracyjnym ustalonym podczas instalacji bazy (zaznaczamy okienko 'as sysdba’).
Z menu na górze strony wybieramy Security > Users. Mamy przed sobą listę użytkowników; znajdujemy HR, zaznaczamy. Na górze po lewej z menu Actions wybieramy Alter Account.
W okienku ustalamy hasło dla użytkownika, odznaczamy pola 'Password expired’ oraz 'Account Locked’, klikamy OK.
- Z poziomu SQL Developera, po zalogowaniu jako SYS: w oknie Connections klikamy plusik przy użytkowniku SYS (chodzi o rolę, nazwa jest dowolna), z rozwiniętej listy na samym dole rozwijamy Other Users, szukamy interesującego nas HR, klikamy na niego prawym klawiszem myszy, z menu podręcznego wybieramy Edit User. Pojawi się okienko nieco podobne do tego z Enterprise Managera; możemy tu odblokować użytkownika i ustawić mu hasło.
- Za pomocą polecenia SQL (musimy być zalogowani jako sys):
alter user HR identified by TwojeHasło account unlock;
Gdy już mamy odblokowanego użytkownika, wystarczy stworzyć mu połączenie (Connections) wg instrukcji powyżej; potem można już zacząć korzystać z dobrodziejstw oferowanych przez danego usera.
