· 6 years ago · Jan 12, 2020, 09:36 PM
1KOLOS
2Z bazy filmy wypisz jedynie tytuły tych filmów i epizodów, których ostatnie słowo ma pięć liter.
3
4jeśli “pięć liter” to po prostu “pięć znaków”:
5select e.title from episodes_list e where length(substring(e.title, '\s(\w{5})$'))=5;
6
7jeśli “pięć liter” to naprawdę “pięć liter” xd:
8select title from episodes_list where title ~ '(\m[a-zA-Z]{5})$';
9
10Dla każdej kategorii wyświetlić sumę długości filmów należących do niej. Dla kategorii, które nie zawierają żadnych filmów suma długości ma być równa 0 (nie NULL!!). Filmy nienależące do żadnej kategorii należy uwzględnić jako osobną kategorię bez nazwy (NULL).
11
12 select coalesce(c.name, 'n/a'), coalesce(sum(m.lenmsec), 0) from categories c full join movies_list m using (cid) group by c.name;
13
14 SELECT COALESCE(SUM(ML.lenmsec), 0) AS case, C.name FROM categories C FULL JOIN movies_list ML USING (cid) GROUP BY C.cid, C.name ORDER BY C.name;
15
16Napisz zapytanie do bazy filmy, które wypisze nazwę podkategorii, do której zalicza się najkrótszy epizod.
17
18 select s.name from episodes_list e, movies_list m, subcategories s where e.mid=m.mid and m.sid=s.sid and e.is_movie=0 order by (e.episode_end - e.episode_start) limit 1;
19
20
21-- zrobione joinami:
22 select s.name from subcategories s inner join movies_list m using (sid) inner join episodes_list e using (mid) where e.is_movie = 0 order by e.episode_end-e.episode_start limit 1;
23
24Zaimportuj zrzut bazy ~/sikor/db/kolos_1B.dump. Z tabeli studenci wypisz osoby w wieku między 20 a 22 lata od najstarszej do najmłodszej. Następnie usuń z tabeli studenci kolumnę grupa.
25
26 cp ~/sikor/db/kolos_1B.dump
27 psql -f kolos_1B.dump
28
29 select * from studenci where wiek between 20 and 22 order by wiek desc;
30
31 alter table studenci drop column grupa;
32
33Zaimportuj zrzut bazy ~/sikor/db/kolos_1B.dump. Wszystkim studentom o imieniu Marian zmień imię na Seweryn, a następnie z tabeli studenci wypisz wszystkich studentów o imieniu Seweryn w kolejności od najstarszego do najmłodszego.
34
35 cp ~/sikor/db/kolos_1B.dump
36 psql -f kolos_1B.dump
37
38 update studenci set imie=’Seweryn’ where imie=’Marian’;
39
40 select * from studenci where imie=’Seweryn’ order by wiek desc;
41
42Z bazy filmy wyświetlić autorów i tytuły wszystkich epizodów uporządkowane od najkrótszego do najdłuższego.
43
44select m.author, e.title from movies_list m, episodes_list e where e.is_movie = 0 and m.mid = e.mid order by (e.episode_end - e.episode_start);
45
46
47Dla bazy filmy napisz zapytanie, które jednoznaczne wykaże (1 row) ile epizodów posiada najdłuższy film
48
49 select m.mid, count(e.eid), sum(m.lenmsec) from movies_list m, episodes_list e where m.mid=e.mid group by (m.mid) order by sum(m.lenmsec) desc limit 1;
50
51select count(e.mid) from episodes_list e where e.mid = (select mid from movies_list order by lenmsec desc limit 1);
52
53select count(e.mid) from episodes_list e where e.mid = (select mid from movies_list order by lenmsec desc limit 1) and e.is_movie=0;
54
55select count(m.mid), m.mid, m.lenmsec from movies_list m, episodes_list e where e.is_movie=0 and e.mid=m.mid group by m.lenmsec, m.mid order by lenmsec desc limit 1;
56
57Wyświetl autorów filmów posiadających co najmniej jeden epizod dłuższy niż 10 sekund.
58
59ver 1:
60select distinct m.author from movies_list m, episodes_list e where e.is_movie = 0 and e.mid = m.mid and (e.episode_end - e.episode_start) > 10000 and m.author is not null;
61
62ver 2:
63 select distinct m.author from movies_list m, episodes_list e where e.is_movie = 0 and e.mid = m.mid and (e.episode_end - e.episode_start) > 10000 and m.author <> '';
64
65
66ver 3:
67select distinct
68case when e.episode_end-e.episode_start>10000 then m.author
69end
70from movies_list m, episodes_list e
71where
72e.mid=m.mid and e.is_movie=0 and m.author <> '';
73
74ver 4:
75select m.author from movies_list m, episodes_list e where m.mid=e.mid and (e.episode_end - e.episode_start)>10000 and e.is_movie=0 and m.author <> '' group by(m.author);
76
77Zaimportuj zrzut bazy ~/sikor/db/kolos_1A.dump a następnie napisz trigger, który nie pozwoli na wpisanie do tabeli studenci osoby starszej niż 25 lat.
78
79 cp ~/sikor/db/kolos_1A.dump .
80 psql -f kolos_1A.dump
81
82 create function starosc_nie_radosc() returns trigger as ‘
83 begin
84 if NEW.wiek <= 25 then
85 return NEW;
86 else
87 raise notice ''Starosc nie radosc - nie mozna wpisac do bazy osobe starsza niz 25 lat!'';
88 return NULL;
89 end if;
90 end;
91 ‘ language ‘plpgsql’;
92
93create trigger starosc_nie_radosc_trigger before insert on studenci for each row execute starosc_nie_radosc();
94
95Zaimportuj zrzut bazy ~/sikor/db/kolos_1E.dump a następnie napisz zapytanie, które odblokuje kasowanie danych w tabeli studenci.
96 cp ~/sikor/db/kolos_1E.dump .
97 psql -f kolos_1E.dump
98
99 drop trigger if exists rdonly_t on studenci;
100
101LABY
102
103Lab02:
104Wyświetlić identyfikatory filmów w których w tytule filmu lub w nazwie autora występuje słowo Techno (... WHERE xxxx ~ 'Techno' ... gdzie xxxx jest nazwą kolumny w której szukamy tego słowa)
105
106SELECT EL.mid FROM episodes_list EL, movies_list ML WHERE EL.mid = ML.mid AND (EL.title ~'Techno' OR ML.author ~ 'Techno');
107
108 Jak wyżej, ale wypisać także autora i tytuł każdego znalezionego filmu
109
110SELECT EL.mid, ML.author, EL.title FROM episodes_list EL, movies_list ML WHERE EL.mid = ML.mid AND (EL.title ~'Techno' OR ML.author ~ 'Techno');
111
112 Załadować ze zrzutu ~rstankie/db/stud2.dump tabelę episodes_list2. Tabela episodes_list2 zawiera dane z tabeli episodes_list z wprowadzonymi drobnymi zmianami. Ustalić, które wiersze zostały zmienione/usunięte/dodane.
113Wyświetlić tytuły filmów (is_movie = 1) z tabeli episodes_list
114
115SELECT title AS tytul_filmu FROM episodes_list WHERE is_movie = 1;
116
117 Wyświetlić tytuł filmu, ID kategorii i ID podkategorii, do której należy dany film
118
119SELECT EL.title AS tytul_filmu, ML.cid AS nr_kat, ML.sid AS nr_podkat FROM episodes_list EL, movies_list ML WHERE EL.is_movie = 1 AND EL.mid = ML.mid;
120
121 Wyświetlić tytuł filmu, NAZWĘ kategorii i NAZWĘ podkategorii, do której należy dany film
122
123SELECT EL.title AS tytul_filmu, C.name AS nazwa_kat, SC.name AS nazwa_podkat FROM categories C, subcategories SC, episodes_list EL, movies_list ML WHERE EL.is_movie = 1 AND SC.sid=ML.sid AND ML.cid=C.cid and EL.mid=ML.mid;
124
125-- roznica pomiedzy outputem z zadania 5 a 6:
126
127SELECT EL.title AS tytul_filmu, ML.cid AS nr_kat, ML.sid AS nr_podkat FROM episodes_list EL, movies_list ML WHERE EL.is_movie = 1 AND EL.mid = ML.mid EXCEPT SELECT EL.title AS tytul_filmu, ML.cid AS nr_kat, ML.sid AS nr_podkat FROM categories C, subcategories SC, episodes_list EL, movies_list ML WHERE EL.is_movie = 1 AND SC.sid=ML.sid AND ML.cid=C.cid and EL.mid=ML.mid;
128
129Wyświetlić numery i tytuły filmów należących do kategorii 63
130
131SELECT ML.mid AS nr_filmu, EL.title AS tytul_filmu FROM categories C, subcategories SC, episodes_list EL, movies_list ML WHERE EL.is_movie = 1 AND SC.sid=ML.sid AND ML.cid=C.cid and EL.mid=ML.mid AND ML.cid = 63;
132
133Wyświetlić numery i tytuły filmów należących do kategorii "Education" posortowane po tytułach
134
135SELECT ML.mid AS nr_filmu, EL.title AS tytul_filmu FROM categories C, subcategories SC, episodes_list EL, movies_list ML WHERE EL.is_movie = 1 AND SC.sid=ML.sid AND ML.cid=C.cid and EL.mid=ML.mid AND C.name ~ 'Education' ORDER BY EL.title;
136
137Wyświetlić listę epizodów (jako episode) oraz tytuły filmów, których te epizody są fragmentami (jako movie). Posortować według tytułu filmu (rosnąco) oraz wg czasu początku epizodu (malejąco)
138
139SELECT EL1.title AS episode, EL2.title AS movie FROM episodes_list EL1, episodes_list EL2 WHERE EL1.is_movie = 0 AND EL2.is_movie=1 AND EL1.mid = EL2.mid ORDER BY EL2.title ASC, EL1.episode_start DESC;
140
141 Jak wyżej tylko dodać jeszcze kolumnę 'start' z czasem początku epizodu w formacie: HH:MM:SS. W tabeli czas podany jest w milisekundach. Zalecane użycie funkcji reltime(int4).
142
143SELECT reltime(EL1.episode_start/1000) AS start, EL1.title AS episode, EL2.title AS movie FROM episodes_list EL1, episodes_list EL2 WHERE EL1.is_movie = 0 AND EL2.is_movie=1 AND EL1.mid = EL2.mid ORDER BY EL2.title ASC, EL1.episode_start DESC;
144
145 Jak wyżej, ale wyświetlić dodatkowo nazwę kategorii i podkategorii.
146
147 SELECT reltime(EL1.episode_start/1000) AS start, EL1.title AS episode, EL2.title AS movie, C.name as category, SC.name AS subcategory FROM episodes_list EL1, episodes_list EL2, categories C, subcategories SC, movies_list ML WHERE EL1.is_movie = 0 AND EL2.is_movie=1 AND EL1.mid = EL2.mid AND SC.sid=ML.sid AND ML.cid=C.cid AND EL1.mid=ML.mid ORDER BY EL2.title ASC, EL1.episode_start DESC;
148
149Wyświetlić tytuły epizodów, których czas trwania jest większy od 100000. Wyświetlić również czasy trwania epizodów i posortować wg czasu trwania.
150
151SELECT EL.title AS episode_title, (EL.episode_end - EL.episode_start) AS episode_length FROM episodes_list EL, categories C, subcategories SC, movies_list ML WHERE EL.is_movie = 0 AND SC.sid=ML.sid AND ML.cid=C.cid AND EL.mid=ML.mid AND (EL.episode_end - EL.episode_start) > 100000 ORDER BY episode_length;
152
153Lab03:
154
155Wyświetlić identyfikator kategorii i sumę czasów trwania filmów należących do poszczególnych kategorii.
156
157SELECT ML.cid, SUM(EL.episode_end - EL.episode_start) FROM movies_list ML, episodes_list EL WHERE ML.mid = EL.mid AND EL.is_movie = 1 GROUP BY ML.cid ORDER BY ML.cid ASC;
158
159Wyświetlić identyfikator kategorii i sumę czasów trwania filmów należących do poszczególnych kategorii. Do obliczania sumy brać tylko filmy, których wartość parametru stream > 1 500 000.
160
161SELECT ML.cid, SUM(EL.episode_end - EL.episode_start) FROM movies_list ML, episodes_list EL WHERE ML.mid = EL.mid AND EL.is_movie = 1 AND ML.stream > 1500000 GROUP BY ML.cid ORDER BY ML.cid ASC;
162
163Wyświetlić identyfikator kategorii i sumę czasów trwania filmów należących do poszczególnych kategorii. Do obliczania sumy brać tylko filmy, których wartość parametru stream > 1 500 000. Wyświetlić tylko kategorie, dla których suma czasów trwania filmu jest większa od 900 000 ms.
164
165SELECT ML.cid, SUM(EL.episode_end - EL.episode_start) FROM movies_list ML, episodes_list EL WHERE ML.mid = EL.mid AND EL.is_movie = 1 AND ML.stream > 1500000 GROUP BY ML.cid HAVING SUM(EL.episode_end - EL.episode_start) > 900000 ORDER BY ML.cid ASC;
166
167Policz dla ilu epizodów (nie filmów) wprowadzono opis jako tekst (descrtype=1).
168
169SELECT COUNT(*) AS liczba_opisow FROM movies_list ML, episodes_list EL WHERE ML.mid = EL.mid AND EL.is_movie = 0 AND EL.descrtype = 1 GROUP BY ML.mid;
170Wyświetl nazwy kategorii, które mają przynajmniej 2 subkategorie. Jako drugą kolumnę wyświetl ile subkategorii należy do danej kategorii.
171
172SELECT C.name AS Nazwa_kategorii, COUNT(SC.sid) FROM categories C, subcategories SC WHERE C.cid = SC.cid GROUP BY C.name HAVING COUNT(SC.sid) >= 2 ORDER BY C.name DESC;
173
174Wypisz nazwy subkategorii, dla których maksymalny czas trwania epizodów należących do danej subkategorii jest dłuższy od 10 sekund. Dodatkowo wypisz czas trwania najdłuższego epizodu i liczbę epizodów spełniających warunek dla danej subkategorii.
175
176--version simplified
177SELECT SC.name AS Subkategoria, MAX(EL.episode_end - EL.episode_start), COUNT(EL.episode_end - EL.episode_start) FROM movies_list ML, episodes_list EL, subcategories SC WHERE ML.mid = EL.mid AND ML.sid = SC.sid AND EL.is_movie = 0 GROUP BY SC.name HAVING MAX(EL.episode_end - EL.episode_start) > 10000;
178
179--version with the same output as in the answer
180SELECT SC.name AS Subkategoria, MAX(EL.episode_end - EL.episode_start), COUNT(EL.episode_end - EL.episode_start) FROM movies_list ML, episodes_list EL, subcategories SC WHERE ML.mid = EL.mid AND ML.sid = SC.sid AND EL.is_movie = 0 GROUP BY SC.name, SC.sid HAVING MAX(EL.episode_end - EL.episode_start) > 10000 ORDER BY SC.sid DESC;
181
182Wypisz nazwy subkategorii, dla których maksymalna długość epizodu należącego do danej subkategorii jest mniejsza od 350 sekund. Jako drugą kolumnę wypisz długość najdłuższego epizodu, a jako trzecią kolumnę liczbę epizodów należących do danej subkategorii. Posortuj wyniki według nazwy subkategorii.
183
184SELECT SC.name AS Nazwa_podkategorii, MAX(EL.episode_end - EL.episode_start), COUNT(EL.episode_end - EL.episode_start) FROM movies_list ML, episodes_list EL, subcategories SC WHERE ML.mid = EL.mid AND ML.sid = SC.sid AND EL.is_movie = 0 GROUP BY SC.name HAVING MAX(EL.episode_end - EL.episode_start) < 350000 ORDER BY SC.name ASC;
185
186Policz ile filmów ma różne słowa kluczowe.
187
188SELECT COUNT(DISTINCT EL.keywords) FROM episodes_list EL WHERE EL.is_movie=1;
189
190Policz ile filmów ma zdefiniowany odnośnik URL (parametr descrhtml).
191
192SELECT COUNT(EL.descrhtml) FROM episodes_list EL WHERE EL.is_movie = 1;--verion 1
193
194SELECT COUNT(*) FROM episodes_list EL WHERE EL.is_movie = 1 AND EL.descrhtml IS NOT NULL; --version 2
195Policz ile filmów nie ma zdefiniowanego odnośnika URL (parametr descrhtml).
196
197SELECT COUNT(*) FROM episodes_list EL WHERE EL.is_movie = 1 AND EL.descrhtml IS NULL;
198
199Policz ile jest zdefiniowanych różnych odnośników URL dla filmów
200
201SELECT COUNT(DISTINCT EL.descrhtml) FROM episodes_list EL WHERE EL.is_movie=1;
202
203Wypisz nazwy filmów i odnośniki URL posortowane według nazwy filmu. Jeśli odnośnik URL nie jest zdefiniowany powinien pojawić się napis "URL not defined" (użyj COALESCE).
204
205SELECT EL.title AS title, COALESCE(EL.descrhtml, 'URL not defined') AS url FROM episodes_list EL WHERE EL.is_movie = 1 GROUP BY EL.title, EL.descrhtml ORDER BY EL.title ASC;
206
207--second version
208SELECT EL.title AS title, COALESCE(EL.descrhtml, 'URL not defined') AS url FROM episodes_list EL WHERE EL.is_movie = 1 ORDER BY EL.title ASC;
209
210KOLOS:
211-- Znajdujemy mid wszystkich filmow, ktore nie maja epizodow
212SELECT mid INTO mid_of_movies_without_episodes FROM episodes_list GROUP BY mid HAVING COUNT(eid)=1;
213
214-- Wyswietlamy title filmow, o mid wyznaczonych w poprzednim punkcie
215SELECT EL.title FROM episodes_list EL, mid_of_movies_without_episodes ELM WHERE EL.mid = ELM.mid ORDER BY EL.title;
216
217-- Usuwamy tabele z pkt 1
218DROP TABLE mid_of_movies_without_episodes;
219
220Lab04:
221
222Wyświetlić nazwy podkategorii należących do kategorii 'Education' i średnie czasy trwania filmów należących do poszczególnych podkategorii.
223
224SELECT SC.name, (SELECT AVG(ML.lenmsec) FROM movies_list ML WHERE ML.sid = SC.sid) FROM subcategories SC, categories C WHERE SC.cid=C.cid AND C.name = 'Education';
225
226Wyświetlić tytuły wszystkich filmów nie należących do kategorii 'Education' (stosując podzapytania), posortowane rosnąco:
227
228SELECT EL.title FROM episodes_list EL WHERE EL.mid = any (SELECT ML.mid FROM movies_list ML WHERE ML.sid = any (SELECT SC.sid FROM subcategories SC, categories C WHERE SC.cid=C.cid AND C.name <> 'Education')) AND EL.is_movie=1 ORDER BY EL.title;
229
230Wyświetlić nazwy kategorii, w których minimalna długość filmu w bajtach jest większa niż 2000000.
231
232SELECT C.name FROM categories C WHERE C.cid = any (SELECT ML.cid FROM movies_list ML GROUP BY ML.cid HAVING MIN(ML.lenbin) > 2000000) ;
233
234Podać statystykę liczby epizodów w filmach. Poniższe wyniki oznaczają, że w bazie jest 40 filmów, dla których nie zdefiniowano epizodów, 2 filmy z jednym epizodem, jeden film z pięcioma epizodami itd.
235
236-- version 1
237SELECT TMP.coe AS no of episodes, COUNT(TMP.mid) AS no of movies FROM (SELECT EL.mid as mid, COUNT(EL.eid)-1 as coe FROM episodes_list EL GROUP BY EL.mid) as Tmp GROUP BY TMP.coe;
238
239-- version 2
240SELECT TMP.noe, COUNT(TMP.mid) FROM (SELECT EL.mid AS mid, COUNT(EL.eid)-1 as noe FROM episodes_list EL GROUP BY EL.mid) AS TMP GROUP BY noe ORDER BY TMP.noe DESC;
241
242Wypisać ID, nazwę i długość filmów, które są dłuższe od średniej z długości pozostałych filmów. Jako ostatnią kolumnę wyświetlić średnią z długości pozostałych filmów
243
244SELECT ML.mid, EL.title, ML.lenmsec, (SELECT AVG(ML2.lenmsec) FROM movies_list ML2 WHERE ML2.mid <> ML.mid) FROM episodes_list EL, movies_list ML WHERE EL.mid=ML.mid AND ML.lenmsec > (SELECT AVG(ML2.lenmsec) FROM movies_list ML2 WHERE ML2.mid <> ML.mid) AND EL.is_movie=1;
245
246Wypisz nazwy subkategorii, dla których sumaryczny czas trwania filmów należących do danej subkategorii jest dłuższy od 1000 sekund. Dodatkowo wypisz czas trwania najdłuższego filmu i liczbę filmów dla danej subkategorii. Użyj podzapytań
247
248-- version 1
249SELECT SC.name, TMP.maxim, TMP.cnt FROM (SELECT ML.sid, SUM(ML.lenmsec), MAX(ML.lenmsec) as maxim, count(ML.mid) as cnt FROM movies_list ML GROUP BY ML.sid HAVING SUM(ML.lenmsec) > 1000000) AS TMP, subcategories SC WHERE TMP.sid=SC.sid;
250
251Wypisz nazwy subkategorii, dla których średni czas trwania epizodów jest dłuższy od 15 sekund. Dodatkowo wypisz średni czas trwania epizodów i liczbę epizodów należących do danej subkategorii. Użyj podzapytań. W zapytaniu głównym nie używaj iloczynu kartezjańskiego.
252
253SELECT EL.mid, AVG(EL.episode_end-EL.episode_start) AS dif FROM episodes_list EL WHERE EL.is_movie=0 GROUP BY EL.mid HAVING AVG(EL.episode_end-EL.episode_start) > 15000;
254
255Wykonaj zadanie 7 bez używania iloczynu kartezjańskiego w zapytaniu głównym i w którymkolwiek z podzapytań
256Utworzyć widok o nazwie srednie_v (polecenie CREATE VIEW) zawierający nazwy podkategorii należących do kategorii „Education” oraz średnie czasy trwania filmów w poszczególnych podkategoriach. Wyświetlić dane z widoku srednie_v
257Utworzyć nową tabelę o nazwie srednie_t (polecenie CREATE TABLE) zawierającą nazwy podkategorii należących do kategorii „Education” oraz średnie czasy trwania filmów w poszczególnych podkategoriach. Wyświetlić dane z tabeli srednie_t
258 Jeden z filmów należących do podkategorii „Biology” ma czas trwania równy 6363459. Zmienić czas trwania tego filmu na 1 000 000. Następnie wyświetlić zawartość widoku srednie_v:
259 Ustawić pole „sysflags” w tabeli movies_list na wartość 7 dla tego filmu, dla którego czas trwania jest najdłuższy ze wszystkich filmów
260 Ustawić pole „sysflags” w tabeli movies_list na wartość 8 dla tych filmów, które są najkrótsze w swoich podkategoriach
261
262KOLOS:
263-- Liczba epizodow dla najdluzszego filmu (jesli byloby kilka filmow o tej samej dlugosci trwania i bylby to max, to wyswietla osobno liczbe epizodow kazdego z nich)
264
265SELECT COUNT(EL2.eid) FROM episodes_list EL2 WHERE (EL2.episode_end-EL2.episode_start) = (SELECT MAX(EL1.episode_end-EL1.episode_start) FROM episodes_list EL1 WHERE EL1.is_movie=1) AND is_movie=0 GROUP BY EL2.mid;
266
267Lab05:
268
269Wyświetlić nazwę kategorii i sumę czasów trwania filmów należących do poszczególnych kategorii. Użyj CROSS JOIN.
270
271SELECT C.name, SUM(ML.lenmsec) FROM categories C CROSS JOIN movies_list ML WHERE C.cid=ML.cid GROUP BY C.name ORDER BY C.name;
272
273 Policzyć ile epizodów (jako episode nr) należy do danego filmu. Wypisać tytuły filmów, których te epizody są fragmentami (jako movie). Posortować według tytułu filmu (rosnąco). Dodatkowo wyświetlić nazwę kategorii i podkategorii. Użyj CROSS JOIN.
274
275SELECT Tab1.count, Tab2.title, C.name, SC.name FROM (SELECT COUNT(*), EL.mid FROM episodes_list EL WHERE is_movie=0 GROUP BY EL.mid) AS Tab1 CROSS JOIN (SELECT EL2.title, EL2.mid FROM episodes_list EL2 WHERE EL2.is_movie=1) AS Tab2 CROSS JOIN movies_list ML CROSS JOIN categories C CROSS JOIN subcategories SC WHERE Tab1.mid=Tab2.mid AND Tab1.mid=ML.mid AND C.cid=ML.cid AND SC.sid=ML.sid ORDER BY Tab2.title;
276
277Wykonaj Zadanie 1 i Zadanie 2 z użyciem INNER JOIN
278
2791:
280SELECT C.name, SUM(ML.lenmsec) AS case FROM categories C INNER JOIN movies_list ML USING (cid) GROUP BY C.cid, C.name ORDER BY C.name;
281
2822:
283SELECT COUNT(EL.eid) AS "episode nr", EL2.title AS "movie", C.name AS "category", SC.name AS "subcategory" FROM episodes_list EL INNER JOIN episodes_list EL2 USING (mid) INNER JOIN movies_list ML USING (mid) INNER JOIN categories C USING (cid) INNER JOIN subcategories SC USING (sid) WHERE EL.is_movie=0 AND EL2.is_movie=1 GROUP BY EL.mid, EL2.title, C.name, SC.name ORDER BY "episode nr";
284
285 Wyświetlić tytuł filmu, NAZWĘ kategorii i NAZWĘ podkategorii, do której należy dany film. Mają być wyświetlone wszystkie filmy, nawet te nie przypisane do kategorii.
286
287SELECT EL.title, C.name, SC.name FROM episodes_list EL LEFT JOIN movies_list ML USING (mid) LEFT JOIN categories C USING (cid) LEFT JOIN subcategories SC ON ML.sid=SC.sid WHERE EL.is_movie=1;
288
289 Policzyć ile filmów należy do danej kategorii. W statystyce uwzględnić filmy, które nie należą do żadnej kategorii pod nazwą "--n/a--".
290
291SELECT COUNT(*), COALESCE(C.name, '--n/a--') FROM categories C RIGHT JOIN movies_list ML USING (cid) GROUP BY ML.cid, C.name ORDER BY C.name;
292
293Wyświetlić listę, zawierającą dwie kolumny: tytuł epizodu, tytuł filmu. Na wyniku mają sie pojawić wszystkie filmy (nawet te, które nie posiadają epizodów).
294
295SELECT EL_mod.title, EL2.title FROM (SELECT EL.title, EL.mid FROM episodes_list EL WHERE EL.is_movie=0) AS EL_mod RIGHT JOIN episodes_list EL2 USING (mid) WHERE EL2.is_movie=1;
296
297Dla każdej kategorii wyświetlić sumę długości filmów należących do niej. Dla kategorii, które nie zawierają żadnych filmów suma długości ma być równa 0 (nie NULL!!!). Filmy nie należące do żadnej kategorii należy uwzględnić jako osobną kategorię bez nazwy (NULL)
298SELECT COALESCE(SUM(ML.lenmsec), 0) AS case, C.name FROM categories C FULL JOIN movies_list ML USING (cid) GROUP BY C.cid, C.name ORDER BY C.name;
299
300
301Lab06:
302
303Wyświetlić wszystkie tytuły filmów i epizodów, które zaczynają się od litery 'T' i nie kończą znakiem 'e'
304
305SELECT EL.title FROM episodes_list EL WHERE El.title ~'^T' AND EL.title !~'e$';
306
307SELECT EL.title FROM episodes_list EL WHERE El.title ~'^T.*[^e]$';
308
309Wyświetlić wszystkie tytuły filmów i epizodów, które zaczynają się od litery 'T' i kończą znakiem 'e'. Użyć tylko jeden warunek
310
311SELECT EL.title FROM episodes_list EL WHERE El.title ~'^T.*e$';
312
313Na dwa sposoby wyświetlić tytuły filmów i epizodów które zawierają słowo 'The' lub 'the'
314--version 1
315SELECT EL.title FROM episodes_list EL WHERE El.title ~* 'the';
316
317--version 2
318SELECT EL.title FROM episodes_list EL WHERE El.title LIKE '%the%' OR El.title LIKE '%The%';
319
320Wyświetlić tytuły filmów i epizodów w których występuje cyfra ze zbioru: 0, 1, 2, 3, 5, 6.
321
322SELECT EL.title FROM episodes_list EL WHERE El.title ~ '[0-356]';
323
324Wyświetlić tytuły filmów i epizodów w których występują koło siebie cztery dowolne cyfry ze zbioru: 0, 1, 2, 3, 5, 6
325
326SELECT EL.title FROM episodes_list EL WHERE El.title ~ '[0-356]{4}';
327
328Wyświetlić identyfikatory filmów dla których rozmiar pliku (lenbin) zawiera się w granicach 10 000 000 i 30 000 000. Zastosowć operator between.
329
330SELECT ML.mid FROM movies_list ML WHERE ML.lenbin BETWEEN 10000000 AND 30000000;
331
332Wyświetlić tytuły filmów z zadania 6. Posłużyć się podzapytaniem (nie krzyżować tabel) i operatorem in.
333SELECT EL.title FROM episodes_list EL WHERE EL.mid in (SELECT ML.mid FROM movies_list ML WHERE ML.lenbin BETWEEN 10000000 AND 30000000);
334
335KOLOS:
336SELECT EL.title FROM episodes_list EL WHERE El.title ~ ' [a-zA-Z]{5}$';
337
338Lab07:
339
340
341Nie było zadanek xd
342
343Lab08:
344
345Wyświetlić po 5 wierszy: tytuł filmu, metodę kompresji i nazwę kategorii, do której należy dany film. Wyniki sortowane po tytule. Można użyć iloczyn kartezjański.
346begin;
347
348DECLARE x CURSOR FOR SELECT EL.title, ML.compress, C.name FROM episodes_list EL, movies_list ML, categories C WHERE ML.mid = EL.mid AND ML.cid = C.cid AND EL.is_movie = 1 ORDER BY EL.title;
349
350FETCH 5 FROM x;
351
352FETCH 5 FROM x;
353
354commit;
355
356Zrealizować zadanie 1 korzystając z klauzul limit/offset. Wyniki jak w zad 2
357
358SELECT EL.title, ML.compress, C.name FROM episodes_list EL, movies_list ML, categories C WHERE ML.mid = EL.mid AND ML.cid = C.cid AND EL.is_movie = 1 ORDER BY EL.title LIMIT 5;
359
360SELECT EL.title, ML.compress, C.name FROM episodes_list EL, movies_list ML, categories C WHERE ML.mid = EL.mid AND ML.cid = C.cid AND EL.is_movie = 1 ORDER BY EL.title LIMIT 5 OFFSET 5;
361
362Należy umożliwić Użytkownikowi dodawanie nowych wierszy do tabeli categories, oraz wyświetlanie tylko kolumn cid oraz name. Użytkownik nie może mieć prawa odczytu do kolumny cserid!
363
364GRANT INSERT, SELECT (cid, name) ON categories TO [username];
365
366
367
368
369
370Napisać funkcję silnia przyjmującą jako argument wartość int4 i zwracająca int4.
371
372create function x_silnia (int4) returns int4 as '
373declare
374 x int4;
375begin
376 IF $1 <= 1 THEN
377 return 1;
378 ELSE
379 return $1*x_silnia($1-1);
380 END IF;
381end;
382' language 'plpgsql';
383
384select x_silnia(0);
385select x_silnia(1);
386select x_silnia(2);
387select x_silnia(3);
388select x_silnia(4);
389select x_silnia(5);
390
391drop function x_silnia(int4);
392
393Napisać funkcję, która liczy wartość średnią z kolumny konta.konto.
394
395create table konta (id serial, konto int4 default 0);
396
397create function x_srednia () returns float as '
398declare
399 s float;
400 n float;
401begin
402 select sum(konto) into s from konta;
403 select count(konto) into n from konta;
404 return s/n;
405end;
406' language 'plpgsql';
407
408insert into konta (konto) values (10);
409insert into konta (konto) values (11);
410
411select x_srednia();
412
413drop function x_srednia();
414drop table konta;
415
416Zmodyfikować trigger x_2t, tak, aby działał również po każdym insert, update oraz delete
417
418create table konta (id serial, konto int4 default 0);
419create function x_2 () returns trigger as '
420declare
421 c int4;
422begin
423 select sum(konto) into c from konta;
424 raise notice ''Suma kont wynosi %'', c;
425 return NEW;
426end;
427' language 'plpgsql';
428
429create trigger x_2ti after insert on konta for each row execute procedure x_2();
430create trigger x_2tu after update on konta for each row execute procedure x_2();
431create trigger x_2td after delete on konta for each row execute procedure x_2();
432
433insert into konta (konto) values (10);
434insert into konta (konto) values (14);
435update konta set konto = 20 where id = 2;
436delete from konta where id = 2;
437
438drop trigger x_2ti on konta;
439drop trigger x_2tu on konta;
440drop trigger x_2td on konta;
441drop function x_2();
442drop table konta;
443
444Napisać trigger, który zapewni unikalność wpisów w kolumnie konto w tabeli konta
445
446create table konta (id serial, konto int4 default 0);
447
448create function uniq_konto() returns trigger as '
449begin
450 IF NEW.konto NOT IN (SELECT konto FROM konta) THEN
451 return NEW;
452 ELSE
453 raise notice ''Wartosc % znajduje sie juz w kolumnie konta'',NEW.konto;
454 return NULL;
455 END IF;
456end;
457' language 'plpgsql';
458
459create trigger check_uniq before insert on konta for each row execute procedure uniq_konto();
460
461insert into konta (konto) values (11); -- INSERT 0 1
462insert into konta (konto) values (16); -- INSERT 0 1
463insert into konta (konto) values (11); -- INSERT 0 0
464insert into konta (konto) values (17); -- INSERT 0 1
465insert into konta (konto) values (17); -- INSERT 0 0
466
467drop trigger check_uniq on konta;
468drop function uniq_konto();
469drop table konta;
470
471Lab09:
472
473Utworzyć tabelę categories tak, aby kolumna cid była kluczem głównym, zaś kolumna name miała unikalne i niepuste wartości.
474
475DROP TABLE "categories" cascade;
476CREATE TABLE "categories" (
477 "cid" int4 PRIMARY KEY,
478 "name" character varying(30) UNIQUE NOT NULL,
479 "cserid" int4
480);
481COPY "categories" FROM stdin;
4821 undefined 1
48360 Amusement 1
48461 Education 1
48562 Others 1
48663 Commercial 1
48764 KT 1
488\.
489
490Utworzyć tabelę subcategories tak, aby sid było kluczem głównym, kolumna cid była kluczem obcym związanym z categories.cid. Kolumna cid w tabeli subcategories ma być automatycznie aktualizaowana w przypadku zmiany cid w tabeli categories. Należy także zapewnić, by dana nazwa podkategorii nie występowała dwa razy w tej samej kategorii (w różnych kategoriach może wystąpić taka sama nazwa podkategorii). Skasowanie kategorii powinno spowodować automatyczne skasowanie podkategorii.
491
492DROP TABLE "subcategories";
493CREATE TABLE "subcategories" (
494 "sid" int4,-- NOT NULL, --PRIMARY KEY,
495 "name" character varying(30),
496 "cid" int4,-- NOT NULL,
497 "sserid" int4,
498 UNIQUE ("name", "cid"),
499 PRIMARY KEY("sid", "cid"), -- <--
500 FOREIGN KEY ("cid") REFERENCES categories("cid") ON UPDATE CASCADE ON DELETE CASCADE
501 --UNIQUE("sid", "cid")
502 );
503COPY "subcategories" FROM stdin;
5041 undefined 1 1
50557 BTI 64 1
50659 Biology 61 1
50760 Physics 61 1
50861 Technology 61 1
50962 Telecommunications 61 1
51056 Others 62 2
51171 Advertisement 60 1
51254 Telecommunications 63 2
51372 budowa 64 1
51474 wykłady 64 1
51553 Mathematics 61 5
51658 Geography 61 3
51752 Cartoons 60 5
51851 Movies 60 15
519\.
520
521Utworzyć tabelę movies_list, w której mid jest kluczem głównym, a pola cid i sid zawierają poprawne wartości identyfikatorów kategorii i podkategorii, przy czym należy zapewnić, że wskazana podkategoria należy do wskazanej kategorii. Domyślną wartością dla tych pól ma być 1. W przypadku usunięcia kategorii bądź podkategorii w polach cid i sid mają być automatycznie wstawiane wartości domyślne. W przypadku aktualizacji wartości identyfikatorów kategorii lub podkategorii w tabelach categories oraz subcategories wartości pól cid oraz sid w movies_list mają być automatycznie aktualizowane.
522
523DROP TABLE "movies_list";
524CREATE TABLE "movies_list" (
525 "mid" int4 PRIMARY KEY,
526 "sysflags" int4,
527 "lenbin" int4,
528 "stream" int4,
529 "compress" character varying(16),
530 "dimensions" character varying(16),
531 "fps" int4,
532 "cid" int4 DEFAULT 1,
533 "sid" int4 DEFAULT 1,
534 "author" character varying(256),
535 "lenmsec" int4,
536 "filename" character varying(256),
537 "tokenrate" int4,
538 "bucketsize" int4,
539 "peakbitrate" int4,
540 "license" character varying(256),
541 "expiry" date,
542 "mserid" int4,
543 --CHECK ... -- Jak sprawdzić czy dana podkategoria nalezy do danej kategorii?
544 --FOREIGN KEY ("cid") REFERENCES categories("cid") ON DELETE SET DEFAULT ON UPDATE CASCADE,
545 --FOREIGN KEY ("sid") REFERENCES subcategories("sid") ON DELETE SET DEFAULT ON UPDATE CASCADE
546 FOREIGN KEY ("sid", "cid") REFERENCES subcategories("sid", "cid") ON DELETE SET DEFAULT ON UPDATE CASCADE
547);
548COPY "movies_list" FROM stdin;
549
550Utworzyć tabelę osoby zawierającą nazwisko, imię i nr. pesel. Nazwisko i imię nie mogą być puste. PESEL musi być poprawny. Algorytm sprawdzania poprawności nr. PESEL znajduje się na stronie: http://wipos.p.lodz.pl/zylla/ut/pesel.html
551
552DROP TABLE osoby;
553CREATE TABLE osoby (
554 nazwisko varchar(11),
555 imie varchar(11),
556 pesel varchar(11),
557 CHECK (
558 ( ( CAST(SUBSTRING(pesel,1,1) AS INTEGER)*9)
559 +(CAST(SUBSTRING(pesel,2,1) AS INTEGER)*7)
560 +(CAST(SUBSTRING(pesel,3,1) AS INTEGER)*3)
561 +(CAST(SUBSTRING(pesel,4,1) AS INTEGER)*1)
562 +(CAST(SUBSTRING(pesel,5,1) AS INTEGER)*9)
563 +(CAST(SUBSTRING(pesel,6,1) AS INTEGER)*7)
564 +(CAST(SUBSTRING(pesel,7,1) AS INTEGER)*3)
565 +(CAST(SUBSTRING(pesel,8,1) AS INTEGER)*1)
566 +(CAST(SUBSTRING(pesel,9,1) AS INTEGER)*9)
567 +(CAST(SUBSTRING(pesel,10,1) AS INTEGER)*7)
568 ) % 10 = CAST(RIGHT(pesel, 1) AS INTEGER) ));
569
570--TEST:
571INSERT INTO osoby (nazwisko, imie, pesel) VALUES ('Kowalski', 'Jan', '49040501580'); --PESEL poprawny
572INSERT INTO osoby (nazwisko, imie, pesel) VALUES ('Kowalski', 'Jan', '46040501580'); --PESEL niepoprawny
573INSERT INTO osoby (nazwisko, imie, pesel) VALUES ('Kowalski', 'Jan', '49040501581'); --PESEL niepoprawny