Ćwiczenia

1. Wyświetlić zawartość tabeli grupy_cen według kolumny grc_rabat rosnąco.
2. Wyświetlić opis zamówień i numery klientów, którzy je składali malejąco według numeru klienta. W wyniku nie powinny się pojawić zamówienia bez wprowadzonego opisu i zamówienia klienta nr 1003.
3. Wybrać bez powtórzeń numery wszystkich klientów, którzy składali zamówienia.
4. Wypisać adresy e-mail wszystkich klientów, którzy mają numer kierunkowy telefonu 32.
5. Wyświetlić informacje o wszystkich klientach indywidualnych w formacie: „Pan/Pani [imię] [nazwisko] mieszka pod adresem [adres].”
6. Wypisać nazwy i ceny towarów, dla których w nazwie towaru znajduje się przynajmniej jedna cyfra.
7. Obliczyć średnią cenę towarów ze stawką podatku 22%.
8. Obliczyć, ile zamówień złożył klient o numerze 1004.
9. Wyświetlić następujące informacje o wszystkich klientach: e-mail, numer telefonu, pełny adres zawierający imię, nazwisko i adres dla klientów indywidualnych, natomiast nazwę i adres dla pozostałych klientów. Wynik posortować rosnąco według kolumny zawierającej pełny adres.
10. Znaleźć największą długość, jaką posiada nazwa towaru.
11. Wyświetlić imię, nazwisko i PESEL w poprawnym formacie dla klientów indywidualnych.
12. Wyświetlić nazwy towarów wyśrodkowane (w środku kolumny). Nazwać tę kolumnę „W środku”.
13. Wyświetlić nazwy towarów w taki sposób, aby zmienić pierwsze wystąpienie litery „a” w nazwie towaru na literę „X”.
14. Wypisać w jednym wierszu raport zawierający ilość towarów objętych podatkiem 22%, 7% i pozostałymi stawkami. Raport powinien wyglądać następująco:

  22%   7%   pozostałe
-----.---- -----------
   16    5           1
                            

15. Wyświetlić aktualną datę w postaci „styczeń 24, 1999”. Niepotrzebnych spacji i zer pozbyć się przy pomocy przedrostka fm.
16. Sprawdzić dzień tygodnia swoich urodzin.
17. Sprawdzić, czy luty 1932 roku był przestępny.
18. Ile pełnych dni upłynęło od bitwy pod Grunwaldem?
19. Znaleźć minimalny czas realizacji zamówienia.
20. Wypisać wszystkie zamówienia złożone w parzyste miesiące.
21. Znaleźć minimalną, maksymalną i średnią cenę towarów obłożonych tymi samymi stawkami podatku.
22. Obliczyć, ile niezrealizowanych zamówień złożył każdy klient. W wyniku powinni się też pojawić klienci z ilością 0.
23. Znaleźć, ile razy był zamawiany każdy towar, i ile średnio, zamawiano sztuk (tylko towary, które były zamawiane przynajmniej jeden raz).
24. Wyznaczyć średni czas realizacji zrealizowanych zadań dla każdego klienta, który ma zrealizowane co najmniej dwa zadania.
25. Znaleźć różnice pomiędzy najniższą a najwyższą ceną.
26. Znaleźć identyfikatory zamówień mające co najmniej 3 pozycje. Wypisać ilość pozycji. Wynik uporządkować według ilości pozycji rosnąco.
27. Sprawdź, czy wszystkie numery zamówień są unikatowe?
28. Podaj cenę najtańszego komponentu każdego złożonego towaru (numer towaru złożonego i cenę najtańszego komponentu).
29. Wypisać wszystkie dane o klientach, którzy złożyli niezrealizowane dotychczas zamówienia.
30. Wypisać klientów, którzy składali zamówienie w 1999 roku, oraz nazwy, ilości i ceny towarów, które zamawiali.
31. Wypisać bez powtórzeń nazwy towarów zamówionych przez klientów indywidualnych z dopiskiem „”indywidualny” oraz nazwy towarów zamówionych przez klientów instytucjonalnych z dopiskiem „gospodarczy”.
32. Znaleźć identyfikatory towarów, których nikt nie zamawiał w 1999 roku.
33. Wypisać nazwy i ceny wszystkich towarów oraz nazwy i ceny towarów, których są składnikiem. W wyniku powinny się znaleźć wszystkie towary, nawet te, które nie są składnikami innych towarów.
34. Wypisać imiona, nazwiska i nazwy klientów, oraz łączną kwotę wszystkich zrealizowanych przez nich zamówień z roku 1999 i 2000.
35. Wypisz wszystkie towary, oraz łączne kwoty, jakie uzyskano z ich sprzedaży klientom instytucjonalnym. Wynik uporządkuj według łącznych kwot.
36. Wypisz informację o towarach najdroższych w swoich grupach podatkowych?
37. Wypisz opis zamówienia i informację o najdroższych towarach z każdego zamówienia złożonego poza rokiem 2000. Uporządkuj wyniki rosnąco według nazwy towaru.
38. Wypisać pełne informacje o klientach, którzy zamawiali w 1999 roku najdroższy towar z oferty.
39. Wypisz dla każdego klienta jego nazwisko, imię, nazwę i opis zamówienia, które złożył najwcześniej.
40. Wypisz opisy wszystkich zamówień. Przed opisem zamówienia złożonego jako ostatnie dodaj znaczek „*”.
41. Wypisz informacje o trzech najdroższych towarach.
42. Na który rok przypada największa ilość złożonych zamówień? Podaj ten rok i liczbę złożonych zamówień.
43. Podaj adresy pracowników, którzy zamówili towarów za większą kwotę w roku 2000 niż w roku 1999. Wypisz również dla każdego z nich łączne kwoty zamówień z 1999 roku i z 2000 roku.
44. Wypisać nazwy wszystkich towarów w postaci drzewa z wcięciami, tak, żeby na najwyższym poziomie znalazły się komponenty najbardziej złożone.
45. Wypisać nazwy wszystkich towarów w postaci drzewa z wcięciami, tak, żeby na najwyższym poziomie znalazły się komponenty, które nie posiadają żadnych składników.
46. Utworzyć i zalogować się na własnego użytkownika.
47. Utworzyć rolę, nadać tej roli uprawnienia do przeglądania tabeli student.towary.
48. Nadać wcześniej utworzoną rolę własnemu użytkownikowi.
49. Sprawdzić, czy utworzony użytkownik może przeglądać tabelę towary użytkownika student.
50. Nadać bezpośrednio utworzonemu wcześniej użytkownikowi prawo do przeglądania tabeli student.klienci. Sprawdzić, czy użytkownik może przeglądać tabelę klienci.

Ćwiczenia 51-57 należy wykonać na stworzonym uprzednio przez siebie użytkowniku.

51. Utworzyć następujące tabele wraz z ograniczeniami:
    • Tabela „klasy”, do przechowywania identyfikatora klasy, nazwy klasy (np. 3b), numeru sali macierzystej.
    • Tabela „uczniowie” do przechowywania identyfikatora, imienia, nazwiska, wieku ucznia oraz do przechowywania informacji o klasie, do której uczęszcza uczeń.

    Na dane mają zostać nałożone następujące ograniczenia:

    • Nie mogą być puste następujące kolumny: identyfikatory uczniów i klas, nazwisko ucznia, imię ucznia, nazwa klasy.
    • Nie mogą się powtarzać: nazwa klasy, łącznie imię i nazwisko ucznia.
    • Kluczami głównymi w tabelach powinny być identyfikatory numeryczne.
    • Wiek uczniów nie może być mniejszy od 5.
    • Imiona i nazwiska powinny być wpisane małymi literami z wyjątkiem pierwszej litery każdego wyrazu.
    • Nie może istnieć uczeń nie przyporządkowany do żadnej klasy
52. Wstawić do tabeli uczniowie 5 rekordów, a do tabeli klasy 3 rekordy.
53. Dodać do tabeli klasy nową kolumnę not null do przechowywania nazwiska wychowawczyni.
54. Stworzyć tabelę nazywającą się klienci2, która będzie dokładną kopią wraz z danymi tabeli klienci z użytkownika student.
55. Utworzyć ze skryptu dostarczonego przez instruktora tabele takie jak na użytkowniku student.
56. Usunąć z tabeli klienci klienta o identyfikatorze 1004.
57. Uzupełnić wszystkie potrzebne tabele danymi tak, aby klienci Piotr Pasko złożył zamówienie z opisem „Nowe zamówienie na meble”, w skład którego będzie wchodzić szafka i 2 sztuki krzeseł.
58. Otworzyć dwie sesje na swojego użytkownika. W pierwszej sesji podnieść o 1000 ceny wszystkich towarów, w drugiej sesji sprawdzić jakie są ceny towarów.
59. Zatwierdzić transakcję w pierwszej sesji, sprawdzić ceny towarów w drugiej sesji.
60. W pierwszej sesji obniżyć ceny towarów objętych podatkiem 22% o 100. W drugiej sesji obniżyć o 200 ceny towarów objętych podatkiem 7%. Następnie w drugiej sesji obniżyć cenę zestawu komputerowego o 1000.
61. Zatwierdzić transakcję w pierwszej sesji. Co stało się w drugiej sesji?
62. W pierwszej sesji podnieść ceny wszystkich towarów. W drugiej sesji zmodyfikować typ wszystkich klientów na ‘IND’. Następnie w pierwszej sesji zmienić typ wszystkich klientów na ‘GSP’, natomiast w drugiej sesji obniżyć ceny wszystkich towarów o 100.
63. Utwórz perspektywę, w której będą się znajdować wszystkie informacje o klientach indywidualnych. Następnie dodaj do tej perspektywy klienta indywidualnego, sprawdź, czy się znalazł w perspektywie. Następnie dodaj do perspektywy klienta gospodarczego. Sprawdź czy rekord został wstawiony.
64. Zmodyfikuj definicję perspektywy w taki sposób, aby można było wstawić do perspektywy tylko wiersze, które będą w niej widoczne.
65. Utwórz sekwencję, którą będziesz używał do generowania klucza głównego w tabeli towary. Dodaj kilka towarów do tabeli używając sekwencji.
66. Przejrzyj informację o obiektach których jesteś właścicielem: tabelach, indeksach, konstraintach, widokach. Przejrzyj zawartość widoku DICTIONARY.
67. Napisać i wykonać prosty blok anonimowy, który wypisze napis: „Wykonałem się” na konsolę klienta. Skorzystać z procedury wbudowanej dbms_output.put_line(tekst). Należy pamiętać o włączeniu opcji SQL*Plus serveroutput.
68. Zamienić powyższy blok na procedurę i wykonać ją.
69. Sprawdź, jaki jest format przechowywania danych w zmiennych typu char i varchar2. Jak są przechowywane spacje?
    1. Utwórz i uruchom następujący blok anonimowy:
       • Zadeklaruj w nim dwie zmienne typu char(25) i jedną zmienną typu varchar2(25).
       • Przypisz do jednej ze zmiennych typu char wartość 'Ala ma kota', do dwu pozostałych zmiennych przypisz po pięć spacji.
       • Wypisz zawartość zmiennych na konsolę korzystając z procedury dbms_output.put_line(tekst).
         Dla lepszego zobrazowania wyników przekształć zmienne przed wypisaniem wywołując funkcję translate:
         translate(zmienna, ' ', '*').
    2. Przeanalizuj wyniki.
70. Napisz i wykonaj blok PL/SQL, który wstawi do pewnej tabeli liczby podzielne przez 7 i liczby podzielne przez 13 z zakresu od jeden do tysiąca.
    1. Utwórz tabelę z kolumną typu number(9).
    2. Napisz blok anonimowy, który:
       • Wyczyści tę tabelę i włoży do kolumny numerycznej odpowiednie wartości zgodne z wymaganiami zadania. Skorzystaj z funkcji mod.
         Dlaczego do czyszczenia tabeli nie mogłeś użyć polecenia truncate table?
       • Zatwierdzi transakcję.
    3. Policz, ile wierszy zostało wstawionych? Poprawna ilość to 208.
    4. Zapisz skrypt w pliku tekstowym.
71. Przekształć skrypt wstawiający liczby podzielne przez 7 i liczby podzielne przez 13 na procedurę. Uruchom ją.
72. Napisz funkcję, która będzie sprawdzała, czy liczba przekazana jej jako parametr jest podzielna przez 7 lub przez 13. Funkcja powinna zwracać fałsz lub prawdę.
73. Zmodyfikuj i uruchom procedurę z ćwiczenia 71, w taki sposób, aby wykorzystywała funkcję napisaną w ćwiczeniu 72.
74. Zmodyfikuj i uruchom procedurę z poprzedniego ćwiczenia w taki sposób, aby miała ona trzy parametry: pierwszy i drugi mają określać zakres, z jakiego liczby mają być wstawiane do tabeli, trzeci ma być parametrem wyjściowym, w którym po wykonaniu się procedury powinna się znaleźć ilość wstawionych do tabeli elementów.
75. Napisz procedurę, dzięki której dowiesz się jaki numer błędu i jaki komunikat jest generowany w momencie, gdy następuje próba wprowadzenia do zmiennej typu data wartości spoza dopuszczalnego zakresu dat. W tym celu zadeklaruj zmienną typu data, przypisz jej datę dzisiejszą, a następnie w pętli nieskończonej zwiększaj datę o 1000 dni. W pewnym momencie wystąpi błąd. Przechwyć go i wyświetl numer i komunikat.
76. Napisać procedurę, która wypisze na konsolę raport obejmujący wszystkie zamówienia w postaci:

Lp                Opis     Zamówiono  Zrealizowano
--------------------------------------------------
1  Zestaw komputerowy    2000.12.10
2       Meble biurowe    2000.05.08    2000.05.12
3   Wyposażenie biura    1999.03.01    2000.05.30
...
...
-------------------------------------------------
Ilość zamówień zrealizowanych:                   9
Ilość zamówień nie zrealizowanych:               3

Skorzystać z kursora opartego na tabeli zamowienia.

77. Zmodyfikować poprzednio napisany blok, w taki sposób, aby raport przyjął postać:

        
    KLIENT: Jan Kowalski
    Lp                  Opis     Zamówiono  Zrealizowano
    ----------------------------------------------------
    1    Zestaw komputerowy    2000.12.10
    ----------------------------------------------------
    Ilość zamówień zrealizowanych:                     0
    Ilość zamówień nie zrealizowanych:                 1
    
    KLIENT: Biuro nieruchomości „Swój domek”
    Lp                  Opis     Zamówiono  Zrealizowano
    ----------------------------------------------------
    1 Akcesoria komputerowe    2000.07.26    2000.07.29
    ...
    ...
    ------------------------------
    Ilość zamówień zrealizowanych:                   9
    Ilość zamówień nie zrealizowanych:               3
    
    ...
    ...
                                

78. Stworzyć kursor oparty na tabeli klienci oraz kursor z parametrem oparty na tabeli zamowienia.
79. Zmodyfikować procedurę z pierwszego ćwiczenia, w taki sposób, aby do procedury było przekazywane jako parametr nazwisko klienta lub jego nazwa. Procedura powinna wypisywać tylko zamówienia wskazanego klienta. Jeżeli nie istnieje żaden klient o podanym nazwisku, powinien zostać wypisany odpowiedni komunikat o błędzie: „Podany klient nie istnieje”.
80. Utwórz pakiet, który będzie zawierał funkcję i procedurę do wstawiania liczb podzielnych przez 7 i 13, które zostały utworzone w poprzednich ćwiczeniach. Procedura powinna być publiczna, a funkcja powinna być prywatna. Uruchom procedurę i spróbuj uruchomić funkcję.
81. Napisać wyzwalacz, który będzie odnotowywał wszystkie operacje DML wykonane na tabeli towary.
• Utworzyć tabelę tglog składającą się z kolumn do przechowywania nazwy użytkownika, czasu, typu operacji, numeru towaru, na którym wykonano operację.
• W odpowiednio zapisanym wyzwalaczu umieścić instrukcję INSERT, która zanotuje wszystkie operacje wykonane na tabeli towary w tabeli test.
82. Napisz wyzwalacz, który niezależnie od polecenia INSERT wykonywanego przez użytkowników zawsze będzie wpisywał do tabeli towary nazwy towarów małymi literami zaczynające się tylko od dużej litery.
83. Napisz wyzwalacz, który nie pozwoli zmniejszyć ceny żadnemu towarowi objętemu stawką podatku 7%.
84. Napisz wyzwalacz, który nie pozwoli żadnemu użytkownikowi stworzyć tabeli, w której będzie się znajdowała kolumna typu LONG.