Laboratorium 2 – 11.10 g. 16
1. Wyznaczyć ilość liczb czterocyfrowych parzystych, których suma cyfr jest mniejsza od 10.
2. Utworzyć zmienną typu Datetime i przypisać dowolną datę. Wypisać informacje, który to wiek, ile dni od początku wieku, ile dni od początku roku, jaki to dzień tygodnia, czy to rok przestępny, ile dni ma miesiąc bieżący.
3. Dla wszystkich zamówień i klientów wypisać informacje w postaci:
nazwa klienta, liczba zamówień o wartości > 2000, liczba zamówień o wartości <1000, 2000>, liczba zamówień o wartości < 1000
Laboratorium 2 – 11.10 g. 18
1. Wyznaczyć ilość liczb pięciocyfrowych, dla których suma cyfr jest parzysta.
2. Utworzyć zmienną typu Datetime i przypisać dowolną datę. Wypisać informacje, który to dzień tygodnia, który wiek, ile lat od roku 1950, ile tygodni od początku wieku, czy to rok przestępny, ile dni ma miesiąc bieżący, ile dni do nowego wieku. Jaka data będzie za 1000 dni, jaka data była 1000 dni temu.
3. Znaleźć 5 ostatnich numerów zamówień (i dat jego realizacji), dla którego suma wartości zamówień < 500000
Laboratorium 3 – 18.10
Do tabeli tymczasowej #Osoby z kolumnami Id, Imię, Nazwisko, Data urodzenia, Płeć, Pesel (z odpowiednimi typami danych) wpisać przykładowe dane (z poprawnymi i niepoprawnymi danymi)
Dla wszystkich danych w tabeli Osoby zwalidować czy to jest poprawny PESEL (jak najwięcej walidacji):
sprawdzić długość napisu, czy zawiera same cyfry, czy dane z daty urodzenia i płci się zgadzają, zweryfikować sumę kontrolną.
Wypisać informacje w postaci Imię: xx, Nazwisko: xx, Poprawny/Niepoprawny PESEL: xx, Informacja o błędzie przy walidacji PESEL
https://www.gov.pl/web/gov/czym-jest-numer-pesel
Laboratorium 4 – 25.10 g. 16
1. Utworzyć procedurę sp_CustomersOrders, do generowania raportu zamówień klientów. Parametry:
@customerName (fragment nazwy firmy klienta companyname), @dateFrom, @dateTo (zakres dat dla zamówienia) wypisującą dane o ilości i wartości zamówień klientów posortowane malejąco po wartości zamówień.
Gdy parametry mają wartość NULL wszystkie dane są uwzględniane.
Wywołać i przetestować procedurę dla różnych danych
2. Napisać procedurę sp_UpdatePrices aktualizująca cenę (unitprice) produktów w zależności od liczby zamówionych produktów.
Parametry:
@year, @qtyForReduce, @qtyForIncrease.
Gdy liczba produktów zamówionych w roku @year jest mniejsza niż @qtyForReduce cena jest obniżana o 10%.
Gdy liczba produktów zamówionych w roku @year jest większa niż @qtyForIncrease cena jest podniesiona o 5%.
Dodać własne reguły sprawdzania poprawności parametrów.
Wywołać i przetestować procedurę dla różnych danych.
Laboratorium 4 – 25.10 g. 18
1) Utworzyć procedurę sp_OrderCountry, do generowania raportu zamówień klientów. Parametry:
@country, @city, @datefrom, @dateto wypisującą dane o wartości zamówień klientów z danego kraju i/lub miasta
Gdy parametry mają wartość NULL wszystkie dane są uwzględniane.
Wywołać i przetestować procedurę dla różnych danych.
2) Napisać procedurę sp_UpdateTopCategoryProductsPrice aktualizującą cenę (unitprice) produktów w zależności od ich popularności (najmniej popularne cena zmniejszana, najbardziej popularne cena zwiększana). Parametry:
@categoryName, @topReduced, @percentReduced, @topIncreased, @percentIncreased,
Dodać własne reguły sprawdzania poprawności parametrów i wartości domyślne parametrów.
Wywołać i przetestować procedurę dla różnych danych.
Laboratorium 5 – 08.11 – g. 16.15
1) Napisać procedurę, która usuwa zamówienie o danym orderId razem ze wszystkim jego szczegółami (z OrderDetails)
Parametry: @orderId not null, @forceDelete, @posCount – maksymalna liczba pozycji zamówienia, dla którego zamówienie może być skasowane
Jeśli @forceDelete = 1 rekord @orderId jest zawsze kasowany ze wszystkimi szczegółami, gdy @forceDelete = 0 tylko zamówienie o liczbie pozycji mniejszej niż @posCount ma być skasowane.
2) Napisać procedurę dodającą nowy produkt wraz z wszystkim danymi.
Parametry: @productname, @categoryname, @suppliername, @unitprice.
Obsłużyć szczególne przypadki, zabezpieczając przed dodaniem produktu o istniejącej nazwie.
W przypadku gdy nie ma kategorii o nazwie @categoryname, dodać nową kategorię.
W przypadku gdy nie ma dostawcy o nazwie @suppliername, wyświetlić info o błędzie i nie dodawać produktu.
3) Napisać procedurę z parametrem OUTPUT (@sumPositionValues), w którym zwracana będzie suma wartości wszystkich zamówień spełniających kryteria określone parametrami zakresu dat zamówienia i kraju klienta (też parametry procedury).
Laboratorium 5 – 08.11 – g.18
1) Napisać procedurę, która usuwa produkty o podanym @productid razem ze wszystkim pozycjami z zamówień (z OrderDetails)
Parametry: @productid not null, @forceDelete, @posCount – maksymalna liczba pozycji zamówienia, dla którego produkt może być skasowany
Jeśli @forceDelete = 1 rekord @productid jest zawsze kasowany ze wszystkimi pozycjami, gdy @forceDelete = 0 tylko pozycji o liczbie pozycji mniejszej niż @posCount ma być skasowane.
2) Napisać procedurę dodającą nowy produkt wraz z wszystkim danymi.
Parametry: @productname, @categoryname, @suppliername, @unitprice.
Obsłużyć szczególne przypadki, zabezpieczając przed dodaniem produktu o istniejącej nazwie.
W przypadku gdy nie ma kategorii o nazwie @categoryname, dodać nową kategorię.
W przypadku gdy nie ma dostawcy o nazwie @suppliername, wyświetlić info o błędzie i nie dodawać produktu.
3) Napisać procedurę z parametrem OUTPUT (@sumPositionValues), w którym zwracana będzie suma wartości wszystkich zamówień spełniających kryteria określone parametrami zakresu dat zamówienia i kraju dostawcy (też parametry procedury).
Laboratorium 6 – 15.11
1. Napisać funkcję, która zwróci dane zamówień klientów o wartości od @from do @to.
2. Wykorzystując funkcję z punktu 1 sporządzić raport w postaci (nazwa klienta i inne dane klienta), liczba zamówień o wartości <0, 500>, <500, 1000>, itd.
3. Napisać funkcję, która dla danego dostawcy @shipperid zwraca informację o średniej liczbie dni (typ decimal(19,2) ), które upłynęły od zamówienia (orderdate) do dostawy (shipperdate) dla wszystkich zrealizowanych zamówień z danego miesiąca @month i roku @year (np. listopad 2006).
4. Wykorzystując funkcję z punktu 3 znaleźć dla każdego z dostawców miesiąc, w którym średni czas realizacji zamówień był najdłuższy.
http://www.psw.na11.pl/xmlmergecte/
Laboratorium 7 – 29.11 – g. 16
Utworzyć tabelę Logs z kolumnami: logid, date, tablename, operationdetails, w której będą logowane informacje z operacji na bazie wypełniane w triggerach. Napisać triggery dla tabeli Production.Categories logujące dane do tabeli Logs przy usuwaniu, dodawaniu i update rekordów. Triggery mają też pilnować poprawności danych np.: nie można dodać rekordu z istniejącą nazwą kategorii, nie można usunąć użytej kategorii itp.
Przedstawić testowo kilka przykładów działania każdego z opracowanych triggerów. W triggerach wypisywać informacje o przeprowadzanych operacjach.
Laboratorium 7 – 29.11 – g. 18
Utworzyć tabelę Logs z kolumnami: logid, date, tablename, operationdetails, w której będą logowane informacje z operacji na bazie wypełniane w triggerach. Napisać triggery dla tabeli Production.Products logujące dane do tabeli Logs przy usuwaniu, dodawaniu i update rekordów. Triggery mają też pilnować poprawności danych np.: nie można dodać rekordu z istniejącą nazwą produktu, nie można usunąć użytego produktu itp.
Przedstawić testowo kilka przykładów działania każdego z opracowanych triggerów. W triggerach wypisywać informacje o przeprowadzanych operacjach.
Laboratorium 8 – 6.12 – obie grupy
Dla dowolnej zrealizowanej na zajęciach procedury aktualizującej dane i triggera (update, insert) obsłużyć wyjątki, błędy, transakcje.
Przy triggerze sprawdzić czy implementacja była poprawna i uwzględniała update, insert wielu rekordów.
Laboratorium 9 – 13.12 – g. 16
1. Utworzyć XML (wyeksportować dane) na podstawie danych o produktach z tabeli Production.Products zawierające productname, z category name, z supplier companyname i dodatkowo dane o liczbie zamówień dla tego produktu oraz wartości tych zamówień.
2. Zaimportować dane produktów na podstawie XML (z informacjami jak w zad. 1 oczywiście zignorować liczbę i wartość zamówień). Gdy productname jest w bazie zrobić update rekordu używając nowych danych z XML, w przeciwnym wypadku dodać nowy produkt (można zastosować MERGE). Gdy nie ma productname w Production.Products zignorować dodanie (aktualizację) produktu. Gdy nie ma categoryname w Production.Categories dodać nową kategorię z dodaniem (aktualizacją) produktu.
http://www.psw.na11.pl/xmlmergecte/
Laboratorium 9 – 13.12 – g. 18
1. Utworzyć XML (wyeksportować dane) na podstawie danych o klientach z tabeli Sales.Customers zawierające wszystkie dane z tej tabeli i dodatkowo dane o liczbie zamówień klienta oraz ich wartości.
2. Zaimportować dane klientów na podstawie XML (z informacjami jak w zad. 1 oczywiście zignorować liczbę i wartość zamówień). Gdy companyname jest w bazie zrobić update rekordu używając nowych danych z XML, w przeciwnym wypadku dodać nowego klienta (można zastosować MERGE).
Laboratorium 10 – 20.12.2023
1. Przy użyciu CTE znaleźć pracownika drugiego szczebla mającego największą liczbę bezpośrednich podwładnych
2. Przy użyciu CTE znaleźć dla wszystkich pracowników liczbę wszystkich ich podwładnych też pośrednich
3. Przy użyciu CTE ustalić, na którym szczeblu w hierarchii jest najwięcej pracowników
4. Dowolny przykład zapytania agregującego używającego 2 derived tables (bez rekurencji).
PROJEKT
Przygotować bazę danych ze strukturą relacyjną (może być baza taka jak w poprzednim semestrze). Dodać indeksy, triggery, procedury do wszystkich operacji bazodanowych, obsłużyc wyjątki, błędy, transakcje. Dla tej bazy do końca semestru będzie realizowany projekt, w którym stworzona zostanie aplikacja CRUD w C# wykorzystująca przygotowane procedury wbudowane. Harmonogram realizacji prac:
1 etap: przygotowanie struktury bazy i wypełnienie jej danymi,
2 etap: przygotowanie procedur wbudowanych lub triggerów z obsługą wyjątków, transakcji itp.
3 etap: przygotowanie aplikacji do obsługi CRUD