postgreSQL zad lab

[ Pobierz całość w formacie PDF ]
//-->Laboratorium PostgreSQLZadania do wykonania-1-1. Dodatkowe informacje1.1.1.2.Ka de zdanie SQL musi by zako czone rednikiem (;).Odwołanie do argumentu funkcji w ciele funkcji jest postaci$n,gdzienjest numerem argumentu.2. Czynno ci wst pne2.1.2.2.2.3.2.4.Zalogowa si do systemu Windows (np. jako u ytkowniklabdo domeny ZTI).Uruchomi serwis Postgresa klikaj c:START→PROGRAMY→PostgreSQL wersja→Start ServiceUruchomi narz dzie do administracji systemem:START→PROGRAMY→PostgreSQL versja→pgAdminIIIDwukrotnie kliknna serwer Postgresa widoczny w lewym panelu okna:2.5.Utworzy przy pomocy menu kontekstowego now baz danych o nazwie TBDX, gdzieXjest numerempodanym przez prowadz cego wiczenie::2.6.Otworzy okno zapyta SQL, poprzez naci ni cie przycisku3. Zadania3.1.Poleceniemcreate tableutworzy tabel o nast puj cej strukturze:Osoby (imie varchar(15), nazwisko varchar(15), PESEL varchar(11), data_ur timestamp)Laboratorium PostgreSQLZadania do wykonania-2-3.2.3.3.Utworzy dodatkow tabelPracownicy,wykorzystuj c polecenie:create table Pracownicy (nr_prac integer, nr_zesp integer, pensja real) INHERITS (Osoby);Wpisa 3 rekordy do tabeliOsoby:JanAdamAnnaNowakKowalskiKrolWicekBialek11111111111 01-01-198822222222222 01-10-198933333333333 10-15-199044444444444 12-12-197855555555555 12-12-1980121010199019913.4.Wpisa 2 rekordy do tabeliPracownicy:TomaszMaria3.5.Wy wietli (poleceniemselect) dane o tabelachOsobyiPracownicywpisane do perspektywypg_tables,dodaj c fraz :… where tablename = 'osoby' or tablename = 'pracownicy'3.6.Wy wietli nazwy i typy atrybutów tabeliOsoby:select pa.attname, pt.typnamefrom pg_class pc, pg_attribute pa, pg_type ptwhere pc.relname='osoby' and pc.oid =pa.attrelid and pt.oid = pa.atttypid;3.7.3.8.3.9.Wy wietli warto ci niejawnej kolumnyoidtabeliPracownicy. Uwaga: kolumna nie nazywa sioid.Wy wietli warto ci niejawnej kolumnyoidtabeliOsoby.Co daje si zauwa y ?Potwierdzi swoje wcze niejsze obserwacje wy wietlaj c wszystkie dane wpisane do tabeliOsoby:select * from Osoby;3.10. Do poprzednio zadanego zapytania doda frazonly:select * from only Osoby;3.11. Spróbowa usunrekord dotycz cy Marii Bialek z tabeliPracownicy.delete from Pracownicy where imie = ‘Maria’;3.12. Sprawdzi czy rekord został usuni tyzarównoz tabeliPracownicy,jak iz tabeliOsoby.3.13. Wpisa 2 rekordy do tabeliPracownicy:WitoldKamilaWrembelBialek88888888888 02-02-197799999999999 12-12-1983231020197019713.14. Ponownie wykona polecenie 3.7. Czy daje si zauwa y jak zmian ?3.15. Stworzy now tabel , w której b d pami tane informacje o premiach poszczególnych pracowników, przyczym atrybutpremia_kwartalnab dzie reprezentowany jako czteroelementowa tablica, a kolejne elementytej tablicy b d liczbami całkowitymi; wska nikiem b dzie numer kwartału:create table premie (nr_prac integer, premia_kwartalna integer[]);3.16. Wpisa nast puj ce dane do nowoutworzonej tabeli:insert into premie values (1, '{100,150,200,250}');3.17. Wy wietli wpisane do tablicy dane, wykonuj c zapytania typu:Select * from premie;select premia_kwartalna[1] from premie;3.18. Stworzy tabelbibliotece:zawieraj cinformacje o ksi kach po yczanych przez pracowników w zakładowejcreate table wypozyczenia (nr_prac integer, autor_tytul text[][]);3.19. Do utworzonej tabeli wpisa 2 rekordy (dotycz ce pracowników o numerach 1, i 2):insert into wypozyczenia values(1, '{{“Tolkien”, “Hobbit”}, {“Dickens”, “Klub Pickwicka”}, {„Stone”, „Pasja zycia”}}’);insert into wypozyczenia values (2, '{{"Pascal", "Przewodnik"}, {"Archer", "Co do grosza"}}');3.20. Wy wietliwynikach:warto ci wpisane w tablicach; zaobserwowaró nice i podobie stwa w otrzymywanychLaboratorium PostgreSQLZadania do wykonania-3-select * from wypo yczenia;select nr_prac, autor_tytul[1][1] from wypozyczenia;select nr_prac, autor_tytul[1:3][1] from wypozyczenia;select nr_prac, autor_tytul[1:3][1:3] from wypozyczenia;select nr_prac, autor_tytul[1:3][2] from wypozyczenia;select nr_prac, autor_tytul[2][2] from wypozyczenia;select nr_prac, autor_tytul[2][1] from wypozyczenia;3.21. Napisa funkcj w j zyku SQL, wy wietlaj c informacje o nazwisku pracownika, którego numer podanyjest parametrem. Ogólna posta funkcji jest nast puj ca:CREATE FUNCTIONnazwafunkcji(typparametru1,typparametru2,…)RETURNStypwynikowyAS ‘ciałofunkcji’LANGUAGE ‘sql’;CREATE FUNCTION dane (integer) RETURNStextAS ‘select nazwisko from Pracownicy where nr_prac = $1’LANGUAGE ‘sql’;3.22. Przetestowa działanie funkcji wpisuj c polecenie:select dane(1) as nazwisko;3.23. Napisa funkcj wy wietlaj c wszystkie dane osobowe pracownika (imi , nazwisko, PESEL), któregonumer podany jest parametrem. W tym celu zdefiniowa najpierw typ, a dopiero w drugiej kolejno cistosown procedur :CREATE TYPE complex AS (i text, n text, p text);CREATE FUNCTION dane2 (integer) RETURNScomplexAS ‘select imie, nazwisko, PESEL from Pracownicy where nr_prac = $1’LANGUAGE ‘sql’;select dane2(2);3.24. Napisa funkcj wy wietlaj c wszystkie dane osobowe (imi , nazwisko, PESEL) wszystkich pracowników:CREATE FUNCTION dane3 () RETURNSsetofcomplexAS ‘select imie, nazwisko, PESEL from Pracownicy’LANGUAGE ‘sql’;select dane3();3.25. Napisa funkcj wy wietlaj c (tylko)tytułyksi ek po yczonych przez pracownika o podanymparametrem funkcji numerze. Podj prób takiego wskazania „współ dnych” atrybutu tablicowego, aby wrzwyniku wykonania poleceniaSELECTfaktycznie pojawił si tylko tytuł ksi ek (a nie np. autoy-tytułyy).3.26. Napisa funkcj w proceduralnym j zyku Postgresa – plpgsql, ł cz c w jedno słowo dwa ci gi tekstowepodane parametrem:CREATE OR REPLACE FUNCTION concat (text, text) RETURNS text AS$$/*to jest delimiter pocz tkowy – mo e by dowolnym znakiem lub ci giem znaków*//*tu mog si pojawi deklaracje poprzedzone słowem DECLARE */BEGINRETURN $1||$2;END;$$/*to jest delimiter ko cowy*/LANGUAGE 'plpgsql';3.27. Przetestowa działanie funkcji – np.:Laboratorium PostgreSQLZadania do wykonania-4-pensji pracownikówselect concat(' ,'po'danie');3.28. Napisa funkcj w proceduralnym j zyku Postgresa – plpgsql, zwracaj c wartopodwy szon o 25% i przetestowa jej działanie.CREATE OR REPLACE FUNCTION extra_money () RETURNS real AS$$DECLARE zm real;BEGINselect 1.25 * pensja into zm from pracownicy;RETURN zm;END;$$LANGUAGE 'plpgsql';3.29. W celu zapami tania numerów telefonów poszczególnych osób, do tabeli Osoby doda 2 kolumny i dla2wybranych osób wpisa do nich przykładowe dane:alter table Osoby add column prefix_tel text;alter table Osoby add column tel text;update Osoby set prefix_tel = '0-16'where imie = 'Witold';update Osoby set tel = '7654321'where imie = 'Witold';update Osoby set prefix_tel = ' where imie = '0'Kamila';update Osoby set tel = '500010203'where imie = 'Kamila';3.30. Napisa funkcj ł cz c dla pracowników warto ci wpisane w kolumnieprefix_telz warto ciami kolumnytel:CREATE OR REPLACE FUNCTION merge_fields(t_row pracownicy) RETURNS text AS$$BEGINRETURN t_row.imie || ' || t_row.nazwisko || ' || t_row.prefix_tel || t_row.tel;''END;$$LANGUAGE plpgsql;SELECT merge_fields(t.*) FROM pracownicy t3.31. Napisa funkcj ł cz c warto ci wpisane w kolumnieprefix_telz warto ciami kolumnytel:CREATE OR REPLACE FUNCTION merge_fields(t_row osoby) RETURNS text AS$$BEGINRETURN t_row.prefix_tel || t_row.tel;END;$$LANGUAGE plpgsql;SELECT merge_fields(t.*) FROM osoby t ;3.32. Napisa reguł uniemo liwiaj c zmian warto ci atrybutupensjadla aktualizowanego pracownika. Regułama ogóln posta :CREATE RULEnazwaregułyAS ONzdarzenieTOobiekt[WHEREwarunek]DO [ALSO|INSTEAD] [akcja | (akcje) | NOTHING];Laboratorium PostgreSQLcreate rule regula1Zadania do wykonania-5-as on update to Pracownicywhere NEW.pensja <> OLD.pensjado instead nothing;3.33. Sprawdzi poprawnodziałania reguł a nast pnie usuny,reguł :select * from pracownicy;update pracownicy set nr_zesp = 30 where nr_zesp = 20;select * from pracownicy;update pracownicy set pensja = 2000 where imie = 'Witold';select * from pracownicy;3.34. Napisa reguł , która nie dopu ci na dopisanie nowego pracownika o numerze mniejszym b d równymzeru.3.35. Za pomoc reguł utworzy modyfikowalne widoki (perspektywy), które normalnie nie s obsługiwane przezPostgresql. W tym celu utworzy perspektyw tabeli Osoby:CREATE VIEW osob_view AS SELECT imie, nazwisko, PESEL FROM osoby WHERE imie=’’;CREATE RULE reg2 AS ON INSERT TO osob_view DO INSTEAD INSERT INTO osobyVALUES (NEW.imie,NEW.nazwisko, NEW.PESEL);3.36. Trigger definiuje si nast puj c składni :CREATE TRIGGER nazwaBEFORE | AFTER /*czy trigger ma by wykonany przed czy po zdarzeniu*/INSERT | UPDATE | DELETE /*których zdarze trigger dotyczy, mo na ł czy kilka przez OR)*/ON tabelaFOR EACHROW | STATEMENT /*czy trigger ma by wywołany raz na rekord, czy raz na instrukcj */EXECUTE PROCEDURE procedura (parametry); /*co ma by wywołane jako obsługa triggera*/Trigger usuwa si nast puj c składni :DROP TRIGGER nazwa ON tabela;3.37. W celu pami tania czasu modyfikacji danych w tabeliPremie, doda do niej 1 kolumn :ALTER TABLE Premie ADD COLUMN last_updated timestamptz;3.38. Napisa funkcjCREATE OR REPLACE FUNCTION upd() RETURNS trigger AS$$BEGINNEW.last_updated = now();RETURN NEW;END;$$LANGUAGE plpgsql;3.39. Utworzy wyzwalacz, który dla ka dego nast pnego wstawienia nowego wiersza (lub modyfikacjiistniej cego) w tabeliPremiespowoduje umieszczenie aktualnego znacznika czasu w polu last_updatedbie cego rekordu tabeli:CREATE TRIGGER last_updBEFORE insert OR update ON PremieFOR EACH ROWEXECUTE PROCEDURE upd();3.40. Przetestowa działanie napisanego wyzwalacza: [ Pobierz całość w formacie PDF ]

zanotowane.pl

doc.pisz.pl

pdf.pisz.pl

fotocafe.xlx.pl