· 8 years ago · Jan 22, 2018, 12:38 PM
128 Tworzenie tabel (CREATE TABLE)
228.1 Utwórz tabelę pracownik2(id_pracownik, imie, nazwisko, pesel, data_zatr, pensja), gdzie * id_pracownik – jest numerem pracownika nadawanym automatycznie, jest to klucz główny * imie i nazwisko – to niepuste łańcuchy znaków zmiennej długości, * pesel – unikatowy łańcuch jedenastu znaków stałej długości, * data_zatr – domyślna wartość daty zatrudnienia to bieżąca data systemowa, * pensja – nie może być niższa niż 1000zł
31. -- 28.1
42. CREATE TABLE pracownik2(
53. id_pracownik INT IDENTITY(1,1) PRIMARY KEY,
64. imie VARCHAR(15) NOT NULL,
75. nazwisko VARCHAR(20) NOT NULL,
86. pesel CHAR(11) UNIQUE,
97. data_zatr DATETIME DEFAULT GETDATE(),
108. pensja MONEY CHECK(pensja>=1000)
119. );
12
1328.2 Utwórz tabelę naprawa2(id_naprawa, data_przyjecia, opis_usterki, zaliczka), gdzie * id_naprawa – jest unikatowym, nadawanym automatycznie numerem naprawy, jest to klucz główny, * data_przyjecia – nie może być późniejsza niż bieżąca data systemowa, * opis usterki – nie może być pusty, musi mieć długość powyżej 10 znaków, * zaliczka – nie może być mniejsza niż 100zł ani większa niż 1000zł.
1410. -- 28.2
1511. CREATE TABLE naprawa2(
1612. id_naprawa INT IDENTITY(1,1) PRIMARY KEY,
1713. data_przyjecia DATETIME CHECK(data_przyjecia<=GETDATE()),
1814. opis_usterki VARCHAR(500) NOT NULL CHECK(LEN(opis_usterki)>10),
1915. zaliczka MONEY CHECK(zaliczka BETWEEN 100 AND 1000)
2016. );
21
2228.3 Utwórz tabelę wykonane_naprawy2(id_pracownik, id_naprawa, data_naprawy, opis_naprawy, cena), gdzie * id_pracownik – identyfikator pracownika wykonującego naprawę, klucz obcy powiązany z tabelą pracownik2, * id_naprawa – identyfikator zgłoszonej naprawy, klucz obcy powiązany z tabelą naprawa2, * data_naprawy – domyślna wartość daty naprawy to bieżąca data systemowa, * opis_naprawy – niepusty opis informujący o sposobie naprawy, * cena – cena naprawy.
2317. -- 28.3
2418. CREATE TABLE wykonanie_naprawy2(
2519. id_pracownik INT FOREIGN KEY REFERENCES pracownik2(id_pracownik),
2620. id_naprawa INT FOREIGN KEY REFERENCES naprawa2(id_naprawa),
2721. data_naprawy DATETIME DEFAULT GETDATE(),
2822. opis_naprawy VARCHAR(500) NOT NULL,
2923. cena MONEY
3024. );
3129 Modyfikacja struktury tabeli (ALTER TABLE)
3229.1 Dana jest tabela CREATE TABLE student2(id_student INT IDENTITY(1,1) PRIMARY KEY, nazwisko VARCHAR(20), nr_indeksu INT, stypendium MONEY); Wprowadź ograniczenia na tabelę student2: * nazwisko – niepusta kolumna, * nr_indeksu – unikatowa kolumna, * stypendium –nie może być niższe niż 1000zł, * dodatkowo dodaj niepustą kolumnę imie.
3325. CREATE TABLE student2(id_student INT IDENTITY(1,1) PRIMARY KEY, nazwisko VARCHAR(20), nr_indeksu INT, stypendium MONEY);
3426.
3527. ALTER TABLE student2 ALTER COLUMN nazwisko VARCHAR(20) NOT NULL;
3628. ALTER TABLE student2 ADD CONSTRAINT unikatowy_nr_indeksu UNIQUE (nr_indeksu);
3729. ALTER TABLE student2 ADD CONSTRAINT sprawdz_stypendium CHECK (stypendium>=1000);
3830. ALTER TABLE student2 ADD imie VARCHAR(15) NOT NULL;
3929.2 Dane są tabele: CREATE TABLE dostawca2(id_dostawca INT IDENTITY(1,1) PRIMARY KEY, nazwa VARCHAR(30)); CREATE TABLE towar2(id_towar INT IDENTITY(1,1) PRIMARY KEY, kod_kreskowy INT, id_dostawca INT); Zmodyfikuj powyższe tabele: * kolumna nazwa z tabeli dostawca2 powinna być unikatowa, * do tabeli towar2 dodaj niepustą kolumnę nazwa, * kolumna kod_kreskowy w tabeli towar2 powinna być unikatowa, * kolumna id_dostawca z tabeli towar2 jest kluczem obcym z tabeli dostawca2.
4031. CREATE TABLE dostawca2(id_dostawca INT IDENTITY(1,1) PRIMARY KEY, nazwa VARCHAR(30));
4132. CREATE TABLE towar2(id_towar INT IDENTITY(1,1) PRIMARY KEY, kod_kreskowy INT, id_dostawca INT);
4233.
4334. ALTER TABLE dostawca2 ADD CONSTRAINT unikatowa_nazwa UNIQUE (nazwa);
4435. ALTER TABLE towar2 ADD nazwa VARCHAR(500) NOT NULL;
4536. ALTER TABLE towar2 ADD CONSTRAINT unikatowy_kod_kreskowy UNIQUE (kod_kreskowy);
4637. ALTER TABLE towar2 ADD CONSTRAINT id_dostawca FOREIGN KEY (id_dostawca) REFERENCES dostawca2(id_dostawca);
4729.3 Dane są tabele: CREATE TABLE kraj2(id_kraj INT IDENTITY(1,1) PRIMARY KEY, nazwa VARCHAR(30)); CREATE TABLE gatunek2(id_gatunek INT IDENTITY(1,1) PRIMARY KEY, nazwa VARCHAR(30)); CREATE TABLE zwierze2(id_zwierze INT IDENTITY(1,1) PRIMARY KEY, id_gatunek INT, id_kraj INT, cena MONEY); Zmodyfikuj powyższe tabele: * kolumny nazwa z tabel kraj2 i gatunek2 mają być niepuste, * kolumna id_gatunek z tabeli zwierze2 jest kluczem obcym z tabeli gatunek2, * kolumna id_kraj z tabeli zwierze2 jest kluczem obcym z tabeli kraj2.
4838. -- 29.3
4939. CREATE TABLE kraj2(id_kraj INT IDENTITY(1,1) PRIMARY KEY, nazwa VARCHAR(30));
5040. CREATE TABLE gatunek2(id_gatunek INT IDENTITY(1,1) PRIMARY KEY, nazwa VARCHAR(30));
5141. CREATE TABLE zwierze2(id_zwierze INT IDENTITY(1,1) PRIMARY KEY, id_gatunek INT, id_kraj INT, cena MONEY);
5242.
5343. ALTER TABLE kraj2 ALTER COLUMN nazwa VARCHAR(20) NOT NULL;
5444. ALTER TABLE gatunek2 ALTER COLUMN nazwa VARCHAR(20) NOT NULL;
5545. ALTER TABLE zwierze2 ADD CONSTRAINT id_gatunek FOREIGN KEY (id_gatunek) REFERENCES gatunek2(id_gatunek);
5646. ALTER TABLE zwierze2 ADD CONSTRAINT id_kraj FOREIGN KEY (id_kraj) REFERENCES kraj2(id_kraj);
5747.
5830 Usuwanie tabel, kolumn w tabeli i ograniczeń (DROP i ALTER)
5930.1 Dane są tabele: CREATE TABLE kategoria2(id_kategoria INT PRIMARY KEY, nazwa VARCHAR(30) ); CREATE TABLE przedmiot2(id_przedmiot INT PRIMARY KEY, id_kategoria INT REFERENCES kategoria2(id_kategoria), nazwa VARCHAR(30)); Napisać instrukcje SQL, która usuną tabele kategoria2 i przedmiot2. Wsk: Zwróć uwagę na kolejność usuwania tabel. Wersja trudniejsza: Czy potrafisz najpierw sprawdzić, czy tabele istnieją i jeśli istnieją to dopiero wtedy je usunąć?
6048. -- 30.1
6149. DROP TABLE przedmiot2;
6250. DROP TABLE kategoria2;
6330.2 Dana jest tabela: CREATE TABLE osoba2(id_osoba INT, imie VARCHAR(15), imie2 VARCHAR(15) ); Napisać instrukcję SQL, która z tabeli osoba2 usunie kolumnę imie2.
6451. CREATE TABLE osoba2(id_osoba
6552. INT, imie VARCHAR(15), imie2 VARCHAR(15) );
6653. ALTER TABLE osoba2 DROP COLUMN imie2;
6730.3 Dana jest tabela: CREATE TABLE uczen2(id_uczen INT PRIMARY KEY, imie VARCHAR(15), nazwisko VARCHAR(20) CONSTRAINT uczen_nazwisko_unique UNIQUE); Napisać instrukcję SQL, która usunie narzucony warunek unikatowości na kolumnę nazwisko. Wersja trudniejsza: Czy potrafiłbyś zrobić powyższe zadanie dla definicji tabeli: CREATE TABLE uczen2(id_uczen INT PRIMARY KEY, imie VARCHAR(15), nazwisko VARCHAR(20) CONSTRAINT UNIQUE); ?
6854. CREATE TABLE uczen2(id_uczen INT PRIMARY KEY, imie VARCHAR(15), nazwisko VARCHAR(20)
6955. CONSTRAINT uczen_nazwisko_unique UNIQUE);
7056.
7157. ALTER TABLE uczen2 DROP CONSTRAINT uczen_nazwisko_unique;
7231 Usuwanie i modyfikacja kaskadowa (CREATE i ALTER)
7331.1 Utwórz tabelę wlasciciel2(id_wlasciciel, imie,nazwisko,data_ur,ulica,numer,kod,miejscowosc) i zwierze2(id_zwierze,id_wlasciciel,rasa,data_ur,imie) w taki sposób, aby po usunięciu informacji o właścicielu zwierzęcia z tabeli wlasciciel2, SZBD automatycznie identyfikator właściciela w tabeli zwierze2 ustawiał na wartość NULL. Nie zapomnij o doborze pozostałych ograniczeń na kolumny (można to zrobić wg uznania).
7458. -- 31.1
7559. CREATE TABLE wlasciciel2(
7660. id_wlasciciel INT IDENTITY(1,1)PRIMARY KEY,
7761. imie VARCHAR(15) NOT NULL CHECK(LEN(imie)>2),
7862. nazwisko VARCHAR(15) NOT NULL CHECK(LEN(nazwisko)>2),
7963. data_ur DATE NOT NULL DEFAULT GETDATE(),
8064. ulica VARCHAR(50),
8165. numer VARCHAR(8),
8266. kod CHAR(6) NOT NULL CHECK(LEN(kod)=6),
8367. miejscowosc VARCHAR(30) NOT NULL CHECK(LEN(miejscowosc)>1)
8468. );
8569. CREATE TABLE zwierze2(
8670. id_zwierze INT IDENTITY(1,1) PRIMARY KEY,
8771. id_wlasciciel INT REFERENCES wlasciciel2(id_wlasciciel) ON DELETE SET NULL,
8872. rasa VARCHAR(30) NOT NULL CHECK(LEN(rasa)>2),
8973. data_ur DATE NOT NULL DEFAULT GETDATE(),
9074. imie VARCHAR(15) NOT NULL CHECK(LEN(imie)>2)
9175. );
9231.2 Dane są tabele: CREATE TABLE film2(id_film INT PRIMARY KEY,tytul VARCHAR(50) NOT NULL); CREATE TABLE gatunek2(id_gatunek INT PRIMARY KEY,nazwa VARCHAR(50) NOT NULL); CREATE TABLE film2_gatunek2(id_film INT,id_gatunek INT,PRIMARY KEY(id_film,id_gatunek)); Zmodyfikuj strukturę powyższych tabel (polecenie ALTER) taka aby: a) w przypadku usunięcia filmu z tabeli film2 zostały automatycznie usuwane informacje jakich gatunków był usunięty film (nie usuwamy gatunków z tabeli gatunek2), b) w przypadku usunięcia gatunku z tabeli gatunek2 zostały automatycznie usuwane informacje jakie filmy były tego gatunku (nie usuwamy filmów z tabeli film2).
9376. CREATE TABLE film2(id_film INT PRIMARY KEY,tytul VARCHAR(50) NOT NULL);
9477. CREATE TABLE gatunek2(id_gatunek INT PRIMARY KEY,nazwa VARCHAR(50) NOTNULL);
9578. CREATE TABLE film2_gatunek2(id_film INT,id_gatunek INT,PRIMARY KEY(id_film,id_gatunek));
9679. ALTER TABLE film2_gatunek2 ADD CONSTRAINTid_film FOREIGN KEY (id_film) REFERENCES film2(id_film) ON DELETE CASCADE;
9780. ALTER TABLE film2_gatunek2 ADD CONSTRAINT id_gatunek FOREIGN KEY(id_gatunek) REFERENCES gatunek2(id_gatunek);
9831.3 Dane są tabele: CREATE TABLE stanowisko2 (id_stanowisko INT PRIMARY KEY, nazwa VARCHAR(30)); CREATE TABLE pracownik2(id_pracownik INT PRIMARY KEY, id_stanowisko INT, nazwisko VARCHAR(20)); Zmodyfikuj struktury powyższych tabel tak, aby po usunięciu stanowiska z tabeli stanowisko2 identyfikator. stanowiska w tabeli pracownik2 był automatycznie ustawiany na wartość NULL oraz podczas modyfikowania identyfikatora stanowiska w tabeli stanowisko2 wszystkie identyfikatory stanowiska w tabeli pracownik2 powinny zostać automatycznie zaktualizowane.
9981. --31.3
10082.
10183. CREATE TABLE stanowisko2 (id_stanowisko INT PRIMARY KEY, nazwa VARCHAR(30));
10284. CREATE TABLE pracownik2(id_pracownik INT PRIMARY KEY,id_stanowisko INT, nazwisko VARCHAR(20));
10385. ALTER TABLE pracownik2 ADD CONSTRAINT klucz_obcy_id_stanowisko FOREIGN KEY (id_stanowisko)REFERENCES stanowisko2(id_stanowisko) ON DELETE SET NULL ON UPDATE CASCADE;
10432 Tworzenie procedur składowych (CREATE PROCEDURE)
10532.1 Napisać procedurę o nazwie wypisz_samochody, która posiada tylko jeden parametr - marka samochodu. Procedura powinna wyświetlać wszystkie informacje z tabeli samochód o samochodach zadanej marki.
106 - 32.1
107CREATE PROCEDURE wypisz_samochody @marka VARCHAR(20)
108AS
109SELECT * FROM samochod WHERE marka=@marka;
110GO
111EXECUTE wypisz_samochody 'opel';
11232.2 Napisać procedurę o nazwie zwieksz_pensje posiadającą dwa parametry: identyfikator pracownika i kwotę. Procedura powinna zwiększyć pensję pracownikowi, na którego wskazuje zadany identyfikator o zadaną kwotę. Przetestuj utworzoną procedurę – zwiększ pracownikowi o identyfikatorze równym 1 pensję o 1000 zł.
113-- 32.2
114CREATE PROCEDURE zwieksz_pensje @id INT, @kwota INT
115AS
116UPDATE pracownik SET pensja=pensja+@kwota WHERE id_pracownik=@id;
117GO
118EXECUTE zwieksz_pensje 1, 1000;
11932.3 Napisz procedurę o nazwie dodaj_klienta umożliwiającą dodanie nowego klienta. Dane klienta powinny być odczytane z parametrów procedury. Dobierz odpowiednio parametry dla tworzonej procedury na podstawie kolumn tabeli klient. Przetestuj utworzoną procedurę – dodaj nowego klienta.
120-- 32.3
121CREATE PROCEDURE dodaj_klienta @id_klient INT, @imie VARCHAR(15), @nazwisko VARCHAR(20),
122@ulica VARCHAR(24), @numer VARCHAR(8), @miasto VARCHAR(24), @kod CHAR(6)
123AS
124INSERT INTO klient (id_klient, imie, nazwisko, ulica, numer, miasto, kod)
125VALUES (@id_klient, @imie, @nazwisko, @ulica, @numer, @miasto, @kod);
126GO
127EXECUTE dodaj_klienta 21, 'Kacper', 'Naroznik', 'dluga', '3', 'Poznan', '82-112';
12833.1 Napisać funkcję o nazwie aktywnosc_klienta, która będzie zwracać ilość wypożyczeń samochodów dla klienta o identyfikatorze zadanym jako parametr funkcji. Przetestuj utworzoną funkcję – sprawdź ile samochodów wypożyczył klient o identyfikatorze równym 3.
129-- 33.1
130CREATE FUNCTION dbo.aktywnosc_klienta (
131@id_klient INT
132) RETURNS INT
133BEGIN
134RETURN (SELECT COUNT(*) FROM wypozyczenie
135WHERE id_klient=@id_klient)
136END;
137GO
138SELECT dbo.aktywnosc_klienta(3) AS ile_wyp;
13933.2 Napisać funkcję o nazwie ile_wypozyczen posiadającą dwa parametry data_od i data_do. Funkcja powinna zwrócić ilość wypożyczeń samochodów w zadanym przedziale czasowym. Przetestuj utworzoną funkcję – sprawdź ile zostało wypożyczonych samochodów od 01.01.2000r. do 31.12.2000r.
140-- 33.2
141CREATE FUNCTION dbo.ile_wypozyczen (
142@data_od DATETIME,
143@data_do DATETIME
144) RETURNS INT
145BEGIN
146RETURN (SELECT COUNT(*) FROM wypozyczenie
147WHERE @data_od <= data_wyp AND @data_do >= data_wyp)
148END;
149GO
150SELECT dbo.ile_wypozyczen('2000-01-01', '2000-12-31') AS ile_wypozyczen;
15133.3 Napisać funkcję o nazwie roznica_pensji nie posiadającą parametrów i zwracającą różnicę pomiędzy największą i najmniejszą pensją wśród pracowników wypożyczalni. Przetestuj utworzoną funkcję.
152-- 33.3
153DROP FUNCTION dbo.roznica_pensji;
154CREATE FUNCTION dbo.roznica_pensji () RETURNS DECIMAL(8,2)
155BEGIN
156DECLARE @max DECIMAL(8,2), @min DECIMAL(8,2);
157SET @max = (SELECT MAX(pensja) FROM pracownik);
158SET @min = (SELECT MIN(pensja) FROM pracownik);
159RETURN @max-@min
160END;
161GO
162SELECT dbo.roznica_pensji() AS roznica_pensji;
16334 Tworzenie widoków (złączenia zewnętrzne, CREATE VIEW)
164 34.1 Utwórz widok o nazwie klient_raport zawierający informacje o ilości wypożyczeń każdego z klientów (id_klient, imie, nazwisko). Uwzględnij klientów, którzy ani razu nie wypożyczyli samochodu. Za pomocą utworzonego widoku znajdź klientów, którzy wypożyczyli samochód więcej niż raz.
165CREATE VIEW klient_raport
166AS
167SELECT k.id_klient, k.imie, k.nazwisko, COUNT(w.id_klient) AS "ilosc_wyp"
168FROM klient k LEFT JOIN wypozyczenie w ON k.id_klient=w.id_klient
169GROUP BY k.id_klient, k.imie, k.nazwisko;
170GO
171SELECT * FROM klient_raport WHERE ilosc_wyp>1;
17234.2 Utwórz widok o nazwie samochod_raport zawierający informacje o ilości wypożyczeń każdego z samochodów (id_samochod, marka, typ). Uwzględnij samochody, które ani razu nie zostały wypożyczone. Za pomocą utworzonego widoku znajdź samochód/samochody, które były najczęściej wypożyczane.
173-- 34.2
174CREATE VIEW samochod_raport
175AS
176SELECT k.id_samochod, k.marka, k.typ, COUNT(w.id_samochod) AS "ilosc_wyp"
177FROM samochod k LEFT JOIN wypozyczenie w ON k.id_samochod=w.id_samochod
178GROUP BY k.id_samochod, k.marka, k.typ;
179GO
180SELECT * FROM samochod_raport ORDER BY ilosc_wyp DESC;
18134.3 Utwórz widok o nazwie pracownik_raport zawierający informacje o ilości wypożyczeń samochodów przez pracowników. Nie zapomnij uwzględnić pracowników, którzy nie wypożyczyli żadnego samochodu. Za pomocą utworzonego widoku znajdź pracowników, dla których ilość wypożyczeń jest większa od średniej ilości wypożyczeń samochodów przez pracowników.
182-- 34.3
183CREATE VIEW pracownik_raport
184AS
185SELECT k.id_pracownik, k.imie, k.nazwisko, COUNT(w.id_pracow_wyp) AS "ilosc_wyp"
186FROM pracownik k LEFT JOIN wypozyczenie w ON k.id_pracownik=w.id_pracow_wyp
187GROUP BY k.id_pracownik, k.imie, k.nazwisko;
188GO
189SELECT * FROM pracownik_raport
190WHERE ilosc_wyp > (SELECT AVG(ilosc_wyp) FROM pracownik_raport);
19135 Tworzenie indeksów (CREATE INDEX)
19235.1 Utwórz unikalny indeks dla kolumny telefon w tabeli klient.
193-- 35.1
194CREATE UNIQUE INDEX index_klient_telefon ON klient(telefon);
195GO
19635.2 Utwórz indeks klastrowy dla kolumn nazwisko i imie w tabeli klient.
197-- 35.2
198CREATE CLUSTERED INDEX index_klient_nazwa ON klient(nazwisko, imie);
199GO
200
20135.3 Utwórz indeks nieklastrowy dla kolumn marka i typ w tabeli samochód.
202-- 35.3
203CREATE INDEX index_samochod_typ ON samochod(marka, typ);
204GO
205
20636 Tworzenie wyzwalaczy typu AFTER (CREATE TRIGGER).
20736.1 Napisać wyzwalacz, który uniemożliwi usunięcie klienta. Przetestuj utworzony wyzwalacz – spróbuj usunąć wszystkich klientów.
2081. --36.1
2092. --DROP TRIGGER klient_anuluj_usuwanie;
2103. --GO
2114. CREATE TRIGGER klient_anuluj_usuwanie ON klient
2125. FOR DELETE
2136. AS
2147. RAISERROR('Zabronione jest usuwanie klientów!',1,2)
2158. ROLLBACK
2169. GO
21710. DELETE FROM klient;
21811.
21936.2 Napisać wyzwalacz, który uniemożliwi dodanie pracownika z pensją i dodatkiem równym zero lub NULL. Przetestuj utworzony wyzwalacz – spróbuj dodać kilku pracowników jednocześnie.
22012. -- 36.2
22113. CREATE TRIGGER pracownk_ins ON pracownik
22214. AFTER INSERT
22315. AS
22416. BEGIN
22517.
22618. DECLARE @pensja MONEY
22719. SET @pensja ='-1'
22820. SELECT @pensja =pensja FROM inserted WHERE pensja=0
22921. IF @pensja=0
23022. BEGIN
23123.
23224. RAISERROR('Pensja musi byc > 0',1,2)
23325. ROLLBACK
23426.
23527. END
23628. END
23729. GO
23836.3 Napisać wyzwalacz o nazwie "duplikat_miejsce", który uniemożliwi jednorazowo dodanie więcej niż jednego miejsca oraz dodatkowo uniemożliwi dodanie miejsca o takich samych ulicach, numerze, mieście i kodzie
23930. -- 36.3 nie dziala
24031. DROP TRIGGER duplikat_miejsce1;
24132. GO
24233.
24334. CREATE TRIGGER duplikat_miejsce ON miejsce
24435. FOR INSERT
24536. AS
24637. BEGIN
24738. DECLARE @id INT, @ulica VARCHAR(20), @numer VARCHAR(8), @miasto VARCHAR(24), @kod CHAR(6)
24839. SELECT @id=id_miejsce, @miasto=miasto, @kod=kod, @numer=numer, @ulica=ulica FROM inserted
24940. IF @@ROWCOUNT>1
25041. BEGIN
25142. RAISERROR('Nie mozna dodac wielu jednoczesnie',1,2)
25243. ROLLBACK
25344. END
25445. ELSE IF EXISTS (SELECT * FROM miejsce WHERE (@ulica IN (SELECT ulica FROM miejsce) AND @miasto IN (SELECT miasto FROM miejsce) AND@kod IN (SELECT kod FROM miejsce)))
25546. BEGIN
25647. RAISERROR('Istnieje juz takie miejsce',1,2)
25748. ROLLBACK
25849. END
25950. END
26051. GO
26152.
26253. INSERT INTO miejsce(id_miejsce, ulica, numer, miasto, kod)
26354. VALUES (568, 'asdfdas1d', '313k', '2131ba', '8k-2c2');
26455.
26537 Tworzenie wyzwalaczy typu INSTEAD OF (CREATE TRIGGER).
26637.1 a) Do tabeli samochód dodaj kolumnę "usuniety" typu BIT o wartości domyślnej równej 0. b) W tabeli samochod zmień wszystkie wartości kolumny usuniety z NULL na wartość 0. c) Utwórz wyzwalacz o nazwie " usuniety_samochod ", który uniemożliwi fizyczne usunięcie samochodu z tabeli samochod, a usuwany samochod oznaczy poprzez ustawienie kolumny usuniety na wartość 1.
267--37.1 ALTER TABLE samochod ADD usuniety BIT DEFAULT 0;
268UPDATE samochod SET usuniety=0;
269GO
270CREATE TRIGGER usuniety_samochod ON samochod
271INSTEAD OF DELETE AS
272BEGIN
273UPDATE samochod
274SET usuniety=1
275WHERE id_samochod IN (SELECT id_samochod FROM deleted)
276END
277GO
278DELETE FROM samochod WHERE id_samochod=3;
279SELECT * FROM samochod;
280
28137.2 Utwórz wyzwalacz o nazwie "usun_miejsce_i_wypozyczenia", który uniemożliwi usunięcie równocześnie więcej niż 1 miejsca z tabeli miejsce oraz przed usunięciem pojedynczego miejsca usunie najpierw wszystkie rekordy w tabeli wypożyczenie zawierające informację o usuwanym miejscu (id_miejsca_wyp, id_miejsca_odd). Przetestuj napisany wyzwalacz.
282
283
28456. CREATE TRIGGER usun_miejsce_i_wypozyczenia ON miejsce
28557. INSTEAD OF DELETE AS
28658. BEGIN
28759. IF @@ROWCOUNT > 1
28860. BEGIN
28961. PRINT 'Nie mozna usunac kilku';
29062. END
29163. ELSE
29264. BEGIN
29365. DELETE FROM wypozyczenie WHERE id_miejsca_wyp IN(SELECT id_miejsce FROM deleted)
29466. OR id_miejsca_odd IN(SELECT id_miejsce FROM deleted);
29567. DELETE FROM miejsce WHERE id_miejsce IN(SELECT id_miejsce FROM deleted)
29668. END
29769. END
29870. GO
29937.3 Utwórz wyzwalacz o nazwie "samochod_blokada", który uniemożliwia wprowadzenie jakiejkolwiek zmiany w tabeli samochód. Przetestuj utworzony wyzwalacz dla instrukcji INSERT, UPDATE, DELETE.
30071. -- 37.3
30172. CREATE TRIGGER samochod_blokada ON samochod
30273. INSTEAD OF DELETE, UPDATE, INSERT AS
30374. BEGIN
30475. DECLARE @cnt INT = @@ROWCOUNT
30576. WHILE @cnt > 0
30677. BEGIN
30778. RAISERROR('Blad', 1, 2)
30879. ROLLBACK
30980. SET @cnt=@cnt-1
31081. END
31182. END
31283. GO
31338 Wyzwalacze i kursory
31438.1 Napisać wyzwalacz, który będzie wypisywał na ekranie nazwiska i imiona usuniętych pracowników. Użyj kursora. Przetestuj utworzony wyzwalacz – usuń wszystkich pracowników.
3151. --38.1 --DROP TRIGGER pracownik_usuwanie;
3162. --GO
3173. CREATE TRIGGER pracownik_usuwanie ON pracownik
3184. FOR DELETE
3195. AS
3206. BEGIN
3217. --deklaracja kursora
3228. DECLARE kursor_deleted CURSOR
3239. FOR SELECT imie, nazwisko FROM deleted;
32410. --otwarcie kursora
32511. OPEN kursor_deleted
32612. --zmienne pomocnicze do przechowywania danych pobranych z kursora
32713. DECLARE @imie VARCHAR(15), @nazwisko VARCHAR(20)
32814. --pobranie pierwszego wiersza z kursora
32915. FETCH NEXT FROM kursor_deleted INTO @imie, @nazwisko
33016. --wykonuj dopóki pobranie wiersza z kursora zakończy się sukcesem
33117. WHILE @@FETCH_STATUS = 0
33218. BEGIN
33319. --wyswietlenie wartosci zmiennych @imie i @nazwisko
33420. print 'Usunięto: '+@imie+' '+ @nazwisko
33521. --pobranie kolejnego wiersza z kursora
33622. FETCH NEXT FROM kursor_deleted INTO @imie, @nazwisko
33723. END
33824. --zamkniecie kursora
33925. CLOSE kursor_deleted
34026. --usuniecie kursora z bazy danych
34127. DEALLOCATE kursor_deleted
34228. END
34329. GO
34430. DELETE FROM pracownik
34538.2 Stwórz tabelę o nazwie samochod_delete o identycznej strukturze jak tabela samochód. Napisz wyzwalacz, który wszystkie usunięte samochody z tabeli samochód wpisze do tabeli samochod_delete. Przetestuj utworzony wyzwalacz – usuń z tabeli samochód samochody o identyfikatorach z przedziału od 1 do 4.
3461. --38.2
3472. CREATE TABLE samochod_delete(
3483. id_samochod INT PRIMARY KEY,
3494. marka VARCHAR(20) NOT NULL,
3505. typ VARCHAR(16) NOT NULL,
3516. data_prod DATETIME NOT NULL,
3527. kolor VARCHAR(16) NOT NULL,
3538. poj_silnika SMALLINT NOT NULL,
3549. przebieg INTEGER NOT NULL
35510. );
35611. CREATE TRIGGER samochod_przenies ON samochod
35712. FOR DELETE
35813. AS BEGIN
35914. DECLARE kursor_przenies CURSOR
36015. FOR SELECT * FROM deleted;
36116. OPEN kursor_przenies
36217. DECLARE @id INT, @marka VARCHAR(20), @typ VARCHAR(16), @data_prod DATETIME, @kolor VARCHAR(16), @poj_silnika SMALLINT, @przebieg INTEGER
36318. FETCH NEXT FROM kursor_przenies INTO @id, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg
36419. WHILE @@FETCH_STATUS=0
36520. BEGIN
36621. INSERT INTO samochod_delete(id_samochod, marka, typ, data_prod, kolor, poj_silnika, przebieg) VALUES
36722. (@id, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg);
36823. FETCH NEXT FROM kursor_przenies INTO @id, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg
36924. END
37025. CLOSE kursor_przenies
37126. DEALLOCATE kursor_przenies
37227. END
37328. GO
37438.3 Napisać wyzwalacz, który po podwyżce pensji pracownikowi będzie zerował jego dodatek. Przetestuj utworzony wyzwalacz – pracownikom o identyfikatorach równych 1, 2 i 3 zwiększ pensję o 200 zł.
3751. CREATE TRIGGER zero_dodatku ON pracownik
3762. AFTER UPDATE
3773. AS BEGIN
3784. DECLARE @id INT, @pensja DECIMAL(8,2)
3795. DECLARE kursor_pensja CURSOR FOR SELECT id_pracownik, pensja FROM inserted;
3806. OPEN kursor_pensja
3817. FETCH NEXT FROM kursor_pensja INTO @id, @pensja
3828. WHILE @@FETCH_STATUS=0
3839. BEGIN
38410. IF @pensja > (SELECT pensja FROM deleted WHERE id_pracownik=@id)
38511. BEGIN
38612. UPDATE pracownik
38713. SET dodatek=0 WHERE id_pracownik=@id;
38814. END
38915. FETCH NEXT FROM kursor_pensja INTO @id, @pensja
39016. END
39117. CLOSE kursor_pensja
39218. DEALLOCATE kursor_pensja
39319. END
39420. GO
39539 Widoki + CASE
39639.1 Utwórz widok o nazwie ocena_klienta, który dla każdego klienta (id_klient, imie, nazwisko) wyświetli informację, czy to jest stały klient (czyli wypisze słowo TAK lub NIE jako wartość kolumny "staly_klient"). Przyjmijmy, że stały klient to taki, który wypożyczył co najmniej dwa razy samochód. Użyj powyższego widoku i wypisz stałych klientów sortując wynik alfabetycznie po nazwisku i imieniu klienta.
3971. --39.1
3982. CREATE VIEW ocena_klienta AS
3993. SELECT k.id_klient, k.imie, k.nazwisko, CASE WHEN COUNT(w.id_klient)>=2 THEN 'TAK' ELSE 'NIE' END AS staly_klient
4004. FROM klient k LEFT JOIN wypozyczenie w ON k.id_klient=w.id_klient
4015. GROUP BY k.id_klient, k.imie, k.nazwisko;
4026. GO
4037. SELECT * FROM ocena_klienta WHERE staly_klient='NIE' ORDER BY nazwisko, imie;
404
40539.2 Utwórz widok o nazwie pensja_pracownika, który dla każdego pracownika (id_pracownik, imie, nazwisko) wyÅ›wietli informacjÄ™ w nowej kolumnie o nazwie "zarobki" o rzÄ™dzie jego zarobków: MAÅO, ÅšREDNIO, DUÅ»O. Przyjmijmy, że pracownik zarabia MAÅO jeÅ›li pensja jest poniżej 1500 zÅ‚, zarabia ÅšREDNIO jeÅ›li pensja jest od 1500 zÅ‚ do 3000 zÅ‚, a zarabia dużo jeÅ›li pensja jest wiÄ™ksza od 3000 zÅ‚. Użyj powyższego widoku do znalezienia pracowników zarabiajÄ…cych MAÅO.
4061. --39.2
4072. CREATE VIEW pensja_pracownika AS
4083. SELECT id_pracownik, imie, nazwisko, CASE WHEN pensja<1500 THEN 'Mała'
4094. WHEN pensja>=1500 AND pensja <3000 THEN 'Åšrednia'
4105. WHEN pensja>=3000 THEN 'Duża' END AS zarobki
4116. FROM pracownik
4127. GO
4138. SELECT * FROM pensja_pracownika WHERE zarobki='Mała'
4149. SELECT * FROM pracownik WHERE pensja<1500
415
416 39.3 Utwórz widok o nazwie "pracownik_informacje", który wyświetla o każdym pracowniku takie informacje jak: a) id_pracownik, b) imie, c) nazwisko, d) kolumna wyliczeniowa "staz_pracy", czyli długość zatrudnienia w latach, e) kolumna wyliczeniowa "wypozyczenia", czyli całkowita ilość wypożyczeń samochodów, f) kolumna wyliczeniowa "dodatek" z wartościami TAK/NIE/BRAK (TAK gdy dodatek>0, NIE gdy dodatek=0, BRAK w pozostałych przypadkach). Przetestuj utworzony widok wyszukując pracowników, dla których dodatek nie został przyznany.
4171. --39.3
4182. CREATE VIEW pracownik_informacje AS
4193. SELECT p.id_pracownik, p.imie, p.nazwisko,
4204. DATEDIFF(YEAR, p.data_zatr, GETDATE()) AS staz_pracy,
4215. COUNT(w.id_pracow_wyp) AS wypozyczenia,
4226. CASE WHEN p.dodatek>0 THEN 'Tak'
4237. WHEN p.dodatek=0 THEN 'Nie'
4248. ELSE 'Brak' END AS dodatek
4259. FROM pracownik p LEFT JOIN wypozyczenie w ON p.id_pracownik=w.id_pracow_wyp
42610. GROUP BY p.id_pracownik,p.imie,p.nazwisko, p.data_zatr, p.dodatek
42711. GO
42812. SELECT * FROM pracownik_informacje WHERE dodatek='Tak'
42913. SELECT * FROM pracownik WHERE id_pracownik=1