TransactSQL.doc

Transact-SQL, procedury, kursory

Zadania podstawowe

1.     Utwórz tabelę „osoba”, o następujących polach:

Id_Osoby int IDENTITY (PRIMARY KEY),
Imie varchar(10) null,
Nazwisko varchar(20) not null,
Wiek int not null
 

Wprowadź do tabeli kilka osób.

2.     Napisz prosty program w Transact-SQL. Zadeklaruj zmienną, przypisz na tą zmienną liczbę rekordów w tabeli "Osoba" (lub jakiejkolwiek innej) i wypisz uzyskany wynik używając instrukcji PRINT, w postaci napisu np. "W tabeli jest 10 osób".

3.     Używając Transact-SQL, policz liczbę pracowników z tabeli EMP. Jeśli liczba jest mniejsza niż 16, wstaw pracownika Kowalskiego i wypisz komunikat. W przeciwnym przypadku wypisz komunikat informujący o tym, że nie wstawiono danych.

4.     Napisz procedurę, która zwróci osoby starsze od wieku zadanego parametrem procedury.

5.     Napisz procedurę służącą do wstawiania działów do tabeli Dept. Procedura będzie pobierać jako parametry: nr_działu, nazwę i lokalizację. Należy sprawdzić, czy dział o takiej nazwie lub lokalizacji już istnieje. Jeżeli istnieje, to nie wstawiamy nowego rekordu.

6.     Utwórz procedurę zwracającą liczbę rekordów w tabeli Osoba. Przećwicz 3 sposoby zwracania danych przez procedurę (parametr typu OUTPUT, RETURN i resultset).

7.     Przy pomocy kursora przejrzyj wszystkich pracowników i zmodyfikuj wynagrodzenia tak, aby osoby zarabiające mniej niż 1000 miały zwiększone wynagrodzenie o 10%, natomiast osoby zarabiające powyżej 1500 miały zmniejszone wynagrodzenie o 10%. Wypisz wszystkie wprowadzane zmiany.

8.     Przerób kod z zadania 6 na procedurę tak, aby wartości 1000 i 1500 nie były stałe, tylko były parametrami procedury.

Zadania dodatkowe

1.     W bloku Transact-SQL sprawdź, czy w tabeli detp jest co najmniej 5 działów. Jeśli tak, to należy wypisać komunikat. Jeśli nie, należy wstawić nowy dział, którego numer (deptno) będzie wynosił o 10 więcej niż maksymalny istniejący już numer działu.

2.     W procedurze sprawdź średnią wartość zarobków z tabeli EMP z działu określonego parametrem procedury. Następnie należy dać prowizję (comm) tym pracownikom tego działu, którzy zarabiają poniżej średniej. Prowizja powinna wynosić 5% ich miesięcznego wynagrodzenia.

3.     Napisz procedurę umożliwiającą użytkownikowi wprowadzanie nowych pracowników do tabeli EMP. Jako parametry będziemy podawać nazwisko i nr działu zatrudnianego pracownika. Procedura powinna wprowadzając nowy rekord sprawdzić, czy wprowadzany dział istnieje (jeżeli nie, to należy zgłosić błąd) oraz obliczyć mu pensję równą minimalnemu zarobkowi w tym dziale.

Zadania zaawansowane

1.     Napisz procedurę, która sprawdzi każdego pracownika i osobom które zarabiają powyżej średniej w ich dziale obniży pensję o 10%, a osobom zarabiającym poniżej średniej, podwyższy pensję o 10%. Wskazówka: aby procedura działała szybciej, najpierw oblicz wartości średnich zarobków i przypisz je do tymczasowej tabeli. Następnie przy pomocy kursora przejrzyj wszystkich pracowników, porównując ich pensję z wartością w tymczasowej tabeli.

4.     Planowana jest zmiana struktury organizacyjnej w firmie. Napisz program w języku Transact-SQL, który przeprowadzi odpowiednie modyfikacje. Zmieniony zostanie system numeracji działów z 10, 20, 30... na 1, 2, 3... Jeżeli dwa działy mają tą samą lokalizację, to zostaną one połączone, a dział powstały w wyniku połączenia będzie miał numer taki, jak mniejszy z istniejących numerów (Uwaga: W bazie nie ma działów o tej samej lokalizacji. Aby przetestować program, dodaj takie działy). Pensje wszystkich pracowników zostaną zmodyfikowane. Każdy z pracowników będzie zarabiał tyle, ile wynosi średnia w jego dziale (po reorganizacji) na jego stanowisku.

Wyzwalacze

Zadania podstawowe

1.     Utwórz wyzwalacz, który nie pozwoli usunąć rekordu z tabeli Emp.

5.     Utwórz wyzwalacz, który przy wstawianiu pracownika do tabeli Emp, wstawi prowizję równą 0, jeśli prowizja była pusta (można to oczywiście osiągnąć również używając DEFAULT).

6.     Utwórz wyzwalacz, który przy wstawianiu lub modyfikowaniu danych w tabeli EMP sprawdzi, czy nowe zarobki (wstawiane lub modyfikowane) są większe niż 1000. W przeciwnym wypadku wyzwalacz powinien zgłosić błąd i nie dopuścić do wstawienia rekordu (można to oczywiście osiągnąć również używając CHECK).

7.     Utwórz tabelę „budzet”:

CREATE TABLE budzet (wartosc INT NOT NULL)
W tabeli tej będzie przechowywana łączna wartość wynagrodzenia wszystkich pracowników. Tabela będzie zawsze zawierała jeden wiersz.

Należy najpierw obliczyć początkową wartość zarobków:
INSERT INTO budzet (wartosc)
SELECT SUM(sal) FROM emp
Utwórz wyzwalacz, który będzie pilnował, aby wartość w tabeli budzet była zawsze aktualna. A więc przy wszystkich operacjach aktualizujących tabelę emp (INSERT, UPDATE, DELETE), wyzwalacz będzie aktualizował wpis w tabeli budzet.

Zadania dodatkowe

1.     Napisz wyzwalacz, który nie pozwoli modyfikować nazw działów w tabeli dept. Powinno być jednak możliwe wstawianie nowych działów.

8.     Napisz jeden wyzwalacz, który:

Nie pozwoli usunąć pracownika, którego pensja jest większa od 0.

Nie pozwoli zmienić nazwiska pracownika.

Nie pozwoli wstawić pracownika, który już istnieje (sprawdzając po nazwisku).

9.     Napisz wyzwalacz, który:

l        Nie pozwoli zmniejszać pensji,
Nie pozwoli usuwać pracowników.