Na današnjih vajah bomo spoznali jezik SQL (Standard Query Language) za delo s podatkovnimi bazami. Delali bomo z različico jezika Postgresql.

Naloga 1: Priklop na podatkovno zbirko

Podatkovna zbirka deluje na strežniku baza.fmf.uni-lj.si. Uporabnik student ima dostop do več zbirk. Na bazo se priklopimo kar z brskalnikom preko spletnega vmesnika phppgadmin.

  1. Z brskalnikom obišči stran http://baza.fmf.uni-lj.si/phppgadmin/.

  2. Klikni na PostgresSQL v levem stolpcu in se prijavi z uporabniškim imenom student in geslom telebajsek.

  3. Na tokratnih vajah bomo vsi delali s podatkovnima zbirkama banka in opb. Izberi opb. Če klikneš na zavihek Schemas, schema public, dobiš seznam tabel, ki so v zbirki. Tabele lahko pregleduješ in urejaš. (V bazi opb je ena sama tabela, student.)

Pozor: uporabik student lahko podatke in podatkovne zbirke tudi briše. Prosim, da se ne igrate in ne brišete tabel in podatkov.

Opomba: V SQL je lepa navada, da se ukazi pišejo z velikimi tiskanimi črkami, imena tabel, stolpcev, funkcij ipd. pa z malimi. Vendar pa to ni nujno; velike in male črke so enakovredne.

Naloga 2: Urejanje tabele s pomočjo phppgadmin

Ta naloga je ultrasupernajobveznejša. Na podlagi podatkov, ki jih vnesete tukaj, boste za preostanek predmeta dobili dostop do strežnika in tabel.

Poveži se na podatkovno bazo opb in si oglej tabelo student. Na dnu strani izberi Insert ter v tabelo dodaj svoje prave podatke. V stolpcu smer vpiši MAT, če si orto matematik, PED, če si pedagog, FM, če si finančnik, ali FIZ, če si fizik. Nato poglej vse obstoječe vnose v tabeli (Browse na strani z opisom tabele) in se prepričaj, da je tudi tvoj med njimi.

Podatkovna zbirka banka

Delali bomo s podatkovno bazo banka, ki vsebuje tabele kraj, oseba, racun in transakcija. Ukazi CREATE TABLE, s katerimi so bile te tabele narejene, so zbrani v datoteki banka.postgresql, ki smo jo (ali pa jo še bomo) obravnavali na predavanjih.

Naloga 3: Ukaz INSERT

  1. Vsak naj se vpiše v tabelo oseba. Po potrebi je treba prej vpisati še kraj v tabelo kraj. Vpiši se z ukazom INSERT. (Najprej klikni na zbirko banka in izberi zavihek SQL. Odpre se polje, v katerega lahko pišeš ukaze SQL in jih izvajaš.)

    Pozor: stolpec emso ima tip text. Številke se bodo sicer pretvorile v besedilo, a ob tem izgubile morebitne začetne ničle. Če se EMŠO začne z ničlo, ga bo torej treba nujno navesti med 'enojnimi navednicami'.

  2. V tabeli racun ustvari še svoj bančni račun.

  3. V tabeli transakcija naredi nekaj transakcij na svojem računu, na primer vsaj dva dviga in dva pologa.

Opomba: v pomoč naj ti bo tudi pregled ukazov na MaFiRaWiki strani za SQL.

Naloga 4: Ukaz UPDATE

V tabeli oseba poskusi spremeniti EMŠO v zapisu, ki si ga naredil v nalogi 3. Uporabi ukaz UPDATE. Ali ti baza to dovoli? Poskusi spremeniti še kakšno drugo polje v tabeli.

Naloga 5: Ukaz DELETE

Iz tabele transakcija zbriši eno transakcijo (vseeno katero). Glej, da ne pobrišeš celotne tabele!

Naloga 6: Ukaz SELECT

Naredi naslednje poizvede:

  1. Za vse osebe, ki živijo v kraju s poštno številko 2000, izpiši celotne pripadajoče vrstice.

  2. Vse osebe v tabeli oseba, vendar izpiši samo ime, priimek in poštno številko.

  3. Vse transakcije, pri katerih je znesek manjši od -1000.

  4. Vse transakcije, pri katerih je znesek manjši od -1000. Zneski transakcij naj bodo pri izpisu zaokroženi na najbližjo stotico proti ničli. (Uporabi celoštevilsko deljenje.)

  5. Vse osebe v tabeli oseba, vendar izpiši samo ime, priimek in ime kraja, v katerem živijo (pomagati si bo treba s tabelo kraj).

  6. Vse osebe, ki živijo v kraju Maribor, pri čemer poštno številko dobiš iz tabele kraj.

Nato naredi še bolj komplicirane poizvedbe:

  1. Z uporabo funkcije count preštej, koliko računov je odprtih na banki.

  2. Preštej, koliko je oseb, ki živijo v kraju s poštno številko 1000.

  3. Z uporabo funkcije sum izračunaj, koliko denarja je trenutno v banki (seštej stolpec znesek v tabeli transakcija).

  4. Z uporabo funkcije sum izračunaj, koliko denarja imaš trenutno na svojem računu.

  5. Izpiši imena in priimke vseh tistih ljudi, ki so naredili vsaj eno transakcijo z absolutno vrednostjo 1000 ali več. Pri izpisu ne podvajaj oseb, ki so opravile več takih transakcij; uporabi določilo DISTINCT.

  6. Nadgradnja prejšnje naloge: za vsako osebo, ki je opravila vsaj eno transakcijo z absolutno vrednostjo 1000 ali več, izpiši niz Ime Priimek iz kraja Kraj. Uporabi operator || za stikanje nizov.

  7. Dodatna naloga: Z uporabo funkcij sum in pow ter ustrezne funkcije, ki izračuna razliko med dvema časoma (poišči jo v dokumentaciji!), izračunaj, koliko denarja imaš trenutno na svojem računu, pri predpostavki, da se obrestuje z 1% na 30 dni. (Obračunavanje pa je zvezno - po enem dnevu in pol recimo profitiraš za faktor 1.011.5/30).

    Rešitev: (počrni z miško)
    SELECT sum(znesek*pow(1.01,extract('epoch' from (now()-cas))/3600/24/30)) FROM transakcija WHERE racun='100038';

Zadnja sprememba: četrtek, 18 februar 2021, 14:26 PM