20.3.2017

TTZC0800 Tietokannat: Harjoitukset (näkymät ja alikyselyt)


Hae tehtävien 1 ja 2 tiedot SQL-kyselyin mysql.labranet.jamk.fi-palvelimelle luomastasi Ari Hovin kirjan harjoitustietokannasta haluamallasi ohjelmalla. Tallenna suorittamasi SQL-kyselyt tekstimuodossa.

Tehtävä 1


NÄKYMÄT

1. Perusta näkymä hesa, joka hakee henkilo-taulusta helsinkiläiset. Ota
   mukaan sarakkeet tässä järjestyksessä: snimi, enimi, htun, kunta ja pvm.
   Kokeile uutta näkymääsi.

2. Perusta näkymä v_prtunnit, jonka avulla saa kustakin projektista
   sarakkeet projektin tunnus, nimi ja tehdyt tunnit yhteensä.

3. Perusta näkymä projektit, josta saa suoraan projektin nimen, sijainnin,
   projektiin kuuluvista henkilöistä nimi, palkka ja tunnit projektia kohti
   (ei siis summattuja tunteja). Kokeile sitten, miten helposti saat näkymästä
   projektien nimet, niiden henkilöt (nimellä) ja kunkin henkilön tunnit 
   per projekti. Lajittele projekteittain. Kokeile myös ottaa ulos henkilöiden
   projektiluettelo, josta näkyy henkilöä kohti, mihin projektiin hän kuuluu ja
   paljonko on tunteja.

4. Tee näkymä Hlotilanne, josta saa henkilön projektitilanteen: henkilön
   nimi, kunta, henkilön tehdyt tunnit yhteensä ja suunnitellut tunnit
   yhteensä sekä edellisten erotus. Kokeile tekemääsi näkymää.

5. Hae henkilön nimi, tunnit, suunnitellut tunnit ja näiden erotus,
   erotuksen mukaan laskevassa järjestyksessä. Käytä edellistä näkymää.

6. (*) EI TARVITSE TEHDÄ. Hae henkilön nimi, kunta, palkka, kunnan palkkasumma 
   sekä palkan prosentuaalinen osuus henkilön kunnan palkkapotista (otsikko aliasnimi
   osuus).

Tehtävä 2

ALIKYSELYT

1. Hae ne projektit (nimellä), joilla on sama prioriteetti kuin P5:llä.
   Käytä alikyselyä.

2. Hae maksimipalkkaiset henkilöt: nimi ja palkka.

3. Hae henkilön 2134 projektien nimet.

4. Anna niiden henkilöiden nimet, joiden projekteista ainakin yksi on
   prioriteetiltaan 3 tai 1.

5. Hae henkilön 2245 projektien nimet käyttäen EXISTS-alikyselyä.

Tehtävä 3


A) Luo kuvitteellista keskustelufoorumi-ohjemiston viestien tallentamista varten tietokannan taulu, jossa perusavaimena on juokseva numero (kokonaisluku). Muita kenttiä tulee olla ainakin viestin otsikkoa, kirjoittajaa ja sisältöä varten. Myös viestin lisäysajankohta tulee tallentaa. Lisäksi tulee olla INTEGER-tyyppinen "parent"-kenttä, joka kertoo numerollaan, minkä viestin vastaus se mahdollisesti on. "Parent"-kentän arvo asetetaan NULL, jos viesti on viestiketjun avaava viesti. "Parent"-kenttä tulee olla viiteavaimena taulun perusavaimelle.

B) Lisää tauluun sisältöä siten, että viestejä on vähintään kolmella tasolla kuten oheisessa esimerkissä. On siis keskustelun avaavia viestejä, niiden vastauksia ja vastauksien vastauksia. Lisättyjen viesttien ei tietenkään tarvitse olla oheisen esimerkin mukaisia

C) Tee SQL-kysely, jolla haetaan kaikki ensimmäisen tason viestien vastaukset.